4 Replies Latest reply: Nov 17, 2012 12:40 AM by Chanchal Wankhade RSS

    Insert and delete operation on same table

    944789
      I have a requirement of deleting and inserting data at the same time on same table, the reason being that i have one table ot_ins_item where user will be entering data as given below, after entering he will enter the new data in ot_ins_heat table and ot_ins_batch table , after inserting in these two tables based on no of records and new data in ot_ins_heat , records will get deleted and inserted in ot_ins_item.
      SQL> CREATE TABLE OT_INS_ITEM
        2      (
        3        II_ID    NUMBER,
        4        II_ITEM_CD  VARCHAR2(12),
        5        II_HEAT_CD  VARCHAR2(12),
        6        II_QTY    NUMBER
        7      )
        8      /
      
      Table created.
      
      SQL> ALTER TABLE OT_INS_ITEM ADD (
        2        CONSTRAINT OT_INS_ITEM_UK
        3       UNIQUE (II_ID))
        4      /
      
      Table altered.
      
      SQL> CREATE SEQUENCE II_ID START WITH 1;
      
      Sequence created.
      
      SQL> CREATE TABLE OT_INS_HEAT
        2      (
        3        IH_ID  NUMBER,
        4        IH_II_ID  NUMBER,
        5        IH_HEAT  VARCHAR2(12),
        6        IH_QTY  NUMBER
        7      )
        8      /
      
      Table created.
      
      SQL> ALTER TABLE OT_INS_HEAT ADD (
        2        CONSTRAINT OT_INS_HEAT_FK_1
        3       FOREIGN KEY (IH_II_ID)
        4       REFERENCES OT_INS_ITEM (II_ID))
        5      /
      
      Table altered.
      
      SQL> CREATE SEQUENCE IH_ID START WITH 1;
      
      Sequence created.
      
      SQL> CREATE TABLE OT_INS_BATCH
        2      (
        3        IB_ID  NUMBER,
        4        IB_II_ID  NUMBER,
        5        IB_BATCH  VARCHAR2(12 BYTE),
        6        IB_QTY  NUMBER
        7      )
        8      /
      
      Table created.
      
      SQL> ALTER TABLE OT_INS_BATCH ADD (
        2        CONSTRAINT OT_INS_BATCH_PK
        3       PRIMARY KEY
        4       (IB_ID))
        5      /
      
      Table altered.
      
      SQL> ALTER TABLE OT_INS_BATCH ADD (
        2        CONSTRAINT OT_INS_BATCH_FK_1
        3       FOREIGN KEY (IB_II_ID)
        4       REFERENCES OT_INS_ITEM (II_ID))
        5      /
      
      Table altered.
      
      SQL> CREATE SEQUENCE IB_ID START WITH 1
        2  /
      
      Sequence created.
      
      SQL> BEGIN
        2        INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM1','A',4);
        3        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'X',1);
        4        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Y',1);
        5        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'Z',2);
        6        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'XXX',4);
        7        INSERT INTO OT_INS_ITEM VALUES (II_ID.NEXTVAL,'ITEM2','B',4);
        8        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'E',1);
        9        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'F',1);
       10        INSERT INTO OT_INS_HEAT VALUES (IH_ID.NEXTVAL,II_ID.CURRVAL,'G',2);
       11        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'YYY',1);
       12        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'ZZZ',2);
       13        INSERT INTO OT_INS_BATCH VALUES (IB_ID.NEXTVAL,II_ID.CURRVAL,'AAA',1);
       14        COMMIT;
       15    END;
       16     /
      
      PL/SQL procedure successfully completed.
      
      SQL> SELECT * FROM OT_INS_ITEM;
      
           II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
      ---------- ------------ ------------ ----------                                 
               1 ITEM1        A                     4                                 
               2 ITEM2        B                     4                                 
      
      SQL> SELECT * FROM OT_INS_HEAT;
      
           IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
      ---------- ---------- ------------ ----------                                   
               1          1 X                     1                                   
               2          1 Y                     1                                   
               3          1 Z                     2                                   
               4          2 E                     1                                   
               5          2 F                     1                                   
               6          2 G                     2                                   
      
      6 rows selected.
      
      SQL> SELECT * FROM OT_INS_BATCH;
      
           IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
      ---------- ---------- ------------ ----------                                   
               1          1 XXX                   4                                   
               2          2 YYY                   1                                   
               3          2 ZZZ                   2                                   
               4          2 AAA                   1                                   
      
      SQL>  CREATE OR REPLACE PROCEDURE insp_heat_chg2 (p_sys_id NUMBER)
        2      IS
        3        v_ib_qty  NUMBER := 0;
        4        v_ih_qty  NUMBER := 0;
        5      BEGIN
        6        FOR r IN
        7        (SELECT i.ii_id, i.ii_item_cd,
        8         h.ih_id, h.ih_heat, h.ih_qty,
        9         b.ib_id, b.ib_batch, b.ib_qty,
       10        ROW_NUMBER () OVER (PARTITION BY i.ii_id, b.ib_id ORDER BY h.ih_id) rn
       11        FROM  ot_ins_item i, ot_ins_heat h, ot_ins_batch b
       12        WHERE  i.ii_id = h.ih_ii_id
       13        AND  i.ii_id = b.ib_ii_id
       14        AND  i.ii_id = p_sys_id
       15        ORDER  BY i.ii_id, b.ib_id, h.ih_id)
       16       LOOP
       17       IF r.rn = 1 THEN
       18         v_ib_qty := v_ib_qty + r.ib_qty;
       19         v_ih_qty := v_ih_qty * -1;
       20       END IF;
       21       IF v_ih_qty < 0 THEN
       22              v_ih_qty := v_ih_qty + r.ih_qty;
       23       ELSIF v_ib_qty - v_ih_qty > 0 THEN
       24         INSERT INTO ot_ins_item (ii_id, ii_item_cd, ii_heat_cd, ii_qty)
       25         VALUES (ii_id.NEXTVAL, r.ii_item_cd, r.ih_heat, r.ih_qty);
       26         INSERT INTO ot_ins_heat (ih_id, ih_ii_id, ih_heat, ih_qty)
       27         VALUES (ih_id.NEXTVAL, ii_id.CURRVAL, r.ih_heat, r.ih_qty);
       28         INSERT INTO ot_ins_batch (ib_id, ib_ii_id, ib_batch, ib_qty)
       29         VALUES (ib_id.NEXTVAL, ii_id.CURRVAL, r.ib_batch, r.ih_qty);
       30         v_ih_qty := v_ih_qty + r.ih_qty;
       31       END IF;
       32       END LOOP;
       33       DELETE ot_ins_batch WHERE ib_ii_id = p_sys_id;
       34       DELETE ot_ins_heat WHERE ih_ii_id = p_sys_id;
       35       DELETE ot_ins_item WHERE ii_id = p_sys_id;
       36     END insp_heat_chg2;
       37     /
      
      Procedure created.
      
      SQL> BEGIN
        2        FOR r IN
        3        (SELECT ii_id FROM ot_ins_item)
        4        LOOP
        5        insp_heat_chg2 (r.ii_id);
        6        END LOOP;
        7      END;
        8      /
      
      PL/SQL procedure successfully completed.
      
      SQL> COMMIT;
      
      Commit complete.
      
      SQL> SELECT * FROM OT_INS_ITEM ORDER BY ii_id;
      
           II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
      ---------- ------------ ------------ ----------                                 
               3 ITEM1        X                     1                                 
               4 ITEM1        Y                     1                                 
               5 ITEM1        Z                     2                                 
               6 ITEM2        E                     1                                 
               7 ITEM2        F                     1                                 
               8 ITEM2        G                     2                                 
      
      6 rows selected.
      
      SQL> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id;
      
           IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
      ---------- ---------- ------------ ----------                                   
               7          3 X                     1                                   
               8          4 Y                     1                                   
               9          5 Z                     2                                   
              10          6 E                     1                                   
              11          7 F                     1                                   
              12          8 G                     2                                   
      
      6 rows selected.
      
      SQL> SELECT * FROM OT_INS_BATCH ORDER BY ib_id;
      
           IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
      ---------- ---------- ------------ ----------                                   
               5          3 XXX                   1                                   
               6          4 XXX                   1                                   
               7          5 XXX                   2                                   
               8          6 YYY                   1                                   
               9          7 ZZZ                   1                                   
              10          8 ZZZ                   2                                   
      
      6 rows selected.
      
      
      --Desired output must be
      
      
      SQL> SELECT * FROM OT_INS_ITEM ORDER BY ii_id;
      
           II_ID II_ITEM_CD   II_HEAT_CD       II_QTY                                 
      ---------- ------------ ------------ ----------                                 
               3 ITEM1        X                     1                                 
               4 ITEM1        Y                     1                                 
               5 ITEM1        Z                     2                                 
               6 ITEM2        E                     1                                 
               7 ITEM2        F                     1 
               8 ITEM2        F                     1                                 
               9 ITEM2        G                     1                                 
      
      6 rows selected.
      
      SQL> SELECT  * FROM OT_INS_HEAT ORDER BY ih_id;
      
           IH_ID   IH_II_ID IH_HEAT          IH_QTY                                   
      ---------- ---------- ------------ ----------                                   
               7          3 X                     1                                   
               8          4 Y                     1                                   
               9          5 Z                     2                                   
              10          6 E                     1                                   
              11          7 F                     1                                   
              12          8 F                     1
              13          9 G                     1                                   
      
      
      
      
      
      
           IB_ID   IB_II_ID IB_BATCH         IB_QTY                                   
      ---------- ---------- ------------ ----------                                   
               5          3              XXX                   1                                   
               6          4              XXX                   1                                   
               7          5              XXX                   2                                   
               8          6              YYY                   1                                   
               9          7               ZZZ                   1                                   
              10          8              ZZZ                   1
              11          9              AAA                   1
      Edited by: Arif75 on Nov 17, 2012 11:13 AM
        • 1. Re: Insert and delete operation on same table
          sb92075
          CONGRATULATIONS!
          • 2. Re: Insert and delete operation on same table
            jeneesh
            Your post is marked as a question, and I cant see any question in it..

            Are you getting an error?
            Or any performance issues?
            Or not getting expected output..?

            Please read FAQ:{message:id=9360002}

            Provide your DB version, sample data, expected output....

            And for INSERT and DELETE on same table, you may be able to use MERGE. Check the same..
            • 3. Re: Insert and delete operation on same table
              rp0428
              Please edit your post and replace
               with \{code} to preserve formatting. See the FAQ for other formatting options. Use the 'preview' tab to see how it looks before you submit.
              
              Also you need to actually ask a question or present an issue. All you did was post a lot of code. Code is nice - but if you don't tell anyone why you are posting it or what the problem is that you are having with the code no one can help you.
              
              All you said was
              {quote}
              I have a requirement of deleting and inserting data at the same time on same table
              {quote}
              Oracle is used for that all the time. Why does that seem to be a problem for you?
              
              Why are you using PL/SQL and a procedure rather than just using SQL?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              • 4. Re: Insert and delete operation on same table
                Chanchal Wankhade
                Hi,

                You need to use MERGE.

                Do search on the same so you will get some idea and confident.