Procedure1:
1 CREATE OR REPLACE PROCEDUREUpdate_ac02_aae140_wyl (pi_aab001inch Number,2Po_fhz outVARCHAR2,3Po_msg outVARCHAR2) is4v_aaz159 Number( -);5 --Description: Take the condition of the old-age insured (that is, AC02), add aae140 = 410,510 of the type of insurance6 --20150919 Wyl7 8 BEGIN9 --1 emptying the backup Table 1Ten DELETE fromAc02_wyl; One --2 Insert data from AC02 to 110 in Backup Table 1 A INSERT intoAc02_wyl - SELECT * - fromAC02 the WHEREAAB001=pi_aab001 - andAAE140= ' the'; - --3 for the table 1 for the old age for reference, to the backup table 2 inserted as 110 data, is used to generate 510 types of insurance - INSERT intoac02_wyl_2 + SELECT * fromAc02_wylWHEREAAE140= ' the'; - --4 for the reference in table 2 of 110, updated to 510 types of insurance + UPDATEac02_wyl_2 A SETAAE140= '510', AAE201= 0, CAC014= 201509 at WHEREAAB001=pi_aab001 - andAAE140= ' the'; - --5 in case of table 1 for the elderly for reference, to the backup table 2 inserted as 110 data, is used to generate 410 of the same steps 3 - INSERT intoac02_wyl_2 - SELECT * fromAc02_wylWHEREAAE140= ' the'; - --6 in case of table 2, 110 as a reference, updated to 410 risk of the same step 4 in UPDATEac02_wyl_2 - SETAAE140= '410', AAE201= 0, CAC014= 201509 to WHEREAAB001=pi_aab001 + andAAE140= ' the'; - --7 cycle, mainly to modify the aaz159, the --Call procedure Update_ac02_aae140_wyl_xh * update_ac02_aae140_wyl_xh (pi_aab001); $ END;
UPDATE_AC02_AAE140_WYL_XH:
1 CREATE OR REPLACE PROCEDUREUpdate_ac02_aae140_wyl_xh (pi_aab001VARCHAR2) is2v_aaz159 Number( -);3 CURSORC_ac02_wyl is4 SELECT * fromAc02_wyl_2WHEREAAB001=pi_aab001;5 BEGIN6 forV_c_ac02_wylinchC_ac02_wyl LOOP7 SELECTseq_bxgx_aaz159. Nextval intov_aaz159 fromDUAL;8 UPDATEAc02_wyl_2SETAAZ159=v_aaz159;9 ENDLOOP;Ten END;
The aaz159 of the last data imported into ac02_wyl_2 are all the same value. One of the steps in the loop should be a mistake.
The second process is not a problem after the change, after the following:
1 CREATE OR REPLACE PROCEDUREUpdate_ac02_aae140_wyl_xh (pi_aab001VARCHAR2) is2v_aaz159 Number( -);3 CURSORC_ac02_wyl is4 SELECT * fromAc02_wyl_2WHEREAAB001=pi_aab001;5 BEGIN6 forV_c_ac02_wylinchC_ac02_wyl LOOP7 SELECTseq_bxgx_aaz159. Nextval intov_aaz159 fromDUAL;8 UPDATEac02_wyl_29 SETAAZ159=v_aaz159Ten WHEREAAZ159=V_c_ac02_wyl. AAZ159 One andAAB001=pi_aab001; A ENDLOOP; - END;
Before the wrong data cause aaz159 to be a number,
Ac02_wyl_2 specific data such as:
Table SQL with AC02 included:
1 Create TableAC022 (3aaz159 Number( -) not NULL,4bae001VARCHAR2( -) not NULL,5aab001 Number( -),6aac001 Number( -) not NULL,7aae140VARCHAR2(6) not NULL,8aac013VARCHAR2(6),9cac013VARCHAR2(6),Tenaaa095VARCHAR2(6) not NULL, Oneaac008VARCHAR2(6) not NULL, Aaac049 Number(6) not NULL, -cac014 Number(8), -aae201 Number(4) not NULL, theaaz099 Number( -), -aac048 Number(6) -)
Oracle cursor Queries