Slip 24
Consider the following entities and their
relationships.
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One.
Constraint: Primary Key, capacity should not be null.
SQL> create table student62(rollno int primary
key,sname varchar(10),class varchar(10),timetable varchar(10),labno int);
SQL>
insert into student62 values(1,'ayush','fybca','practical',1);
SQL>
create table lab62(labno int primary key,lname varchar(10),capacity
int,equipment varchar(10));
SQL>
insert into lab62 values(1,'c lab',20,'comp');
1)Write a procedure to display details of students which perform
practical sessions in a given Lab.
SQL> set serveroutput on
SQL> create or replace procedure p1( n in
varchar)as cursor c1 is select sname,class,lname,timetable
from
student62,lab62 where student62.labno=lab62.labno and lab62.lname='computer';
2 c c1%rowtype;
3 begin
4 open c1;
5
dbms_output.put_line('sname'||''||'class'||''||'lname'||''||'timetable');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 if(c.sname=n)then
10
dbms_output.put_line(c.sname||''||c.class||''||c.lname||''||c.timetable);
11 end if;
12 end loop;
13 close c1;
14 end;
/
SQL> begin
2 p1('computer');
3 end;
4 /
2)Write a trigger which will fire before delete on Lab (Raise user
defined exception and give appropriate message)
