stored-procedures - 在 WHERE 子句中使用变量的存储过程中的 DELETE 问题

Question

我试图在进行后续插入之前删除 SP 中的一些记录。当我在 DELETE 的 WHERE 子句中使用变量时，它不起作用。当我用它的值替换变量时，它会删除记录。我猜这是我将变量放在 WHERE 子句中所做的事情，但无法弄清楚它是什么。我尝试了不同的方法，但都没有奏效。

PROCEDURE USPGETOUTCOME
(
IPSITEMIDS                VARCHAR2,
....
CUR_OUT                   OUT GETDATACURSOR
)
IS
....
....    
V_TEMP VARCHAR(200);
V_NEWITEMSLIST VARCHAR2(4000) := REPLACE(IPSITEMIDS, '''', '');


CURSOR cur IS
SELECT  REGEXP_SUBSTR(V_NEWITEMSLIST, '[^,]+', 1, LEVEL) V_NEWITEM2 FROM DUAL CONNECT BY instr(V_NEWITEMSLIST, ',',1, LEVEL -1) > 0;

BEGIN

-- IPSITEMIDS is ''EM060500103','LP060500105''   
V_TEMP := TRIM(BOTH '''' FROM IPSITEMIDS);

-- After above statement V_TEMP shows as 'EM060500103','LP060500105'

-- These don't work
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN (IPSITEMIDS);
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN (V_TEMP);
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN ('''|| V_TEMP ||''');
-- This works   
      DELETE FROM TEMPOUTCOME WHERE ITEMID IN ('EM060500103','LP060500105');
COMMIT;

-- This is using cursor to loop through each item and delete one by one; works but seems a waste!

-- Delete existing records from temp table for given ITEM IDs
FOR rec IN cur LOOP   
    V_NEWITEM := rec.V_NEWITEM2;
    DELETE FROM IDCTEMPOUTCOME WHERE ITEMID IN (V_NEWITEM);
END LOOP;
COMMIT;

标签： stored-proceduresoracle12csql-delete

Answer 1

您必须拆分这些值。

检查我的例子

--      BANNER
-- 1    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-- 2    PL/SQL Release 11.2.0.2.0 - Production
-- 3    CORE    11.2.0.2.0  Production
-- 4    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
-- 5    NLSRTL Version 11.2.0.2.0 - Production

DECLARE
  l_n NUMBER;
  l_filter VARCHAR2(100) := '''PL/SQL Release 11.2.0.2.0 - Production'',''NLSRTL Version 11.2.0.2.0 - Production''';
BEGIN
  SELECT COUNT(banner) AS n INTO l_n FROM v$version where banner IN (select REGEXP_SUBSTR(l_filter, '\''(.*?)\''(?:\,)?', 1, LEVEL, NULL, 1) FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(l_filter, '''(?: +)?(\,)(?: +)?''', 1) + 1);
  DBMS_OUTPUT.put_line(l_n);
END;