Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

By: FYIcenter.com

Part:   1  2  3  4  5  6  7  

(Continued from previous part...)

What Is the Scope of a Local Variable?

The scope of a variable can be described with these rules:

• A variable is valid within the procedure or function where it is defined.
• A variable is also valid inside a sub procedure or function defined.
• If a variable name is collided with another variable in a sub procedure or function, this variable becomes not visible in that sub procedure or function.

Here is a sample script to show you those rules:

SQL> CREATE OR REPLACE PROCEDURE PARENT AS
  2    X CHAR(10) := 'FYI';
  3    Y NUMBER := 999999.00;
  4    PROCEDURE CHILD AS
  5      Y CHAR(10) := 'CENTER';
  6      Z NUMBER := -1;
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
  9      DBMS_OUTPUT.PUT_LINE('Y = ' || Y); -- Y from CHILD
 10      DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
 11    END;
 12  BEGIN
 13    DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
 14    DBMS_OUTPUT.PUT_LINE('Y = ' || TO_CHAR(Y));
 15    -- DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
 16    CHILD;
 17  END;
 18  /

SQL> EXECUTE PARENT;
X = FYI
Y = 999999
X = FYI
Y = CENTER
Z = -1

Part:   1  2  3  4  5  6  7