Insert data into multiple tables using an Oracle stored procedure

Question

I have 4 tables: table1, table2, table3 and table4, which are interconnected. Table 1 will generate the primary key, which will be used as the reference key in the rest of the tables.

I need to insert multiple records into table 4 using this primary key. Since the requirement is that the transaction must either be completed successfully, or it must undo all changes. It is for this reason that I thought of writing this in a stored procedure. But stuck when I had to pass a few rows of data to a table4.

Can anyone suggest how I can achieve this?

Thanks in advance.

Answer 1

I think 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;