Slip 22 - C) 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. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure to display name of citizens having mother toung “Marathi “ and from “India”; 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)

Solution:

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.