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

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;