mercredi 18 septembre 2019

How to write an SQL statement that runs only if condition is fulfilled?

I want to write a procedure that copies records from SYS.AUD$ to AUDIT_USER.AUDIT_HISTORY_TABLE and then TRUNCATE SYS.AUD$. I want Truncate to be executed ONLY if copy / insert went well.

So something like this:

CREATE OR REPLACE PROCEDURE proc_copy_records_and_then_delete
    IS
    BEGIN
     DECLARE number_of_records NUMBER(900000000) :=SELECT COUNT(*) FROM SYS.AUD$;
     insert into AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; 
     IF number_of_records <= rows_inserted THEN
     COMMIT;
     TRUNCATE TABLE SYS.AUD$;
     /

So I just want to TRUNCATE TABLE SYS.AUD$ only if records were INSERTED into the other table.

Aucun commentaire:

Enregistrer un commentaire