linux list 类型转换,[20120110]字符串转换成列表(Inlist的绑定优化)

在实际应用中,经常会遇到带in的子查询,许多采用直接拼接的方式,导致大量硬分析,在piner的高可用一书中建立二个类型以及函数来解决这个问题.代码如下:

/* Formatted on 2009/01/16 09:48 (Formatter Plus v4.8.8) */

CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER

/

CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2)

RETURN numtabletype

AS

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 TYPE vartabletype AS TABLE OF VARCHAR2 (4000)

/

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 * from table (cast(STR2VARLIST(:st2) as vartabletype));

在11G下,可以通过很轻松解决这个问题:

例子:

SQL> var a varchar2(60);

SQL> exec :a := '10,20';

PL/SQL procedure successfully completed.

SQL> select * from dept where deptno in  (select (column_value).getnumberval() from xmltable(:a));

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

SQL> exec :a := '"SALES","RESEARCH"';

PL/SQL procedure successfully completed.

SQL> select * from dept where dname in  (select (column_value).getstringval() from xmltable(:a));

DEPTNO DNAME          LOC

---------- -------------- -------------

20 RESEARCH       DALLAS

30 SALES          CHICAGO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714644/，如需转载，请注明出处，否则将追究法律责任。