存储过程 游标使用实例

　　使用游标对记录集循环进行处理的时候一般操作如以下几个步骤：1、把记录集传给游标; 2、打开游标；3、开始循环；4、从游标中取值；5、检查那一行被返回；6、数据处理；7、关闭循环；8、关闭游标。

如下示例：

DECLARE
 CURSOR FirstCursor IS --声明显示游标，并指定结果集
  SELECT T.ACC_NUM ACC_NUM,t.ext_prod_id EXT_PROD_ID, COUNT(1) COUNT,SYSDATE AUDI_TIME
  FROM (SELECT DISTINCT T3.PAY_ACCT_ID,T2.ACCT_ID, T1.ACC_NUM,t1.ext_prod_id
          FROM CRM2_XX.PROD_INST@DB_GROUP_XX     T1,
               CRM2_XX.Serv_Acct_Rel@DB_GROUP_XX T2,
               CRM2_XX.PAYMENT_PLAN@DB_GROUP_XX T3,
               CRM2_XX.COMMON_REGION@DB_GROUP_XX CR
         WHERE T1.REGION_ID = CR.COMMON_REGION_ID
           AND T1.PROD_INST_ID = T2.PROD_INST_ID
     AND T3.SERV_ACCT_REL_ID=T2.SERV_ACCT_REL_ID
           AND T1.STATUS_CD <> '110000'
           AND T2.STATUS_CD = '1') T
 GROUP BY T.ACC_NUM,t.ext_prod_id
HAVING COUNT(1) > 1;
 BEGIN--语法
  FOR REC IN FirstCursor LOOP--开始循环遍历，rec相当java语法中的i，数组的下标
   INSERT INTO NUMCOUNT_MHR_GD_COUSOR
    (acc_num,
    ext_prod_id,
    count,
    audi_time)
   VALUES(
    REC.ACC_NUM,
    REC.EXT_PROD_ID,
    REC.COUNT,
    REC.AUDI_TIME);
   COMMIT;--从游标中取值，并插入目标表
   EXIT WHEN FirstCursor%NOTFOUND;--当游标中再无更多记录时退出
  COMMIT;
 END LOOP;--遍历结束，关闭循环
END;--结束

同下：

create table CS_COUNT_LOG
(
  it          VARCHAR2(30),
  idate       DATE,
  irecore_num NUMBER
)

declare
  iStep  number;
  iCount number;
begin
  iStep  := 0;
  iCount := 0;
  for rec in (SELECT T1.*
                FROM CRM2_XX.V_OFFER_INST_INFO T1, AUDI_SAMPLE_JSNJ T2
               where t1.PROD_INST_ID = t2.prod_inst_id) loop
    INSERT INTO ASA_OFFER_INFO_PROV_JSNJ_BK(
      PROV_CODE,
      PROD_INST_ID,
      OFFER_INST_ID,
          OFFER_ID,
          EXT_OFFER_ID,
          OWNER_CUST_ID,
          CHANNEL_ID,
          EFF_DATE,
          EXP_DATE,
          AUDI_DATE,
          AUDI_BATCH
  )
    values
      ('JSNJ',
       rec.PROD_INST_ID,
       rec.OFFER_INST_ID,
       rec.OFFER_ID,
       rec.EXT_OFFER_ID,
       rec.OWNER_CUST_ID,
       rec.CHANNEL_ID,
       rec.EFF_DATE,
       rec.EXP_DATE,
     sysdate,
       '2017-08');
    iStep  := iStep + 1;
    iCount := iCount + 1;
    if iStep = 2000 then
      iStep := 0;
      insert into CS_COUNT_LOG values ('CP',sysdate, iCount);--iStep到2000则置为0，且插入iCount
      commit;
    end if;
  end loop;
  insert into CS_COUNT_LOG values ('CP',sysdate, iCount);--iStep不到2000，则在循环结束后插入iCount
 
end;
/

示例：

 

CREATE OR REPLACE PROCEDURE sp_sync_plan IS
  CURSOR C_EMP IS --声明显式游标 
    SELECT * FROM dc_check_todo; 
  C_ROW C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录 
BEGIN
  --For 循环 
  FOR C_ROW IN C_EMP LOOP 
    DBMS_OUTPUT.PUT_LINE(C_ROW.todo_id || '--' ); 
  END LOOP; 

  --Fetch 循环 
  OPEN C_EMP;--必须要明确的打开和关闭游标 
  LOOP 
    FETCH C_EMP 
      INTO C_ROW; 
    EXIT WHEN C_EMP%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE(C_ROW.todo_id || '++' ); 
  END LOOP; 
  CLOSE C_EMP; 

  --While 循环 
  OPEN C_EMP;--必须要明确的打开和关闭游标 
    FETCH C_EMP INTO C_ROW; 
    WHILE C_EMP%FOUND LOOP DBMS_OUTPUT.PUT_LINE(C_ROW.todo_id || '**' ); 
      FETCH C_EMP INTO C_ROW; 
    END LOOP; 
  CLOSE C_EMP; 
END sp_sync_plan;