Consider
the following entities and their relationship.
Route(route_no,
source, destination, no_of_station)
Bus
(bus_no, capacity, depot_name)
Relationship
between Route and Bus is one-to-many
Constraints:primary
key, foreign key,depot_name should not be null,bus capacity should be greater
than 40.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will return total number of routes having number of stations
greater than 10.
SQL>
set serveroutput on
SQL>
create or replace function fun1(tr in varchar) return varchar as tnr
varchar(15);
2
begin
3
select count(rno) into tnr from route where noofstation>10;
4 if
sql %found then
5
return(tnr);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('total no. of routes- '||fun1('rno'));
3 end;
4 /
total
no. of routes- 2
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display route wise bus details. (Use parameterized cursor)
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(rwise route.rno%type)is select bus2.rno, capacity, depotname
from route, bus2 where route.rno=bus2.rno order by route.rno;
3 c
c1%rowtype;
4
begin
5 open
c1('&rwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.rno||' '||c.capacity||' '||c.depotname);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for rwise: 101
old 5: open c1('&rwise');
new 5: open c1('101');
101
64 pune
PL/SQL
procedure successfully completed.
