在biti_rainy的这篇文章http://blog.itpub.net/post/330/18893中，讨论了让查询依照in列表顺序进行排序的方法。

但该方法有一个问题，就是依赖于查询时的执行计划。（因此biti在他的查询中加入了hint来强制使用nl方式）

我们通过加入一个序号域的方式，加以改进，从而避免对执行计划的依赖：

1. 把numTableType定义成一个如下的table:

create or replace type numRecType as object(id number, num number);
create or replace type numTableType as table of numRecType;

2.在str2numList函数中，将序号赋给id

3. 在查询中加入order by id

由于查询语句中显式的使用了order by, 从而有效的避免了依赖执行计划。

完整的脚本及测试：

scott@O9I.US.ORACLE.COM> create or replace type numRecType as object(id number,
num number);
  2  /

类型已创建。

scott@O9I.US.ORACLE.COM>
scott@O9I.US.ORACLE.COM> create or replace type numTableType as table of numRecT
ype;
  2  /

类型已创建。

scott@O9I.US.ORACLE.COM> create or replace function str2numList( p_string in var
char2 ) return numTableType
  2  as
  3          v_str long default p_string || ',';
  4          v_n number;
  5          v_data numTableType := numTableType();
  6  begin
  7          loop
  8          v_n := to_number(instr( v_str, ',' ));
  9          exit when (nvl(v_n,0) = 0);
 10          v_data.extend;
 11          v_data(v_data.count) := numrectype(v_data.count, ltrim(rtrim(substr
(v_str,1,v_n-1))));
 12          v_str := substr( v_str, v_n+1 );
 13          end loop;
 14          return v_data;
 15  end;
 16  /

函数已创建。

scott@O9I.US.ORACLE.COM>


scott@O9I.US.ORACLE.COM> select empno, ename from emp where empno in (7839,7844,
7876,7782,7788,7369,7499,7521,7566,7654,7698);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

已选择11行。

scott@O9I.US.ORACLE.COM>   select   b.empno ,b.ename
  2  from table (str2numList('7839,7844,7876,7782,7788,7369,7499,7521,7566,7654,
7698') ) a,
  3  emp b
  4  where a.num= b.empno
  5  order by a.id;

     EMPNO ENAME
---------- ----------
      7839 KING
      7844 TURNER
      7876 ADAMS
      7782 CLARK
      7788 SCOTT
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE

已选择11行。

scott@O9I.US.ORACLE.COM>

ITPub博客

让查询出来的数据和in中列表顺序一致 - 对biti一篇文章的改进。

最新文章