Oracle 游标疑问

procedure1：

CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL(PI_AAB001 IN NUMBER,
                                                   PO_FHZ    OUT VARCHAR2,
                                                   PO_MSG    OUT VARCHAR2) IS
  V_AAZ159 NUMBER(20);
  --说明：以养老参保的情况为标准(即ac02为准)，添加aae140 = 410,510的险种
  --20150919 wyl

BEGIN
  --1 清空 备份表1
  DELETE FROM AC02_WYL;
  --2 往备份表1里 插入 ac02中险种为110的数据
  INSERT INTO AC02_WYL
    SELECT *
      FROM AC02
     WHERE AAB001 = PI_AAB001
       AND AAE140 = '110';
  --3 以备份表1养老 为 参照，往备份表2中插如110数据，是用来生成 510 险种的
  INSERT INTO AC02_WYL_2
    SELECT * FROM AC02_WYL WHERE AAE140 = '110';
  --4 以备份表2 中的 110为参照，更新为 510险种
  UPDATE AC02_WYL_2
     SET AAE140 = '510', AAE201 = 0, CAC014 = 201509
   WHERE AAB001 = PI_AAB001
     AND AAE140 = '110';
  --5 以备份表1的养老 为 参照，往备份表2中插如110数据，是用来生成 410 险种的 同步骤3
  INSERT INTO AC02_WYL_2
    SELECT * FROM AC02_WYL WHERE AAE140 = '110';
  --6 以备份表2 中的 110为参照，更新为 410险种  同步骤4
  UPDATE AC02_WYL_2
     SET AAE140 = '410', AAE201 = 0, CAC014 = 201509
   WHERE AAB001 = PI_AAB001
     AND AAE140 = '110';
  --7循环 ，主要是修改 aaz159,
  --调用 procedure UPDATE_AC02_AAE140_WYL_xh
  UPDATE_AC02_AAE140_WYL_XH(pi_aab001);
END;

UPDATE_AC02_AAE140_WYL_XH：

CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL_XH(PI_AAB001 VARCHAR2) IS
  V_AAZ159 NUMBER(20);
  CURSOR C_AC02_WYL IS
    SELECT * FROM AC02_WYL_2 WHERE AAB001 = PI_AAB001;
BEGIN
  FOR V_C_AC02_WYL IN C_AC02_WYL LOOP
    SELECT SEQ_BXGX_AAZ159.NEXTVAL INTO V_AAZ159 FROM DUAL;
    UPDATE AC02_WYL_2 SET AAZ159 = V_AAZ159;
  END LOOP;
END;

最后导入到ac02_wyl_2的数据的aaz159竟然都是同一个值。应该是loop的时候的某个步骤弄错了。

第二个过程改后就没问题了，改后的如下：

CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL_XH(PI_AAB001 VARCHAR2) IS
  V_AAZ159 NUMBER(20);
  CURSOR C_AC02_WYL IS
    SELECT * FROM AC02_WYL_2 WHERE AAB001 = PI_AAB001;
BEGIN
  FOR V_C_AC02_WYL IN C_AC02_WYL LOOP
    SELECT SEQ_BXGX_AAZ159.NEXTVAL INTO V_AAZ159 FROM DUAL;
    UPDATE AC02_WYL_2
       SET AAZ159 = V_AAZ159
     WHERE AAZ159 = V_C_AC02_WYL.AAZ159
       AND AAB001 = PI_AAB001;
  END LOOP;
END;

之前错误的数据导致aaz159都是用一个号，

ac02_wyl_2的具体数据如下图：

附ac02的建表sql：

create table AC02
(
  aaz159 NUMBER(20) not null,
  bae001 VARCHAR2(14) not null,
  aab001 NUMBER(20),
  aac001 NUMBER(20) not null,
  aae140 VARCHAR2(6) not null,
  aac013 VARCHAR2(6),
  cac013 VARCHAR2(6),
  aaa095 VARCHAR2(6) not null,
  aac008 VARCHAR2(6) not null,
  aac049 NUMBER(6) not null,
  cac014 NUMBER(8),
  aae201 NUMBER(4) not null,
  aaz099 NUMBER(20),
  aac048 NUMBER(6)
)