An important aspect of using procedures and functions is the capability to pass data from calling programs to the procedures or functions and to receive data back from the procedures or functions. This is completed by using parameters. This topic describes the basic information about parameters.

Parameters are declared in procedure or function definitions and enclosed within parentheses () after a procedure or function name. Parameters declared in procedure or function definitions are formal parameters. When a procedure or a function is called, the calling program provides the actual data that is to be used in the processing of the called program and the variables that are used 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 expression shows the general format of a formal parameter declaration:

(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])
  • name is an identifier that is assigned to the formal parameter. IN defines the parameter for receiving input data into the procedure or function.
  • An IN parameter can also be initialized to a default value. OUT defines the parameter for returning data from the procedure or function. IN OUT allows the parameter to be used for data input and output. If all of IN, OUT, and IN OUT are not specified, the parameter acts as an IN parameter by default. IN, OUT, or IN OUT is called parameter mode.
  • data_type defines the data type of the parameter.
  • value is a default value that is assigned to an IN parameter in the called program when an actual parameter is not specified in the call.

Examples

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 preceding example, no maximum length is specified for the VARCHAR2 parameters and no precision and scale are specified for the NUMBER parameters. You cannot specify a length limit, 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 shows 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