详解Oracle游标的简易用法

数据库  /  管理员 发布于 6年前   172

下面看下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;declarecursor 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游标的简易用法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！