IN條件下的綁定變量做法

  create or replace type myTableType as table of number;

Type created.

 create or replace function str2tbl( p_str in varchar2 ) return myTableType
    as
        l_str   long default p_str || ',';
        l_n        number;
        l_data    myTableType := myTabletype();
    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;

Function created.

 

 select * from all_users
    where user_id in ( select *
      from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
 

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99