oracle project 2

-----------------------------------------------------------------------------------------------------------
customer account sanction :=
---------------------------------------------------------------------------------------------------------------
create sequence loactno start with 2000000000000001 increment by 1 maxvalue 2000000000000010;

create table cust_det5077 (cin number(10),custactno number(10),csurname varchar2(8),cname varchar2(12),cadd varchar2(10),dos date default sysdate,balance number(10,3),status_flag char(1));


create or replace procedure custsan (cin1 number)

is
v_cin number;
v_csurname varchar2(8);
v_cname varchar2(12);
v_cadd varchar2(10);
v_balance number;
act char(1);

v_cin cust_reg.cin%type;
cursor c1 is select cin from cust_reg5077;
invalidcin exception;
begin
v_cin:=cin1;
select count(*) into a from cust_reg where cin=cin1;
for z in c1 loop
if a>=1 then

select active into act from bank_master;
select csurname,cname,cadd,minbal into v_csurname,v_cname,v_cadd,v_balance from cust_reg5077 where cin=v_cin;
insert into cust_det5077 (cin,custactno,csurname,cname,cadd,balance,status_flag)
values(cinseq.currval,actno.nextval,v_csurname,v_cname,v_cadd,v_balance,act);

exit when c1%found;

else
raise invalidcin;
end if;
end loop;
exception when invalidcin then
dbms_output.put_line('cin number is invalid');
end;
/
---------------------------------------------------------------------------------------------------------------


transaction of a customer:=deposit,withdraw,balance enquiry.
------------------------------------------------------------------------------------------------------------------
create table transaction(cin number(17),transdate date,txnbal number(10,3);


create or replace package transactions
as
z number;
mt number;
b1 number
mb number;
x1 number
tw1 char(1);
mb1 number;
tf number;
procedure balance(a number);
procedure withdraw(a number,b number);
procedure deposit(a number,b number);
procedure fundtransfer(a number,a1 number,b number);

end;
/
create or replace package body transactions
as
procedure balance(a number)
is
cursor c1 is select * from cust_det5077 where cin=a;
begin
dbms_output.put_line(cardno||' '||transdate||' '||transactionbal);
for i in c1 loop
exit when c1%notfound;
dbms_output.put_line(i.cin||' '||i.transdate||' '||i.txnbal);


end loop;
end balance;

procedure withdraw(a number,b number)
is

cursor c1 is select * from cust_det where cin=a;
begin
select mintrans,tw,minbal into mt,tw1,mb from bank_master;
select count(*) into x1 from transactions where cin=a and tw='w' and to_char(transdate)=to_char(sysdate);
select balance into bal1 from cust_det5077 where cin=a;

if (x1<=mt) && (bal1>=mb) then
for i in c1 loop
exit when c1%notfound;
update cust_det set bal=i.balance-b where cin=a;
dbms_output.put_line('updated');
dbms_output.put_line('prevoious balance is'||' '||i.balance);
select balance into z from cust_det where cin=a;
dbms_output.put_line('current balance is'||' '||z);
insert into transactions values(i.cin,sysdate,z,td1);
dbms_output.put_line('inserted into transations');
end loop;
else
dbms_output.put_line('daily limit exceeded');
ens if
end withdraw;


procedure deposit(a number,b number)
is

cursor c1 is select * from cust_det where cin=a;
begin
select deposit into b1 from bank_master;
if(b>=b1) then

for i in c1 loop
exit when c1%notfound;
update cust_det5077 set bal=i.balance+b where cin=a;
dbms_output.put_line('updated');
dbms_output.put_line('prevoious balance is'||' '||i.balance);
select balance into z from cust_det5077 where cin=a;
dbms_output.put_line('current balance is'||' '||z);
insert into transactions values(i.cin,i.transdate,i.txnbal);
end loop;
else
dbms_output.put_line('deposit minimum of 10000 for further transactions');
end if;
end deposit;