-
Notifications
You must be signed in to change notification settings - Fork 1
/
dbms project.txt
173 lines (120 loc) · 3.51 KB
/
dbms project.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
----------------------- table ---------------------------------
create table bus(bus_name char(15) primary key,total_seats number(3),reserved_seats number(3), price number(3));
create table busreservation(bus_name char(15) references bus_details(bus_name),seat_id number(3),reserved char(2) ,customer_name char(15));
---------------------bus detail enter --------------
declare
bus_name char(15);
total_seats number(3);
price number;
begin
bus_name :=:bus_name;
total_seats :=:total_seats ;
price :=:price;
insert into bus values(bus_name ,total_seats ,0,price);
end;
-- run using
select* from bus;
-----------------Bus reservation table ------------------
declare
bname char(15);
tot number(3);
resv number(3);
p number;
cursor cur is select * from bus;
begin
open cur;
loop
fetch cur into bname,tot,resv,p;
if cur%found then
for i in 1 .. tot loop
insert into busreservation values(bname,i,'n',null);
end loop;
else
exit;
end if;
end loop;
close cur;
end;
-- run using
select* from busreservation ;
------------------------------- bus reserve seat -------------------------
declare
bname char(15);
tot number(3);
resv number(3);
p number;
cursor cur is select * from bus;
begin
open cur;
loop
fetch cur into bname,tot,resv,p;
if cur%found then
for i in 1 .. tot loop
insert into busreservation values(bname,i,'n',null);
end loop;
else
exit;
end if;
end loop;
close cur;
end;
declare
cname char(15);
bname char(15);
sid number(3);
tot number(3);
resv number(3);
totalseat number(2);
cursor c1 is select * from busreservation ;
var c1%rowtype;
begin
cname:= :cname;
bname:=:bname;
totalseat :=:totalseat ;
select total_seats into tot from bus where bus_name=bname;
select reserved_seats into resv from bus where bus_name=bname;
if tot>resv then
for i in 1 .. totalseat loop
select MIN(seat_id) into sid from busreservation where bus_name=bname and reserved='n';
update busreservation set reserved='y' where bus_name=bname and seat_id=sid;
update busreservation set customer_name=cname where bus_name=bname and seat_id=sid;
update bus set reserved_seats=reserved_seats+1 where bus_name=bname;
end loop;
else
dbms_output.put_line('No seat avalable');
end if;
for i in c1 loop
dbms_output.put_line(i.bus_name || ' ' || i.seat_id || ' ' || i.reserved || ' ' || i.customer_name );
end loop;
end;
-------------------------bus seat cancel ---------------------------
declare
cname char(15);
bname char(15);
sid number(3);
resv number(3);
seat number(2);
cursor c1 is select * from busreservation ;
var c1%rowtype;
begin
cname:= :cname;
bname:= :bname;
seat:= :seat;
select reserved_seats into resv from bus where bus_name=bname;
if resv>0 then
for i in 1 .. seat loop
select MAX(seat_id) into sid from busreservation where bus_name=bname and reserved='y'and customer_name=cname;
update busreservation set reserved='n' where bus_name=bname and seat_id=sid;
update busreservation set customer_name=null where bus_name=bname and seat_id=sid;
update bus set reserved_seats=reserved_seats-1 where bus_name=bname;
end loop;
else
dbms_output.put_line('Cancelation not allow');
end if;
for i in c1 loop
dbms_output.put_line(i.bus_name || ' ' || i.seat_id || ' ' || i.reserved || ' ' || i.customer_name );
end loop;
end;
-- run using
select * from busreservation;
-- Created By Manan Patel