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.
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; /
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.
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;
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
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