我正在尝试编写一个简单的查询,其中要求使用替换变量,该变量可用于为用于过滤查询的列输入多个可能的值。
要求是生成以下查询
select
CONTRACT,
ORDER_NO,
CUSTOMER_NO
from CUSTOMER_ORDER
where state='Picked'
and contract in ('ABC','DEF')
但是需要在运行时通过替换变量输入合约值。我正在处理只能编写静态 SQL 查询(“select ..from..where..”)的限制,并且不能在 pl sql 块中编写动态代码。
所以,我尝试的是以下内容,
select
CONTRACT,
ORDER_NO,
CUSTOMER_NO
from CUSTOMER_ORDER_JOIN
where contract in (select '''' || REPLACE('&CONTRACT',';',''',''') || '''' from dual)
当出现替换提示时,我输入 ABC;DEF
但这似乎不起作用。虽然当我单独运行以下时,
select '''' || REPLACE('&CONTRACT',';',''',''') || '''' from dual
我得到 'ABC','DEF' 结果。
为什么这不起作用?有没有办法达到我想要的结果?
谢谢
一种选择是将这些值拆分为行并将它们用作子查询。
基于 Scott 的 EMP 表的示例:
SQL> select ename, job, sal from emp;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 920
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 10000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
14 rows selected.
SQL> select ename, job, sal
2 from emp
3 where ename in (select regexp_substr(replace(q'[&&par_ename]', chr(39), ''), '[^,]+', 1, level)
4 from dual
5 connect by level <= regexp_count(q'[&&par_ename]', ',') + 1
6 );
Enter value for par_ename: 'SMITH','ALLEN'
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 920
ALLEN SALESMAN 1600
SQL>
或者,使用sys.odcivarchar2list
:
SQL> select ename, job, sal
2 from emp
3 where ename in (select column_value
4 from table(sys.odcivarchar2list(&par_ename))
5 );
Enter value for par_ename: 'SMITH','ALLEN'
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 920
ALLEN SALESMAN 1600
SQL>
它也可以在没有单引号的情况下工作:
SQL> Select ename, job, sal
2 from emp
3 where ename in (select regexp_substr(replace(q'[&&par_ename]', chr(39), ''), '[^,]+', 1, level)
4 from dual
5 connect by level <= regexp_count(q'[&&par_ename]', ',') + 1
6 );
Enter value for par_ename: SMITH,ALLEN,KING
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 920
ALLEN SALESMAN 1600
KING PRESIDENT 10000
SQL>
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句