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)
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 procedure which will accept owner number and display details of all estates
of given owner which belongs to pune city.
SQL>
set serveroutput on
SQL>
create or replace procedure p3(n in varchar) as cursor c1 is select
etype,ecity,eprice from owner,estate where owner.ono=estate.ono;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('etype'||' '||'ecity'||' '||'eprice');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.ecity=n) then
10
dbms_output.put_line(c.etype||' '||c.ecity||' '||c.eprice);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p3('pune');
3 end;
4 /
etype
ecity eprice
flat
pune 65465
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display type wise estate details. (Use parameterized
cursor)
SQL>
set serveroutput on
SQL>
declare
2 cursor c1(etyp estate.etype%type)is select
etype,ecity,eprice from owner,estate where owner.ono=estate.ono order by etype;
3 c
c1%rowtype;
4
begin
5 open
c1('&etyp');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.etype||' '||c.ecity||' '||c.eprice);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for etyp: land
old 5: open c1('&etyp');
new 5: open c1('land');
bungalow
sangli 6181241
bungalow
nasik 84152424
flat
pune 65465
flat
nagpur 68156652
land
mumbai 353552
PL/SQL
procedure successfully completed.
