PL/SQL by Example / Procedures and Functions

Named blocks include procedures and functions. These can be called repeatedly. The declarative section within both types of methods is indicated by the keyword IS, which is mandatory. Further division into the executable part and the exception handling part is identical to anonymous blocks.

Procedure

DECLARE
  /* */ -- empty comment just for visual separation of internal methods
  PROCEDURE mp_PrintCurrentYear
  IS
    l_Year CHAR(4):=TO_CHAR(SYSDATE, 'YYYY'); -- local variable
  BEGIN
    dbms_output.put_line(l_Year); -- 2016
  END;
  /* */
BEGIN
  -- calling a parameterless procedure
  mp_PrintCurrentYear;
  -- reusing the procedure
  mp_PrintCurrentYear;
END;

Function

The main difference between a procedure and a function is the return value. A procedure only executes the given logic (it can be compared to void methods in other languages), while a function additionally returns a value of a predefined type.

DECLARE
  l_CurrentYear CHAR(4);
  /* */
  FUNCTION mf_ReturnCurrentYear
    RETURN CHAR -- return value
  IS
  BEGIN
    RETURN TO_CHAR(SYSDATE, 'YYYY');
  END;
  /* */
BEGIN
  -- assigning the result of the parameterless function to a variable
  l_CurrentYear:=mf_ReturnCurrentYear;
  dbms_output.put_line(l_CurrentYear); -- 2016
END;

Parameters

Input

Both procedures and functions can be generalized by adding input parameters. This allows them to execute the same code, but with different data each time.

DECLARE
  l_Result NUMBER;
  /* */
  FUNCTION mf_Multiply(
      p_A NUMBER -- input parameters
    , p_B NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (p_A*p_B);
  END;
  /* */
BEGIN
  -- passing parameters to the function call
  -- and storing the result in a variable
  l_Result:=mf_Multiply(
      p_A => 10
    , p_B => 2
  );
  dbms_output.put_line(l_Result); -- 20
END;

When calling methods, it is not necessary to explicitly name the parameters. You can rely on the order in the definition, but if this changes, the program will most likely stop working. It is also possible to define a default value for an input parameter using the assignment operator.

DECLARE
  l_Result NUMBER;
  /* */
  FUNCTION mf_Power(
      p_Base NUMBER
    , p_Exponent NUMBER:=2 -- default value
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (POWER(p_Base,p_Exponent)); -- Oracle power function
  END;
  /* */
BEGIN
  -- sequential parameter passing
  l_Result:=mf_Power(8, 3);
  dbms_output.put_line(l_Result); -- 512
  -- return value of a function as an input parameter of another function
  -- and omitting the second parameter (using the default value)
  dbms_output.put_line(mf_Power(8)); -- 64
END;

Output

The procedural language PL/SQL allows defining so-called output parameters, which largely blur the difference between a procedure and a function. They are used wherever a single return value is not sufficient.

DECLARE
  l_Day CHAR(2);
  l_Month CHAR(2);
  l_Year CHAR(4);
  /* */
  PROCEDURE mp_SplitDate(
      p_Date DATE
    , o_Day OUT CHAR
    , o_Month OUT CHAR
    , o_Year OUT CHAR
  )
  IS
  BEGIN
    -- assignment to OUT parameters is allowed
    o_Day:=TO_CHAR(p_Date, 'DD');
    o_Month:=TO_CHAR(p_Date, 'MM');
    o_Year:=TO_CHAR(p_Date, 'YYYY');
  END;
  /* */
BEGIN
  mp_SplitDate(
      p_Date => SYSDATE -- 5.4.2016
    , o_Day => l_Day
    , o_Month => l_Month
    , o_Year => l_Year
  );
  dbms_output.put_line(l_Day||'/'||l_Year||'/'||l_Month); -- 05/2016/04
END;

Purely for clarity, I recommend using a function if we require returning a single value, and for two or more values, use a procedure with OUT parameters.

DECLARE
  l_Day CHAR(2);
  l_Month CHAR(2);
  l_Year CHAR(4);
  /* */
  FUNCTION mf_SplitDate(
      p_Date DATE
    , o_Day OUT CHAR
    , o_Month OUT CHAR
  ) RETURN CHAR
  IS
  BEGIN
    o_Day:=TO_CHAR(p_Date, 'DD');
    o_Month:=TO_CHAR(p_Date, 'MM');
    RETURN TO_CHAR(p_Date, 'YYYY'); -- year as return value
  END;
  /* */
BEGIN
  -- possible, but unclear and illogical
  l_Year:=mf_SplitDate(
      p_Date => SYSDATE -- 5.4.2016
    , o_Day => l_Day
    , o_Month => l_Month
  );
  dbms_output.put_line(l_Day||'/'||l_Year||'/'||l_Month); -- 05/2016/04
END;

Input/Output

By combining the previous two types, we get the IN OUT parameter. The variable passed to such a parameter internally copies its value into the method, and after processing is complete, copies the resulting value back out to the variable. Forcing the passing of only a memory reference (thus preventing the “copying” of the variable’s value) is facilitated by the NOCOPY clause before the data type definition.

DECLARE
  l_Date DATE:=TO_DATE('01.01.2016', 'DD.MM.YYYY');
  /* */
  PROCEDURE mp_AddMonth(
      b_Date IN OUT DATE -- binary parameter
  )
  IS
  BEGIN
    b_Date:=ADD_MONTHS(b_Date, 1);
  END;
  /* */
BEGIN
  mp_AddMonth(l_Date);
  dbms_output.put_line(TO_CHAR(l_Date, 'DD.MM.YYYY')); -- 01.02.2016
END;

Overloading

Similar to other languages, methods in PL/SQL can also be overloaded, i.e., defining several with the same name differing only in the number or data type of parameters.

DECLARE
  /* */
  FUNCTION mf_ReturnPerimeter(
      p_A NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN (p_A*4);
  END;
  /* */
  FUNCTION mf_ReturnPerimeter(
      p_A NUMBER
    , p_B NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN ((p_A+p_B)*2);
  END;
  /* */
BEGIN
  -- square
  dbms_output.put_line(mf_ReturnPerimeter(10)); -- 40
  -- rectangle
  dbms_output.put_line(mf_ReturnPerimeter(10, 5)); -- 30
END;

Stored procedures

For larger applications, I recommend concentrating most of the business logic in packages (more in one of the next parts). However, stored procedures and functions can still be encountered in many, predominantly older, projects, so it is advisable to mention them at least briefly.

CREATE OR REPLACE FUNCTION sf_ReturnCurrentYear
  RETURN CHAR
AS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'YYYY');
END;
/

SELECT sf_ReturnCurrentYear
  FROM dual
;
-- 2016