详解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;

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 ;

/