[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/，如需轉載，請註明出處，否則將追究法律責任。