Nested Subprograms Better Than Nested Blocks

View All Scripts Login to Run Script

Script Name Nested Subprograms Better Than Nested Blocks
Description Sure, you can create nested (anonymous) blocks inside other blocks (executable sections). You can use a <<label>> to give that block a "name." You can attach that "name" to the END statement of the block. But it is way better (in my opinion) to move those nested blocks up to the declaration section as nested subprograms and then invoke those subprograms by name. No need to for labels, cleaner, simpler, easier to read code.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Tuesday May 17, 2016

Statement 1

CREATE TABLE employees
AS
   SELECT * FROM hr.employees

Table created.

Statement 2

Nested Blocks

DECLARE 
   l_name   employees.last_name%TYPE; 
BEGIN 
  <<show_name>> 
   BEGIN 
      SELECT employee_id 
        INTO l_name 
        FROM employees 
       WHERE employee_id = 100; 
 
      DBMS_OUTPUT.put_line ('Old name=' || l_name); 
   END show_name; 
 
  <<change_name>> 
   BEGIN 
      UPDATE employees 
         SET last_name = UPPER (last_name) 
       WHERE employee_id = 100 
       RETURNING last_name INTO l_name; 
 
      DBMS_OUTPUT.put_line ('New name= ' || l_name); 
   END change_name; 
END;

Old name=100
New name= KING

Statement 3

Nested Subprograms

DECLARE 
   PROCEDURE show_name 
   IS 
      l_name employees.last_name%TYPE; 
   BEGIN 
      SELECT employee_id 
        INTO l_name 
        FROM employees 
       WHERE employee_id = 100; 
 
      DBMS_OUTPUT.put_line ('Old name=' || l_name); 
   END show_name; 
 
   PROCEDURE change_name 
   IS 
      l_name employees.last_name%TYPE; 
   BEGIN 
      UPDATE employees 
         SET last_name = UPPER (last_name) 
       WHERE employee_id = 100 
       RETURNING last_name INTO l_name; 
 
      DBMS_OUTPUT.put_line ('New name= ' || l_name); 
   END change_name; 
BEGIN 
   show_name; 
   change_name; 
END;

Old name=100
New name= KING

Statement 4

Notice that within each of these nested subprograms I reference directly the "global" parameter. This is generally considered a bad idea in programming circles, as Bryn Llewellyn said to me: "The source code of a subprogram should not refer to a variable defined in an outer scope -- with the rule relaxed for a package-global variable." To avoid this, you would....

Watch out Out-of-Scope Global References!

CREATE OR REPLACE PROCEDURE with_filter (filter_in IN VARCHAR2) 
IS 
   PROCEDURE show_name 
   IS 
      l_name   employees.last_name%TYPE; 
   BEGIN 
      SELECT employee_id 
        INTO l_name 
        FROM employees 
       WHERE employee_id = 100 AND last_name LIKE filter_in; 
 
      DBMS_OUTPUT.put_line ('Old name=' || l_name); 
   END show_name; 
 
   PROCEDURE change_name 
   IS 
      l_name   employees.last_name%TYPE; 
   BEGIN 
         UPDATE employees 
            SET last_name = UPPER (last_name) 
          WHERE employee_id = 100 AND last_name LIKE filter_in 
      RETURNING last_name 
           INTO l_name; 
 
      DBMS_OUTPUT.put_line ('New name= ' || l_name); 
   END change_name; 
BEGIN 
   show_name; 
   change_name; 
END;

Procedure created.

Statement 5

You can and should pass any "globals" - variables declared outside the nested subprogram - as parameters. This of course can get annoying when you have LOTS of such references in the code you are relocating to the nested subprogram. It can even reduce readability to the point that....you should stick with a nested block! :-)

Pass Program Unit "Globals" as Parameters

CREATE OR REPLACE PROCEDURE with_filter (filter_in IN VARCHAR2) 
IS 
   PROCEDURE show_name (filter_in IN VARCHAR2) 
   IS 
      l_name   employees.last_name%TYPE; 
   BEGIN 
      SELECT employee_id 
        INTO l_name 
        FROM employees 
       WHERE employee_id = 100 AND last_name LIKE filter_in; 
 
      DBMS_OUTPUT.put_line ('Old name=' || l_name); 
   END show_name; 
 
   PROCEDURE change_name (filter_in IN VARCHAR2) 
   IS 
      l_name   employees.last_name%TYPE; 
   BEGIN 
         UPDATE employees 
            SET last_name = UPPER (last_name) 
          WHERE employee_id = 100 AND last_name LIKE filter_in 
      RETURNING last_name 
           INTO l_name; 
 
      DBMS_OUTPUT.put_line ('New name= ' || l_name); 
   END change_name; 
BEGIN 
   show_name (filter_in); 
   change_name (filter_in); 
END;

Procedure created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database