Question

ORACLE 如何将参数变量作为select in()的参数使用

CREATE OR REPLACE PROCEDURE TEST_1 AS
V_CUR_GOODS VARCHAR2(100);
V_GOODS VARCHAR2(4);
V_PCTRANS VARCHAR2(100);
BEGIN
DECLARE
CURSOR CUR_GOODS IS
SELECT A.PARAM_4
FROM LT_PARAMTOPARAM A
WHERE A.PARAM_1 = '7'
AND A.PARAM_2 = '0001'
AND A.PARAM_3 = '0058';
BEGIN
OPEN CUR_GOODS;
LOOP
FETCH CUR_GOODS
INTO V_GOODS;
EXIT WHEN CUR_GOODS%NOTFOUND;
V_CUR_GOODS := V_CUR_GOODS ||CHR(39)|| V_GOODS ||CHR(39)|| ',';
END LOOP;
CLOSE CUR_GOODS;
END;
SELECT TRIM(',' FROM V_CUR_GOODS) INTO V_CUR_GOODS FROM DUAL;
DBMS_OUTPUT.put_line(V_CUR_GOODS);

SELECT COUNT(*)
INTO V_PCTRANS
FROM LT_ORDERS A
WHERE A.DD_SOURCE = '0042'
AND A.DD_ORDERSTATUS = '0'
AND A.DD_STOREID = '0001'
AND A.DD_GOODS IN (V_CUR_GOODS);
DBMS_OUTPUT.put_line(V_PCTRANS);
END;
/

以上存储过程中最后第三行，“AND A.DD_GOODS IN (V_CUR_GOODS);” V_CUR_GOODS变量的值为：‘0001’,'0045'
为什么查不出结果，只有使用”AND A.DD_GOODS IN (‘0001’,'0045');“才能查出结果

请各位大侠赐教

Answer 1

用动态sql
或者
将V_CUR_GOODS 中的值 变成多行数据插入临时表中 再使用

SELECT COUNT(*) INTO V_PCTRANS
FROM LT_ORDERS A
WHERE A.DD_SOURCE = '0042' AND A.DD_ORDERSTATUS = '0'
AND A.DD_STOREID = '0001'
AND A.DD_GOODS IN (
SELECT A.PARAM_4
FROM LT_PARAMTOPARAM A
WHERE A.PARAM_1 = '7'
AND A.PARAM_2 = '0001'
AND A.PARAM_3 = '0058';
);

Answer 2

1. 用动态sql

   或者

2. 将V_CUR_GOODS 中的值 变成多行数据插入临时表中 再使用

话说为什么要这么复杂呢？

像下面就可以了？！

SELECT COUNT(*)    INTO V_PCTRANS    
FROM LT_ORDERS A   
WHERE A.DD_SOURCE = '0042'     AND A.DD_ORDERSTATUS = '0'
     AND A.DD_STOREID = '0001'
     AND A.DD_GOODS IN (
SELECT A.PARAM_4        
FROM LT_PARAMTOPARAM A       
WHERE A.PARAM_1 = '7'         
AND A.PARAM_2 = '0001'         
AND A.PARAM_3 = '0058';
);

Answer 3

可以用正则表达式和分层的方式将变量先转化为查询结果，再去in
这么写：“AND A.DD_GOODS IN (SELECT trim(regexp_substr(str, '[^,]+', 1, level)) strRows FROM(SELECT :V_CUR_GOODS as str from dual) t CONNECT BY instr(str, ',', 1, level - 1) > 0)”