Oracle cursor Queries

Source: Internet
Author: User
Tags oracle cursor

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.