Table of Contents Previous Next


3 Stored Procedure Language : 3.2 SPL Programs : 3.2.5 Procedure and Function Parameters

Parameters are declared in the procedure or function definition, enclosed within parenthesis following the procedure or function name. Parameters declared in the procedure or function definition are known as formal parameters. When the procedure or function is invoked, the calling program supplies the actual data that is to be used in the called program’s processing as well as the variables that are to receive the results of the called program’s processing. The data and variables supplied by the calling program when the procedure or function is called are referred to as the actual parameters.
(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])
name is an identifier assigned to the formal parameter. If specified, IN defines the parameter for receiving input data into the procedure or function. An IN parameter can also be initialized to a default value. If specified, OUT defines the parameter for returning data from the procedure or function. If specified, IN OUT allows the parameter to be used for both input and output. If all of IN, OUT, and IN OUT are omitted, then the parameter acts as if it were defined as IN by default. Whether a parameter is IN, OUT, or IN OUT is referred to as the parameter’s mode. data_type defines the data type of the parameter. value is a default value assigned to an IN parameter in the called program if an actual parameter is not specified in the call.
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.
Note: In the previous example, no maximum length was specified on the VARCHAR2 parameters and no precision and scale were specified on the NUMBER parameters. It is illegal to specify a length, precision, scale or other constraints on parameter declarations. These constraints are automatically inherited from the actual parameters that are used when the procedure or function is called.
The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another SPL program that calls emp_query.
In this example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are the actual parameters.
You can use either positional or named parameter notation when passing parameters to a function or procedure. If you specify parameters using positional notation, you must list the parameters in the order that they are declared; if you specify parameters with named notation, the order of the parameters is not significant.
To specify parameters using named notation, list the name of each parameter followed by an arrow (=>) and the parameter value. Named notation is more verbose, but makes your code easier to read and maintain.
As previously discussed, a parameter has one of three possible modes - IN, OUT, or IN OUT. The following characteristics of a formal parameter are dependent upon its mode:
As shown by the table, an IN formal parameter is initialized to the actual parameter with which it is called unless it was explicitly initialized with a default value. The IN parameter may be referenced within the called program, however, the called program may not assign a new value to the IN parameter. After control returns to the calling program, the actual parameter always contains the same value as it was set to prior to the call.
The OUT formal parameter is initialized to the actual parameter with which it is called. The called program may reference and assign new values to the formal parameter. If the called program terminates without an exception, the actual parameter takes on the value last set in the formal parameter. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.
Like an IN parameter, an IN OUT formal parameter is initialized to the actual parameter with which it is called. Like an OUT parameter, an IN OUT formal parameter is modifiable by the called program and the last value in the formal parameter is passed to the calling program’s actual parameter if the called program terminates without an exception. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.
You can set a default value of a formal parameter by including the DEFAULT clause or using the assignment operator (:=) in the CREATE PROCEDURE or CREATE FUNCTION statement.
(name [ IN|OUT|IN OUT ] data_type [{DEFAULT | := } expr ])
name is an identifier assigned to the parameter.
IN|OUT|IN OUT specifies the parameter mode.
data_type is the data type assigned to the variable.
expr is the default value assigned to the parameter. If you do not include a DEFAULT clause, the caller must provide a value for the parameter.
The default value is evaluated every time the function or procedure is invoked. For example, assigning SYSDATE to a parameter of type DATE causes the parameter to have the time of the current invocation, not the time when the procedure or function was created.
If the parameter declaration includes a default value, you can omit the parameter from the actual parameter list when you call the procedure. Calls to the sample procedure (hire_emp) must include two arguments: the employee number (p_empno) and employee name (p_empno). The third parameter (p_hiredate) defaults to the value of SYSDATE:
Adds a new employee with a hiredate of February 15, 2010, regardless of the current value of SYSDATE.
You can write the same procedure by substituting the DEFAULT keyword for the assignment operator:
The PROCEDURE clause specified in the declaration section defines and names a subprocedure local to that block.
The term block refers to the SPL block structure consisting of an optional declaration section, a mandatory executable section, and an optional exception section. Blocks are the structures for standalone procedures and functions, anonymous blocks, subprograms, triggers, packages, and object type methods.
The phrase the identifier is local to the block means that the identifier (that is, a variable, cursor, type, or subprogram) is declared within the declaration section of that block and is therefore accessible by the SPL code within the executable section and optional exception section of that block.
Subprocedures can only be declared after all other variable, cursor, and type declarations included in the declaration section. (That is, subprograms must be the last set of declarations.)
PROCEDURE name [ (parameters) ]
{ IS | AS }
[ declarations ]
statements
END [ name ];
name is the identifier of the subprocedure.
parameters is a list of formal parameters.
declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements are SPL program statements (the BEGIN - END block may contain an EXCEPTION section).
The FUNCTION clause specified in the declaration section defines and names a subfunction local to that block.
The term block refers to the SPL block structure consisting of an optional declaration section, a mandatory executable section, and an optional exception section. Blocks are the structures for standalone procedures and functions, anonymous blocks, subprograms, triggers, packages, and object type methods.
The phrase the identifier is local to the block means that the identifier (that is, a variable, cursor, type, or subprogram) is declared within the declaration section of that block and is therefore accessible by the SPL code within the executable section and optional exception section of that block.
FUNCTION name [ (parameters) ]
RETURN data_type
{ IS | AS }
[ declarations ]
statements
END [ name ];
name is the identifier of the subfunction.
parameters is a list of formal parameters.
data_type is the data type of the value returned by the function’s RETURN statement.
declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements are SPL program statements (the BEGIN - END block may contain an EXCEPTION section).
A block is the basic SPL structure consisting of an optional declaration section, a mandatory executable section, and an optional exception section. Blocks implement standalone procedure and function programs, anonymous blocks, triggers, packages, and subprocedures and subfunctions.
An identifier (variable, cursor, type, or subprogram) local to a block means that it is declared within the declaration section of the given block. Such local identifiers are accessible from the executable section and optional exception section of the block.
The parent block contains the declaration of another block (the child block).
Descendent blocks are the set of blocks forming the child relationship starting from a given parent block.
Ancestor blocks are the set of blocks forming the parental relationship starting from a given child block.
The level is an ordinal number of a given block from the highest, ancestor block. For example, given a standalone procedure, the subprograms declared within the declaration section of this procedure are all at the same level, for example call it level 1. Additional subprograms within the declaration section of the subprograms declared in the standalone procedure are at the next level, which is level 2.
The sibling blocks are the set of blocks that have the same parent block (that is, they are all locally declared in the same block). Sibling blocks are also always at the same level relative to each other.
The two vertical lines on the left-hand side of the blocks indicate there are two pairs of sibling blocks. block_1a and block_1b is one pair, and block_2a and block_2b is the second pair.

The rules for invoking subprograms based upon block location is described starting with Section 3.2.6.4. The rules for accessing variables based upon block location is described in Section 3.2.6.7.
[[qualifier_1.][...]qualifier_n.]subprog [(arguments)]
If specified, qualifier_n is the subprogram in which subprog has been declared in its declaration section. The preceding list of qualifiers must reside in a continuous path up the hierarchy from qualifier_n to qualifier_1. qualifier_1 may be any ancestor subprogram in the path as well as any of the following:
An anonymous block label included prior to the DECLARE keyword if a declaration section exists, or prior to the BEGIN keyword if there is no declaration section.
Note: qualifier_1 may not be a schema name, otherwise an error is thrown upon invocation of the subprogram. This Advanced Server restriction is not compatible with Oracle databases, which allow use of the schema name as a qualifier.
arguments is the list of actual parameters to be passed to the subprocedure or subfunction.
Note: The Advanced Server search algorithm for subprogram invocation is not quite compatible with Oracle databases. For Oracle, the search looks for the first match of the first qualifier (that is qualifier_1). When such a match is found, all remaining qualifiers, the subprogram name, subprogram type, and arguments of the invocation must match the hierarchy content where the matching first qualifier is found, otherwise an error is thrown. For Advanced Server, a match is not found unless all qualifiers, the subprogram name, and the subprogram type of the invocation match the hierarchy content. If such an exact match is not initially found, Advanced Server continues the search progressing up the hierarchy.
The following example contains a single hierarchy of blocks contained within standalone procedure level_0. Within the executable section of procedure level_1a, the means of invoking the local procedure level_2a are shown, both with and without qualifiers.
Also note that access to the descendant of local procedure level_2a, which is procedure level_3a, is not permitted, with or without qualifiers. These calls are commented out in the example.
When the standalone procedure is invoked, the output is the following, which indicates that procedure level_2a is successfully invoked from the calls in the executable section of procedure level_1a.
If you were to attempt to run procedure level_0 with any of the calls to the descendent block uncommented, then an error occurs.
In this example, the executable section of procedure level_3a invokes procedure level_2a, which is its parent block. (Note that v_cnt is used to avoid an infinite loop.)
In a similar example, the executable section of procedure level_3a invokes procedure level_1a, which is further up the ancestor hierarchy. (Note that v_cnt is used to avoid an infinite loop.)
In this example, the executable section of procedure level_1b invokes procedure level_1a, which is its sibling block. Both are local to standalone procedure level_0.
Note that invocation of level_2a or equivalently, level_1a.level_2a from within procedure level_1b is commented out as this call would result in an error. Invoking a descendent subprogram (level_2a) of sibling block (level_1a) is not permitted.
In the following example, procedure level_1a, which is the sibling of procedure level_1b, which is an ancestor of procedure level_3b is successfully invoked.
This is accomplished by inserting a forward declaration in the SPL code prior to its invocation. The forward declaration is the specification of a subprocedure or subfunction name, formal parameters, and return type if it is a subfunction.
Subfunction test_max invokes subfunction add_one, which also invokes subfunction test_max, so a forward declaration is required for one of the subprograms, which is implemented for add_one at the beginning of the anonymous block declaration section.
Different parameter modes (IN, IN OUT, OUT) for the corresponding formal parameters
However, certain data types have alternative names referred to as aliases, which can be used for the table definition.
For example, there are fixed length character data types that can be specified as CHAR or CHARACTER. There are variable length character data types that can be specified as CHAR VARYING, CHARACTER VARYING, VARCHAR, or VARCHAR2. For integers, there are BINARY_INTEGER, PLS_INTEGER, and INTEGER data types. For numbers, there are NUMBER, NUMERIC, DEC, and DECIMAL data types.
Using the PSQL \d command to display the table definition, the Type column displays the data type internally assigned to each column based upon its data type in the table definition:
In the example, the base set of data types are bytea, integer, real, double precision, numeric, character, and character varying.
When attempting to declare overloaded subprograms, a pair of formal parameter data types that are aliases would not be sufficient to allow subprogram overloading. Thus, parameters with data types INTEGER and PLS_INTEGER cannot overload a pair of subprograms, but data types INTEGER and REAL, or INTEGER and FLOAT, or INTEGER and NUMBER can overload the subprograms.
Note: The overloading rules based upon formal parameter data types are not compatible with Oracle databases. Generally, the Advanced Server rules are more flexible, and certain combinations are allowed in Advanced Server that would result in an error when attempting to create the procedure or function in Oracle databases.
Note: If the subprogram signature contains formal parameters, these may be accessed in the same manner as local variables of the subprogram. In this section, all discussion related to variables of a subprogram also applies to formal parameters of the subprogram.
[qualifier.]variable
If specified, qualifier is the subprogram or labeled anonymous block in which variable has been declared in its declaration section (that is, it is a local variable).
Note: In Advanced Server, there is only one circumstance where two qualifiers are permitted. This scenario is for accessing public variables of packages where the reference can be specified in the following format:
schema_name.package_name.public_variable_name
Note: The Advanced Server process for accessing variables is not compatible with Oracle databases. For Oracle, any number of qualifiers can be specified and the search is based upon the first match of the first qualifier in a similar manner to the Oracle matching algorithm for invoking subprograms.
The following example is an object type whose object type method, display_emp, contains record type emp_typ and subprocedure emp_sal_query. Record variable r_emp declared locally to emp_sal_query is able to access the record type emp_typ declared in the parent block display_emp.

3 Stored Procedure Language : 3.2 SPL Programs : 3.2.5 Procedure and Function Parameters

Table of Contents Previous Next