Nested SubProcedure

Nested SubProcedure

Purpose of Nested SubProcedure/Function:

A Nested function/procedure definitions are a form of information hiding and are useful for dividing procedural tasks into subtasks which are only meaningful locally.  This avoids confusion with other parts of the program with functions and variables that are unrelated to those specific parts.

After go through the below article we will have a detailed understanding of Nested subprocedure with below usage:

  • Forward Declaration of nested subprocedure/function
  • Function Overloading of nested subprocedure/function
  • The scope/visibility of Global and Local variables.
  • Use of Nested subprocedure/function in SQL query
  • Use of hierarchy (like: func1 -> proc1 -> func2 -> proc1 ) of Nested SubProcedure/function

Nested function/procedure are typically used as helper functions inside another function. This has the structural benefit of organizing and simplifying the code with proper scope and visibility. As nested function can access local variables of the enclosing function. It allows functions to share state easily as sharing of a state is possible without passing parameters to the nested function or use a global variable.

Nested SubProcedure allows the database user to write a procedure or function block within another procedure or function. In other words, we can use another procedure/function as a simple variable declaration and use those database blocks(procedure/function) in the body as per requirement. The inner sub-procedure is part of the outer main procedure so there won’t be any catalog entry created for the nested sub-procedure/sub-function. The inner sub-procedure have no visibility outside the outer main procedure that encloses it.

Example:

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  FUNCTION inner_func(b number) RETURN number IS
  BEGIN
    return b + 1;
  END;
BEGIN
  return inner_func(a);
END;
/

Function created.

SQL> SELECT outer_func(1) FROM DUAL;

OUTER_FUNC(1)
————-
   2

NOTE: we cannot call inner_func() from outside, however, it is callable within outer_func() function body.

Since the nested function is part of the function enclosing it, it can use all variables, types and other nested functions defined in its parent. Also, it can access parent functions parameters. For example,

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  v number := 10;
  FUNCTION inner_func(b number) RETURN number IS
  BEGIN
    return a + b + v;
  END;
BEGIN
  return inner_func(a);
END;
/

Function created.

SQL> SELECT outer_func(1) FROM DUAL;

OUTER_FUNC(1)
————-
  12

NOTE: A Nested function/procedure definitions are a form of information hiding and are useful for dividing procedural tasks into subtasks which are only meaningful locally and is not required by other procedures

A Nested subprocedure/subfunction is just like regular procedure/function so that,

  • Nested subprocedure can be called like other procedure but within the scope
  • Nested subprocedure can accept IN, OUT and INOUT parameters
  • Nested subprocedure can accept and return polymorphic types
  • Nested subprocedure can recursively call the same function

We can write a nested function or procedure within:

  • Function
  • Procedure
  • Trigger
  • Anonymous block

The nested procedure/function is enclosed within another function, so these options are inherited from the parent function.

Forward Declaration:

The nested function can call another nested function within its scope. If there are two nested functions sibling to each other, then they can call each other. However when the first function calls the other, it will not find it as that function is not visible yet, resulting in an error.

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  FUNCTION inner_func1(b1 number) RETURN number IS
  BEGIN
    return inner_func2(5);
  END;
  6  
  FUNCTION inner_func2(b2 number) RETURN number IS
  BEGIN
    return 10;
  END;
BEGIN
  return inner_func1(a);
END;
/

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION OUTER_FUNC:

LINE/COL ERROR
——– —————————————————————–
4/5 PL/SQL: Statement ignored
4/12 PLS-00313: ‘INNER_FUNC2’ not declared in this scope

To avoid such error, we need to declare the function “inner_func2” before using such function within “inner_func1” nested subprocedure/function definition as below:

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  FUNCTION inner_func2(b2 number) RETURN number; — Forward declaration
  3  
  FUNCTION inner_func1(b1 number) RETURN number IS
  BEGIN
    return inner_func2(5);
  END;
  8  
  FUNCTION inner_func2(b2 number) RETURN number IS
  BEGIN
    return 10;
  END;
BEGIN
  return inner_func1(a);
END;
/

Function created.

SQL> SELECT outer_func(1) from dual;

OUTER_FUNC(1)
————-
  10

Function overloading and qualified names:

The concept of procedure/function overloading is also applicable for Nested subprocedure/functions.
So if you don’t want to bloat your catalog with many functions with the same name but accepting different arguments, you can enclose them within a single function. This is one kind of encapsulation too. A simple example will look like as:

SQL> CREATE OR REPLACE PROCEDURE outer_func(v1 number) IS
  FUNCTION inner_func(b1 number) RETURN number IS
  BEGIN
    return b1;
  END;

  FUNCTION inner_func(b1 number, b2 number) RETURN number IS
  BEGIN
    return b1 + b2;
  END;

  FUNCTION inner_func(b1 varchar2) RETURN number IS
  BEGIN
    return length(b1);
  END;
BEGIN
  dbms_output.put_line(inner_func(1));
  dbms_output.put_line(inner_func(10, 20));
  dbms_output.put_line(inner_func(‘EnterpriseDB’));
END;
/

Procedure created.

SQL> begin
  2    outer_func(1);
  3  end;
  4  /
1
30
12

PL/SQL procedure successfully completed.

The scope/visibility of Global and Local variables:

SQL> CREATE OR REPLACE PACKAGE ovrld AS
    vb VARCHAR2(20) := ‘Global variable.’;
    FUNCTION fun1 (v1 IN CHAR) RETURN NUMBER;
    FUNCTION fun1 (v1 IN NUMBER) RETURN NUMBER;
END;
/

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY ovrld AS
  FUNCTION fun1 (v1 IN CHAR) RETURN NUMBER IS
  BEGIN
    dbms_output.put_line(vb);
      vb := v1;
    dbms_output.put_line(ovrld.vb);
    RETURN (100);
  END;
  FUNCTION fun1 (v1 IN NUMBER) RETURN NUMBER IS
    Fun1_vb VARCHAR2(20) := ‘qwerty’;
  BEGIN
    dbms_output.put_line(vb);
    vb := Fun1_vb;
    dbms_output.put_line(ovrld.vb);
    RETURN (200);
  END;
END;
/

Package body created.

SQL> SELECT ovrld.fun1(‘abc’) o_char, ovrld.fun1(123) o_num FROM dual;

    O_CHAR O_NUM
———- ———-
       100  200

Global variable.
abc
abc
qwerty

SQL> DECLARE
 ret_f1 NUMBER;
BEGIN
 ovrld.vb := ‘ABCD’;
 ret_f1 := ovrld.fun1(‘abc’);
 dbms_output.put_line(‘fun1 v1 in char: ‘||ret_f1);
 ret_f1 := ovrld.fun1(123);
 dbms_output.put_line(‘fun1 v1 in number: ‘||ret_f1);
END;
/

ABCD
abc
fun1 v1 in char: 100
abc
qwerty
fun1 v1 in number: 200

PL/SQL procedure successfully completed.

If there are multiple functions with the same name but at a different level, then calling with just the function name is not enough. To call the exact required function, you need to uniquely qualify the function name, as below:

SQL> CREATE OR REPLACE PROCEDURE outer_func(v1 number) IS
 vr number;
  FUNCTION inner_func(a number) RETURN number IS
    FUNCTION nestedfunc(b number) RETURN number IS
      FUNCTION inner_func(c number) RETURN number IS
      BEGIN
        dbms_output.put_line(‘… … … In inner_func’);
        IF c = 1 THEN
          return inner_func(2); — This calls itself
        END IF;
        return c;
      END;
    BEGIN
      dbms_output.put_line(‘… … In nestedfunc’);
      return inner_func(1);
    END;
  BEGIN
    dbms_output.put_line(‘… In inner_func’);
    IF a = 2 THEN
      return 2;
   END IF;
    return nestedfunc(3);
  END;
BEGIN
  dbms_output.put_line(‘In outer_func’);
  vr := inner_func(1);
END;
/

Procedure created.

SQL> begin
  2    outer_func(1);
  3  end;
  4  /
In outer_func
… In inner_func
… … In nestedfunc
… … … In inner_func
… … … In inner_func

PL/SQL procedure successfully completed.

To call inner_func() which is in the outer scope, we need to qualify that with its parent. as below:

SQL> CREATE OR REPLACE PROCEDURE outer_func(v1 number) IS
  vr number;
  FUNCTION inner_func(a number) RETURN number IS
    FUNCTION nestedfunc(b number) RETURN number IS
      FUNCTION inner_func(c number) RETURN number IS
      BEGIN
        dbms_output.put_line(‘… … … In inner_func’);
        IF c = 1 THEN
          return outer_func.inner_func(2); — This calls outer inner_func
        END IF;
        return c;
      END;
    BEGIN
      dbms_output.put_line(‘… … In nestedfunc’);
      return inner_func(1);
    END;
  BEGIN
    dbms_output.put_line(‘… In inner_func’);
    IF a = 2 THEN
      return 2;
    END IF;
    return nestedfunc(3);
  END;
BEGIN
  dbms_output.put_line(‘In outer_func’);
  vr :=  inner_func(1);
 27  END;
 28  /

Procedure created.

SQL> begin
  2    outer_func(1);
  3  end;
  4  /
In outer_func
… In inner_func
… … In nestedfunc
… … … In inner_func
… In inner_func

PL/SQL procedure successfully completed.

Nested function in SQL query:

  • The nested function cannot be used in an explicit SQL query. We can only refer the nested subprocedure/function inside the outer plsql block and within its scope.
  • Only the procedure/function which can be referred with the SQL query or standalone/outside blocks will have catalogs entry, as below.

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  v number;
  FUNCTION sign(b1 number) RETURN number IS
  BEGIN
    dbms_output.put_line(‘Nested function’);
    return 1;
  END;
BEGIN
  select sign(a) into v from dual;
  return v;
 11  END;
 12  /

Function created.

SQL> select outer_func(1) from dual;

OUTER_FUNC(1)
————-
   1

SQL> SELECT text FROM user_source WHERE name LIKE ‘%OUTER%’;

TEXT
————————————————————————
FUNCTION outer_func(a number) RETURN number IS
  v number;
  FUNCTION sign(b1 number) RETURN number IS
  BEGIN
    dbms_output.put_line(‘Nested function’);
    return 1;
  END;
BEGIN
  select sign(a) into v from dual;
  return v;
end;

11 rows selected.

SQL> SELECT outer_func.sign(1) FROM DUAL;
SELECT outer_func.sign(1) FROM DUAL
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-225: subprogram or cursor ‘OUTER_FUNC’ reference is out of scope

SQL> select text from user_source where name like ‘%SIGN%’ ;

no rows selected

However, if there is no matching global function found but the nested function, then it throws an error.

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  v number;
  FUNCTION inner_func(b1 number) RETURN number IS
  BEGIN
    dbms_output.put_line(‘Nested function’);
    return 1;
  END;
BEGIN
  select inner_func(a) into v from dual;
  return v;
END;
/

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION OUTER_FUNC:

LINE/COL ERROR
——– —————————————————————–
9/3 PL/SQL: SQL Statement ignored
9/10 PL/SQL: ORA-00904: : invalid identifier
9/10 PLS-00231: function ‘INNER_FUNC’ may not be used in SQL


But, calling such a nested function and assigning it to a variable with in block works fine as below:

SQL> CREATE OR REPLACE FUNCTION outer_func(a number) RETURN number IS
  v number;
  FUNCTION inner_func(b1 number) RETURN number IS
  BEGIN
    dbms_output.put_line(‘Nested function’);
    return 1;
  END;
BEGIN
  v := inner_func(a);
  return v;
END;
/

Function created.

SQL> SELECT outer_func(1) from dual;

OUTER_FUNC(1)
————-
   1

Nested function

Use of hirarchy (like: func1 -> proc1 -> func2 -> proc1 ) of Nested SubProcedure:

Trying to access the inner most subprocedure from function func2, with notation func1.proc1.func2.proc1();

SQL> CREATE OR REPLACE PACKAGE nestedproc_pkg1
AS
 function func1(par1 IN VARCHAR2, par2 IN VARCHAR2) return NUMBER;
END nestedproc_pkg1;
/

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY nestedproc_pkg1
IS
function func1(par1 IN VARCHAR2, par2 IN VARCHAR2)
return NUMBER
IS
ret_func NUMBER;
    procedure proc1(par1 IN VARCHAR2, par2 IN VARCHAR2)
    IS
        function func2(par1 IN VARCHAR2, par2 IN VARCHAR2)
        return NUMBER
        IS
            procedure proc1(par1 IN VARCHAR2, par2 IN VARCHAR2)
            IS
            BEGIN
                DBMS_OUTPUT.PUT_LINE(‘… … … func1.proc1.func2.proc1 start..’);
                DBMS_OUTPUT.PUT_LINE(‘… … … func1.proc1.func2.proc1 end..’);
            END proc1;
        BEGIN
            DBMS_OUTPUT.PUT_LINE(‘… … func1.proc1.func2 start..’ );
                func1.proc1.func2.proc1(par1, par2);
            DBMS_OUTPUT.PUT_LINE(‘… … func1.proc1.func2 end..’);
            return 1;
        END func2;
    BEGIN
        DBMS_OUTPUT.PUT_LINE(‘… func1.proc1 start..’);
        ret_func := func2(par1, par2);
        DBMS_OUTPUT.PUT_LINE(‘… func1.proc1 end..’);
    END proc1;
BEGIN
    DBMS_OUTPUT.PUT_LINE(‘outer block func1 start..’ );
    proc1(par1, par2);
    DBMS_OUTPUT.PUT_LINE(‘outer block func1 end..’);
    return 1;
END func1;
END nestedproc_pkg1;
/

Package body created.

SQL> DECLARE
    par1 VARCHAR2(20) := ‘A’;
    par2 VARCHAR2(20) := ‘B’;
    ret_func NUMBER;
BEGIN
    ret_func := nestedproc_pkg1.func1(par1, par2);
END;
/
outer block func1 start..
… func1.proc1 start..
… … func1.proc1.func2 start..
… … … func1.proc1.func2.proc1 start..
… … … func1.proc1.func2.proc1 end..
… … func1.proc1.func2 end..
… func1.proc1 end..
outer block func1 end..

PL/SQL procedure successfully completed.

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn