Slip
27
Consider
the following entities and their relationships.
Plan
(plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer
(cust_no, cust_name, mobile_no)
Relation
between Plan and Customer is One to Many. Constraint: Primary key, fix_amt
should be greater than 0.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL>
create table plan(plan_no int primary key,plan_name varchar(10),nooffreecalls
varchar(10),freecalltime varchar(10),fix_amt varchar(10));
SQL>
insert into plan values(1,'monthly','61','12:00','10000');
SQL>
create table cust(cno int primary key,cname varchar(10),mbno int,plan_no int);
SQL>
insert into cust values(1,'aaa',2345678,1);
1)Write a procedure to display the
plan having minimum response.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(y in varchar)as cursor c1 is select
nooffreecalls,plan_name from plan,cust
where plan.plan_no=cust.plan_no;
2 c
c1%rowtype;
3 begin
4 open
c1;
5
dbms_output.put_line('nooffreecalls'||''||'plan_name');
6 loop
7
fetch c1 into c;
8 exit
when c1 %notfound;
9
if(c.plan_name=y)then
10
dbms_output.put_line(c.nooffreecalls||''||c.plan_name);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1('monthly');
3 end;
4 /
nooffreecallsplan_name
61monthly
--------------------
SQL>
set serveroutput on
SQL>
create or replace procedure p5(n in varchar) as min_nooffreecalls
plan.nooffreecalls %type;
2
begin
3
select min(nooffreecalls) into min_nooffreecalls from plan,cust where plan.plan_no=cust.plan_no;
4
if(min_nooffreecalls > 0) then
5
dbms_output.put_line('min nooffreecalls='||min_nooffreecalls);
6 else
7
dbms_output.put_line('plan does not exists');
8 end
if;
9 end
p5;
10 /
Procedure
created.
SQL>
execute p5('61');
min
nooffreecalls=50
PL/SQL
procedure successfully completed.
2)Write a trigger which will fire
before insert or update on mobile number having length less than or greater
than10. (Raise user defined exception and give appropriate message)
SQL> Set serveroutput on
SQL> Create or replace trigger t5 before insert or update on cust
2 for each row
3 begin
4 if(:new. mbno <=10) then
5 raise_application_error(-20001, mbno >10');
6 end if;
7 end;
8 /
SQL> Create or replace trigger t5 before insert or update on cust
2 for each row
3 begin
4 if(:new. mbno <=10) then
5 raise_application_error(-20001, mbno >10');
6 end if;
7 end;
8 /
