Slip 8 - 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 function which will accept plan number from user and display all the details of the selected plan 2) Write a cursor which will display customer wise plan details.(Use Parameterized Cursor)

Solution:

SQL>Create table plan(pno int primary key,pname varchar(15),nc varchar(15), fct varchar(15),f_amt varchar(15));

SQL>Insert into plan values(1,’summer’,’10’,’10min’,’500’);

SQL>Create table cust(cno int primary key,cname varchar(15),mno int,pno int);

SQL>Insert into cust values(1,’swarup’,9763162617,1);

1) Write a function which will accept plan number from user and display all the details of the selected plan

SQL>set serveroutput on

SQL>create or replace function fun1(nocomp in varchar)return varchar as detalis varchar(10);

  2 begin

  3 select ( plan.pname)into detalis from plan,cust where plan.pno=cust.pno and plan.pno='1';

  4 if sql %found then

  5 return(detalis);

  6 else

  7 return null;

  8 end if;

  9 end fun1;

 10  /

Function created.

SQL>begin

  2 dbms_output.put_line('detalis-'||fun1('1'));

  3 end;

  4 /

2) Write a cursor which will display customer wise plan details.(Use Parameterized Cursor)

SQL> set serveroutput on

SQL> declare

  2    cursor c1(yyyy  cust.cname %type)is select cname,pname from plan,cust  where plan.pno=cust.pno order by cname;

  3    c c1%rowtype;

  4    begin

  5    open c1('&cname ');

  6    loop

  7    fetch c1 into c;

  8    exit when c1%notfound;

  9    dbms_output.put_line(c.cname||''||c.pname);

 10   end loop;

 11    close c1;

 12    end;

 13 

 14  /

Enter value for cname: anita;

old   5:    open c1('&cname ');

new   5:    open c1('anita; ');

anitadiwali

manoharwinter

swarupsummer