SQL> create table ACCTMASTER(accno INT primary key,name char(25),balance number(10));
SQL> create table ACCTTRAN(accno INT references ACCTMASTER(accno),tran_date date default sysdate,deb_cred char(7),flag char(2) default 'N',amount number(10));
SQL> insert into ACCTMASTER values(&accno,'&name',&balance);
Enter value for accno: 101
Enter value for name: Tuttu
Enter value for balance: 10000
old 1: insert into ACCTMASTER values(&accno,'&name',&balance)
new 1: insert into ACCTMASTER values(101,'Tuttu',10000)
1 row created.
SQL> /
Enter value for accno: 102
Enter value for name: Tintumon
Enter value for balance: 19000
old 1: insert into ACCTMASTER values(&accno,'&name',&balance)
new 1: insert into ACCTMASTER values(102,'Tintumon',19000)
1 row created.
SQL> select * from ACCTMASTER;
ACCNO NAME BALANCE
-------------------------------------------------------------
101 Tuttu 10000
102 Tintumon 19000
SQL> insert into ACCTTRAN values(&accno,'&tran_date','&deb_cred','&flag',&amt);
Enter value for accno: 101
Enter value for tran_date: 05-JAN-10
Enter value for deb_cred: Debit
Enter value for flag: n
Enter value for amt: 1000
old 1: insert into ACCTTRAN values(&accno,'&tran_date','&deb_cred','&flag',&amt)
new 1: insert into ACCTTRAN values(101,'05-JAN-10','Debit','n',1000)
1 row created.
SQL> /
Enter value for accno: 102
Enter value for tran_date: 05-JAN-10
Enter value for deb_cred: Credit
Enter value for flag: n
Enter value for amt: 1000
old 1: insert into ACCTTRAN values(&accno,'&tran_date','&deb_cred','&flag',&amt)
new 1: insert into ACCTTRAN values(102,'05-JAN-10','Credit','n',1000)
1 row created.
SQL> select * from ACCTTRAN;
ACCNO TRAN_DATE DEB_CRE FL AMOUNT
-------------------------------------------------------------------------------------
101 05-JAN-10 Debit n 1000
102 05-JAN-10 Credit n 1000
declare
no INT;
bal number(10);
trdate date;
dc char(7);
a number(10);
fl char(2);
cursor ctrn is select * from ACCTTRAN where flag='n' or flag='N';
begin
open ctrn;
loop
fetch ctrn into no,trdate,dc,fl,a;
if ctrn%found then
if dc='Debit' then
update ACCTMASTER set balance=balance-a where accno=no;
update ACCTTRAN set flag='Y' where accno=no;
elsif dc='Credit' then
update ACCTMASTER set balance=balance+a where accno=no;
update ACCTTRAN set flag='Y' where accno=no;
end if;
else
exit;
end if;
end loop;
commit;
close ctrn;
end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from ACCTMASTER;
ACCNO NAME BALANCE
---------------------------------------------------------------------
101 Tuttu 9000
102 Tintumon 20000
SQL> select * from ACCTTRAN;
ACCNO TRAN_DATE DEB_CRE FL AMOUNT
--------------------------------------------------------------------------------
101 05-JAN-10 Debit Y 1000
102 05-JAN-10 Credit Y 1000