Slip 8
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.
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
