在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博客 ” ,链接:http://blog.itpub.net/32/viewspace-184184/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/32/viewspace-184184/