CODEWITHSUNDEEP
oracle-databaseplsql
10IEI
10IEI

Reputation: 21

how to use one procedure to insert into two table

I want to insert value into EVENT table and EventCost table by using only one procedure.

The PK of event is EventID and it is the FK of EventCost table. I tried to use SCOPE_IDENTITY() for EventID but still fail to create the procedure.

2)Do I need to declare the v_EventID in the header for two times? (One for the Event table , one for the eventCost table)

CREATE OR REPLACE PROCEDURE PRC_ADD_OVER_BUDGET_EVENT
(  V_eventID IN VARCHAR
  ,V_EventType IN VARCHAR
  ,V_EventBudget IN NUMBER
  ,V_organizerID IN VARCHAR
  ,v_CostID IN VARCHAR
  ,v_FoodBeverage IN NUMBER
  ,v_wine IN NUMBER 
  ,v_Decoration IN NUMBER
  ,v_rentalfee IN NUMBER
  ,v_facility IN NUMBER
  ,v_EventID IN VARCHAR)

AS
BEGIN

INSERT INTO Event 
values 
(next_eventid_seq,null,v_eventbudget,null,null,null,v_organizerID)


INSERT INTO EventCost 
values 
(next_Costid_seq,v_FoodBeverage,v_Wine,v_Decoration,v_RentalFee,v_Facility,SCOPE_IDENTITY())

End;
/

Upvotes: 0

Views: 39

Answers (1)

Pavel Smirnov
Pavel Smirnov

Reputation: 4809

Use returning into clause:

CREATE OR REPLACE PROCEDURE PRC_ADD_OVER_BUDGET_EVENT
(  V_eventID IN VARCHAR
  ,V_EventType IN VARCHAR
  ,V_EventBudget IN NUMBER
  ,V_organizerID IN VARCHAR
  ,v_CostID IN VARCHAR
  ,v_FoodBeverage IN NUMBER
  ,v_wine IN NUMBER 
  ,v_Decoration IN NUMBER
  ,v_rentalfee IN NUMBER
  ,v_facility IN NUMBER
  ,v_EventID IN VARCHAR)

AS
  event_ID varchar2(64);
BEGIN

INSERT INTO Event 
values 
(next_eventid_seq,null,v_eventbudget,null,null,null,v_organizerID) returning eventID into event_ID;


INSERT INTO EventCost 
values 
(next_Costid_seq,v_FoodBeverage,v_Wine,v_Decoration,v_RentalFee,v_Facility,event_ID);

End;
/

P.S. Also a few notes:

  1. Use varchar2 datatype instead of varchar.
  2. When inserting, provide the names of the columns you insert into: insert into Event (col1, col2, col3, ...). In case your table structure is changed, the insert statements will still work.

Upvotes: 1

Related Questions