RelationalDBDesign RelationalDBDesign





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

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
IS
  year_total NUMBER;
BEGIN
  year_total := 0;
  /* Beginning of nested block */
  DECLARE
    month_total NUMBER;
  BEGIN
    month_total := year_total / 12;
  END set_month_total;
  /* End of nested block */
END;

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.
DECLARE
  CURSOR emp_cur IS ...;
BEGIN
  DECLARE
    total_sales NUMBER;
  BEGIN
    DECLARE
      l_hiredate DATE;
    BEGIN
      force= mass * acceleration;
    END;
  END;
END;