Oracle 游标疑问
procedure1：
1 CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL(PI_AAB001 IN NUMBER, 2 PO_FHZ OUT VARCHAR2, 3 PO_MSG OUT VARCHAR2) IS 4 V_AAZ159 NUMBER(20); 5 --说明：以养老参保的情况为标准(即ac02为准)，添加aae140 = 410,510的险种 6 --20150919 wyl 7 8 BEGIN 9 --1 清空 备份表1 10 DELETE FROM AC02_WYL; 11 --2 往备份表1里 插入 ac02中险种为110的数据 12 INSERT INTO AC02_WYL 13 SELECT * 14 FROM AC02 15 WHERE AAB001 = PI_AAB001 16 AND AAE140 = '110'; 17 --3 以备份表1养老 为 参照，往备份表2中插如110数据，是用来生成 510 险种的 18 INSERT INTO AC02_WYL_2 19 SELECT * FROM AC02_WYL WHERE AAE140 = '110'; 20 --4 以备份表2 中的 110为参照，更新为 510险种 21 UPDATE AC02_WYL_2 22 SET AAE140 = '510', AAE201 = 0, CAC014 = 201509 23 WHERE AAB001 = PI_AAB001 24 AND AAE140 = '110'; 25 --5 以备份表1的养老 为 参照，往备份表2中插如110数据，是用来生成 410 险种的 同步骤3 26 INSERT INTO AC02_WYL_2 27 SELECT * FROM AC02_WYL WHERE AAE140 = '110'; 28 --6 以备份表2 中的 110为参照，更新为 410险种 同步骤4 29 UPDATE AC02_WYL_2 30 SET AAE140 = '410', AAE201 = 0, CAC014 = 201509 31 WHERE AAB001 = PI_AAB001 32 AND AAE140 = '110'; 33 --7循环 ，主要是修改 aaz159, 34 --调用 procedure UPDATE_AC02_AAE140_WYL_xh 35 UPDATE_AC02_AAE140_WYL_XH(pi_aab001); 36 END;
UPDATE_AC02_AAE140_WYL_XH：
1 CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL_XH(PI_AAB001 VARCHAR2) IS 2 V_AAZ159 NUMBER(20); 3 CURSOR C_AC02_WYL IS 4 SELECT * FROM AC02_WYL_2 WHERE AAB001 = PI_AAB001; 5 BEGIN 6 FOR V_C_AC02_WYL IN C_AC02_WYL LOOP 7 SELECT SEQ_BXGX_AAZ159.NEXTVAL INTO V_AAZ159 FROM DUAL; 8 UPDATE AC02_WYL_2 SET AAZ159 = V_AAZ159; 9 END LOOP; 10 END;
最后导入到ac02_wyl_2的数据的aaz159竟然都是同一个值。应该是loop的时候的某个步骤弄错了。
第二个过程改后就没问题了，改后的如下：
1 CREATE OR REPLACE PROCEDURE UPDATE_AC02_AAE140_WYL_XH(PI_AAB001 VARCHAR2) IS 2 V_AAZ159 NUMBER(20); 3 CURSOR C_AC02_WYL IS 4 SELECT * FROM AC02_WYL_2 WHERE AAB001 = PI_AAB001; 5 BEGIN 6 FOR V_C_AC02_WYL IN C_AC02_WYL LOOP 7 SELECT SEQ_BXGX_AAZ159.NEXTVAL INTO V_AAZ159 FROM DUAL; 8 UPDATE AC02_WYL_2 9 SET AAZ159 = V_AAZ159 10 WHERE AAZ159 = V_C_AC02_WYL.AAZ159 11 AND AAB001 = PI_AAB001; 12 END LOOP; 13 END;
之前错误的数据导致aaz159都是用一个号，
ac02_wyl_2的具体数据如下图：
附ac02的建表sql：
1 create table AC02 2 ( 3 aaz159 NUMBER(20) not null, 4 bae001 VARCHAR2(14) not null, 5 aab001 NUMBER(20), 6 aac001 NUMBER(20) not null, 7 aae140 VARCHAR2(6) not null, 8 aac013 VARCHAR2(6), 9 cac013 VARCHAR2(6), 10 aaa095 VARCHAR2(6) not null, 11 aac008 VARCHAR2(6) not null, 12 aac049 NUMBER(6) not null, 13 cac014 NUMBER(8), 14 aae201 NUMBER(4) not null, 15 aaz099 NUMBER(20), 16 aac048 NUMBER(6) 17 )
