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.
(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.
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;
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 the preceding example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are actual parameters.
Department : 30
Employee No: 7900
Name : JAMES
Job : CLERK
Hire Date : 03-DEC-81
Salary : 950