Oracle Pl/SQL Stored Procedures:Procedure Parameters
Stored procedures and functions can take parameters.
The following example shows a stored procedure that is similar to the anonymous block :
PROCEDURE get_emp_names (dept_num IN NUMBER) IS
emp_name VARCHAR2(10);
CURSOR c1 (depno NUMBER) IS
SELECT ename FROM emp
WHERE deptno = depno;
BEGIN
OPEN c1(dept_num);
LOOP
FETCH c1 INTO emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_name);
END LOOP;
CLOSE c1;
END;
In the stored procedure example, the department number is an input parameter, which is used when the parameterized cursor C1 is opened.
-The formal parameters of a procedure have three major parts:
1)name :
The name of the parameter, which must be a legal PL/SQL identifier.
2)mode :
The parameter mode, which indicates whether the parameter is an input-only parameter (IN), an output-only parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, IN is assumed.
3)datatype :
The parameter datatype is a standard PL/SQL datatype.
Parameter Modes
-You use parameter modes to define the behavior of formal parameters.
- The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram.
- However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
The following table summarizes the information about parameter modes.
| IN | OUT | IN OUT |
| the default | must be specified | must be specified |
| passes values to a subprogram | returns values to the caller | passes initial values to a subprogram; returns updated values to the caller |
| formal parameter acts like a constant | formal parameter acts like an uninitialized variable | formal parameter acts like an initialized variable |
| formal parameter cannot be assigned a value | formal parameter cannot be used in an expression; must be assigned a value | formal parameter should be assigned a value |
| actual parameter can be a constant, initialized variable, literal, or expression | actual parameter must be a variable | actual parameter must be a variable |
Parameter Datatypes
The datatype of a formal parameter consists of one of the following:
- an unconstrained type name, such as NUMBER or VARCHAR2.
- a type that is constrained using the %TYPE or %ROWTYPE attributes.
Attention: Numerically constrained types such as NUMBER(2) or VARCHAR2(20) are not allowed in a parameter list.
%TYPE and %ROWTYPE Attributes
- However, you can use the type attributes %TYPE and %ROWTYPE to constrain the parameter.
- For example, the GET_EMP_NAMES procedure specification could be written as
PROCEDURE get_emp_names(dept_num IN emp.deptno%TYPE)
- to have the DEPT_NUM parameter take the same datatype as the DEPTNO column in the EMP table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.
- Using %TYPE is recommended, since if the type of the column in the table changes, it is not necessary to change the application code.
- If the GET_EMP_NAMES procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:
dept_number number(2);
...
PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);
- You use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the GET_EMP_REC procedure, that returns all the columns of the EMP table in a PL/SQL record, for the given EMPNO:
PROCEDURE get_emp_rec (emp_number IN emp.empno%TYPE,
emp_ret OUT emp%ROWTYPE) IS
BEGIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO emp_ret
FROM emp
WHERE empno = emp_number;
END;
You could call this procedure from a PL/SQL block as follows:
DECLARE
emp_row emp%ROWTYPE; -- declare a record matching a
-- row in the EMP table
BEGIN
get_emp_rec(7499, emp_row); -- call for emp# 7499
DBMS_OUTPUT.PUT(emp_row.ename || ' ' || emp_row.empno);
DBMS_OUTPUT.PUT(' ' || emp_row.job || ' ' || emp_row.mgr);
DBMS_OUTPUT.PUT(' ' || emp_row.hiredate || ' ' || emp_row.sal);
DBMS_OUTPUT.PUT(' ' || emp_row.comm || ' ' || emp_row.deptno);
DBMS_OUTPUT.NEW_LINE;
END;
- Stored functions can also return values that are declared using %ROWTYPE. For example:
FUNCTION get_emp_rec (dept_num IN emp.deptno%TYPE)
RETURN emp%ROWTYPE IS ...
Tables and Records
- You can pass PL/SQL tables as parameters to stored procedures and functions.
- You can also pass tables of records as parameters.
Default Parameter Values
- Parameters can take default values. You use the DEFAULT keyword or the assignment operator to give a parameter a default value. For example, the specification for the GET_EMP_NAMES procedure could be written as
PROCEDURE get_emp_names (dept_num IN NUMBER DEFAULT 20) IS ...
or as
PROCEDURE get_emp_names (dept_num IN NUMBER := 20) IS ...
- When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure.
- When you do specify the parameter value on the call, it overrides the default value.
DECLARE Keyword
- Unlike in an anonymous PL/SQL block, you do not use the keyword DECLARE before the declarations of variables, cursors, and exceptions in a stored procedure.
- In fact, it is an error to use it.