Использование типа объекта SQL в процедуре и использование их в функции конвейера

Здесь я пытаюсь использовать функцию конвейера, которая будет принимать коллекцию в качестве входных данных и возвращать коллекцию после некоторой проверки, которую я использую для вставки данных в таблицу;

Вот несколько тестовых объектов, которые я создал, чтобы объяснить свою проблему.

create table tst_cri_sdb (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));  


insert into TST_CRI_SDB values (100, to_date('13032019','ddmmyyyy'), 'EUR', 'STM');  
insert into TST_CRI_SDB values (101, to_date('14032019','ddmmyyyy'), 'GBP', 'CTM');  
insert into TST_CRI_SDB values (102, to_date('15032019','ddmmyyyy'), 'USD', 'STM');  
insert into TST_CRI_SDB values (103, to_date('16032019','ddmmyyyy'), 'INR', 'CTM');  
insert into TST_CRI_SDB values (104, to_date('17032019','ddmmyyyy'), 'EUR', 'STM');  


create type tst_rec as object (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));  


create type tst_table_rec as table of tst_rec;  


create table sdb_gpcs (curr varchar2(3), ctmstm varchar2(5), goca number, cust_grp varchar2(30));  


insert into sdb_gpcs values ('EUR','CTM', 100345 ,'A1105');  
insert into sdb_gpcs values ('EUR','CTM', 200345 ,'A4405');  
insert into sdb_gpcs values ('EUR','STM', 300345 ,'A3305');  
insert into sdb_gpcs values ('USD','CTM', 500345 ,'A5505');  
insert into sdb_gpcs values ('USD','STM', 600345 ,'A6605');  
insert into sdb_gpcs values ('USD','STM', 700345 ,'A7705');  


select * from sdb_gpcs where curr = 'EUR' and ctmstm = 'CTM';  


create table tst_cri_plus_sdb (deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));  


create type tst_plus_rec as object(deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));  


create type tst_plus_table_rec as table of tst_plus_rec;  


create or replace function get_plus_sdb_w ( p_tab IN tst_table_rec)  
return tst_plus_table_rec PIPELINED  
is  

l_rec tst_plus_rec;  

begin  

  for i in 1..p_tab.count  
  loop  

    for j in (select * from sdb_gpcs)  
    loop  

      l_rec := tst_plus_rec(p_tab(i).icri, p_tab(i).datesitu, j.goca, j.cust_grp ,p_tab(i).curr, p_tab(i).ctmstm);  

      PIPE row(l_rec);  

    end loop;  

  end loop;  

end;   


CREATE or replace procedure tst_insert  
is  

  cursor c1 is select * from tst_cri_sdb;  

  l_tab tst_table_rec := tst_table_rec();  

  l_tab_plus tst_plus_table_rec := tst_plus_table_rec();  

begin  


  for i in c1j  
  loop  

    l_tab.extend;  
    l_tab(l_tab.last) := tst_table_rec(tst_rec(i.icri, i.datesitu, i.curr, 
   i.ctmstm));  

  end loop;    

  SELECT *  
  bulk collect into l_tab_plus  
  FROM   TABLE(get_plus_sdb_w(l_tab));  

  forall idx IN INDICES OF l_tab_plus  
   insert into tst_cri_plus_sdb values l_tab(idx);  

end;

Моя идея здесь состоит в том, чтобы собрать все данные таблицы tst_cri_sdb в коллекцию, а затем передать эту коллекцию функции конвейера, которая снова вернет коллекцию, чтобы я мог собрать ее и вставить в таблицу tst_cri_plus_sdb.

Помогите мне собрать данные в процедуре в коллекцию, а также с функцией конвейера.

Пожалуйста, спросите меня больше информации, если требуется.

Я использую --

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 — 64-битная производственная версия

можно описать в чем проблема? или какой у вас вопрос?

hotfix 22.05.2019 13:12

Почему вы вообще хотите использовать типы объектов — что они добавляют к существующим типам строк таблицы?

Alex Poole 22.05.2019 13:38

@hotfix - я просто хочу передать коллекцию в конвейерную функцию, которая вернет строку, чтобы я мог ее массово собрать, а затем вставить в таблицу. Я не могу вставить сюда настоящий код, так как он очень длинный.

mradul 22.05.2019 13:47

@Alex - я тип объекта, потому что я хочу заполнить коллекцию в процедуре, а затем передать ее функции конвейера для дальнейшей проверки данных.

mradul 22.05.2019 13:48
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
0
4
46
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

В вашей процедуре есть пара простых ошибок; имя курсора c1, но позже вы ссылаетесь на него как c1j, и вы пытаетесь назначить весь экземпляр объекта таблицы как элемент таблицы, а не просто запись:

l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);

Более серьезная проблема заключается в том, что вы смешиваете объектные и собственные типы. Когда вы удаляете коллекцию с помощью предложения table(), вы возвращаете несколько столбцов, а не один тип объекта; поэтому вам придется реконструировать свой объект:

  select tst_plus_rec(deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  bulk collect into l_tab_plus
  from table(get_plus_sdb_w(l_tab));

и тогда вставка forall должна будет вернуться к каждому атрибуту объекта:

  forall idx IN INDICES OF l_tab_plus
  insert into tst_cri_plus_sdb (deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  values (l_tab_plus(idx).deal_id, l_tab_plus(idx).datesitu, l_tab_plus(idx).acc_code,
    l_tab_plus(idx).acca_cust_grp, l_tab_plus(idx).curr, l_tab_plus(idx).ctmstm);

Итак, складываем их вместе:

create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- no need to initialise this one as bulk collect will replace it
--  l_tab_plus tst_plus_table_rec := tst_plus_table_rec();
  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  select tst_plus_rec(deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  bulk collect into l_tab_plus
  from table(get_plus_sdb_w(l_tab));

  forall idx IN INDICES OF l_tab_plus
  insert into tst_cri_plus_sdb (deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  values (l_tab_plus(idx).deal_id, l_tab_plus(idx).datesitu, l_tab_plus(idx).acc_code,
    l_tab_plus(idx).acca_cust_grp, l_tab_plus(idx).curr, l_tab_plus(idx).ctmstm);
end;
/
exec tst_insert;

select * from tst_cri_plus_sdb;

   DEAL_ID DATESITU     ACC_CODE ACCA_CUST_ CUR CTMST
---------- ---------- ---------- ---------- --- -----
       100 2019-03-13     100345 A1105      EUR STM  
       100 2019-03-13     200345 A4405      EUR STM  
       100 2019-03-13     300345 A3305      EUR STM  
       100 2019-03-13     500345 A5505      EUR STM  
       100 2019-03-13     600345 A6605      EUR STM  
       100 2019-03-13     700345 A7705      EUR STM  
       101 2019-03-14     100345 A1105      GBP CTM  
       101 2019-03-14     200345 A4405      GBP CTM  
...
       104 2019-03-17     600345 A6605      EUR STM  
       104 2019-03-17     700345 A7705      EUR STM  

30 rows selected. 

Вам не нужна промежуточная коллекция с bulk collect и forall, вы можете вставить ее напрямую:

create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- you don't need this variable at all now
--  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/

который получает тот же результат. И вы можете избежать цикла заполнения, изменив его на массовый сбор:

create or replace procedure tst_insert
is
  l_tab tst_table_rec;
begin
  select tst_rec(icri, datesitu, curr, ctmstm)
  bulk collect into l_tab
  from tst_cri_sdb;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/

дб <> рабочий пример


В более поздних версиях Oracle вы могли использовать сделать это все в пакете без каких-либо типов объектов уровня схемы; но поскольку вы используете 11g, это не сработает (предложение table() выдаст PLS-00642).

Удивительно, я хочу, чтобы когда-нибудь я приобрел знания, как вы действительно прибили каждую возможность сценария. Однако для последнего я не смогу массово собирать, так как для каждой строки курсора есть другая проверка. Еще раз большое спасибо за подробный ответ, пусть живет дольше и здоровее :)

mradul 22.05.2019 14:41

Другие вопросы по теме