Nested Subprograms

Precautions

• This is used in the ORA compatibility database.
• The maximum number of nesting layers is specified by the max_subpro_nested_layers parameter. The default value is 3, and the value range is 0 to 100. If a nested subprogram contains an anonymous block, the layer of anonymous block is not counted, but the nested subprograms in the anonymous block are counted in the total number of layers.
• Nested subprograms do not support overloading or SETOF.
• Nested subprograms cannot be defined as autonomous transactions. They can call stored procedures or functions that contain autonomous transactions.
• Subfunctions (FUNCTION) cannot be directly called and must have return values. Sub-stored-procedures (PROCEDURE) cannot be called in expressions.
• Nested subprograms cannot be called by PERFORM. Dynamic statements cannot contain nested subprograms.
• Currently, only the following modifiers are supported for nested subprograms:

  {IMMUTABLE  | STABLE  | VOLATILE }  
  {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } 

• Only one qualifier can reference a nested subprogram or a variable of a nested subprogram.
• When the return value type of a subfunction (FUNCTION) is the record type customized by the function, subfunc().col cannot be used to access the column attribute of the return value of the subfunction. As a result, an error is reported during execution.
• The declaration of a nested subprogram must be at the end of the declaration part (declare the nested subprogram after the declaration of variables, cursors, and types is complete).
• Nested subprograms can be called only inside declared functions or stored procedures and cannot be used externally.
• The debugger breakpoint is not supported when nested subprograms are used. Step-by-step debugging is supported.
• Other precautions are the same as those for stored procedures and functions.

Syntax

• Syntax format for creating a sub-stored-procedure:

  1 2 3 4 5 6 7 8

  PROCEDURE procedure_name [ (parameters) ] [{IMMUTABLE | STABLE | VOLATILE } | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }] { IS | AS } [ declarations ] BEGIN plsql_body END;

• Syntax for creating a subfunction:

  1 2 3 4 5 6 7 8

  FUNCTION function_name [ (parameters) ] RETURN rettype [{IMMUTABLE | STABLE | VOLATILE } | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }] { IS | AS } [ declarations ] BEGIN plsql_body END;

  In the declarations part, you can define the nested subprograms of the lower layer.

• Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  -- Create a stored procedure. CREATE OR REPLACE PROCEDURE proc_test() AS -- Declare and define a sub-stored-procedure. PROCEDURE proc_sub() IS BEGIN dbe_output.put_line('this is subpragram'); END; BEGIN dbe_output.put_line('this is a procedure'); -- Call a sub-stored-procedure in an execution block. proc_sub(); END; / -- Call a stored procedure externally. BEGIN proc_test; END; / -- Output the result. this is a procedure this is subpragram ANONYMOUS BLOCK EXECUTE

Declaration and Definition Rules of Nested Subprograms

• Nested subprograms cannot be declared or defined repeatedly. Overloading is not supported.
• The identifier of a nested subprogram cannot be the same as the variable name or keyword.
• Declaration before definition is supported. The definition of subprogram declared first must be found in the subsequent declaration block.

Calling Rules of Nested Subprograms

• Nested subprograms can call themselves to achieve recursive calling effects.
• Nested subprograms can call upper-layer subprograms.
• Nested subprograms can call locally declared lower-layer subprograms, but cannot call nested subprograms in lower-layer subprograms.
• Nested subprograms can call subprograms declared earlier than themselves at the same layer.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

-- Call itself.
CREATE OR REPLACE PROCEDURE proc_test(var1 int) AS
    PROCEDURE proc_sub(var2 int) IS
    BEGIN
        dbe_output.put_line('var = ' || var2);
        IF var2 > 1 THEN
            proc_sub(var2 - 1);
        END IF;
    END;
BEGIN
    proc_sub(var1);
END;
/
BEGIN
    proc_test(3);
END;
/
-- Output the result.
var = 3
var = 2
var = 1

-- Call the upper-layer subprogram.
CREATE OR REPLACE PROCEDURE proc_test(var1 int) AS
    PROCEDURE procsub_1(var2 int) IS
    BEGIN
	proc_test(var2 - 1);
    END;
BEGIN
    dbe_output.put_line('proc_test var1 = ' || var1);
	IF var1 > 1 THEN
	      procsub_1(var1);
	END IF;
END;
/
BEGIN
    proc_test(3);
END;
/
-- Output the result.
proc_test var1 = 3
proc_test var1 = 2
proc_test var1 = 1

-- Call the lower-layer subprogram declared locally.
CREATE OR REPLACE PROCEDURE proc_test() AS
    PROCEDURE proc_sub1 IS
        procedure proc_sub2 IS
        BEGIN
            dbe_output.put_line('--this is subpragram2 begin');
            dbe_output.put_line('--this is subpragram2 end');
        END;
    BEGIN
	dbe_output.put_line('this is subpragram1 begin');
	proc_sub2();
	dbe_output.put_line('this is subpragram1 end');
    END;
BEGIN
    dbe_output.put_line('this is a procedure begin');
    proc_sub1();
    dbe_output.put_line('this is a procedure end');
END;
/
BEGIN
    proc_test;
END;
/
-- Output the result.
this is a procedure begin
this is subpragram1 begin
--this is subpragram2 begin
--this is subpragram2 end
this is subpragram1 end
this is a procedure end

Variables of Nested Subprograms

Variable types include basic types, record types, TABLE OF types, cursor types, and varray types supported by PL/SQL.

• Accessible variables:
  • Variable declared by itself.
  • Variable declared by the upper-layer subprogram.
• Variable access rules:
  • If a variable does not contain a qualifier, the variable is first searched in the program. If the variable name does not exist, the variable is searched at the upper layer, and so on.
  • If a variable has a qualifier, it is searched in the area of the qualifier. Currently, only one qualifier can be called.