Oracle Insert,delete,update procedures

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




Advertisement