CODEWITHSUNDEEP
oracle-database
Stalinn
Stalinn

Reputation: 39

Execute Stored Procedure with parameters and insert on one or another table

I'm learning on a Oracle PL/SQL and I'm having a doubt because I don't know if this procedure can be executed.

First, I have 3 tables 

TABLE PERSON              TABLE CARD               TABLE CASH
============             ==========               ===========
id_per number            number_card number       amount   number
name varchar2(40)        date_expiration date     id_per   number
payment type varchar(4)  ccv          number
payment date date        payment fees number
                         id_per number

Well I want to create a stored procedure that ask me the payment type (credit card or cash) and choose, if write card the stored procedure insert data in the tables person and card else insert data in the tables person and cash. My idea it's okay or it's impossible to create that procedure

Upvotes: 0

Views: 236

Answers (1)

Jason Moore
Jason Moore

Reputation: 26

Adding ontop of Stilgar:

Starting off: Proc with 3 parameters IN from the person, cash and card tables%rowtypes. Insert on person (As this happens for either card or cash) Then a case statement on payment_type which will determine insert on cash or card tables else (Null or nonsense value) return back to calling package/proc.

Then: Don't know how much or i'm thinking too much of how much else you could do like null handling, validation on your other fields error handling and so forth and rollback / commit on failure/success in the proc's scope. Either get you 'Gotchas' before the proc is called or handle them prior to the inserts in proc.

You can do it!

Upvotes: 1

Related Questions