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 on 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.
Access of variables includes those defined as a data type and others such as record types, collection types, and cursors.
The variable may be accessed by up to one qualifier, which is the name of the subprogram or labeled anonymous block in which the variable has been locally declared.
The following content shows the syntax to reference a variable:
[qualifier.]variable
If specified, qualifier is the subprogram or labeled anonymous block in which variable has been declared in its declaration section (that is, variable is a local variable).
In PolarDB for PostgreSQL(Compatible with Oracle), 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
The following content 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 the specified local variable does not exist, the search for the variable is made in the parent of the current block, and so forth, proceeding up the ancestor hierarchy. If the specified variable is not found, 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 the specified match is not found, 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.
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 output shows 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 output shows 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 control 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 output shows 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 of display_emp contains the emp_typ record type and the emp_sal_query subprocedure. The r_emp record variable declared locally to emp_sal_query is able to access the emp_typ record type declared in the display_emp parent block.
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 output is displayed when an instance of the object type is created and the display_emp procedure 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 output is generated 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