SQL> create or replace procedure proc_test_cursor(i_num number,o_cur out sys_ref cursor) 2 as 3 begin 4 if i_num=1 then 5 open o_cur for select 1 from dual; 6 else 7 open o_cur for select empno from emp where empno=7369; 8 end if; 9 end; 10 /Procedure created.SQL> var v_cur refcursor SQL> exec proc_test_cursor(1,:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur 1 ---------- 1SQL> exec proc_test_cursor(2,:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur EMPNO ---------- 7369SQL>
我想在cur_mult_fab 里加if语句。请问如何修改。谢谢 DECLARE CURSOR cur_mult_fab is select * from ... begin FOR mult_fab_rec IN cur_mult_fab LOOP BEGIN ..... END; END LOOP; end;
这样啊. 没试过cursor套cursor.
SQL> var v_cur refcursorSQL> create or replace procedure proc_test_cursor(o_cur out sys_refcursor) 2 as 3 cv_cur1 sys_refcursor; 4 v_empno number(10); 5 begin 6 open cv_cur1 for select empno from emp; 7 loop 8 fetch cv_cur1 into v_empno; 9 exit when cv_cur1%notfound; 10 exit when o_cur%isopen; 11 if v_empno=7934 then 12 open o_cur for select 1 from dual; 13 end if; 14 end loop; 15 close cv_cur1; 16 end; 17 /Procedure created.SQL> exec proc_test_cursor(:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur 1 ---------- 1SQL>
我只是用for loop的方式来使用cursor,不是cursor嵌套cursor。郁闷啊
--最好是写明白点DECLARE CURSOR cur_mult_fab is select * from ... begin FOR mult_fab_rec IN cur_mult_fab LOOP if mult_fab_rec.col=1 then ..... else ... end if; END LOOP; end;
SQL> declare 2 cursor cv_1 is select empno from emp; 3 v_num number(10); 4 begin 5 for ccc in cv_1 6 loop 7 dbms_output.put_line(ccc.empno); 8 end loop; 9 end; 10 /
cursor)
2 as
3 begin
4 if i_num=1 then
5 open o_cur for select 1 from dual;
6 else
7 open o_cur for select empno from emp where empno=7369;
8 end if;
9 end;
10 /Procedure created.SQL> var v_cur refcursor
SQL> exec proc_test_cursor(1,:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur 1
----------
1SQL> exec proc_test_cursor(2,:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur EMPNO
----------
7369SQL>
DECLARE
CURSOR cur_mult_fab is
select * from ...
begin
FOR mult_fab_rec IN cur_mult_fab
LOOP
BEGIN
.....
END;
END LOOP;
end;
没试过cursor套cursor.
2 as
3 cv_cur1 sys_refcursor;
4 v_empno number(10);
5 begin
6 open cv_cur1 for select empno from emp;
7 loop
8 fetch cv_cur1 into v_empno;
9 exit when cv_cur1%notfound;
10 exit when o_cur%isopen;
11 if v_empno=7934 then
12 open o_cur for select 1 from dual;
13 end if;
14 end loop;
15 close cv_cur1;
16 end;
17 /Procedure created.SQL> exec proc_test_cursor(:v_cur);PL/SQL procedure successfully completed.SQL> print v_cur 1
----------
1SQL>
--最好是写明白点DECLARE
CURSOR cur_mult_fab is
select * from ...
begin
FOR mult_fab_rec IN cur_mult_fab
LOOP
if mult_fab_rec.col=1 then
.....
else
...
end if; END LOOP;
end;
2 cursor cv_1 is select empno from emp;
3 v_num number(10);
4 begin
5 for ccc in cv_1
6 loop
7 dbms_output.put_line(ccc.empno);
8 end loop;
9 end;
10 /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
PL/SQL procedure successfully completed
SQL>