Slip 27 - C) 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: 1) Write a procedure to display the plan having minimum response. 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)

Solution:

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  /