Inserting data in multiple tables using Oracle stored procedure

Question

I have 4 tables say, table1, table2, table3 and table4, which are interrelated. Table1 will generate a primary key, that will be used in rest of the tables as reference key.

I have to insert multiple records in table 4 using this primary key. Since the requirement is the transaction should either commit successfully or it should rollback all the changes. That is the reason I thought of writing this in stored procedure. But got stuck, when I had to pass multiple rows data for table4.

Can anyone please suggest, how can I achieve this?

Thanks, in advance.

Answer 1

i guess you want to do something like this

CREATE OR REPLACE PROCEDURE myproc
(
 invId IN NUMBER,
 cusId IN NUMBER
)
IS
    temp_id  NUMBER; 
BEGIN 
    INSERT INTO myTable (INV_ID) 
    VALUES (invId)
    returning id into temp_id;

    INSERT INTO anotherTable (ID, custID) 
    VALUES (temp_id, custId);  
END myproc;