Akdora’s Blog

Programming, Oracle, Life, Fun

Oracle Insert,delete,update procedures January 7, 2007

Filed under: Oracle — Akdora @ 10:20 am
Tags: , , , , ,

Package about inserting, deleting and updating processes.

CREATE SEQUENCE tt1_seq start with 1
/

CREATE TABLE tt1
(
 temp_id   NUMBER not null primary key,
 data1   VARCHAR2(50),
 data2   VARCHAR2(50),
 create_time TIMESTAMP
)
/

CREATE OR REPLACE TRIGGER tt1_ti
   before insert on tt1
   for each row
   declare 
    l_temp_id number;
   begin
    select tt1_seq.nextval
      into l_temp_id
      from dual;
   :new.temp_id := l_temp_id;
   end;
/

CREATE OR REPLACE PACKAGE DATA_PROCESSES IS
 PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2,
    l_vdata2  IN VARCHAR2);
 PROCEDURE delete_tt1(l_nid  IN NUMBER);
 PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2,
    l_vdata2  IN VARCHAR2);
END DATA_PROCESSES;

CREATE OR REPLACE PACKAGE BODY DATA_PROCESSES IS

 PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2,
     l_vdata2  IN VARCHAR2) IS
  l_stmt_str VARCHAR2(200);
 BEGIN
  SAVEPOINT start_transaction;  —- mark a savepoint
  l_stmt_str := ‘INSERT INTO tt1 (data1,  data2, create_time) ‘ ||
    ‘VALUES(:data1, :data2, SYSDATE)’;
  EXECUTE IMMEDIATE l_stmt_str
   USING l_vdata1, l_vdata2;
 EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK TO start_transaction; —- undo changeS
   dbms_output.put_line(‘An error occurred while creating query: ‘ ||
         SQLERRM ||
         ‘.  Please try again later.’);
 END;

 PROCEDURE delete_tt1(l_nid  IN NUMBER) IS
  l_stmt_str VARCHAR2(200);
 BEGIN
  SAVEPOINT start_transaction;  —- mark a savepoint
  l_stmt_str := ‘DELETE FROM tt1 WHERE temp_id = :1’;
  EXECUTE IMMEDIATE l_stmt_str USING l_nid;
 EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK TO start_transaction; —- undo changeS
   dbms_output.put_line(‘An error occurred while creating query: ‘ ||
         SQLERRM ||
         ‘.  Please try again later.’);
 END;

 PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2,
     l_vdata2  IN VARCHAR2) IS
  l_stmt_str VARCHAR2(200);
 BEGIN
  SAVEPOINT start_transaction;  —- mark a savepoint
  l_stmt_str := ‘UPDATE tt1 SET data1=:1, data2=:2 WHERE temp_id = :3’;
  EXECUTE IMMEDIATE l_stmt_str USING l_vdata1, l_vdata2, l_nid;
 EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK TO start_transaction; —- undo changeS
   dbms_output.put_line(‘An error occurred while creating query: ‘ ||
         SQLERRM ||
         ‘.  Please try again later.’);
 END;
 

END DATA_PROCESSES;
/

———————————————————————————————————–
select * from tt1;
exec DATA_PROCESSES.insert_tt1(‘data_clm1′,’data_clm2’);
exec DATA_PROCESSES.insert_tt1(‘data_clm3′,’data_clm4′);
select * from tt1;
exec DATA_PROCESSES.delete_tt1(5);
select * from tt1;
exec DATA_PROCESSES.update_tt1(4,’data1111′,’data2222’);
select * from tt1;
———————————————————————————————————–



SQL> select * from tt1;
no rows selected

SQL> exec DATA_PROCESSES.insert_tt1('data_clm1','data_clm2');

PL/SQL procedure successfully completed.

SQL> exec DATA_PROCESSES.insert_tt1('data_clm3','data_clm4');

PL/SQL procedure successfully completed.

SQL> select * from tt1;

   TEMP_ID DATA1                                                               
---------- --------------------------------------------------                  
DATA2                                                                          
--------------------------------------------------                             
CREATE_TIME                                                                    
---------------------------------------------------------------------------    
         4 data_clm1                                                           
data_clm2                                                                      
07/01/2007 12:16:26,000000                                                     
                                                                               
         5 data_clm3                                                           
data_clm4                                                                      
07/01/2007 12:16:26,000000                                                     

   TEMP_ID DATA1                                                               
---------- --------------------------------------------------                  
DATA2                                                                          
--------------------------------------------------                             
CREATE_TIME                                                                    
---------------------------------------------------------------------------    
                                                                               

SQL> exec DATA_PROCESSES.delete_tt1(5);

PL/SQL procedure successfully completed.

SQL> select * from tt1;

   TEMP_ID DATA1                                                               
---------- --------------------------------------------------                  
DATA2                                                                          
--------------------------------------------------                             
CREATE_TIME                                                                    
---------------------------------------------------------------------------    
         4 data_clm1                                                           
data_clm2                                                                      
07/01/2007 12:16:26,000000                                                     
                                                                               

SQL> exec DATA_PROCESSES.update_tt1(4,'data1111','data2222');

PL/SQL procedure successfully completed.

SQL> select * from tt1;

   TEMP_ID DATA1                                                               
---------- --------------------------------------------------                  
DATA2                                                                          
--------------------------------------------------                             
CREATE_TIME                                                                    
---------------------------------------------------------------------------    
         4 data1111                                                            
data2222                                                                       
07/01/2007 12:16:26,000000                                                     
                                                                               

SQL> spool off


 

5 Responses to “Oracle Insert,delete,update procedures”

  1. Selam,

    SAVEPOINT kullanımı için doğru bir örnek olmayabilir, çünkü buradaki gibi tek INSERT veya UPDATE söz konusu iken cümle atomikliği gereği(ya hep-ya hiç) işlem SAVEPOINT noktalarına Oracle tarafından geri alınacaktır.

    WHEN OTHERS da mesaj verme, gerekirse log kaydı atma sonrasında bence doğru olan tekrar hatayı bu uygulamayı çağıran uygulamanın da bilgisi olabilmesi için RAISE etmektir.

    Blog’unu beğendim, emeğine sağlık, devamlı takip edeceğim🙂

    Görüşürüz,
    Tonguç

  2. Selvaraj V Says:

    Hi, Akdora’s Blog, Oracle Insert, Update, Delete operations using Procedures with Packages is First class Example. It’s very very Helpful. Thanks….

  3. Excellent goods from you, man. I’ve understand your stuff previous to and you are just extremely great. I actually like what you have acquired here, certainly like what you are saying and the way in which you say it. You make it entertaining and you still care for to keep it wise. I cant wait to read much more from you. This is actually a terrific website.

  4. Markos Says:

    Thanks It’s very HELPFUL


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s