CODEWITHSUNDEEP
oracle-databasestored-procedures
Chatra
Chatra

Reputation: 3139

Insert Data into multiple tables in oracle stored procedure

I have five tables and 5 tables are linked.

1) Rules_Group (GroupId-Primary Key)
2) Rules_Sub_Group (GroupId - Foreign Key from Rules_Group)
3) Rules (SubGroupId - Foreign Key from Rules_Sub_Group)
4) Rules_Expression (RulesId - Foreign Key from Rules)
5) Rules_Expression_Eval (RulesId - Foreign Key from Rules)

How can I write a stored procedure to insert/update records all at a time and if anything fails, I want to roll back the transaction.

What is the best way to do this?

Upvotes: 0

Views: 690

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

How can I write a stored procedure to insert/update records all at a time and if anything fails, I want to roll back the transaction.

A common way is to establish a SAVEPOINT and, if errors occur, ROLLBACK to the savepoint. That gives you the option of continuing the transaction with the next record, if you like. It also, preserves the transaction to allow you to process a single COMMIT only at the very end (which is good for several reasons which may be controversial and weren't the point of your question).

Here is the general idea:

FOR r IN ( ... select your source data, or maybe load it from a file ... ) LOOP
BEGIN
    SAVEPOINT myproc_pre_insert;
    INSERT INTO rules_group ...;
    INSERT INTO rules_sub_group ...;
    INSERT INTO rules ...;
    INSERT INTO rules_expression ...;
    INSERT INTO rules_expression_eval ...;
EXCEPTION
    WHEN others THEN
      ROLLBACK TO myproc_pre_insert;
      ... log error, possibly ...
      ... either (A) abort the process ...
      RAISE;
      ... or (B) continue with the next record
      CONTINUE;
END;
END LOOP;
COMMIT;

Depending on how your tables are and how your source data is, you might also combine some of the five (5) INSERT statements, using INSERT ALL.

Upvotes: 4

Related Questions