Oracle, how to declare local variable inside executable block

Question

Typically variables are declared in the DECLARE section, and are available inside BEGIN block. I find this style stiff and tedious.

Is it possible to declare variables inside the BEGIN block just when they are needed? It is stupid to declare new global variable, if it's needed just to store some temporary value for further calculations, queries and assertions.

Answer 1

Try this - I added comments in the code for you to understand the visibility domain of each variable. Also you can remove the comment from the last DBMS_OUTPUT to see that var2 is no longer available in the outer code.

set serveroutput on;
<<tag>>        
DECLARE 
  var1 INT := 1; -- global variable
BEGIN  
  DECLARE 
     var1 INT := 2; -- local variable
     var2 INT := 0;
  BEGIN   
     DBMS_OUTPUT.PUT_LINE(var1); -- will display 2 (value of local var1);
     DBMS_OUTPUT.PUT_LINE(tag.var1); -- will display 1 (value of global var1);
     DBMS_OUTPUT.PUT_LINE(var2); -- will display 0 (value of local var2);
  END;  
 DBMS_OUTPUT.PUT_LINE(var1); -- will display 1 (value of global var1); 
 -- DBMS_OUTPUT.PUT_LINE(var2); -- will crash since var2 is no longer in memory;
END;