游标变量动态SQL语句如何接收参数？

我想写一个存储过程，根据输入的参数对一个表进行查询，如果输入的某个参数无效，则忽略这个参数

比如表如下：
Create Table tb_person (
    Id Integer Primary Key, 
    aid Integer Not Null, -- 所在地区id
    pname Nvarchar2(10) Not Null, -- 姓名
    paddress Nvarchar2(20) Not Null -- 地址
);

查询的存储过程：search1(p_aid integer, p_name nvarchar2)
如果调用时使用：search1(1, null);
则执行的查询是：select * from tb_person where aid = 1;
而如果调用：search1(1, 'abc');
则执行的查询是：select * from tb_person where aid = 1 and pname = 'abc';

Create Or Replace Procedure search1(p_aid Integer, p_name Nvarchar2)
    Is
        Type ref_cursor Is Ref Cursor;
        cursor_person ref_cursor;
        v_sql Varchar2(2000);
        v_person tb_person%Rowtype;
        v_pre Varchar2(20);
    Begin
        v_sql := 'select * from tb_person';
        
        v_pre := ' where ';
        If (p_aid Is Not Null) And (p_aid > 0) Then
         v_sql := v_sql || v_pre;
            v_sql := v_sql || ' aid = ' || p_aid;
            v_pre := ' and ';
        End If;
        If (p_name Is Not Null) And length(Trim(p_name)) > 0 Then
         v_sql := v_sql || v_pre || ' pname = ''' || replace(p_name, '''', '''''') || '''';
        End If;
        
        dbms_output.put_line (v_sql);
        Open cursor_person For v_sql;
        Loop
Fetch cursor_person Into v_person;
            Exit When cursor_person%Notfound;
            
            dbms_output.put_line(v_person.pname);
        End Loop;
        
    End;

=========================================================

我的问题是，当传入的字符串参数中如果含有 单引号时，就必须要用 replace 函数把一个单引号转换成两个连续的单引号，而如果有日期类型的参数传入，则还要把日期类型转换成字符串再拼接成 sql 语句

有没有办法直接把参数传进去？

17 个解决方案

#1

不解为什么会要传日期，TABLE中没有DATE字段，PRO中的参数没有DATE！

#2

我的问题是，当传入的字符串参数中如果含有 单引号时，就必须要用 replace 函数把一个单引号转换成两个连续的单引号，而如果有日期类型的参数传入，则还要把日期类型转换成字符串再拼接成 sql 语句


设置两个参数就好了   一个是字符串  一个是日期   每个参数都进行is null判断 然后再进行str的拼接

#3

-- 可用using形式来传值

Create Or Replace Procedure search1(p_aid Integer, p_name Nvarchar2)
  Is
  Type ref_cursor Is Ref Cursor;
  cursor_person ref_cursor;
  v_sql Varchar2(2000);
  v_person tb_person%Rowtype;
  v_pre Varchar2(20);
  Begin
  v_sql := 'select * from tb_person';
    
  v_pre := ' where ';
  If (p_aid Is Not Null) And (p_aid > 0) Then
  v_sql := v_sql || v_pre;
  v_sql := v_sql || ' aid = :1' ;   --形参用占位符代替
  v_pre := ' and ';
  End If;
  If (p_name Is Not Null) And length(Trim(p_name)) > 0 Then
  v_sql := v_sql || v_pre || ' pname = :2';  --形参用占位符代替
  End If;
    
  dbms_output.put_line (v_sql);
  If (p_aid Is Not Null) And (p_aid > 0) THEN
      IF (p_name Is Not Null) And length(Trim(p_name)) > 0 Then
          Open cursor_person For v_sql USING p_aid,p_name;     --这里用using来传实参
      ELSE
          Open cursor_person For v_sql USING p_aid;   --这里用using来传实参
      END IF;
  ELSE
      Open cursor_person For v_sql;    
  end IF;
  Loop
      Fetch cursor_person Into v_person;
      Exit When cursor_person%Notfound;
    
      dbms_output.put_line(v_person.pname);
  End Loop;
End;

--测试:
    insert into tb_person values(1,1,'abc','xxx');
    insert into tb_person values(2,2,'xyz','yyy');
    commit;
    exec search1(1,null);
--结果:
     PL/SQL block, executed in 0.015 sec.   
     select * from tb_person where  aid = :1
     abc                                    
     Total execution time 0.015 sec.        
                     

exec search1(1,'abc');
    PL/SQL block, executed in 0 sec.                       
    select * from tb_person where  aid = :1 and  pname = :2
    abc                                                    
    Total execution time 0 sec.                            

exec search1(null,null);
     PL/SQL block, executed in 0 sec.
     select * from tb_person         
     abc                             
     xyz                             
     Total execution time 0.016 sec. 

#4

调整游标，将游标变为select * from tb_person.然后判断通过获取游标记录，判断变量 p_aid是否等于cursor_person.aid，来获取所需的结果

#5

向3楼的学习啊，这个用法还没有用过呢！！

#6

该回复于2011-03-29 16:26:16被版主删除

#7

引用 1 楼 getmydream 的回复:

不解为什么会要传日期，TABLE中没有DATE字段，PRO中的参数没有DATE！

前面我是举个例子而已，我考虑的是其它的表的其它类型的字段

#8

3 楼的办法好是好，可以解决问题

但是麻烦的是如果过程传进去的参数如果很多的话，里面将会套很多个 if 语句，并且参数的数量和顺序出现的可能性要全部写出来

#9

参考3楼的写法，我重新写了一次，但是用 execute immediate 好象不能打开游标参数：

Create Sequence sq_product Start With 1 Increment By 1 Minvalue 1 Maxvalue 9999999999;
Create Table product (
	Id Int Primary Key,
    aid Int, -- 所在地区id
    cat Int, -- 类型
    adddate Date, -- 记录添加时间
    title Nvarchar2(50), -- 标题
    price Integer, -- 价格
    otherdesc Nvarchar2(200) -- 其它说明
);

/
insert into product	values(sq_product.nextval, 1, 1, sysdate - 34, 'mytitle', 200, 'abcdefg aaagtest');
insert into product values(sq_product.nextval, 1, 2, sysdate - 32, 'your title', 220, 'abcdefg aaagtest');
insert into product values(sq_product.nextval, 1, 3, sysdate - 33, 'test ', 210, 'abcdefg aaagtest');
insert into product values(sq_product.nextval, 2, 4, sysdate - 31, 'abcd', 250, 'abcdefg aaagtest');
insert into product values(sq_product.nextval, 2, 2, sysdate - 36, '汉字', 2640, 'abcdefg aaagtest');
insert into product values(sq_product.nextval, 3, 1, sysdate - 32, '*', 234, 'abcdefg aaagtest');
commit;

/

Create Or Replace Procedure searche1(
	p_aid Integer, p_cat Int, p_start Date, p_end Date, p_minprice Integer, p_maxprice Integer,
    p_titlePre Nvarchar2
)
Is
	Type cursor_ref Is Ref Cursor;
    cursor_result cursor_ref;
    v_product product%Rowtype;
	v_sql Varchar2(2000);
    v_sqlPre Varchar2(10) := ' where ';
    v_param Varchar2(2000) := '';
    v_paramPre Varchar2(10) := ' using ';
Begin

	v_sql := 'select * from product';
    -- aid 
    If (p_aid Is Not Null) And p_aid > 0 Then
    	v_sql := v_sql || v_sqlPre || ' aid = :aid';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_aid';
        v_paramPre := ', ';
    End If;
    
    -- cat
    If (p_cat Is Not Null) And (p_cat > 0) Then
    	v_sql := v_sql || v_sqlPre || ' cat = :cat';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_cat';
        v_paramPre := ', ';
    End If;

    -- adddate
    If (p_start Is Not Null) Then
    	v_sql := v_sql || v_sqlPre || ' adddate >= :start ';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_start';
        v_paramPre := ', ';
    End If;
    
    If (p_end Is Not Null) Then
    	v_sql := v_sql || v_sqlPre || ' adddate <= :end ';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_end';
        v_paramPre := ', ';
    End If;
    
    -- price 
    If (p_minprice Is Not Null) And p_minprice > 0 Then
    	v_sql := v_sql || v_sqlPre || ' price >= :minprice ';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_minprice';
        v_paramPre := ', ';
    End If;
    If (p_maxprice Is Not Null) And p_maxprice > 0 Then
    	v_sql := v_sql || v_sqlPre || ' price <= :maxprice ';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_maxprice';
        v_paramPre := ', ';
    End If;
    
    -- title
    If (p_titlePre Is Not Null) And length(Trim(p_titlePre)) > 0 Then
    	v_sql := v_sql || v_sqlPre || ' title = :title';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_titlePre';
        v_paramPre := ', ';
    End If;
    
    v_sql := 'open cursor_result for ' || v_sql || v_param;
    dbms_output.put_line(v_sql);
    Execute Immediate v_sql;
    
    Loop 
    	Fetch cursor_result Into v_product;
        Exit When cursor_result%Notfound;
        dbms_output.put_line(v_product.title);
    End Loop;
    
End;

/

Begin
	searche1(null, null, null, null, null, null, null);
End;

#10

execute immediate 少了 using 实参！

#11

using 不能放在动态SQL的拼接中，要单独出来

#12

v_sql := 'open cursor_result for ' || v_sql || v_param;
dbms_output.put_line(v_sql);
Execute Immediate v_sql;

改成下面这样就可以了：
v_sql := v_sql || v_param;
dbms_output.put_line(v_sql);
open cursor_result for v_sql;

#13

最后一句少了using 参数：
open cursor_result for v_sql using 你的参数;

#14

可以 定义一个异常处理 根据处理返回的结果  去执行

#15

现在我已经改成了：

    If (v_param Is Null) Or length(trim(v_param)) = 0 Then
Open cursor_result For v_sql;
    Else
     Open cursor_result For v_sql Using v_param;
    End If;


运行结果是正确的

但是另一个问题又来了，如果我针对 nvarchar2 类型的字段不是进行相等匹配，而是 like 怎么办?

下面的写法好象不行
    -- title
    If (p_titlePre Is Not Null) And length(Trim(p_titlePre)) > 0 Then
     v_sql := v_sql || v_sqlPre || ' title like  :title ';
        v_sqlPre := ' and ';
        
        v_param := v_param || v_paramPre || 'p_titlePre || ''%''';
        v_paramPre := ', ';
    End If;

#16

晕死，好象不对

看错了

#17

我也遇到这个问题。