游标有关问题

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
----------
         1

SQL> exec proc_test_cursor(2,:v_cur);

PL/SQL procedure successfully completed.

SQL> print v_cur

     EMPNO
----------
      7369

SQL>

------解决方案--------------------
SQL code
SQL> var v_cur refcursor

SQL> 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
----------
         1

SQL>

------解决方案--------------------
探讨

我想在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;


------解决方案--------------------
SQL code
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  /
 
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
 
PL/SQL procedure successfully completed
 
SQL>