Slip22
Consider the following entities and their
relationships.
Country (CId, CName , no_of_states, area, location,
population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many.
Constraint: Primary key, area should not be null.
SQL>create table country9(c_id int primary
key,cname varchar(15),Nos int,area varchar(15),location varchar(15),population
int);
SQL>create table citizen9(id int primary key,
name varchar(15),motherT varchar(10),sname varchar(15),c_id int);
SQL>insert into country9
values(1,’india’,29,’abc’,’asia’,1650);
SQL>insert into citizen9
values(1,’kishor’,’marathi’,’maharashtra’,1);
1)Write a
procedure to display name
of citizens having mother
toung “Marathi “ and
from “India”;
SQL> set serveroutput on
SQL> create or replace procedure p1(n in
varchar)as cursor c1 is select location,name,motherT from c
ountry9,citizen9
where country9.c_id=citizen9.c_id and motherT ='marathi' and
cname='india';
c
c1%rowtype;
begin
open c1;
dbms_output.put_line('location'||''||'name'||''||'motherT');
loop
fetch c1
into c;
exit when
c1%notfound;
if
(c.motherT = n) then
dbms_output.put_line(c.location||''||c.name||''||c.motherT);
end if;
end loop;
close c1;
end;
/
Procedure created.
SQL> begin
p1('marathi');
end;
/
locationnamemotherT
asiakishormarathi
PL/SQL procedure successfully completed.
2)Write a trigger which will fire before insert or
update on country having no_of_state
less than equal to zero. (Raise user defined
exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t5 before insert
or update on country9
2 for each row
3 begin
4 if(:new.no_state >=0) then
5 raise_application_error(-20001,' no_state
<0');
6 end if;
7 end;
8 /
Trigger created.
SQL> insert into country9 values(5,'india',29,'abc','asia',1650);
insert into country9
values(5,'india',29,'abc','asia',1650)
*
ERROR at line 1:
ORA-20001:
no_state <0
ORA-06512: at "SCOTT.T5", line 3
ORA-04088: error during execution of trigger
'SCOTT.T5'
