淘宝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)?
Execution Plan
----------------------------------
? ?0? ? ? SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=999 Bytes=51948)
? ?1? ? 0? ?NESTED LOOPS (Cost=430 Card=999 Bytes=51948)
? ?2? ? 1? ? ?VIEW OF 'VW_NSO_1' (Cost=11 Card=999 Bytes=12987)
? ?3? ? 2? ? ? ?SORT (UNIQUE)
? ?4? ? 3? ? ? ? ?COUNT (STOPKEY)
? ?5? ? 4? ? ? ? ? ?COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2NUMLIST'
? ?6? ? 5? ? ? ? ? ? ?TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
? ?7? ? 1? ? ?TABLE ACCESS (BY INDEX ROWID) OF 'BMW_USERS' (Cost=1 Card=1 Bytes=39)
? ?8? ? 7? ? ? ?INDEX (UNIQUE SCAN) OF 'UK_BMW_USERS_USERID' (UNIQUE)
Statistics
----------------------------------
? ? ? ? ? 0? recursive calls
? ? ? ? ? 0? db block gets
? ? ? ? ? 16? consistent gets
? ? ? ? ? 0? physical reads
? ? ? ? ? 0? redo size
……