An important capability of stored procedures and functions is to receive data from the calling program and return data. This is achieved by using parameters.

Parameters are declared after the names of stored procedures or functions, enclosed in parentheses. Parameters defined in stored procedures or functions are called formal parameters. When a stored procedure or function is called, the calling program provides actual values for the called function or stored procedure. The calling program also provides the called function or stored procedure with the variables used to receive the results. The values and variables provided by a program when the program calls a stored procedure or function are called actual parameters.

The following code provides the syntax of a parameter declaration:
(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])

name specifies the identifier assigned to the formal parameter. If an IN clause is specified, the IN parameter receives input data that is intended to be used by the stored procedure or function. You can use default values to initialize the input parameters. If an OUT clause is specified, the OUT parameter returns the results of the stored procedure or function to the calling program. If an IN OUT clause is specified, the IN OUT parameter can be used as both input and output parameters. If no IN, OUT, or IN OUT clause is specified, the parameter is defined as an input parameter by default. The use of a parameter is determined by IN, OUT, and IN OUT. data_type specifies the data type of the parameter. value specifies the default value assigned to an IN parameter if the actual parameter is not specified during a call.

The following example shows a stored procedure with parameters:
    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
    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));
In this example, p_deptno is an IN formal parameter. p_empno and p_ename are IN OUT formal parameters. p_job, p_hiredate, and p_sal are OUT formal parameters.
Note In the preceding example, the maximum length of the VARCHAR2 type parameter and the precision and scale of the NUMBER type parameter are not specified. In the parameter declarations, you cannot specify the length, precision, value range, or other limits. The limits are automatically inherited from the actual parameters that are used when you call a stored procedure or function.
Other programs can call the emp_query stored procedure and pass actual parameters to it. The following example describes another SPL program that calls the emp_query stored procedure.
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
    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);

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

The output of the preceding example is provided as follows:
Department : 30
Employee No: 7900
Name       : JAMES
Job        : CLERK
Hire Date  : 03-DEC-81
Salary     : 950