Kindly Note that Ignore Issues at the time of program execution. All programs are reference only.
Consider
the following entities and their relationship.
Owner
(o_no, o_name, o_city, o_ph_no)
Estate
(e_no, e_type, e_city, e_price)
The
Relationship between Owner and Estate is one-to-many.
Constraints: primary key, foreign key,o_name should not
be null,e_type can be flat, bungalow or land.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will count and returns number of owners who have purchase
estate in the same city in which they live.
SQL>
set serveroutput on
SQL>
create or replace function fun1(noofown in varchar) return number as nofown
number;
2
begin
3
select count(oname) into nofown from owner, estate where
owner.ono=estate.ono and ocity='delhi';
4 if
sql %found then
5
return(nofown);
6 else
7
return null;
8 end
if;
9 end
fun1;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('no of owner- '||fun1('delhi'));
3 end;
4 /
no
of owner- 1
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion or updation of estate having price less
than 1 lakh. (Raise user defined
exception and give appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger t4 before insert or update on estate
2 for
each row
3
begin
4
if(:new.eprice<100000)then
5
raise_application_error(-20001,'price should be more than 1 lakh.');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into estate values(107,'land','delhi',14000,7);
insert
into estate values(107,'land','delhi',14000,7)
*
ERROR
at line 1:
ORA-20001:
price should be more than 1 lakh.
ORA-06512:
at "SCOTT.T4", line 3
ORA-04088:
error during execution of trigger 'SCOTT.T4'
