Consider
the following entities and their relationship.
Customer
(c_no, c_name, c_city, c_ph_no )
Ticket
(t_no, booking_date, fare, traveling_date)
Relationship
between Customer and Ticket is one-to-many.
Constraints:primary
key, foreign key,c_name should not be null,fare should be greater than zero.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will calculate and return total fare collected from customers
on given date.
SQL>
set serveroutput on
SQL>
create or replace function fun1(tfrc in varchar) return number as tfc number;
2
begin
3
select sum(fare) into tfc from customer, ticket where
customer.cno=ticket.cno;
4 if
sql %found then
5
return(tfc);
6 else
7
return null;
8 end
if;
9 end
fun1;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('total fare collected-'||fun1('12/5/15'));
3 end;
4 /
total
fare collected-2409
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display date wise ticket booked by customer. (Use
parameterized cursor)
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(dwise ticket.travelingdate%type)is select
cname,bookingdate,fare,travelingdate from customer, ticket where
customer.cno=ticket.cno order by travelingdate;
3 c
c1%rowtype;
4
begin
5 open
c1('&dwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.cname||' '||c.bookingdate||' '||c.fare||'
'||c.travelingdate);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for dwise: 12/5/15
old 5: open c1('&dwise');
new 5: open c1('12/5/15');
pradip
21/7/15 1000 10/9/15
lokesh
5/4/15 255 12/5/15
omkar
25/2/14 254 3/5/14
roshan
6/9/13 600 8/12/11
sashikant
1/2/07 300 9/4/13
PL/SQL
procedure successfully completed.
