Oracle PL/SQL Tutorial/PL SQL Statements/Nested Block

A nested block which declares and prints a variable of the same name

SQL>
SQL> set echo on
SQL> set serveroutput on
SQL> declare
  2    state_name  varchar2(30);
  3
  4  begin
  5    state_name := "A";
  6
  7    declare
  8      state_name  varchar2(30);
  9    begin
 10      state_name := "B";
 11      dbms_output.put_line(state_name);
 12    end;
 13
 14    --Now print the state_name variable"s value in the outer block.
 15    dbms_output.put_line (state_name);
 16  end;
 17  /
B
A
PL/SQL procedure successfully completed.
SQL>
SQL>

Call function in PL/SQL

SQL> set serveroutput on
SQL> set echo on
SQL>
SQL>    CREATE OR REPLACE function letterCounter(myString VARCHAR2)
  2          RETURN NUMBER IS
  3               v_current_position INTEGER := 1;
  4               v_counter NUMBER := 0;
  5     BEGIN
  6          WHILE v_current_position <= LENGTH(myString) LOOP
  7               IF SUBSTR(myString,v_current_position,1) != " " THEN
  8                    v_counter := v_counter + 1;
  9               ELSE
 10                   NULL;
 11               END IF;
 12               v_current_position := v_current_position + 1;
 13         END LOOP;
 14         RETURN v_counter ;
 15    END letterCounter;
 16    /
Function created.
SQL>
SQL>   DECLARE
  2         v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
  3    BEGIN
  4         DBMS_OUTPUT.PUT_LINE("Total count is " || letterCounter(v_MYTEXT));
  5    END;
  6    /
Total count is 11
PL/SQL procedure successfully completed.
SQL>
SQL>

Nested Blocks

SQL>
SQL> --PL/SQL blocks can be nested, one inside the other.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    error_flag  BOOLEAN := false;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("We are going to count from 100 to 1000.");
  5     DECLARE
  6       hundreds_counter  NUMBER(1,-2);
  7     BEGIN
  8       hundreds_counter := 100;
  9       LOOP
 10         DBMS_OUTPUT.PUT_LINE(hundreds_counter);
 11         hundreds_counter := hundreds_counter + 100;
 12         IF hundreds_counter > 1000 THEN
 13           EXIT;
 14         END IF;
 15        END LOOP;
 16     EXCEPTION
 17     WHEN OTHERS THEN
 18       error_flag := true;
 19     END;
 20     IF error_flag THEN
 21       DBMS_OUTPUT.PUT_LINE("Sorry, I cannot count that high.");
 22      ELSE
 23       DBMS_OUTPUT.PUT_LINE("Done.");
 24     END IF;
 25  END;
 26   /
We are going to count from 100 to 1000.
100
200
300
400
500
600
700
800
900
Sorry, I cannot count that high.
PL/SQL procedure successfully completed.
SQL>

Nesting Functions and Procedures

SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2  temp  NUMBER;
  3
  4    FUNCTION iifn(boolean_expression IN BOOLEAN,
  5                true_number IN NUMBER,
  6                false_number IN NUMBER)
  7    RETURN NUMBER IS
  8    BEGIN
  9      IF boolean_expression THEN
 10        RETURN true_number;
 11      ELSIF NOT boolean_expression THEN
 12        RETURN false_number;
 13      ELSE
 14        --nulls propagate, i.e. null input yields null output.
 15        RETURN NULL;
 16      END IF;
 17    END;
 18  BEGIN
 19    DBMS_OUTPUT.PUT_LINE(iifn(2 > 1,1,0));
 20    DBMS_OUTPUT.PUT_LINE(iifn(2 > 3,1,0));
 21
 22    temp := iifn(null,1,0);
 23    IF temp IS NULL THEN
 24      DBMS_OUTPUT.PUT_LINE("NULL");
 25    ELSE
 26      DBMS_OUTPUT.PUT_LINE(temp);
 27    END IF;
 28  END;
 29  /
1
0
NULL
PL/SQL procedure successfully completed.
SQL>
SQL>

This is an example of nested anonymous blocks

SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    error_flag  BOOLEAN := false;
  3
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE("We are going to count from 100 to 1000.");
  6
  7    DECLARE
  8      hundreds_counter  NUMBER(1,-2);
  9    BEGIN
 10      hundreds_counter := 100;
 11      LOOP
 12        DBMS_OUTPUT.PUT_LINE(hundreds_counter);
 13        hundreds_counter := hundreds_counter + 100;
 14        IF hundreds_counter > 1000 THEN
 15          EXIT;
 16        END IF;
 17       END LOOP;
 18    EXCEPTION
 19    WHEN OTHERS THEN
 20      error_flag := true;
 21    END;
 22
 23    IF error_flag THEN
 24      DBMS_OUTPUT.PUT_LINE("Sorry, I cannot count that high.");
 25     ELSE
 26      DBMS_OUTPUT.PUT_LINE("Done.");
 27    END IF;
 28  END;
 29  /
We are going to count from 100 to 1000.
100
200
300
400
500
600
700
800
900
Sorry, I cannot count that high.
PL/SQL procedure successfully completed.
SQL>
SQL>