Slip 14 - C) Consider the following entities and their relationships. 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. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure which will accept teacher name from user and display his/her college details. 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)

Solution:

1) Write a procedure which will accept teacher name frm 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.tid=teacher2.tid and tname ='aaa';

c c1%rowtype;

begin

open c1;

dbms_output.put_line('tname'|| " || 'cname');

loop

fetch c1 into c;

 exit when c1%notfound;

if(c.tname=n)then

dbms_output.put_line(c.tname || " || c.cname);

end if;

end loop;

 close c1;

 end;

/

Procedure created.

SQL> begin

p1 ('aaa')

end;

/

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

for each row

begin

if(:new.salary<=0)then

raise_application_error(-20001,'salary>0');

end if;

end;

/

Trigger created.