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.
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