--- Q1 ---
create or replace procedure newIntrest (aBranchName Deposit.branchName%type, intrest Deposit.balance%type) as
begin
update Deposit set balance = balance * intrest
where aBranchName = branchName;
end;
/
execute newIntrest('RoyalBank', 1.1)
--- Q2 ---
create or replace procedure newIntrest (aBranchName Deposit.branchName%type, intrest Deposit.balance%type) as
cursor list is
select customerName, balance
from deposit
where aBranchName = branchName;
acustomerName deposit.customerName%type;
abalance deposit.balance%type;
begin
open list;
loop
fetch list into acustomerName, abalance;
exit when list%notfound;
update Deposit set balance = balance * intrest
where aBranchName = branchName;
abalance := abalance * intrest;
dbms_output.put_line('balance: '||abalance||' name: '||acustomerName);
end loop;
close list;
end;
/
--- Q3 ---
create or replace procedure totalAmount (aBranchName Loan.branchName%type) as
total Loan.amount%type;
begin
select sum(amount) into total from loan where branchName = aBranchName;
dbms_output.put_line(total);
end;
/
Notes
select * from deposit;
execute newIntrest('RoyalBank', 1.1);
select * from deposit;