Accessing Subprogram Variables v11
Variable declared in blocks such as subprograms or anonymous blocks can be accessed from the executable section or the exception section of other blocks depending upon their relative location.
Accessing a variable means being able to reference it within a SQL statement or an SPL statement as is done with any local variable.
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.
Access of variables not only includes those defined as a data type, but also includes others such as record types, collection types, and cursors.
The variable may be accessed by at most one qualifier, which is the name of the subprogram or labeled anonymous block in which the variable has been locally declared.
The syntax to reference a variable is shown by the following:
[<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
For more information about supported package syntax, see the Database Compatibility for Oracle Developers Built-In Package Guide.
The following summarizes how variables can be accessed:
- Variables can be accessed as long as the block in which the variable has been locally declared is within the ancestor hierarchical path starting from the block containing the reference to the variable. Such variables declared in ancestor blocks are referred to as global variables.
- If a reference to an unqualified variable is made, the first attempt is to locate a local variable of that name. If such a local variable does not exist, then the search for the variable is made in the parent of the current block, and so forth, proceeding up the ancestor hierarchy. If such a variable is not found, then an error occurs upon invocation of the subprogram.
- If a reference to a qualified variable is made, the same search process is performed as described in the previous bullet point, but searching for the first match of the subprogram or labeled anonymous block that contains the local variable. The search proceeds up the ancestor hierarchy until a match is found. If such a match is not found, then an error occurs upon invocation of the subprogram.
The following location of variables cannot be accessed relative to the block from where the reference to the variable is made:
- Variables declared in a descendent block cannot be accessed,
- Variables declared in a sibling block, a sibling block of an ancestor block, or any descendants within the sibling block cannot be accessed.
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 displays how variables in various blocks are accessed, with and without qualifiers. The lines that are commented out illustrate attempts to access variables that would result in an error.
CREATE OR REPLACE PROCEDURE level_0 IS v_level_0 VARCHAR2(20) := 'Value from level_0'; PROCEDURE level_1a IS v_level_1a VARCHAR2(20) := 'Value from level_1a'; PROCEDURE level_2a IS v_level_2a VARCHAR2(20) := 'Value from level_2a'; BEGIN DBMS_OUTPUT.PUT_LINE('...... BLOCK level_2a'); DBMS_OUTPUT.PUT_LINE('........ v_level_2a: ' || v_level_2a); DBMS_OUTPUT.PUT_LINE('........ v_level_1a: ' || v_level_1a); DBMS_OUTPUT.PUT_LINE('........ level_1a.v_level_1a: ' || level_1a.v_level_1a); DBMS_OUTPUT.PUT_LINE('........ v_level_0: ' || v_level_0); DBMS_OUTPUT.PUT_LINE('........ level_0.v_level_0: ' || level_0.v_level_0); DBMS_OUTPUT.PUT_LINE('...... END BLOCK level_2a'); END level_2a; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1a'); level_2a; -- DBMS_OUTPUT.PUT_LINE('.... v_level_2a: ' || v_level_2a); -- Error - Descendent block ----^ -- DBMS_OUTPUT.PUT_LINE('.... level_2a.v_level_2a: ' || level_2a.v_level_2a); -- Error - Descendent block ---------------^ DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1a'); END level_1a; PROCEDURE level_1b IS v_level_1b VARCHAR2(20) := 'Value from level_1b'; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1b'); DBMS_OUTPUT.PUT_LINE('.... v_level_1b: ' || v_level_1b); DBMS_OUTPUT.PUT_LINE('.... v_level_0 : ' || v_level_0); -- DBMS_OUTPUT.PUT_LINE('.... level_1a.v_level_1a: ' || level_1a.v_level_1a); -- Error - Sibling block -----------------^ -- DBMS_OUTPUT.PUT_LINE('.... level_2a.v_level_2a: ' || level_2a.v_level_2a); -- Error - Sibling block descendant ------^ DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1b'); END level_1b; BEGIN DBMS_OUTPUT.PUT_LINE('BLOCK level_0'); DBMS_OUTPUT.PUT_LINE('.. v_level_0: ' || v_level_0); level_1a; level_1b; DBMS_OUTPUT.PUT_LINE('END BLOCK level_0'); END level_0;
The following is the output showing the content of each variable when the procedure is invoked:
BEGIN level_0; END; BLOCK level_0 .. v_level_0: Value from level_0 .. BLOCK level_1a ...... BLOCK level_2a ........ v_level_2a: Value from level_2a ........ v_level_1a: Value from level_1a ........ level_1a.v_level_1a: Value from level_1a ........ v_level_0: Value from level_0 ........ level_0.v_level_0: Value from level_0 ...... END BLOCK level_2a .. END BLOCK level_1a .. BLOCK level_1b .... v_level_1b: Value from level_1b .... v_level_0 : Value from level_0 .. END BLOCK level_1b END BLOCK level_0
The following example shows similar access attempts when all variables in all blocks have the same name:
CREATE OR REPLACE PROCEDURE level_0 IS v_common VARCHAR2(20) := 'Value from level_0'; PROCEDURE level_1a IS v_common VARCHAR2(20) := 'Value from level_1a'; PROCEDURE level_2a IS v_common VARCHAR2(20) := 'Value from level_2a'; BEGIN DBMS_OUTPUT.PUT_LINE('...... BLOCK level_2a'); DBMS_OUTPUT.PUT_LINE('........ v_common: ' || v_common); DBMS_OUTPUT.PUT_LINE('........ level_2a.v_common: ' || level_2a.v_common); DBMS_OUTPUT.PUT_LINE('........ level_1a.v_common: ' || level_1a.v_common); DBMS_OUTPUT.PUT_LINE('........ level_0.v_common: ' || level_0.v_common); DBMS_OUTPUT.PUT_LINE('...... END BLOCK level_2a'); END level_2a; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1a'); DBMS_OUTPUT.PUT_LINE('.... v_common: ' || v_common); DBMS_OUTPUT.PUT_LINE('.... level_0.v_common: ' || level_0.v_common); level_2a; DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1a'); END level_1a; PROCEDURE level_1b IS v_common VARCHAR2(20) := 'Value from level_1b'; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1b'); DBMS_OUTPUT.PUT_LINE('.... v_common: ' || v_common); DBMS_OUTPUT.PUT_LINE('.... level_0.v_common : ' || level_0.v_common); DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1b'); END level_1b; BEGIN DBMS_OUTPUT.PUT_LINE('BLOCK level_0'); DBMS_OUTPUT.PUT_LINE('.. v_common: ' || v_common); level_1a; level_1b; DBMS_OUTPUT.PUT_LINE('END BLOCK level_0'); END level_0;
The following is the output showing the content of each variable when the procedure is invoked:
BEGIN level_0; END; BLOCK level_0 .. v_common: Value from level_0 .. BLOCK level_1a .... v_common: Value from level_1a .... level_0.v_common: Value from level_0 ...... BLOCK level_2a ........ v_common: Value from level_2a ........ level_2a.v_common: Value from level_2a ........ level_1a.v_common: Value from level_1a ........ level_0.v_common: Value from level_0 ...... END BLOCK level_2a .. END BLOCK level_1a .. BLOCK level_1b .... v_common: Value from level_1b .... level_0.v_common : Value from level_0 .. END BLOCK level_1b END BLOCK level_0
As previously discussed, the labels on anonymous blocks can also be used to qualify access to variables. The following example shows variable access within a set of nested anonymous blocks:
DECLARE v_common VARCHAR2(20) := 'Value from level_0'; BEGIN DBMS_OUTPUT.PUT_LINE('BLOCK level_0'); DBMS_OUTPUT.PUT_LINE('.. v_common: ' || v_common); <<level_1a>> DECLARE v_common VARCHAR2(20) := 'Value from level_1a'; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1a'); DBMS_OUTPUT.PUT_LINE('.... v_common: ' || v_common); <<level_2a>> DECLARE v_common VARCHAR2(20) := 'Value from level_2a'; BEGIN DBMS_OUTPUT.PUT_LINE('...... BLOCK level_2a'); DBMS_OUTPUT.PUT_LINE('........ v_common: ' || v_common); DBMS_OUTPUT.PUT_LINE('........ level_1a.v_common: ' || level_1a.v_common); DBMS_OUTPUT.PUT_LINE('...... END BLOCK level_2a'); END; DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1a'); END; <<level_1b>> DECLARE v_common VARCHAR2(20) := 'Value from level_1b'; BEGIN DBMS_OUTPUT.PUT_LINE('.. BLOCK level_1b'); DBMS_OUTPUT.PUT_LINE('.... v_common: ' || v_common); DBMS_OUTPUT.PUT_LINE('.... level_1b.v_common: ' || level_1b.v_common); DBMS_OUTPUT.PUT_LINE('.. END BLOCK level_1b'); END; DBMS_OUTPUT.PUT_LINE('END BLOCK level_0'); END;
The following is the output showing the content of each variable when the anonymous block is invoked:
BLOCK level_0 .. v_common: Value from level_0 .. BLOCK level_1a .... v_common: Value from level_1a ...... BLOCK level_2a ........ v_common: Value from level_2a ........ level_1a.v_common: Value from level_1a ...... END BLOCK level_2a .. END BLOCK level_1a .. BLOCK level_1b .... v_common: Value from level_1b .... level_1b.v_common: Value from level_1b .. END BLOCK level_1b END BLOCK level_0
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
.
CREATE OR REPLACE TYPE emp_pay_obj_typ AS OBJECT ( empno NUMBER(4), MEMBER PROCEDURE display_emp(SELF IN OUT emp_pay_obj_typ) ); CREATE OR REPLACE TYPE BODY emp_pay_obj_typ AS MEMBER PROCEDURE display_emp (SELF IN OUT emp_pay_obj_typ) IS TYPE emp_typ IS RECORD ( ename emp.ename%TYPE, job emp.job%TYPE, hiredate emp.hiredate%TYPE, sal emp.sal%TYPE, deptno emp.deptno%TYPE ); PROCEDURE emp_sal_query ( p_empno IN emp.empno%TYPE ) IS r_emp emp_typ; v_avgsal emp.sal%TYPE; BEGIN SELECT ename, job, hiredate, sal, deptno INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno FROM emp WHERE empno = p_empno; DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename); DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal); DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno); SELECT AVG(sal) INTO v_avgsal FROM emp WHERE deptno = r_emp.deptno; IF r_emp.sal > v_avgsal THEN DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the ' || 'department average of ' || v_avgsal); ELSE DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the ' || 'department average of ' || v_avgsal); END IF; END; BEGIN emp_sal_query(SELF.empno); END; END;
The following is the output displayed when an instance of the object type is created and procedure display_emp
is invoked:
DECLARE v_emp EMP_PAY_OBJ_TYP; BEGIN v_emp := emp_pay_obj_typ(7900); v_emp.display_emp; END; Employee # : 7900 Name : JAMES Job : CLERK Hire Date : 03-DEC-81 00:00:00 Salary : 950.00 Dept # : 30 Employee's salary does not exceed the department average of 1566.67
The following example is a package with three levels of subprocedures. A record type, collection type, and cursor type declared in the upper level procedure can be accessed by the descendent subprocedure.
CREATE OR REPLACE PACKAGE emp_dept_pkg IS PROCEDURE display_emp ( p_deptno NUMBER ); END; CREATE OR REPLACE PACKAGE BODY emp_dept_pkg IS PROCEDURE display_emp ( p_deptno NUMBER ) IS TYPE emp_rec_typ IS RECORD ( empno emp.empno%TYPE, ename emp.ename%TYPE ); TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER; TYPE emp_cur_type IS REF CURSOR RETURN emp_rec_typ; PROCEDURE emp_by_dept ( p_deptno emp.deptno%TYPE ) IS emp_arr emp_arr_typ; emp_refcur emp_cur_type; i BINARY_INTEGER := 0; PROCEDURE display_emp_arr IS BEGIN DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); FOR j IN emp_arr.FIRST .. emp_arr.LAST LOOP DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || ' ' || emp_arr(j).ename); END LOOP; END display_emp_arr; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; LOOP i := i + 1; FETCH emp_refcur INTO emp_arr(i).empno, emp_arr(i).ename; EXIT WHEN emp_refcur%NOTFOUND; END LOOP; CLOSE emp_refcur; display_emp_arr; END emp_by_dept; BEGIN emp_by_dept(p_deptno); END; END;
The following is the output displayed when the top level package procedure is invoked:
BEGIN emp_dept_pkg.display_emp(20); END; EMPNO ENAME ----- ------- 7369 SMITH 7566 JONES 7788 SCOTT 7876 ADAMS 7902 FORD