SQL- bank account



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




Post a Comment

0 Comments