Вставка в тип коллекции таблиц в табличной функции без использования явного курсора в PL/SQL

Я пишу следующий код на PL/SQL, и он работает:

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;

Как я могу сделать это внутри табличной функции, которая возвращает вложенную коллекцию statTable. Я понимаю, что этого, вероятно, можно добиться с помощью явных курсоров; однако можно ли это сделать без использования курсора?


oracle bulkinsert plsql database-cursor table-functions
person surabhi gupta    schedule 12.10.2020    source источник
comment
Непонятно, что вы делаете. Где вставить заявление в вашем сообщении? Что именно вы пытаетесь сделать? Пожалуйста, объясните немного больше, думая, что мы ничего не знаем.   -  person Ranagal    schedule 12.10.2020
comment
Если я вас понял, у вас есть два варианта: 1. создать тип объекта и использовать его вместо локального типа 2. Поместите тот же код, который вы показали, в пакет и сделайте функцию конвейерной.   -  person Sujitmohanty30    schedule 12.10.2020
comment
Кроме того, предоставьте описание таблицы (ddl), образцы тестовых данных в виде форматированного текста — никаких изображений и ожидаемого результата этих данных.   -  person Belayer    schedule 12.10.2020

Ответы (1)


arrow_upward
3
arrow_downward

Для контекста, я начинаю с этого в качестве основы

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.

Чтобы преобразовать это, чтобы разрешить табличную функцию, эти типы должны быть типами SQL, поэтому

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.

Теперь, когда это сделано, запрос должен вернуть таблицу объектов, поэтому

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.

которую теперь можно легко преобразовать в табличную функцию

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.

Если вы возвращаете МНОГО строк, рассмотрите вместо этого конвейерную функцию, чтобы избежать накладных расходов памяти на сбор всех строк во вложенную таблицу.

person Connor McDonald    schedule 13.10.2020