Oracle cursor Queries

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