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 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
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.

Re: Data manipulation [message #560587 is a reply to message #560586] Sun, 15 July 2012 16:11 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
./fa/3943/0/
Re: Data manipulation [message #560608 is a reply to message #560586] Mon, 16 July 2012 00:43 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much barbara mam, this is what exactly i was looking for.I Appreciate your help sincerely.Seriously the kind of support you are doing is great.
Previous Topic: ORA-00972: identifier is too long
Next Topic: Regexp
Goto Forum:
  


Current Time: Wed Aug 27 22:32:08 CDT 2014

Total time taken to generate the page: 0.10348 seconds