oracle游标使用错误的结果。

code 1:

代码1:

OPEN P_CURSOR FOR V_STR_SQL2
        USING P_USER_ID, P_USER_ID, V_NODEID, V_PROCID, V_ADDRESSE;

output : 0,0,0,0

输出:0,0,0,0

when i change this code to :

当我将代码更改为:

 V_STR_SQL2 := replace(V_STR_SQL2,':P_USER_ID',P_USER_ID);
   V_STR_SQL2 := replace(V_STR_SQL2,':V_NODEID',V_NODEID);
   V_STR_SQL2 := replace(V_STR_SQL2,':V_PROCID',V_PROCID);
   V_STR_SQL2 := replace(V_STR_SQL2,':V_ADDRESSE',V_ADDRESSE);
 OPEN P_CURSOR FOR V_STR_SQL2;

output : 1,0,0,0

输出:1,0,0,0

second output is correct. what could be the reason? any idea? datatype of bind variables are as below.

第二个输出是正确的。原因何在?任何想法?绑定变量的数据类型如下。

userid varchar2, input
userid varchar2,输入
nodeid,procid, number(10)
nodeid procid,数量(10)
address varchar2(250);
地址varchar2(250);

-- Edited

——编辑

When I debug my stored proc, this is the value of v_str_sql2 before executing open cursor. When I run this query by giving its vlaue in sqldeveloper it gives me correct result. but wrong result with open-for-using.

当我调试存储的proc时，这是在执行open cursor之前的v_str_sql2的值。当我在sqldeveloper中给它的vlaue运行这个查询时，它给了我正确的结果。但使用开放的方式会导致错误的结果。

SELECT COUNT(DECODE(ZC.STATUS, NULL, 1)) "NEW",
   COUNT(DECODE(ZC.STATUS, 'KEEP', 1)) "KEEP",
   COUNT(DECODE(ZC.STATUS, 'LOCK', 1)) "LOCK",
   COUNT(DECODE(ZC.STATUS,
                'KEEP',
                DECODE(UPPER(ZC.STATUS_BY), UPPER(:P_USER_ID), 1))) "PKEEP",
   COUNT(DECODE(ZC.STATUS,
                'LOCK',
                DECODE(UPPER(ZC.STATUS_BY), UPPER(:P_USER_ID), 1))) "PLOCK"
  FROM tab1 PARTITION(DCL_OTHERS) DCL,
   tab2      ZC,
   tab3      TC
 WHERE DCL.NODE_ID = TC.NODE_ID
   AND DCL.PROC_ID = TC.PROC_ID
   AND DCL.CASE_REF_NO = TC.CLAIM_REF_NO
   AND DCL.NODE_ID = ZC.NODE_ID(+)
   AND DCL.PROC_ID = ZC.PROC_ID(+)
   AND DCL.CASE_NAME = ZC.CASENUM(+)
   AND DCL.USER_NAME = ZC.USERNAME(+)
   AND DCL.NODE_ID = :V_NODEID
   AND (DCL.PROC_ID = :V_PROCID)
   AND (1 = 1)
   AND DCL.USER_NAME = :V_ADDRESSE

Edit 2

编辑2

I am using this syntax. Is that something wrong with this syntax?

我使用的是这个语法。这种语法有问题吗?

    CREATE OR REPLACE PROCEDURE USP_HTH_QUEUEPAGE(P_QUEUENAME VARCHAR2,
                                              P_CURSOR    OUT SYS_REFCURSOR,
                                              P_REC_CNT   OUT NUMBER) IS
BEGIN
  -- CODE TO GENERATE DYNAMIC SQL
  OPEN P_CURSOR FOR V_STR_SQL1
    USING V_ADDRESSE, V_NODEID, V_PROCID, V_STATUS, V_USER_ID, P_EP, P_SP;
  /*END IF;*/
  EXECUTE IMMEDIATE V_STR_SQL2
    INTO P_REC_CNT
    USING V_NODEID, V_PROCID, V_USER_ID, V_STATUS, V_ADDRESSE;
END;

2 个解决方案

#1

Kindly try the below

请尝试以下

EXECUTE IMMEDIATE V_STR_SQL2
INTO P_REC_CNT
USING  V_USER_ID,V_USER_ID,V_NODEID,V_PROCID, V_ADDRESSE;

The below link would give you more idea why

下面的链接会告诉你为什么。

Dynamic SQL Statement

动态SQL语句

#2

What concerns me is this line here:

我担心的是这条线:

V_STR_SQL2 := replace(V_STR_SQL2,':V_ADDRESSE',V_ADDRESSE);

This puts the contents of the VARCHAR2 variable V_ADDRESSE directly into the query, without any quoting.

这将VARCHAR2变量V_ADDRESSE的内容直接放到查询中，不需要引用。

Given the value of V_STR_SQL2 you obtained during debugging, the value of V_ADDRESSE must be something that is a valid SQL expression. If its value was something like 1 High Street, you would end up with V_STR_SQL2 containing something like

考虑到在调试过程中获得的V_STR_SQL2的值，V_ADDRESSE的值必须是一个有效的SQL表达式。如果它的值类似于1号大街，那么您最终将使用V_STR_SQL2，其中包含类似的内容。

AND DCL_USER_NAME = 1 High Street

which isn't valid SQL. You'd get an error attempting to execute this.

这并不是有效的SQL。试图执行这个操作会得到一个错误。

So, what could V_ADDRESSE contain? Two possibilities suggest themselves to me:

那么，V_ADDRESSE可以包含什么呢?我有两种可能:

The value of your variable V_ADDRESSE contains a ' at either end, such as '1 High Street'.
变量V_ADDRESSE的值包含“在任何一端，比如‘1号大街’”。
The value of your variable V_ADDRESSE contains a string such as 00012493 and you want this to match a value 12493.
变量V_ADDRESSE的值包含一个字符串，例如00012493，您希望它匹配一个值12493。

What exactly does V_ADDRESSE contain?

V_ADDRESSE包含什么?

#1