存储过程中查询如何拼字符串执行并将结果放入CURSOR?

Question

如下语句：
CREATE OR REPLACE procedure up_SearchByApprovalInfoDts
(
       I_UserID             IN VARCHAR2
,      I_ApprovalTypeCode   IN NUMBER  
,      O_Cursor         OUT SYS_REFCURSOR
)
AS
       STRSQL NVARCHAR2(2000);
BEGIN                 
       OPEN O_Cursor FOR
       SELECT * 
           FROM ApprovalInfoDts a
           WHERE (I_UserID='' OR a.UserID=I_UserID)
              OR (I_ApprovalTypeCode='' OR a.ApprovalTypeCode=I_ApprovalTypeCode)
END;我想改为：
CREATE OR REPLACE procedure up_SearchByApprovalInfoDts
(
       I_UserID             IN VARCHAR2
,      I_ApprovalTypeCode   IN NUMBER  
,      O_Cursor         OUT SYS_REFCURSOR
)
AS
       STRSQL NVARCHAR2(2000);
BEGIN
       STRSQL:='SELECT * 
           FROM ApprovalInfoDts a
           WHERE 1=1';       IF I_UserID!='' THEN STRSQL:=STRSQL+'AND a.UserID='''+I_UserID+'''';
       IF I_ApprovalTypeCode!='' THEN STRSQL:=STRSQL+'AND a.ApprovalTypeCode='''+I_ApprovalTypeCode+'''';
                 
       OPEN O_Cursor FOR
       ?????;
END;上面代码？？？？部分该怎么写？或者还有其它更合理的方法实现？
刚从SQL SERVER转过来，有点摸不着头绪。
另请问ORACLE里定义变量是否能和SQL SERVER里一样用符号做前缀，类似 @ApprovalTypeCode 这样的。
谢谢啦！

Answer 1

OPEN O_Cursor FOR STRSQL;

Answer 2

if 语句后你少了两个end if;

Answer 3

把你的STRSQL NVARCHAR2(2000); 
改成
STRSQL VARCHAR2(2000); 应该就可以了

Answer 4

还有!=''也有错，要改成is not null
字符串拼接要用||而不是+
还有你的两个if
试试CREATE OR REPLACE procedure up_SearchByApprovalInfoDts 
( 
      I_UserID            IN VARCHAR2 
,      I_ApprovalTypeCode  IN NUMBER  
,      O_Cursor        OUT SYS_REFCURSOR 
) 
AS 
      STRSQL NVARCHAR2(2000); 
BEGIN 
      STRSQL:='SELECT * 
          FROM ApprovalInfoDts a 
          WHERE 1=1';       IF I_UserID is not null THEN STRSQL:=STRSQL||'AND a.UserID='''||I_UserID||''''; end if; 
      IF I_ApprovalTypeCode is not null THEN STRSQL:=STRSQL||'AND a.ApprovalTypeCode='''||I_ApprovalTypeCode||''''; 
      end if;
                
      OPEN O_Cursor FOR 
      strsql; 
END; 

Answer 5

还有两个if后的'and ...'的and前要加个空格，要不会出错

Answer 6

又出现新的问题，一下代码编译通过，但执行时在STRSQL := STRSQL || ' AND a.UserID=''' || I_UserID || '''';这句提示字符到数字的转换错误,这里没有数字型的变量为什么会出现这样的提示呢？CREATE OR REPLACE procedure up_SearchByApprovalInfoDts
(
       I_UserID             IN VARCHAR2
,      I_ApprovalTypeCode   IN NUMBER  
,      O_String             OUT VARCHAR2
,      O_Cursor             OUT SYS_REFCURSOR
)
AS
       STRSQL VARCHAR2(4000);
BEGIN
       STRSQL:='SELECT * 
           FROM ApprovalInfoDts a
           WHERE 1=1';
       
       STRSQL := STRSQL || ' AND a.UserID=''' || I_UserID || '''';
       O_String:=STRSQL;
       
       OPEN O_Cursor FOR STRSQL;
END;

Answer 7

无论我为I_UserID传递什么值都有这个错误。

Answer 8

是数字到字符转换错误还是数字或值错误？
是不是你外部O_String  传到外面的那个变量长度太小，检查一下
比如你调用up_SearchByApprovalInfoDts (a,b,c,d)
你看看变量c的定义

Answer 9

O_String定义的是OUT VARCHAR2，应该没有问题。
错误信息是字符到数字转换错误。
即使我将O_String这个变量拿掉错误依旧。是因为有这个错误，为了测试方便才加入O_String的。

Answer 10

我指的不是o_string，而是你调用时o_string传给的那个变量
8楼的代码是完整的吗
调用的代码是怎么写的？
是不是fetch的时候类型没对上
我测试了一下没问题
CREATE OR REPLACE procedure up_SearchByApprovalInfoDts 
( 
      I_UserID            IN VARCHAR2 
,      I_ApprovalTypeCode  IN NUMBER  
,      O_String            OUT VARCHAR2 
,      O_Cursor            OUT SYS_REFCURSOR 
) 
AS 
      STRSQL VARCHAR2(4000); 
BEGIN 
      STRSQL:='SELECT * 
          FROM a 
          WHERE 1=1'; 
      
      STRSQL := STRSQL || ' AND a1=''' || I_UserID || ''''; 
      O_String:=STRSQL; 
      
      OPEN O_Cursor FOR STRSQL; 
END;
declare
a varchar2(200);
c sys_refcursor;
v1 number;
v2 number;
v3 number;
v4 number;
begin
up_SearchByApprovalInfoDts('1',2,a,c);
loop
  fetch c into v1,v2,v3,v4;
  exit when c%notfound;
  dbms_output.put_line(v1||v2||v3||v4);
end loop;
end;
1112