PL/SQL FUNCTION AND PROCEDURAL PROGRAM FOR FINDING BALANCE AND DISPLAY THE DATA USING SQL IN DATABASE

Algorithm:
Accept the value of td in the procedure calc through parameter.
Check if td=credit if so increment the balance.
Otherwise, decrement the balance.
Return the output to the main function.

Algorithm for main function:
Get the value of table amount.
Call the procedure calc with parameters.
Display the table with the balance amount calculated.
Stop the program.

PROGRAM:
Function:
SQL> create or replace procedure calc(pb in number,t in number,td in char,i in
number)as
2 begin
3 if(td='d')then
4 update account set balance=pb+t where acctno=i;
5 else
6 if(td='w')then
7 up date account set balance=pb-t where acctno=i;
8 end if;
9 end if;
10 end;
11 /
Procedure created.
Main program:
SQL> set serveroutput on
SQL> declare
2 i number(4);
3 pb account.prevbal %type;
4 t account.tamt %type;
5 td account.ttype %type;
6 begin for i in 1000..1002 loop
7 select prevbal into pb from account where acctno=i;
8 select tamt into t from account where acctno=i;
9 select ttype into td from account where acctno=i;
10 calc(pb,t,td,i);
11 end loop;
12 end;
13 /




Related post