Haw can I make a sub procedure inside a parent function in a Package in PL/SQL?

Question

I want to use a code like this:

Create or Replace PACKAGE BODY MyPackage
... declarations, other functions ...

Function Parent(param1, param2)
        Return Boolean
    As
        v_Pcs Number Default 0;
    Begin
        SubProc(paramA, paramB)
            v_MyNestedtable.EXTEND(1);
            v_MyNestedtable(v_MyNestedtable.Count).P_A := ParamA;
            v_MyNestedtable(v_MyNestedtable.Count).P_B := ParamB;
        End SupProcedure;

        Select Count(*) Into v_Pcs From myTableA Where...;
            If v_Pcs> 0 Then
                SubProc(param3, param4);
                v_Pcs := 0;
            End If;
        Select Count(*) Into v_Pcs From myTableB Where...;
            If v_Pcs> 0 Then
                SubProc(param5, param6);
                v_Pcs := 0;
            End If;
        
        If v_MyNestedtable.Count > 0 Then
            For 1..v_MyNestedtable.Count
            Loop
                dbms_output.Put_line(v_MyNestedtable(i).P_A||', '||
                                     v_MyNestedtable(i).P_B);
            End Loop;
            Return False;
        Else
            Return True;
    End Parent;
...
End MyPackage;

My code works well when the SupProcedure is outside of the Parent function and they are on the same level. I only call this SupProcedure from Parent function. So I want to make my code better appearence with declare a sub/nested procedure. How can I make it?

Answer 1

With exactly the same syntax as you would any other PROCEDURE. However, you need to put it in the DECLARE section before the BEGIN:

CREATE OR REPLACE PACKAGE BODY MyPackage IS
  TYPE MyRecord IS RECORD( P_A NUMBER, P_B NUMBER );
  TYPE MyNestedTable IS TABLE OF MyRecord;

  FUNCTION Parent(
    param1 IN NUMBER,
    param2 IN NUMBER
  ) RETURN BOOLEAN
  AS
    found BOOLEAN := FALSE;
    v_MyNestedtable MyNestedTable := MyNestedTable();

    PROCEDURE SubProc(
      paramA IN NUMBER,
      paramB IN NUMBER
    )
    IS
    BEGIN
      v_MyNestedtable.EXTEND(1);
      v_MyNestedtable(v_MyNestedtable.Count).P_A := ParamA;
      v_MyNestedtable(v_MyNestedtable.Count).P_B := ParamB;
    End SubProc;
  BEGIN
    -- ... do stuff
    SubProc(3, 4);
    SubProc(5, 6);

    FOR i in 1 .. v_MyNestedTable.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        v_MyNestedTable(i).P_A||', '||v_MyNestedTable(i).P_B
      );
      found := TRUE;
    END LOOP;

    RETURN found;
  END Parent;
END MyPackage;
/

---

However, you can write your procedure with a single query and a cursor loop:

CREATE OR REPLACE PACKAGE BODY MyPackage IS
  FUNCTION Parent(
    param1 IN NUMBER,
    param2 IN NUMBER
  ) RETURN BOOLEAN
  AS
    found BOOLEAN := FALSE;
  BEGIN
    FOR rw IN (
      SELECT 'param3' AS p_a, 'param4' AS p_b
      FROM   MytableA
      WHERE  1 = 1
      HAVING COUNT(*) > 0
    UNION ALL
      SELECT 'param5', 'param6'
      FROM   MytableB
      WHERE  1 = 1
      HAVING COUNT(*) > 0
    )
    LOOP
      DBMS_OUTPUT.PUT_LINE(rw.P_A||', '||rw.P_B);
      found := TRUE;
    END LOOP;

    RETURN found;
  END Parent;
End MyPackage;
/

(Note: you did not declare the param variables so I have replaced them with literals.)

fiddle