Consider
the following entities and their relationship.
University(u_no,
u_name, u_city)
College(c_no,
c_name, c_city, year_of_establishment )
Relationship
between University and College is one-to-many
Constraints:primary
key, foreign key,u_name and c_name 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 display year wise details of colleges belongs to given
University.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select uname,
cname, ccity, yearofest, ucity from university,college where
university.uno=college.uno;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('uname'||' '||'cname'||' '||'ccity'||'
'||'yearofest');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.ucity=n) then
10
dbms_output.put_line(c.uname||' '||c.cname||' '||c.ccity||'
'||c.yearofest);
11 end
if;
12 end
loop;
13 end;
14 /
Procedure
created.
SQL>
begin
2
p1('delhi');
3 end;
4 /
uname
cname ccity yearofest
delhi
university delhi college delhi 25-NOV-91
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion of college having year of establishment
greater than current year. (Raise user defined exception and give appropriate
message)
SQL>
set serveroutput on
SQL>
create or replace trigger t3 before insert or update on college
2 for
each row
3 declare
4 d1
varchar(10);
5 d2
varchar(10);
6
begin
7
d1:=to_char(:new.yearofest,'yyyy');
8
d2:=to_char(sysdate,'yyyy');
9
if(d1>d2) then
10
raise_application_error(-20001,'investment date should be less than
current date.');
11 end
if;
12 end;
13 /
Trigger
created.
SQL>
insert into college values(1,'pune university','pune','1 jan 2016',101);
insert
into college values(1,'pune university','pune','1 jan 2016',101)
*
ERROR
at line 1:
ORA-20001:
investment date should be less than current date.
ORA-06512:
at "SCOTT.T3", line 8
ORA-04088:
error during execution of trigger 'SCOTT.T3'
