Stored Procedure and Cursor ParametersStored Procedure and Cursor ParametersStored procedures use parameters to pass information back and forth between the stored procedure and the calling program. There are two different kinds of parameters: actual and formal. Formal parameters are the names that are declared in the header of a procedure or function. Actual parameters are the values placed in the parameter list when the procedure or function is called.
The syntax for a parameter is:
Parameter_name [mode] datatype [ := value ||DEFAULT value]
The simplest parameter can be coded as:
(p_squared NUMBER)
p_squared is the parameter_name, and NUMBER is the datatype.
Mode
The optional mode of a parameter specifies whether the parameter
can be read from or written to. Mode can have one of three values:
IN, OUT or IN OUT. The mode indicates how the parameter can be
used inside the procedure. If the mode isn't supplied, it defaults
to IN. You can use an IN parameter only in an expression or on
the right side of an assignment. You use an OUT parameter only
on the left side of an assignment. You can use an IN OUT parameter
anywhere.
Datatype
The datatype can be any PL/SQL or programmer-defined datatype,
but cannot be constrained by a size (NUMBER is valid, NUMBER(10)
is not valid). The actual size of the parameter is determined
from the calling program or via a %TYPE constraint. The actual
parameter and formal parameter must belong to compatible datatypes.
For example, PL/SQL cannot convert an actual parameter with a
datatype of DATE to a formal parameter with a datatype of LONG.
Positional or Named Notation
PL/SQL lets you use either of two styles for passing arguments
in parameter lists: positional or named notation. Positional is
the default. With positional, you don't need to give the name
of the parameter when providing a value; its position relative
to the other parameters is sufficient. If a parameter list shows
a VARCHR2 as the first parameter and a DATE for the second parameter,
when you call the procedure, you must provide a VARCHAR2 value
and a DATE value in that order.
If you want to change the order of the parameters or skip a parameter entirely, you use named notation. Because you provide the name of the formal parameter, PL/SQL no longer needs to rely on the order of the parameters to make the association from actual to formal. You explicitly associate the name of the parameter with the value of the parameter, right in the call to the program, using the combination symbol =>. So, if you use named notation, you can supply arguments in any order and you can skip over IN arguments that have default values.
The general syntax for named notation is:
Formal_parameter_name => argument_value
Default Values
You can provide a default value for both IN and IN OUT parameters.
If an IN parameter has a default value, you do not need to include
that parameter in the call to the program. You must include an
actual parameter for any IN OUT parameters, even if they have
default values. When using default values, make them the last
parameters in the argument list if possible. That way, either
positional or named notation can be used. There are two ways to
specify a default value: either with the keyword DEFAULT or with
the assignment operator :=.
In the procedure example below, both ways to specify a default value are used. This procedure is then called showing how positional and named notation work.
CREATE OR REPLACE PROCEDURE add_dept
(p_name IN dept.dname%TYPE := "unknown'
p_loc IN dept.loc%TYPE DEFULT 'unknown')
IS
BEGIN
INSERT INTO dept
VALUES (dept_deptno.nextval, p_name, p_loc);
END add_dept;
/
The following anonymous block shows four different ways of calling the ADD_DEPT procedure. These are examples use default values, positional and named notation:
begin
add_dept; -- example of default values
add _dept ('TRAINING', 'NEW JERSEY') ; /* example of positional
notation */
add dept (p_loc => 'TEXAS', p_name => 'EDUCATION') ; /*
example of named notation */
add dept (p_loc => 'UTAH') ; /* example of named notation and
default values */
end ;
/
The result can be viewed with this select statement:
SELECT * FROM dept;
DEPTNO DNAME LOC
1 unknown unknown
2 TRAINING NEW JERSEY
3 EDUCATION TEXAS
4 unknown UTAH
Cursor Parameters
Cursor parameters should be viewed in the same manner as procedures
that pass parameters. Define them when it is necessary to make
the cursor dynamic and to eliminate hardcoding. With a parameterized
cursor, the OPEN statement is used to pass actual values into
the cursor. Parameters can be passed using positional or named
notation. Just like procedures, cursor parameters can also have
default values assigned when declared. The default values take
effect, if you pass no values to the cursor in the OPEN statement.
Cursor parameters are visible only to that cursor. You cannot
reference a cursor parameter outside the context of the cursor.
In Oracle, you can use as many parameters as you need. The syntax
for declaring a cursor is :
CURSOR cursor_name ( parameter_1 datatype, parameter_2 datatype
… )
IS SELECT_statement
In this syntax, cursor_name is the name you assign to the cursor. Parameter_1 and parameter_2 are the parameters that are passed into the cursor. The datatypes correspond to the parameters and the SELECT statement is the statement that defines the cursor contents.