Slip 14
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null.
SQL> create table teacher2(tid int primary key,tname varchar(20), qualification varchar(20),specialization varchar(20),salary int,desg varchar(20));
SQL> insert into teacher2 values(100,'aaa','m.bba','english',15000,'head_of_department');
SQL> create table college20(cid int primary key,cname varchar(20),addr varchar(20),tid int);
SQL> insert into college20 values(1,'mmm','pune',100);
1)Write a procedure which will accept teacher name from user and display his/her college details.
SQL> set serveroutput on
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select tname,cname from college20,teacher2 where college20.tid=teacher2.tid and tname='aaa';
2 c c1 %rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('tname'||''||'cname');
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 if(c.tname=n)then
10 dbms_output.put_line(c.tname ||''||c.cname);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('aaa');
3 end;
4 /
tnamecname
aaammm
2)Write a trigger which will fire before insert or update on Teacher having salary less than or equal to zero (Raise user defined exception and give appropriate message)
SQL> set
serveroutput on
SQL>
create or replace trigger t4 before insert or update on teacher2
2 for each row
3 begin
4 if(:new.salary<=0)then
5
raise_application_error(-20001,'salary>0');
6 end if;
7 end;
8 /
Trigger created.
SQL> insert into teacher2
values(400,'vvv','m.ca','marathi',-20000,'teacher');
insert into teacher2
values(400,'vvv','m.ca','marathi',-20000,'teacher')
*
ERROR at line 1:
ORA-20001: salary>0
ORA-06512: at "SCOTT.T4", line 3
ORA-04088: error during execution of trigger
'SCOTT.T4'
