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 = '';
--3 以备份表1养老 为 参照，往备份表2中插如110数据，是用来生成 510 险种的
INSERT INTO AC02_WYL_2
SELECT * FROM AC02_WYL WHERE AAE140 = '';
--4 以备份表2 中的 110为参照，更新为 510险种
UPDATE AC02_WYL_2
SET AAE140 = '', AAE201 = 0, CAC014 = 201509
WHERE AAB001 = PI_AAB001
AND AAE140 = '';
--5 以备份表1的养老 为 参照，往备份表2中插如110数据，是用来生成 410 险种的 同步骤3
INSERT INTO AC02_WYL_2
SELECT * FROM AC02_WYL WHERE AAE140 = '';
--6 以备份表2 中的 110为参照，更新为 410险种 同步骤4
UPDATE AC02_WYL_2
SET AAE140 = '', AAE201 = 0, CAC014 = 201509
WHERE AAB001 = PI_AAB001
AND AAE140 = '';
--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)
)