淘宝Str2varlist与str2numlist 介绍
?
CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;?
create or replace type vartabletype as table of varchar2(1000);?
create or replace function str2numList( p_string in varchar2 ) return numTableTypeas v_str long default p_string || ','; v_n number; v_data numTableType := numTableType();begin loop v_n := to_number(instr( v_str, ',' )); exit when (nvl(v_n,0) = 0); v_data.extend; v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 ); end loop; return v_data;end;?create or replace function str2varList( p_string in varchar2 ) return VarTableType as v_str long default p_string || ','; v_n varchar2(2000); v_data VarTableType := VarTableType(); begin loop v_n :=instr( v_str, ',' ); exit when (nvl(v_n,0) = 0); v_data.extend; v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 ); end loop; return v_data;end;?
?
SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth from table(STR2NUMLIST(:bind0)) a, bmw_users u where u.user_id = a.column_value;SELECT /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth from bmw_users u where user_id in (select * from table(STR2NUMLIST(:bind0)) a);
SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id from bmw_users where user_id in (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE) FROM dual) WHERE rownum<1000);?SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id from table(STR2NUMLIST('1,2,3')) a, bmw_users u where u.user_id = a.column_value?SQL> SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id from bmw_users where user_id in (SELECT * FROM THE (SELECT CAST(STR2NUMLIST('1,2,3') AS NUMTABLETYPE) FROM dual) WHERE rownum<1000)?
