詳解Oracle游標(biāo)的簡(jiǎn)易用法

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;

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;
/