Home » SQL & PL/SQL » SQL & PL/SQL » Data manipulation (Oracle 10.2.0.1.0)
| Data manipulation [message #560582] |
Sun, 15 July 2012 07:45  |
|
|
I have a table where i need to perform update on a field it_grade based on data in another field it_flex_01 of the same table ,which will be provided by user after insert is done, actually i am copying this table data into a temporary table and then updating it there in temporary table and then inserting it back again using procedure due to reason that whatever data gets inserted into ot_ins_item table there will be a record inserted in stock_balance table as well, everytime i need to execute this procedure to do this changes.Is there a simpler way to avoid this manual execution.
create table ot_ins_head (ins_no number,ins_flex_01 varchar2(12));
ALTER TABLE ot_ins_head ADD (
CONSTRAINT ot_ins_head_pk
PRIMARY KEY
(ins_no));
create sequence s_it_no;
CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),it_flex_01 VARCHAR2(12));
ALTER TABLE ot_ins_item ADD (
CONSTRAINT ot_ins_item_fk_01
FOREIGN KEY (it_ins_no)
REFERENCES ot_ins_head (ins_no));
INSERT INTO OT_INS_HEAD VALUES (1,NULL);
create table stk_bal (stk_it_no number, stk_it_grade varchar2(12));
CREATE OR REPLACE TRIGGER ins_stk
AFTER INSERT
ON ot_ins_item
FOR EACH ROW
BEGIN
INSERT INTO stk_bal
VALUES (:NEW.it_no, :NEW.it_grade);
END;
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','1');
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','1');
commit;
SQL> select * from ot_ins_item;--data before change of grade.
IT_INS_NO IT_NO IT_GRADE IT_FLEX_01
--------- --------- ------------ ------------
1 3 A 1
1 4 A 1
2 rows selected.
CREATE TABLE OT_INS_ITEM_HT AS SELECT * FROM OT_INS_ITEM WHERE 1=2 ; -- this is the temporary table
--now execution what i am doing and what i want
SQL> select * from stk_bal;
STK_IT_NO STK_IT_GRADE
--------- ------------
3 A
4 A
2 rows selected.
--after execution of below procedure
CREATE OR REPLACE PROCEDURE aic_i_chg (
p_h_sys IN NUMBER,
p_d_sys IN NUMBER
)
IS
CURSOR c_item
IS
SELECT it_ins_no, it_no, it_grade, it_flex_01
FROM ot_ins_item
WHERE it_ins_no = p_h_sys AND it_no = p_d_sys;
BEGIN
FOR i IN c_item
LOOP
INSERT INTO ot_ins_item_ht
(it_ins_no, it_no, it_grade, it_flex_01
)
VALUES (i.it_ins_no, i.it_no, i.it_grade, i.it_flex_01
);
COMMIT;
END LOOP;
DELETE ot_ins_item
WHERE it_ins_no = p_h_sys AND it_no = p_d_sys;
COMMIT;
UPDATE ot_ins_item_ht
SET it_grade = DECODE (NVL (it_flex_01, '*'),'*', it_grade, it_flex_01 )
WHERE it_ins_no = p_h_sys AND it_no = p_d_sys;
COMMIT;
INSERT INTO ot_ins_item
(SELECT *
FROM ot_ins_item_ht
WHERE it_ins_no = p_h_sys AND it_no = p_d_sys);
COMMIT;
DELETE ot_ins_item_ht
WHERE it_ins_no = p_h_sys AND it_no = p_d_sys;
COMMIT;
END;
/
SQL> exec aic_i_chg(1,3);
PL/SQL procedure successfully completed.
SQL> select * from stk_bal; --after execution this has recorded new entry in stk_bal
STK_IT_NO STK_IT_GRADE
--------- ------------
3 A
4 A
3 1
3 rows selected.
SQL> exec aic_i_chg(1,4);
PL/SQL procedure successfully completed.
SQL> select * from stk_bal;
STK_IT_NO STK_IT_GRADE
--------- ------------
3 A
4 A
3 1
4 1
4 rows selected.
SQL> select * from ot_ins_item;-- after execution it_grade will change based on it_flex_01.
IT_INS_NO IT_NO IT_GRADE IT_FLEX_01
--------- --------- ------------ ------------
1 3 1 1
1 4 1 1
2 rows selected.
|
|
|
|
| Re: Data manipulation [message #560586 is a reply to message #560582] |
Sun, 15 July 2012 15:36  |
 |
Barbara Boehmer
Messages: 7667
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- tables, constraints, and sequence you provided:
SCOTT@orcl_11gR2> create table ot_ins_head
2 (ins_no number,
3 ins_flex_01 varchar2(12))
4 /
Table created.
SCOTT@orcl_11gR2> ALTER TABLE ot_ins_head ADD
2 (CONSTRAINT ot_ins_head_pk
3 PRIMARY KEY (ins_no))
4 /
Table altered.
SCOTT@orcl_11gR2> CREATE TABLE ot_ins_item
2 (it_ins_no NUMBER,
3 it_no NUMBER,
4 it_grade VARCHAR2(12),
5 it_flex_01 VARCHAR2(12))
6 /
Table created.
SCOTT@orcl_11gR2> ALTER TABLE ot_ins_item ADD
2 (CONSTRAINT ot_ins_item_fk_01
3 FOREIGN KEY (it_ins_no)
4 REFERENCES ot_ins_head (ins_no))
5 /
Table altered.
SCOTT@orcl_11gR2> create table stk_bal
2 (stk_it_no number,
3 stk_it_grade varchar2(12))
4 /
Table created.
SCOTT@orcl_11gR2> create sequence s_it_no start with 3
2 /
Sequence created.
-- revised trigger (no procedure and no ot_ins_item_ht table):
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ins_stk
2 BEFORE INSERT OR UPDATE
3 ON ot_ins_item
4 FOR EACH ROW
5 BEGIN
6 IF INSERTING THEN
7 SELECT s_it_no.NEXTVAL INTO :NEW.it_no FROM DUAL;
8 INSERT INTO stk_bal (stk_it_no, stk_it_grade)
9 VALUES (:NEW.it_no, :NEW.it_grade);
10 -- delete the next three lines of code if you don't want to
11 -- update the it_grade on insert and
12 -- insert the updated values into stk_bal
13 :NEW.it_grade := NVL (:NEW.it_flex_01, :NEW.it_grade);
14 INSERT INTO stk_bal (stk_it_no, stk_it_grade)
15 VALUES (:NEW.it_no, :NEW.it_grade);
16 ELSIF UPDATING THEN
17 :NEW.it_grade := NVL (:NEW.it_flex_01, :NEW.it_grade);
18 INSERT INTO stk_bal (stk_it_no, stk_it_grade)
19 VALUES (:NEW.it_no, :NEW.it_grade);
20 END IF;
21 END;
22 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- inserts you provided::
SCOTT@orcl_11gR2> INSERT INTO OT_INS_HEAD (ins_no) VALUES (1)
2 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO OT_INS_ITEM (it_ins_no, it_grade, it_flex_01) VALUES (1, 'A', '1')
2 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO OT_INS_ITEM (it_ins_no, it_grade, it_flex_01) VALUES (1, 'A', '1')
2 /
1 row created.
SCOTT@orcl_11gR2> commit
2 /
Commit complete.
-- results:
SCOTT@orcl_11gR2> select * from ot_ins_head
2 /
INS_NO INS_FLEX_01
---------- ------------
1
1 row selected.
SCOTT@orcl_11gR2> select * from ot_ins_item order by it_ins_no, it_no
2 /
IT_INS_NO IT_NO IT_GRADE IT_FLEX_01
---------- ---------- ------------ ------------
1 3 1 1
1 4 1 1
2 rows selected.
SCOTT@orcl_11gR2> select * from stk_bal order by stk_it_grade desc, stk_it_no
2 /
STK_IT_NO STK_IT_GRADE
---------- ------------
3 A
4 A
3 1
4 1
4 rows selected.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 13:23:58 CDT 2013
Total time taken to generate the page: 0.82586 seconds
|
] 
Members
Search
Help
Register
Login
Home
