欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

详解Oracle游标的简易用法

程序员文章站 2022-07-02 20:21:16
下面看下oracle游标的简易用法,具体代码如下所示: create or replace procedure nw_delyw(ioperation_id nu...

下面看下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;
/ 

总结

以上所述是小编给大家介绍的详解oracle游标的简易用法,希望对大家有所帮助