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