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

Question

Нужно сделать процедуру, которая бы находила дублирующие записи в таблице и удаляла бы их, но перед этим, записи вставила бы во временную таблицу.

Возможно ли вообще с помощью динамического SQL сделать так, чтобы в одной процедуре сделать все?

Вот, что у меня получилось, но так не работает пока не созданы таблицы вне процедуры:

create or replace procedure delete_doubles is
begin    

  execute immediate 'create table new_agreements as 
                     select *
                     from agreements
                     where rownum < 20001';
  
  execute immediate 'create table tmp_duplicates(
                     count_dupl integer,
                     agreement_number varchar2(200)
                     )';                       

  for i in (select count(na.agreement_id), na.agreement_number, min(rowid) as rid
            from new_agreements na
            group by na.agreement_number 
            having count(na.agreement_id) > 1) loop
      
    insert into tmp_duplicates(count_dupl,
                               agreement_number)
    select count(na.agreement_id), na.agreement_number
            from new_agreements na
            group by na.agreement_number 
            having count(na.agreement_id) > 1;                               
      
    delete from excellent.new_agreements na 
    where i.rid != na.rowid          
          and na.agreement_number = i.agreement_number; 
  end loop;    
end delete_doubles;

Answer 1

Нет, невозможно обратится статическим запросом к таблице, созданной динамически в одной и той же единице компиляции.

create or replace procedure proc as
begin
    execute immediate 'create table t (col int)';
    for r in (select * from t) loop null;
    end loop;
end;
/
LINE/COL  ERROR
--------- -------------------------------------------------------------
4/15      PL/SQL: SQL Statement ignored
4/29      PL/SQL: ORA-00942: table or view does not exist

Компилятор видит символьное значение динамического запроса (стр. 3), это пока только строка или то, что будет послано в SQL контекст во время выполнения процедуры. Далее следует статический запрос (стр. 4), который PL/SQL компилятор сразу же посылает в SQL контекст, чтобы полученый обратно, полностью подготовленный байт-код запроса, сохранить вместе с скомпилированным кодом процедуры. Естественно, SQL парсер не видит ещё не созданную таблицу и возвращает ошибку.

Как решение, можно воспользоваться временными таблицами в памяти:

create or replace procedure proc as
    type rec is record (id int, memo varchar2 (96));
    type tab is table of rec;
    temptab tab; 
begin
    select rownum id, 'memo '||rownum memo bulk collect into temptab 
    from dual connect by level<=1e5;
    dbms_output.put_line ('temptab rowcount='||temptab.count);
    dbms_output.put_line ('temptab 2nd row: '||temptab(2).id||'|'||temptab(2).memo); 
end;
/
exec proc

temptab rowcount=100000  
temptab 2nd row: 2|memo 2

Answer 2

Возможно ли вообще с помощью динамического SQL сделать так, чтобы в одной процедуре сделать все?

Несмотря на бытующее мнение, что создавать таблицы и другие объекты БД в PL/SQL коде это плохая практика, иногда (редко в продуктивной среде, чаще в тестовой) можно воспользоваться динамическим SQL и выполнить всё в одном блоке PL/SQL кода.

Когда это может быть полезным?

В этом случае PL/SQL код будет выполнен точно также, как любое клиентское приложение написанное на других ЯП, например, Python, Java и др., которые по своей природе могут использовать только динамический SQL. Но для приложения на PL/SQL не надо заботится об установке и поддержке окружения для компиляции или запуска кода на других ЯП. Будет полностью готовое к запуску кросс-платформенное приложение.

В воспроизводимом примере приведены наиболее полно все случаи использования динамического SQL с таблицей созданной в этом же блоке PL/SQL кода:

create table tab (id, code) as 
    select  rownum, 'code-'||to_char(rownum,'FM099') 
    from dual connect by level<=10
/
create or replace procedure proc as
    type rowarr is table of tab%rowtype;
    rows rowarr; 
    rc sys_refcursor;
begin 
    execute immediate q'[
        create table tmptab as
        select * from tab where 1=0]';     
    execute immediate q'[
        insert into tmptab select * from tab order by id fetch first 3 rows only]';
    dbms_output.put_line ('inserted into tmptab '||sql%rowcount||' row(s)');     
    execute immediate q'[
        insert into tmptab values (123, 'code-123')]';
    dbms_output.put_line ('inserted into tmptab '||sql%rowcount||' row(s)');     
    
    execute immediate q'[select * from tmptab]' bulk collect into rows;    
    dbms_output.put_line ('fetched from tmptab '||rows.count||' row(s)');
    --или
    open rc for q'[select * from tmptab]';
    fetch rc bulk collect into rows;    
    dbms_output.put_line ('fetched from tmptab '||rows.count||' row(s)');
    for i in 1..rows.count loop
        dbms_output.put_line ('tmptab row('||i||')='||rows(1).id||' '||rows(i).code);
    end loop;    
    
    execute immediate q'[drop table tmptab purge]';
end proc;
/

Запуск и результат выполнения:

exec proc

inserted into tmptab 3 row(s)
inserted into tmptab 1 row(s)
fetched from tmptab 4 row(s)
fetched from tmptab 4 row(s)
tmptab row(1)=1 code-001
tmptab row(2)=2 code-002
tmptab row(3)=3 code-003
tmptab row(4)=123 code-123

БЛОГ НА HUSL

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

Ответы (2 шт):