如何将带单引号的varchar传递给Oracle中的存储过程

问题描述

我有这样的存储过程:

调用程序包名称.StoredProc('''A''，''B''')

call packagename.StoredProc('''A'',''B''')

"A"，"B"部分将被用作IN子句中过程代码的参数.

'A', 'B' part is then meant to be used as parameter in procedure's code in the IN clause.

当我使用in子句作为paramvalue IN ('A', 'B')运行该代码时，它将按预期返回行.

When I run that code with in clause as paramvalue IN ('A', 'B') it returns rows as expected.

但是当执行对存储的proc的调用时，没有行，这使我认为这些引号一定是错误的，但是我显然无法正确地做到这一点.谁能看到这为什么不起作用?谢谢

But when call to stored proc is executed no rows which makes me think those quotes must be wrong but I can't get it right apparently. Can anyone see why is this not working? Thanks

来自proc的代码:

SELECT COUNT(*)
            INTO v_PendingCheckCount
            FROM FUND_CHANGE_REQUEST
            WHERE STATUS IN ('PENDING_CHK', 'PEND_2ND_CHK')
            AND PROVIDER IN (pProviderList);

进行呼叫:

CALL PackageName.ProcedureName('''A'',''B''')

pProviderList是传递给过程的参数.

pProviderList is parameter passed to procedure.

推荐答案

传递集合而不是字符串，并使用MEMBER OF而不是IN:

Pass a collection not a string and use MEMBER OF rather than IN:

CREATE OR REPLACE TYPE characterlist IS TABLE OF CHAR(1);
/

CREATE PACKAGE your_package AS
  PROCEDURE countPending(
    pProviderList IN  characterlist
    pCount        OUT INTEGER
  )
  AS
  BEGIN
    SELECT COUNT(*)
    INTO pCount
    FROM FUND_CHANGE_REQUEST
    WHERE STATUS IN ('PENDING_CHK', 'PEND_2ND_CHK')
    AND PROVIDER  MEMBER OF pProviderList;
  END;
END;
/

然后您可以将其称为:

DECLARE
  vCount INTEGER;
BEGIN
  your_package.countPending(
    characterlist( 'A', 'B' ),
    vCount
  );
  DBMS_OUTPUT.PUT_LINE( vCount );
END;
/

您传递的是单个字符串而不是值列表-因此IN条件正在测试以查看PROVIDER列是否与您的整个输入字符串完全匹配，而不是您假设的那样，用引号引起来的定界列表.

You are passing a single string and not a list of values - so the IN condition is testing to see whether the PROVIDER column exactly matches your entire input string and not, as you are assuming, each element of your quoted delimited list.

 WHERE 'A' IN ( q'['A', 'B']' )

永远不会匹配，因为两个'A'都不等于q'['A', 'B']'(或'''A'', ''B''')，并且计数始终为零.

Will never match since neither 'A' does not equal q'['A', 'B']' (or '''A'', ''B''') and the count will always be zero.

 WHERE 'A' IN ( 'A', 'B' )

会匹配，但是IN条件的表达式列表中有两个术语.

Will match but there are two terms in the expression list of the IN condition.

这篇关于如何将带单引号的varchar传递给Oracle中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！