cursor C_RECORD (P_pra in varchar2) is select * from TABLE where ......
游标参数P_pra值为 and type1=1
我需要将P_pra做为游标的查询条件字符传递,因为这个条件也有可能是 and type2=2
写成我以下的方式是不行的:
cursor C_RECORD (P_pra in varchar2) is select * from TABLE where ...... P_pra
请教高手:该如何将参数值 and type1=1 传递至游标.注:不可再定义其它游标.
游标参数P_pra值为 and type1=1
我需要将P_pra做为游标的查询条件字符传递,因为这个条件也有可能是 and type2=2
写成我以下的方式是不行的:
cursor C_RECORD (P_pra in varchar2) is select * from TABLE where ...... P_pra
请教高手:该如何将参数值 and type1=1 传递至游标.注:不可再定义其它游标.
--将其定义为动态游标
type refCur is ref cursor;--定义动态游标
C_RECORD refCur ;
...
...
open C_RECORD for 'select * from TABLE where ...... '||P_pra||'';
....
....
type rc is ref cursor;
l_cursor rc;
v_emp emp%rowtype;
begin
open l_cursor for 'select * from emp where deptno = :v_detpno'
using &v_deptno;
loop
fetch l_cursor into v_emp;
exit when l_cursor%notfound;
dbms_output.put_line(v_emp.empno);
end loop;
close l_cursor;
end;
SQL> declare
2 type cursor_type is ref cursor;
3 cur cursor_type;
4 v_id tt.id%type;
5 sql1 varchar2(100);
6 begin
7 sql1:=&sql;
8 open cur for 'select id from tt where '||sql1;
9 fetch cur into v_id;
10 while cur%found loop
11 dbms_output.put_line(v_id);
12 fetch cur into v_id;
13 end loop;
14 close cur;
15 end;
16 /
输入 sql 的值: 'name=''wkc168'''
原值 7: sql1:=&sql;
新值 7: sql1:='name=''wkc168''';
168PL/SQL 过程已成功完成。