Friday 26 August 2011

Same names for variables, nested anonymous blocks = Confusion!

Question:
How can I refer to anonymous block variables when I use the same name in inner and outer block? The following code block should give you a sense of what I want to do:

Declare
x pls_integer := 10;
begin
declare
x number := 20.17;
begin
-- How do I refer to the outer block's variable here?
DBMS_OUTPUT.PUT_LINE ('Show me 10: ' || TO_CHAR (x));
end;
end;

Answer:
PL/SQL makes it very easy to distinguish between variables with the same name declared in different scopes, and I will show you how to do that below. But first, since this is a best practices column, I will start with the following recommendation:
Use unique names for all the variables that are accessible (can be read or modified) in a particular block of code. Avoid reusing names, and avoid reusing the same variable for different purposes. Declare a unique variable with a unique name for each of your needs.
In particular, do not use the same name for variables in outer and inner blocks. PL/SQL doesn't get the least bit confused, but you and anyone maintaining your code certainly will have trouble figuring out what is going on.
Now I will show you how to distinguish between variables that have the same name but are declared in different scopes.
The block of code you provided in your question is problematic because any reference to the variable named x in the inner block will always be resolved to the x declared as number in the inner block. That is because the first place the PL/SQL compiler looks to resolve all references is the current block. If a reference is unresolved in then moves to the outer block and checks there, and so on. In this case, however, PL/SQL finds an x and declares itself done.
You can tell the compiler exactly how to resolve the reference to a data structure (or program, for that matter) by qualifying the name of that variable with the name of its scope. If, for example, I declared a variable named x in the specification of a package, as in:

CREATE OR REPLACE PACKAGE x_marks_the_spot
IS
x DATE DEFAULT SYSDATE;
END x_marks_the_spot;

then I can work with that packaged variable in my anonymous block by prefixing the variable name with the package name:

DECLARE
x NUMBER := 10;
BEGIN
DECLARE
x NUMBER := 20;
BEGIN
DBMS_OUTPUT.put_line ( 'Show me the date: ' ||
TO_CHAR ( x_marks_the_spot.x ));
END;
END;
/

Now, anonymous blocks do not by default have names; that's why they are called "anonymous." You can, however, use a label to give a name to a block, and then use that name to qualify references to variables declared in that block.
Below you will find a version of the block in your question, in which both the outer and inner blocks are assigned names. I then qualify each reference to the x variables with the block name, and I can see exactly what was desired:

SQL> set serveroutput on
SQL> <<block1>>
2 DECLARE
3 x NUMBER := 10;
4 BEGIN
5 <>
6 DECLARE
7 x NUMBER := 20;
8 BEGIN
9 -- How do I refer to the outer block's variable here?
10 DBMS_OUTPUT.put_line ( 'Show me 10: ' || TO_CHAR ( block1.x ));
11 DBMS_OUTPUT.put_line ( 'Show me 20: ' || TO_CHAR ( block2.x ));
12 END;
13 END;
14 /
Show me 10: 10
Show me 20: 20

No comments:

Post a Comment