尝试在oraclesql中动态创建IN查询时出现Parenthis错误

Question

在sql developer中试图在Pl/sql中创建一个选择查询，必须在oracle sql developer中将姓氏变量提示出来，然后会得到结果

SELECT DISTINCT 
t1.fullname, t2.salary
FROM TABLE1 t1  
JOIN TABLE2 t2  ON t1.ID = t2.employeeID
WHERE t1.city = 'denver' 
AND t1.surname IN ( select REPLACE ( &surname, ',' , ''',''' ) from dual )
AND t2.payment = 'cheque';

&姓氏将作为输入示例-

'KAHN,GIGGS,BANKS'

它将成为'KAHN','GIGGS','BANKS'

但其给出的误差为ORA-00907: missing right parenthesis

如果它不能以这种方式实现，请建议任何其他方式，但在这种方式下，我必须使用&姓氏变量，以便将其输入到查询

Answer 1

实现这一点的最简单方法(在SQL Developer中可复制(：

create table t1 (surname) as
select trim (column_value) from xmlTable ('"KAHN","GIGGS","BANKS"')
/
define surname='KAHN,GIGGS,BANKS'
select * 
from t1
where surname in (
    select trim (column_value) 
    from xmlTable(('"'||replace ('&surname',',','","')||'"'))
    )
/

结果：

SURNAME
--------
KAHN
GIGGS
BANKS

Answer 2

用like而不是in怎么样？

SELECT DISTINCT 
t1.fullname, t2.salary
FROM TABLE1 t1  
JOIN TABLE2 t2  ON t1.ID = t2.employeeID
WHERE t1.city = 'denver' 
AND &surname like '%'||t1.surname||'%'
AND t2.payment = 'cheque';

Bobby

Answer 3

您检查了这个select REPLACE ( &surname, ',' , ''',''' ) from dual结果吗？它将是一个类似'KAHN','GIGGS','BANKS'的字符串，这只是一个值，而不是一个列表。

一种方式如下：

with t(res,lev) as (
               select trim(regexp_substr(:surname,'[^,]+', 1, 1 )) res, 1 as lev
                 from dual
                where regexp_substr(:surname, '[^,]+', 1, 1) is not null
                union all           
                select trim(regexp_substr(:surname,'[^,]+', 1, lev+1) ) res,  lev+1 as lev
                  from t
                  where regexp_substr(:surname, '[^,]+', 1, lev+1) is not null
            )
    SELECT DISTINCT 
    t1.fullname, t2.salary
    FROM TABLE1 t1  
    JOIN TABLE2 t2  ON t1.ID = t2.employeeID
    WHERE t1.city = 'denver' 
    AND t1.surname IN ( 
      select res
          from t
    )
    AND t2.payment = 'cheque';

您应该在提示中输入：KAHN,GIGGS,BANKS