rtc project in table format

select * from ticketheader ;

FLEET_ID TICKET_NO DOI DOT
---------- ---------- --------- ---------
1 1 10-APR-96 10-MAY-96
2 2 12-APR-96 05-MAY-96
3 3 21-APR-96 15-MAY-96
4 4 25-APR-96 25-MAY-96
5 5 30-APR-96 22-MAY-96

using exception using rtc

declare
fleet number(3);
d date;
route number(4);
code number(4);
e number(4):=&e;
cantravel exception;
cantravel1 exception;
cantravel2 exception;
begin
select fleet_id,day,route_id,cat_code into fleet,d,route,code from fleet_header where fleet_id=e;
if d='24-jun-96' then
raise cantravel;
elsif d='20-apr-96' then
raise cantravel1;
elsif d='25-jun-96' then
raise cantravel;
end if;
exception when cantravel then
dbms_output.put_line(d);
when cantravel1 then
dbms_output.put_line(d);
when cantravel2 then
dbms_output.put_line(d);
end;
/

rtc using exception

declare
origin1 varchar2(10);
ticketno number(4);
e1 number(10):=&e1;
chen exception;
ban exception;
begin
select origin,ticket_no into origin1,ticketno from tick_header where ticket_no =e1;
if origin1='chennai' then
raise chen;
elsif origin1='banglore' then
raise ban;
end if;
exception
when chen then
dbms_output.put_line(origin1||' '||ticketno);
when ban then
dbms_output.put_line(origin1||' '||ticketno);
end;
/

rtc project in exception

declare
catcode number(5);
catdesc varchar2(15);
e1 number(7):=&e1;
first exception;
second exception;
begin
select cat_code,cat_desc into catcode,catdesc from category_header where cat_code=e1;
if catcode=1 then
raise first;
elsif catcode=5 then
raise second;
end if;
exception when first then
dbms_output.put_line(catdesc);
when second then
dbms_output.put_line(catdesc);
end;
/

rtc project in exception

declare
catcode number(5);
catdesc varchar2(15);
e1 number(7):=&e1;
first exception;
second exception;
begin
select cat_code,cat_desc into catcode,catdesc from category_header where cat_code=e1;
if catcode=1 then
raise first;
elsif catcode=5 then
raise second;
end if;
exception when first then
dbms_output.put_line(catdesc);
when second then
dbms_output.put_line(catdesc);
end;
/

rtc project in cursors 1

rtc project in cursor

declare
cursor c1 is select t.fleet_id,t.ticket_no,u.total_fare,u.route_id from ticket_header t,ticket_header3 u;
r1 c1%rowtype;
begin

dbms_output.put_line('z.fleet_id ticket_no total_fare route_id' );

for z in c1 loop
exit when c1%notfound;
dbms_output.put_line(z.fleet_id||' '||z.ticket_no||' '||z.total_fare||' '||z.route_id );
end loop;
end;
/

rtc project

declare
cursor c1 is select * from route_header where route_id=&v_route_id;
--r1 c1%rowtype;
v_distance route_header.distance%type;
v_route_id route_header.route_id%type;
begin
for z in c1 loop
exit when c1%notfound;
if v_distance<500 then
update route_header set fare=198.98 where route_id=v_route_id;
elsif v_distance>501 and v_distance<=1000 then
update route_header set fare= 876.98 where route_id=v_route_id;
elsif v_distance>1000 then
dbms_output.put_line('fare is fixed');
end if;
end loop;
end;
/

oracle project 3

procedure fundtransfer(a number,a1 number,b number)
is
cursor c1 is select * from cust_det where cin=a;
cursor c2 is select * from cust_det where cin=a1;
begin
select balance into bal1 from cust_det where cin=a;
select minbal into mb1 from bank_master;
select count(*) into fw from transactions where cin=a and to_char(tdate)=to_char(sysdate) and txntype='w';

if(bal1>b) && (bal1>=mb1) then

for i in c1 loop
exit when c1%notfound;
dbms_output.put_line('previous balance is'||' '||i.balance);
update cust_det set bal=i.bal-b where cin=a;
select balance into z from cust_det where cin=a;
dbms_output.put_line('current balance is'||' '||z);
insert into transactions values(i.cin,i.transdate,i.txnbal);

for i in c2 loop
exit when c2%notfound;
dbms_output.put_line('previous balance is'||' '||i.bal);
update bank_details set bal=i.bal+b where cin=a1;
select bal into z from bank_details where cin=a1;
dbms_output.put_line('current balance is'||' '||z);
end loop;
end loop;
else
dbms_output.put('insufficient funds');
end fundtransfer;

end transactions;
/

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;

oracle project1

customer registration:-
--------------------------
create sequence cinseq increment by 1 start with 8000000000000001 maxvalue 8000000000000010;

create table cust_reg(cin number(17),csurname varchar2(8),cname varchar2(12),cdob date,cproofs varchar2(10),cadd varchar2(10),surity_name varchar2(15),doreg date default sysdate,minbal number(10,3),status_flag char(1));


create or replace trigger updatebal
before update or insert on custreg
for each row
begin
if(:new.minbal<=5000) then
raise_application_error(-20999,'bal cant be less than 5000');
end if;
end;
/


create or replace procedure custreg
is
inact char(1);
minb number(8);
begin
select inactive,minbal into inact,minb from bank_master;
insert into cust_reg(cin,csurname,cname,cdob,cproofs,cadd,surity_name,minbal,status_flag)
values(cinseq.nextval,'&csurname','&cname','&cdob','&cproofs',&cadd','&surity_name',minb,inact);
end;
/