PL/SQL Datatypes  «Prev  Next»

Lesson 3 PL/SQL Variables Nested blocks
Objective Define the scope of variables within nested blocks.

PL/SQL Variables Nested Blocks

In Oracle PL/SQL, the concept of variable scope refers to the range within a program where a variable can be referenced or manipulated. The scope of variables is primarily determined by the block structure, particularly in the case of nested blocks.
Here are the key points about the scope of variables within nested blocks in PL/SQL:
  1. Local and Global Variables: Variables in PL/SQL can be either local or global. A local variable is one that is declared within a block and can only be accessed within that block and its nested blocks. Once the block execution is complete, local variables are no longer accessible. On the other hand, a global variable is declared in a package specification and can be accessed by any block in the package.
  2. Variable Shadowing: When a variable in a nested block has the same name as a variable in an outer block, the inner variable "shadows" the outer one. This means that within the nested block, any reference to that variable name refers to the inner variable, not the outer one. Once the execution exits the nested block, references to the variable name revert to the outer variable.
  3. Initialization and Finalization: Each time execution enters a block, PL/SQL initializes the block's local variables to their default values. When the block completes, the values of those variables are discarded, and the variables are finalized.
  4. Nested Blocks and Exception Handlers: If a variable is declared in a block that contains an exception handler, and the exception is raised within that block, the value of the variable at the point the exception was raised is preserved. This means you can use exception handlers to capture and examine variable values at the point of error.

In summary, understanding the scope of variables in Oracle PL/SQL, particularly within nested blocks, is crucial to writing effective and bug-free code. Always be mindful of where variables are declared and how they might be shadowed within nested blocks to ensure your variables always contain the expected values.

Break your Program Logic into Smaller Blocks

Often you will want to break your program logic into smaller blocks to make the code easy to understand and maintain.
You can nest blocks within blocks and thereby break your program logic into smaller blocks.
The EXCEPTION section can contain nested blocks.

Scope of Variables

The scope of a variable is the section of the program that refers to the variable.
PL/SQL first looks for any variable referenced in a block locally. If the block does not find the variable declared locally, it looks up to the declarative section of the enclosing (or parent) blocks. A block will never look down to enclosed (or child) blocks.

Oracle Nested Block consisting of an Internal Block
Oracle Nested Block consisting of an Internal Block

In the nested block shown above, the variable y can reference the variable x. Variable x cannot reference variable y, however. If the variable y in the nested block is given the same name as the variable named x in the outer block, its value is valid only for the duration of the nested block.
In the next lesson, you will learn how to define identifiers and literals within a PL/SQL block.

PL/SQL Nested Block

PL/SQL shares with Ada and Pascal the additional definition of being a block-structured language, that is, blocks may "nest" within other blocks. In contrast, the C language has blocks, but standard C is not strictly block-structured, because its subprograms cannot be nested.
Here is a PL/SQL example showing a procedure containing an anonymous, nested block:

PROCEDURE calc_totals
  year_total NUMBER;
  year_total := 0;
  /* Beginning of nested block */
    month_total NUMBER;
    month_total := year_total / 12;
  END set_month_total;
  /* End of nested block */

The /* and */ delimiters indicate comments. You can nest anonymous blocks within anonymous blocks to more than one level, as shown in Figure 3-3. Other terms you may hear for nested block are
  1. enclosed block,
  2. child block, or
  3. subblock;
the outer PL/SQL block may be called the enclosing block or the parent block.In general, the advantage of nesting a block is that it gives you a way to control both scope and visibility in your code.

  CURSOR emp_cur IS ...;
    total_sales NUMBER;
      l_hiredate DATE;
      force= mass * acceleration;

Beginning Oracle PL/SQL