Is it possible to pass an array of elements to the function as an argument and use it in a SELECT query?

For example (pseudo-code):

 function get_by_list( IDS массив numeric ) return таблица_типа_Х pipelined is begin for rw in ( begin select что-то from таблица WHERE ID in IDS; ) loop pipe row (rw); end loop; end get_by_list;

    2 answers 2

    You can somehow implement this:

     create or replace type num_list_type as table of number; / create type item_row as object (id number, item varchar2(32)); / create type items_type as table of item_row; / -- тестовая таблица create table items as select level as id, 'item no ' || level as item from dual connect by level<=7; / create or replace function getbyList(num_list num_list_type) return items_type pipelined is begin for one in ( select item_row(id, item) item from items where id in (select * from table(num_list)) ) loop pipe row(one.item); end loop; end getByList; / select t.* from table(getbyList(num_list_type(1,5))) t union all select count(1), ' total items' from items ;

    Conclusion:

      ID ITEM ---------- -------------------------------- 1 item no 1 5 item no 5 7 total items

      You can write extra. function to convert a CSV row to a table:

       create or replace type admin.strtable as table of varchar2(1000); / create or replace function admin.str2tbl ( p_str in varchar2 ) return strtable as l_str long default p_str || ','; l_n number; l_data strtable := strtable(); begin loop l_n := instr( l_str, ',' ); exit when (nvl(l_n,0) = 0); l_data.extend; l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1))); l_str := substr( l_str, l_n+1 ); end loop; return l_data; end; /

      Test:

       SQL> select * from x; I ---------- 11 22 33 44 4 rows selected. SQL> select * from x where i in (select * from TABLE(admin.str2tbl('11,33'))); I ---------- 11 33 2 rows selected.
      • It was interesting just if I could pass a sub-request as an argument to a function, but now I began to think that this is something not quite right ... - Batanichek