An important aspect of using procedures and functions is the capability to pass data from the calling program to the procedure or function and to receive data back from the procedure or function. This is completed with parameters.

Parameters are declared in the procedure or function definition, enclosed within parentheses () following the procedure or function name. Parameters declared in the procedure or function definition are formal parameters. When the procedure or function is invoked, the calling program provides the actual data that is to be used in the processing of the called program as well as the variables that are to receive the results of the processing of the called program. The data and variables provided by the calling program when the procedure or function is called are actual parameters.

The following content shows the general format of a formal parameter declaration:

(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])

name is an identifier assigned to the formal parameter. If specified, IN defines the parameter for receiving input data into the procedure or function. An IN parameter can also be initialized to a default value. If specified, OUT defines the parameter for returning data from the procedure or function. If specified, IN OUT allows the parameter to be used for both input and output. If all of IN, OUT, and IN OUT are omitted, the parameter acts as if it were defined as IN by default. Whether a parameter is IN, OUT, or IN OUT, it is called parameter mode. data_type defines the data type of the parameter. value is a default value assigned to an IN parameter in the called program when an actual parameter is not specified in the call.

The following example describes a procedure that takes parameters:

CREATE OR REPLACE PROCEDURE emp_query (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
    p_job           OUT    VARCHAR2,
    p_hiredate      OUT    DATE,
    p_sal           OUT    NUMBER
)
IS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
           OR  ename = UPPER(p_ename));
END;

In this example, p_deptno is an IN formal parameter, p_empno and p_ename are IN OUT formal parameters, and p_job, p_hiredate, and p_sal are OUT formal parameters.

Note In the previous example, no maximum length was specified on the VARCHAR2 parameters and no precision and scale were specified on the NUMBER parameters. It is invalid to specify a length, precision, scale, or other constraints on parameter declarations. These constraints are automatically inherited from the actual parameters that are used when the procedure or function is called.

The emp_query procedure can be called by another program to pass the actual parameters to the program. The following example describes another SPL program that calls emp_query:

DECLARE
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
BEGIN
    v_deptno := 30;
    v_empno  := 7900;
    v_ename  := '';
    emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal);
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
END;

In this example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are actual parameters.

The following output is generated:

Department : 30
Employee No: 7900
Name       : JAMES
Job        : CLERK
Hire Date  : 03-DEC-81
Salary     : 950