详解Oracle游标的简易用法

下面看下oracle游标的简易用法，具体代码如下所示：

create or replace procedure nw_delyw(ioperation_id number,
                 suserid   varchar2) is
 scurdjbh yw_operation_link.djbh%type;
 cursor table_yw(ywid yw_operation.id%type) is
  select * from yw_operation_link t1 where t1.operation_id = ywid;
begin
 for dr in table_yw(ioperation_id) loop
  scurdjbh := dr.djbh;
  --取得opercationid
  /*  select t1.operation_id
   into soperationid
   from yw_operation_link t1
  where t1.djbh = scurdjbh;*/

  --写日志
  insert into log_zfywinfo
   (djbh,
    djdl,
    djxl,
    dlmc,
    xlmc,
    slr,
    slrid,
    sqrxm,
    fwzl,
    zfrq,
    zfrid,
    zfr)
   select distinct scurdjbh,
       t4.id,
       t3.id,
       t4.name,
       t3.name,
       t1.slry,
       t1.slryid,
       t1.sqrxm,
       t1.zl,
       sysdate,
       suserid,
       (select tt.name from pw_user tt where tt.id=suserid)
    from yw_operation t1
    join yw_operation_link t2
     on t2.operation_id = t1.id
    join business_type t3
     on t3.id = t1.business_id
    join business_class t4
     on t4.id = t3.parent_id
    where t1.id = dr.operation_id;
exception
 when others then
  rollback;
  dbms_output.put_line(sqlerrm);
end nw_delyw;

oracle使用cursor 游标循环添加删除更新。

知识点扩展：

oracle游标简单示例

使用游标打印员工姓名和薪水

set serveroutput on;
declare
cursor cemp is select ename,sal from emp;
cname emp.ename%type;
csal emp.sal%type;
begin
 open cemp;
 loop
  fetch cemp into cname,csal;
  exit when cemp%notfound;
  dbms_output.put_line(cname || '的薪水是' || csal);
 end loop;
end;
/ 

 带参数的游标

使用游标打印某部门号的所有员工姓名

set serveroutput on;
declare 
cursor cemp(cno emp.deptno%type) is select ename from emp where emp.deptno = cno;
cname emp.ename%type;
begin
 open cemp(10);
 loop 
  fetch cemp into cname;
  exit when cemp%notfound;
  dbms_output.put_line(cname);
  
 end loop;
end;
/ 

总结

以上所述是www.887551.com给大家介绍的详解oracle游标的简易用法，希望对大家有所帮助