Oracle中的游标，存储过程，循环三两事

一.oracle中游标的循环loop，for循环的爱恨纠葛

游标分为静态游标，ref游标。

我们会发现静态游标在定义时就已经确定了对应的sql语句。如下：

cursor cur_emp is select * from emp_c;

---

但是ref游标在定义时尚不知sql语句，如下：

type cur_ref_emp is ref cursor;
cur_ref cur_ref_emp;

---

这里循环有loop和for两种

loop需要我们自己打开和关闭游标；

for可以自动为我们打开和关闭游标。

那么循环方式对两种游标的适用性就不同。如下：

对于静态的游标两种循环都适用。

declare
     cursor cur_emp is select * from emp_c;
     v_row emp_c%rowtype;
     v_sal emp_c.sal%type;
begin
      --open cur_emp;
    /* loop
    fetch cur_emp into v_row;
   exit when cur_emp%notfound;
   v_sal:=v_row.sal;
   if v_sal<2500 then
     v_sal:=v_sal+100;
     update emp_c set emp_c.sal=v_sal where emp_c.empno=v_row.empno;
     commit;
   end if;
  end loop;*/
  for e_row in cur_emp loop
    dbms_output.put_line(e_row.sal);
    end loop;
  --关闭游标(普通loop)
 -- close cur_emp;
end;

---

但是动态游标使用for似乎就不太合适。

declare
type cur_ref_emp is ref cursor;
cur_ref cur_ref_emp;
   v_t_name varchar2(100);
   v_empname emp.ename%type;
   v_sal emp.sal%type;
begin
   v_t_name:='&请输入表的名称';
   open cur_ref for 'select ename,sal from '||v_t_name;-- 打开游标，for关联sql语句
 /*loop  --循环
        fetch cur_ref into v_empname,v_sal;
        exit when cur_ref%notfound;
         dbms_output.put_line(v_empname||'  '||v_sal); 
   end loop;*/
  /* for e_row in cur_ref loop
     dbms_output.put_line(e_row.sal); 
     end loop;不适用于ref游标*/
   close cur_ref;--关闭游标
end;

因为在for中会重开游标但是有没有对应的sql语句，所以会报错。

---

二.返回游标的存储过程正确运行代码

由于这里要使用ref游标所以不能使用for！

--包头
create or replace package test_package
is
      type cur_emp is ref cursor;
      procedure show_page_list(pageNo in number,pageSize in number,ref_cur out  test_package.cur_emp);     
end test_package;
--包体
create or replace package body test_package
is
        procedure show_page_list(pageNo in number,pageSize in number,ref_cur out test_package.cur_emp)
        as
        max_p number:=pageSize+pageNo;
        str varchar2(100):='select * from (select rownum r,emp_c.* from emp_c where rownum<'||max_p||')T where r>'||pageNo;
        begin
           open ref_cur for str;
          -- dbms_output.put_line(str); 
         end;     
end test_package;
--实现（test）
create table n_emp as select rownum r,emp_c.* from emp_c;
declare
cur_ref test_package.cur_emp;
e_row n_emp%rowtype;
begin
   test_package.show_page_list(1,3,cur_ref);--给游标赋值
   loop  --循环
        fetch cur_ref into e_row;
        exit when cur_ref%notfound;
         dbms_output.put_line(e_row.sal); 
   end loop;
   close cur_ref;--关闭游标
end;