Using Local PL/SQL Procedures and Functions in PL/SQL Blocks

Procedures and functions (subprograms) are named PL/SQL blocks that can be called with a set of parameters from inside of a PL/SQL block.

A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. A function is a subprogram that computes and returns a value. Functions and procedures are structured alike, except that functions return a value.

When passing parameters to functions and procedures, the parameters can be declared as IN or OUT or IN OUT parameters.

IN indicates that you must supply a value for the argument when calling the function or procedure. This is the default.
OUT indicates that the function or procedure will set the value of the argument.
IN OUT indicates that a value for the argument can be supplied by you and can be set by the function or procedure.

Example: Declaring a Local PL/SQL Procedure With IN OUT Parameters is an example of a declaration of a PL/SQL procedure in a PL/SQL block. Note that the v1 and v2 variables are declared as IN OUT parameters to a subprogram.

Declaring a Local PL/SQL Procedure With IN OUT Parameters

DECLARE -- declare variables and subprograms
  fname    VARCHAR2(20) := 'randall';
  lname    VARCHAR2(25) := 'dexter';

-- declare a local procedure which can only be used in this block
  PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS
    BEGIN
      v1 := UPPER(v1); -- change the string to uppercase
      v2 := UPPER(v2); -- change the string to uppercase
    END upper_name;

-- start of executable part of block
BEGIN
  DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values
  upper_name (fname, lname); -- call the procedure with parameters
  DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values
END;
/

Example: Declaring a Local PL/SQL Function With IN Parameters is an example of a declaration of a PL/SQL function in a PL/SQL block. Note that the value returned by the function is used directly in the DBMS_OUTPUT.PUT_LINE statement. Note that the v1 and v2 variables are declared as IN parameters to a subprogram. An IN parameter passes an initial value that is read inside of a subprogram. Any update to the value of the parameter inside of the subprogram is not accessible outside of the subprogram.

Declaring a Local PL/SQL Function With IN Parameters

DECLARE -- declare variables and subprograms
  fname    VARCHAR2(20) := 'randall';
  lname    VARCHAR2(25) := 'dexter';

-- declare local function which can only be used in this block
  FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2)
    RETURN VARCHAR2 AS
    v3     VARCHAR2(45);  -- this variable is local to the function
    BEGIN
    -- build a string that will be returned as the function value 
      v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2);
      RETURN v3;  -- return the value of v3
    END upper_name;

-- start of executable part of block 
BEGIN
-- call the function and display results
  DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname)); 
END;
/

In Example: Declaring a Complex Local Procedure in a PL/SQL Block, both a variable and a numeric literal are passed as a parameter to a more complex procedure.

Declaring a Complex Local Procedure in a PL/SQL Block

DECLARE  -- declare variables and subprograms
  empid NUMBER;

-- declare local procedure for this block
  PROCEDURE avg_min_max_sal (empid IN NUMBER) IS
    jobid     VARCHAR2(10);
    avg_sal   NUMBER;
    min_sal   NUMBER;
    max_sal   NUMBER;
  BEGIN
    -- determine the job ID for the employee
    SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
    -- calculate the average, minimum, and maximum salaries for that job ID
    SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal
      FROM employees WHERE job_id = jobid;
    -- display data
    DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid);
    DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid 
                            || ' is ' || TO_CHAR(avg_sal));
    DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid 
                            || ' is ' || TO_CHAR(min_sal));
    DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid 
                            || ' is ' || TO_CHAR(max_sal));
  END avg_min_max_sal;
-- end of local procedure

-- start executable part of block
BEGIN
-- call the procedure with several employee IDs
  empid := 125;
  avg_min_max_sal(empid);
  avg_min_max_sal(112);
END;
/

Subprograms can also be declared in packages. For an example of a subprogram declaration in a package, see Example: Creating a Package Body. You can create standalone subprograms that are stored in the database. These subprograms can be called from other subprograms, packages, and SQL statements. See Using Procedures, Functions, and Packages.