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

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

   <source lang="sql">

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></source>

Call function in PL/SQL

   <source lang="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></source>

Nested Blocks

   <source lang="sql">

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></source>

Nesting Functions and Procedures

   <source lang="sql">

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></source>

This is an example of nested anonymous blocks

   <source lang="sql">

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></source>