Hope this task find you well.
Please help me out with this,
My requirement is to transfer money from one account to another account which is in a same table.
the procedure should have 3 in parameters,
They are,
--> from_account_number
--> To_account_number
--> Credit_amount
Actual data:
acc_num acc_bal
12345 50000
67890 40000
Expected data:
eg: exec trans_sp(12345,67890,10000);
ac_num ac_bal
12345 40000
67890 60000
`trans_sp`
A simple option (with no controls at all; add them yourself):
Before:
SQL> select * from money;
ACCOUNT_NU AMOUNT
---------- ----------
12345 10000
65789 20000
86420 30000
Procedure:
SQL> create or replace procedure trans_sp (par_acc_from in varchar2, par_acc_to in varchar2, par_amount in number)
2 is
3 begin
4 update money set
5 amount = amount - par_amount
6 where account_number = par_acc_from;
7
8 update money set
9 amount = amount + par_amount
10 where account_number = par_acc_to;
11 end;
12 /
Procedure created.
Testing:
SQL> exec trans_sp('86420', '12345', 5000);
PL/SQL procedure successfully completed.
After:
SQL> select * from money;
ACCOUNT_NU AMOUNT
---------- ----------
12345 15000
65789 20000
86420 25000
SQL>
