oracle-databaseplsqlbulkinsertdatabase-cursortable-functions

Insert into table collection type in table function without using explicit cursor in PL/SQL


I write the following code in PL/SQL and it works:

declare 
type deliveryStat_o IS record  (
       warehouseName varchar2(20), shipMode char(30), thirty_days number, sixty_days number,
        ninety_days number, oneTwenty_days number, veryLate number
   );
type deliveryStat_t is TABLE OF deliveryStat_o;

statTable deliveryStat_t;

begin

        SELECT w_warehouse_name, sm_type, 1 AS thirty_days, 1 AS sixty_days, 1 AS ninety_days, 
               1 AS oneTwenty_days, 1 AS veryLateDelivery
        bulk collect into statTable
        FROM   catalog_sales, warehouse, ship_mode, date_dim 
        WHERE   cs_ship_date_sk = d_date_sk 
               AND cs_warehouse_sk = w_warehouse_sk 
               AND cs_ship_mode_sk = sm_ship_mode_sk 
        GROUP  BY w_warehouse_name, 
                  sm_type ;
end;

How can I do this inside a table function that returns the nested collection statTable. I understand that this can probably be accomplished using explicit cursors; however, is it possible to do it without using a cursor?


Solution

  • For context, I'm starting with this as the base

    SQL> set serverout on
    SQL> declare
      2    type deliveryStat_o IS record  (
      3           empno number, ename varchar2(20)
      4       );
      5    type deliveryStat_t is TABLE OF deliveryStat_o;
      6
      7    statTable deliveryStat_t;
      8
      9  begin
     10
     11          SELECT empno, ename
     12          bulk collect into statTable
     13          FROM   emp;
     14    dbms_output.put_line('recs='||statTable.count);
     15  end;
     16  /
    recs=14
    
    PL/SQL procedure successfully completed.
    

    To convert that to allow a table function, then those types need to be SQL types, hence

    SQL> create or replace
      2  type deliveryStat_o as object (
      3  empno number, ename varchar2(20)
      4       );
      5  /
    
    Type created.
    
    SQL> create or replace
      2  type deliveryStat_t as table of deliveryStat_o
      3  /
    
    Type created.
    

    Now that this is done, the query must return a table of objects, so

    SQL> set serverout on
    SQL> declare
      2    statTable deliveryStat_t;
      3  begin
      4
      5          SELECT deliveryStat_o(empno, ename)
      6          bulk collect into statTable
      7          FROM   emp;
      8    dbms_output.put_line('recs='||statTable.count);
      9  end;
     10  /
    recs=14
    
    PL/SQL procedure successfully completed.
    

    which can now be easily converted to a table function

    SQL> create or replace
      2  function my_func return deliveryStat_t is
      3    statTable deliveryStat_t;
      4  begin
      5
      6          SELECT deliveryStat_o(empno, ename)
      7          bulk collect into statTable
      8          FROM   emp;
      9          return statTable;
     10  end;
     11  /
    
    Function created.
    
    SQL>  select * from my_func();
    
         EMPNO ENAME
    ---------- --------------------
          7369 SMITH
          7499 ALLEN
          7521 WARD
          7566 JONES
          7654 MARTIN
          7698 BLAKE
          7782 CLARK
          7788 SCOTT
          7839 KING
          7844 TURNER
          7876 ADAMS
          7900 JAMES
          7902 FORD
          7934 MILLER
    
    14 rows selected.
    

    If you're returning a LOT of rows, then consider a pipelined function instead to avoid the memory overhead of collecting all the rows into the nested table