oracle存储过程中游标的使用

这里，我们将会学习一下游标在存储过程中的大部分使用方式，表用的scott用户下的emp表。下面这些存储过程是在oracle 11g中亲测过。

--案例1、使用游标查询部门编号为10的所有人姓名和薪水
create or replace procedure test2 is
begin
  declare
    type c is ref cursor;    
    emp_sor c;      
    cname emp.ename%type;     
    csal emp.sal%type;
 begin
   open emp_sor for select ename,sal from emp where deptno=10;       
   loop        
     fetch emp_sor into cname,csal;  --取游标的值给变量。             
     dbms_output.put_line('ename:'||cname||'sal'||csal);        
     exit when emp_sor%notfound;        
    end loop;         
    close emp_sor;     
 end;
end test2;

案例2、直接定义游标
create or replace procedure test3 is
begin
  declare
    cursor emp_sor  is select ename,sal from emp where deptno=10;
    cname emp.ename%type;
    csal emp.sal%type;
 begin
   open emp_sor;
   loop
     fetch emp_sor into cname,csal;  --取游标的值给变量。
     dbms_output.put_line('ename:'||cname||'sal'||csal);
     exit when emp_sor%notfound;
    end loop;
    close emp_sor;
 end;
end test3;

案例3、使用记录变量来接受游标指定的表的数据
create or replace procedure test4 is
begin
  declare
    cursor emp_sor is
      select ename, sal from emp where deptno = 10;
    --使用记录变量来接受游标指定的表的数据
    type emp_type is record(
      v_ename emp.ename%type,
      v_sal   emp.sal%type);
    --用emp_type声明一个与emp_type类似的记录变量。该记录有两列，与emp表的ename，sal同类型的列。
    emp_type1 emp_type;
  begin
    open emp_sor;
    loop
      fetch emp_sor into emp_type1; --取游标的值给变量。
      dbms_output.put_line(emp_type1.v_ename || ',' || emp_type1.v_sal);
      exit when emp_sor%notfound;
    end loop;
    close emp_sor;
  end;
end test4;

案例4、用for游标取值
create or replace procedure test5 is
begin
  declare
    cursor emp_sor is select a.ename from emp a;
    type ename_table_type is table of varchar2(20);
    ename_table ename_table_type;
  begin
   --用for游标取值
    open emp_sor;
    --通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率
    --Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。
    --采用bulk collect可以将查询结果一次性地加载到collections中。
    --而不是通过cursor一条一条地处理。
    --可以在select into,fetch into,returning into语句使用bulk collect。
    --注意在使用bulk collect时，所有的into变量都必须是collections
      fetch emp_sor bulk collect into ename_table;
      for i in 1 ..ename_table.count loop
        dbms_output.put_line(ename_table(i));
    end loop;
    close emp_sor;
  end;
end test5;

案例5、用for取值，带隐式游标会自动打开和关闭
create or replace procedure test6 is
begin
  declare
    cursor emp_sor is select a.ename from emp a;
    type emp_table_type is table of varchar(20);
    begin 
    for emp_record in emp_sor
      loop
        dbms_output.put_line('第'||emp_sor%rowcount||'雇员名:'||emp_record.ename);
      end loop;
  end;
end test6;

案例6、判断游标是否打开
create or replace procedure test7 is
begin
  declare
    cursor emp_sor is select a.ename from emp a;
    type emp_table_type is table of varchar(20);
    emp_table emp_table_type;
  begin
   --用for取值，判断游标是否打开
    if not emp_sor%isopen then
      open emp_sor;
    end if;
    fetch emp_sor bulk collect into emp_table;
    dbms_output.put_line(emp_sor%rowcount);
    close emp_sor;
  end;
end test7;

案例7、使用游标变量取值
create or replace procedure test8 is
begin
   --使用游标变量取值
  declare
    cursor emp_sor is select a.ename,a.sal from emp a;
    emp_record emp_sor%rowtype;
  begin
   open emp_sor;
   loop
     fetch emp_sor into emp_record;
     exit when emp_sor%notfound;
      --exit when emp_sor%notfound放的位置不一样得到的结果也不一样。如果放到dbms_....后，
      --结果会多显示一行数据，即查询结果的最后一行显示了两次。
     dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename||'薪水:'||emp_record.sal);
   end loop;
   close emp_sor;
  end;
end test8;

案例8、带参数的游标，在打开游标的时候传入参数
create or replace procedure test9 is
begin
   --带参数的游标，在打开游标的时候传入参数
  declare
    cursor emp_sor(no number) is select a.ename from emp a where a.deptno=no;
    emp_record emp_sor%rowtype;
  begin
   open emp_sor(10);
   loop
     fetch emp_sor into emp_record;
     exit when emp_sor%notfound;
     dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename);
   end loop;
   close emp_sor;
  end;
end test9;

案例9、使用游标做更新操作
create or replace procedure test10 is
begin
   --使用游标做更新、删除操作，必须在定义游标的时候加上for update
   --当然也可以用for update nowait
  declare
    cursor emp_sor is select a.ename,a.sal from emp a for update;
    cname emp.ename%type;
    csal emp.sal%type;
  begin
   open emp_sor;
   loop
     fetch emp_sor into cname,csal;
     exit when emp_sor%notfound;
     dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
     if csal < 2000 then
       update emp set sal = sal+200 where current of emp_sor;
     end if;
   end loop;
   close emp_sor;
   --要查看更新后的数据，必须得重新打开游标去查询
   open emp_sor;
   loop
     fetch emp_sor into cname,csal;
      exit when emp_sor%notfound;
      dbms_output.put_line('名称:'||cname||','||'new薪水:'||csal);
   end loop;
   close emp_sor;
  end;
end test10;

案例10、使用游标做删除操作
create or replace procedure test11 is
begin
   --使用游标做更新、删除操作，必须在定义游标的时候加上for update
  declare
    cursor emp_sor is select a.empno from emp a for update;
    pempno emp.empno%type;
  begin
   open emp_sor;
   loop
     fetch emp_sor into pempno;
     exit when emp_sor%notfound;
     dbms_output.put_line('旧的empno:'||pempno);
     if pempno = 2009 then
        delete emp where current of emp_sor;
     end if;
   end loop;
   close emp_sor;
   --要查看删除后的数据，必须得重新打开游标去查询
   open emp_sor;
   loop
     fetch emp_sor into pempno;
     exit when emp_sor%notfound;
     dbms_output.put_line('新的empno:'||pempno);
   end loop;
   close emp_sor;
  end;
end test11;

案例11、直接使用游标而不用去定义
create or replace procedure test12 is
  begin
    for emp_record in(select empno,sal,deptno from emp)
      loop
        dbms_output.put_line('员工编号:'||emp_record.empno||',薪水:'||emp_record.sal||',部门编号'||emp_record.deptno);
      end loop;
end test12;

案例12、带sql的统计查询
create or replace procedure test13 is
begin
  declare
    type test_cursor_type is ref cursor;
    test_cursor test_cursor_type;
    v_name user_tables.TABLE_NAME%type;
    v_count number;
    str_sql varchar2(100);
  begin
   open test_cursor for select table_name from user_tables;
   loop
     fetch test_cursor into v_name;
     if v_name is not null then
       str_sql := 'select count(*) from '|| v_name;
       execute immediate str_sql into v_count;
     end if;
     exit when test_cursor%notfound;
     dbms_output.put_line(v_name||','||v_count);
   end loop;
   close test_cursor;
  end;
end test13;

 最后说一下，当我们写完存储过程之后，我们可以在 command window下执行，oracle默认是不显示输出的，所以我们要 set serveroutput on 命令来显示输出结果，然后 exec test1()即可输出结果。