Oracle游标（三）

游标：

        用来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标分类：

静态游标：
       分为显式游标和隐式游标。

REF游标：
       是一种引用类型，类似于指针。

---------------------------------------------------------------------------------------

显式游标：

       CURSOR 游标名 ( 参数 ) [返回值类型] IS
       Select 语句

隐式游标：

       不用明确建立游标变量，分两种：
       1.在PL/SQL中使用DML语言，使用ORACLE提供的名为SQL的隐示游标
       2.CURSOR FOR LOOP，用于for loop 语句

       示例：

       declare
       begin
           for my_dept_rec in ( select department_name, department_id from departments)
           loop
              dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
           end loop;
       end;

        

REF游标：

        TYPE CUR_TYPE IS REF CURSOR;

---------------------------------------------------------------------------------------

游标生命周期：

在大多数时候我们在设计程序的时候都遵循下面的步骤：
　1、打开游标 open cs1;
　2、开始循环 while cs1%found loop | for column_name in .. LOOP
　3、从游标中取值 fetch .. into.. |
　4、检查那一行被返回
　5、处理
　6、关闭循环 end loop;
　7、关闭游标 if cs1&isopen then close cs1;

选项：参数和返回类型

最后，提供一个综合示例说明游标生命周期

CREATE OR REPLACE PROCEDURE OMS_BATCH_ADDBILI070 (
  OrgID number,     /*商场ID*/
  Remarks varchar2 ,/*备注*/
  O_RET_MSG out varchar2,
  O_RET_CODE out varchar2
)
AS
  TYPE CUR_TYPE IS REF CURSOR;
  cur CUR_TYPE;
  bilaa tb_bilaa%ROWTYPE;
  dbID number;
  seqID number;
  dbCode varchar2(30);
BEGIN
  O_RET_MSG  := '操作成功';
  O_RET_CODE := '0';


  OPEN cur FOR
  SELECT AA.* FROM TB_BILAA AA
    WHERE AA.FLAG = 0 AND AA.BILAA005 = OrgID AND AA.BILAA014 = ''||Remarks||''
      and exists (select 1 from tb_bilab ab
                  where ab.bilab001 = aa.bilaa_id and abs(nvl(bilab009,0)-nvl(bilab017,0)) > 0);


  /*取得bili070的单别*/
  select fa.pubfa_id into dbID
  from tb_pubfa fa
left join tb_sysac ac on fa.pubfa003=ac.sysac_id
where ac.sysac001 = 'bili070'
 and fa.flag = 0 and rownum < 2;


  LOOP
    FETCH cur INTO bilaa;
    EXIT WHEN cur%NOTFOUND;


    /*生成单号信息*/
    select seq_tb_bilae.nextval into seqID from dual;
    select 'SK-99'||lpad(seqID,12,0) into dbCode from dual;


    /*插入费用收款单主表信息*/
    INSERT INTO TB_BILAE (BILAE_ID,BILAE001,BILAE002,BILAE003,BILAE004,BILAE005,BILAE006,BILAE007,
      BILAE008,BILAE014,BILAE019,BILAE022,BILAE024,BILAE025,BILAE030,
      CREATE_USER,CREATE_DATE,FLAG)
    SELECT seqID,dbID,''||dbCode||'',sysdate as bilae003,be.conbe008 as bilae004,be.conbe010 as bilae005,
      be.conbe_id as bilae006,aa.bilaa005 as bilae007,'4' as bilae008,aa.bilaa002 as bilae014,aa.bilaa014 as bilaa019,
      'Y' as bilae022,sysdate as bilae024,'2' as bilae025,be.conbe079 as bilae030,'admin',sysdate,0
    FROM TB_BILAA AA
    join tb_conbe be on aa.bilaa008 = be.conbe_id
    WHERE AA.FLAG = 0 AND AA.BILAA_ID = bilaa.bilaa_id;


    /*插入费用收款单子表信息*/
    insert into tb_bilaf (bilaf_id,bilaf001,bilaf002,bilaf003,bilaf004,bilaf005,bilaf006,bilaf007,
      bilaf008,bilaf009,bilaf010,bilaf012,bilaf014,bilaf015,bilaf016,bilaf017,
      bilaf018,CREATE_USER,CREATE_DATE,FLAG)
    select seq_tb_bilab.nextval,seqID,'1' as bilaf002,aa.bilaa002 as bilaf003,aa.bilaa_id as bilaf004,
      ab.bilab_id as bilaf005,ab.bilab002 as bilaf006,nvl(bilab009,0)-nvl(bilab017,0) as bilaf007,
      ab.bilab011 as bilaf008,ab.bilab012 as bilaf009,ab.bilab010 as bilaf010,aa.bilaa028 as bilaf012,
      nvl(nvl(nvl(pubjl005,pubjm005),pubjn004),pubjo003) as bilaf014,nvl(nvl(nvl(pubjl006,pubjm006),pubjn005),pubjo004) as bilaf015,
      aa.bilaa007 as bilaf016,aa.bilaa008 as bilaf017,aa.bilaa031 as bilaf018,'admin',sysdate,0
    from tb_bilab ab
    join tb_bilaa aa on ab.bilab001 = aa.bilaa_id
    left join tb_pubjl on pubjl001=bilab002 and pubjl002=aa.bilaa005 and pubjl003=bilaa008
left join tb_pubjm on pubjm001=bilab002 and pubjm002=aa.bilaa005 and pubjm003=bilaa026 and pubjm004=bilaa027
left join tb_pubjn on pubjn001=bilab002 and pubjn002=aa.bilaa005 and pubjn003=bilaa026
left join tb_pubjo on pubjo001=bilab002 and pubjo002=aa.bilaa005
    where ab.flag = 0 and ab.bilab001 = bilaa.bilaa_id
      and abs(nvl(bilab009,0)-nvl(bilab017,0)) > 0;


    /*更新费用收款单单头合计金额*/
    update tb_bilae ae set ae.bilae009 = (select nvl(sum(af.bilaf007),0) from tb_bilaf af
                    where af.flag = 0 and af.bilaf001 = ae.bilae_id and af.bilaf001 = seqID)
where ae.flag = 0 and ae.bilae_id = seqID;


    /*更新费用单的收款金额*/
    update tb_bilab ab set ab.bilab008 = nvl(ab.bilab008,0) + (select nvl(af.bilaf007,0) from tb_bilaf af
                                                               where af.bilaf005 = ab.bilab_id),
                           ab.bilab009 = nvl(ab.bilab009,0) - (select nvl(af.bilaf007,0) from tb_bilaf af
                                                               where af.bilaf005 = ab.bilab_id)
      where ab.bilab001 = bilaa.bilaa_id;
  END LOOP;
  CLOSE cur;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    O_RET_CODE := '-100';
    O_RET_MSG  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
END OMS_BATCH_ADDBILI070;