Блог

У меня есть тип таблицы, объявленный как

 TYPE t_table IS TABLE OF VARCHAR2(15) INDEX BY PLS_INTEGER;
  

Я пытаюсь использовать ее в процедуре с инструкцией select, но она не работает:

 procedure get_something (
    p_in_list  IN  t_table,
    p_out_list OUT t_table
)
IS
BEGIN
    SELECT item 
    BULK collect into p_out_list 
    from my_table 
    where myrow in (select * from table(p_in_list));
END get_something;
  

Как я могу использовать это в select … в инструкции?

Это было протестировано в 19.0. У меня нет более ранних версий для тестирования прямо сейчас, но я думаю, что для этого потребуется как минимум 12.1.

Во-первых, если вам нужен тип, который должен быть ассоциативным массивом (‘index by’), он должен быть в спецификации пакета:

 create or replace package demo_pkg
as
    type t_table is table of varchar2(15) index by pls_integer;
end demo_pkg;
  

Тогда SQL сможет это увидеть:

 declare
    -- type t_table is table of varchar2(15) index by pls_integer;
    subtype t_table is demo_pkg.t_table;

    from_list t_table;
    to_list   t_table;

    procedure get_something
        ( p_in_list  in  t_table
        , p_out_list out t_table )
    is
    begin
        select dummy bulk collect into p_out_list
        from   dual
        where  dummy in (select * from table(p_in_list));
    end get_something;
begin
    from_list(1) := 'X';
    from_list(2) := 'Y';
    from_list(3) := 'Z';

    get_something(from_list, to_list);
end;
  

Начиная с 18c, вы можете заполнить массив декларативно, используя квалифицированное выражение, например:

 from_list t_table := demo_pkg.t_table(1 => 'X', 2 => 'Y', 3 => 'Z');
  

или

 get_something
( demo_pkg.t_table(1 => 'X', 2 => 'Y', 3 => 'Z')
, to_list );
  

Некоторые из этих ограничений связаны с тем, что ассоциативные массивы на самом деле не подходят для SQL-запросов естественным образом, и добавление их поддержки заняло некоторое время. Если вы объявляете t_table как обычную вложенную таблицу, она должна работать в более ранней версии:

 create or replace package demo_pkg
as
    type t_table is table of varchar2(15);
end demo_pkg;
  

или создайте ее как отдельный объект SQL:

 create or replace type t_table as table of varchar2(15);
  

Это также делает возможным member of построение:

 declare
    from_list t_table := t_table('X','Y','Z');
    to_list   t_table;

    procedure get_something
        ( p_in_list  in  t_table
        , p_out_list out t_table )
    is
    begin
        select dummy bulk collect into p_out_list
        from   dual
        where  dummy member of p_in_list;
    end get_something;
begin
    get_something(from_list, to_list);
end;
  

member of работает только с коллекциями «вложенных таблиц», а не с ассоциативными массивами или переменными массивами. Я никогда не смогу понять смысл переменных, если только ограничение размера не настолько полезно для вашей бизнес-логики, что вы можете жить со всей утраченной функциональностью.

Вот один из вариантов; посмотрим, поможет ли это.

Пример таблицы и тип:

 SQL> create table my_table as
  2    select dname item, loc myrow from dept;

Table created.

SQL> create or replace type t_table as varray(20) of varchar2(20);
  2  /

Type created.
  

Процедура:

 SQL> create or replace procedure
  2    get_something (
  3      p_in_list  in  t_table,
  4      p_out_list out t_table
  5    )
  6    is
  7    begin
  8      select item
  9      bulk collect into p_out_list
 10      from my_table
 11      where myrow in (select * from table(p_in_list));
 12    end get_something;
 13  /

Procedure created.

SQL>
  

Тестирование:

 SQL> set serveroutput on
SQL> declare
  2    l_in  t_table;
  3    l_out t_table;
  4  begin
  5    l_in := t_table();
  6    l_in.extend(2);
  7    l_in(1) := 'DALLAS';
  8    l_in(2) := 'NEW YORK';
  9
 10    get_something(l_in, l_out);
 11
 12    for i in 1 .. l_out.count loop
 13      dbms_output.put_line(l_out(i));
 14    end loop;
 15  end;
 16  /
RESEARCH
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>