ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120110]字符串转换成列表(Inlist的绑定优化)

[20120110]字符串转换成列表(Inlist的绑定优化)

原创 Linux操作系统 作者:lfree 时间:2012-01-10 09:32:06 0 删除 编辑
在实际应用中,经常会遇到带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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2348
  • 访问量
    6090851