[Hint] Scope of variables

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 26 May 2004 6:55 pm

See how interesting this: I created 2 blocks (one inside the other) to declare the same variable. I found that the Oracle works like Java in terms of \"scope\" of variables: variables with the value of its scope.

Code: Select all
DECLARE VTEMP NUMBER:=5;
BEGIN
  DBMS_OUTPUT.PUT_LINE(VTEMP);

  DECLARE VTEMP NUMBER:=10;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(VTEMP);
  END;

  DBMS_OUTPUT.PUT_LINE(VTEMP);
END;
/

5
10
5

PL/SQL procedure successfully completed.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Sat, 26 May 2012 9:41 am

Po super cool.
Could detail how works these nested blocks?
In delphi but wore the plsql get with doubts using it.

Agradçeo in advance.
abarbosa

Poston Mon, 28 May 2012 9:09 am

Then, a nested block when no declaration of variables, use to manage exceptions to that block.
The value of the variables defined outside of the block, are valid and can be handled normally inside the block.
Example:
Code: Select all
SQL> set serveroutput on
SQL> DECLARE
  2     vtemp  NUMBER:=10;
  3  BEGIN
  4 
  5    DBMS_OUTPUT.PUT_LINE ( vtemp );
  6    --nao tem outra declaracao!
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE ( vtemp );
  9      vtemp := 20;
10      --aqui  vai um select...
11      EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
12    END;
13    DBMS_OUTPUT.PUT_LINE ( vtemp );
14  END;
15  /

10
10
20

PL/SQL procedure successfully completed

SQL>
is another example, when we have a statement inside the other. If different variable names, is the same thing. The only difference that the variable declared inside the block from within exists only within that block! Here's the proof:
Code: Select all
SQL> DECLARE
  2    vtemp  NUMBER:=10;
  3  BEGIN
  4    DECLARE outra NUMBER:=20;
  5    BEGIN
  6      DBMS_OUTPUT.PUT_LINE ( outra );
  7    END;
  8 
  9    DBMS_OUTPUT.PUT_LINE ( outra );   --citando fora do seu bloco!
10  END;
11  /

DECLARE
  vtemp  NUMBER:=10;
BEGIN
  DECLARE outra NUMBER:=20;
  BEGIN
    DBMS_OUTPUT.PUT_LINE ( outra );
  END;

  DBMS_OUTPUT.PUT_LINE ( outra );   --citando fora do seu bloco!
END;

ORA-06550: line 9, column 26:
PLS-00201: identifier 'OUTRA' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

SQL>
see that by quoting the variable ANOTHER out of his block, it doesn't exist!

And we still have that example from above, where is declared two variables with the same name, hence the value of the second only exists inside your block.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: Google [Bot] and 6 guests