存储过程游标使用实例

　　使用游标对记录集循环进行处理的时候一般操作如以下几个步骤：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;

存储过程 游标使用实例

存储过程游标使用实例