Nested Subprograms

Contact Us or call 1-877-932-8228
Nested Subprograms

Nested Subprograms

Nesting does not only apply to blocks themselves. Like other programming languages, PL/SQL allows you to nest control structures (conditional statements and loops). In addition, subprograms can be nested in certain situations but it is more common to use packages as containers for subprograms. It is not important to understand the details of the subprograms presented in the examples. Simply note that it is possible to create functions or procedures in other subprograms, whether anonymous blocks of PL/SQL code or stored.

Code Sample:

Nested-Blocks/Demos/nested_subprogram_in_anonymous.sql
declare
 CURSOR crs is 
            SELECT street_address, city,
                   state_province, postal_code
            FROM locations;
              
 type location_type is record
  (street_address   locations.street_address%type,
   city             locations.city%type,
   state_province   locations.state_province%type,
   postal_code      locations.postal_code%type);
     
 location_rec location_type;

 procedure process_locations
 as
 begin
  if crs%rowcount = 5 then
   -- force an exception for the 5th location
   raise value_error;
  end if;
  dbms_output.put_line(location_rec.street_address
                       || ', '
                       || location_rec.city
                       || ', '
                       || location_rec.state_province
                       || ' '
                       || location_rec.postal_code);
  exception
  when others then
   dbms_output.put_line('Problem with Location #'
                        || crs%rowcount);
 end process_locations;

begin

 if not crs%isopen then
  open crs;
 end if;

 loop
  fetch crs into location_rec;
    
  exit when crs%notfound;
 
  process_locations;
 end loop;

 if crs%isopen then
  close crs;
 end if;
  
exception
 when others then
  if crs%isopen then
   close crs;
  end if;
end;
/

Code Explanation

One or more subprograms (functions or procedures) can be nested within an anonymous block of PL/SQL code by declaring them in the DECLARE block. This effectively creates a subprogram that can be called one or more times from within the executable section of the anonymous block of PL/SQL code.

Note that any variables declared within the nested subprogram are only within the scope of the nested program and are not visible to the enclosing anonymous block of PL/SQL code.

Code Sample:

Nested-Blocks/Demos/nested_subprograms.sql
create or replace PROCEDURE outer_procedure  IS

   x VARCHAR2(10):= 'outer';
  
   FUNCTION inner_function  RETURN VARCHAR2 IS

      x VARCHAR2(10):= 'inner';

   BEGIN
      RETURN x;     
   END;
   
BEGIN
  dbms_output.put_line('Outer: ' || x);
  dbms_output.put_line('Inner: ' ||
                       inner_function());
END;

Code Explanation

A function or procedure can be nested within another stored subprogram. The nesting occurs in the DECLARE block. This effectively creates a subprogram that is private to the enclosing subprogram.

Variables within a nested subprogram are not visible to the enclosing subprogram. Note the following line in the procedure, results in an error that indicates subprogram or cursor 'INNER_FUNCTION' reference is out of scope

dbms_output.put_line('Inner: '||inner_function.x);

In a similar manner, a call to the following will result in an error as well that indicates subprogram or cursor 'OUTER_PROCEDURE' reference is out of scope

outer_procedure.inner_function()
Next