Slip
17
Consider the following Item_Supplier database
Company (name , address , city , phone , share_value)
Person (pname ,pcity )
Relationship between Company and Person is M to M relationship with
descriptive attribute No_of_shares iConstraints: name,pname primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL> create table company0(cid int primary key,cname varchar(20),addr
varchar(20),city varchar(20),phone int,share_value int);
SQL> create table person3(pno int primary key,pname varchar(20),pcity
varchar(20));
SQL> create table com_per(cid int,pno int,no_of_shares int);
SQL> insert into company0
values(1,'aaa','koregaon','pune',1122334455,200);
SQL> insert into person3 values(11,'mmm','pimpari');
SQL> insert into com_per values(1,11,20);
1)Write
a trigger before insert or update on No_of_shares field should not be zero.(Raise user
defined exception and give
appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t3 before insert
or update on com_per
2 for each row
3 begin
4 if(:new.no_of_shares<=0)then
5
raise_application_error(-20001,'no_of_shares>0');
6 end if;
7 end;
8 /
Trigger created.
SQL> insert into com_per values(6,16,-1000);
insert into com_per values(6,16,-1000)
*
ERROR at line 1:
ORA-20001: no_of_shares>0
ORA-06512: at "SCOTT.T3", line 3
ORA-04088: error during execution of trigger
'SCOTT.T3'
2)Write
a function to display total no_of_shares of a specific person.
SQL> set serveroutput on
SQL> create or replace function
f2(xyz in varchar) return number as abc number;
2
begin
3
select sum(person3.pno) into abc from company0,person3,com_per where
company0.cid=com_per.cid and
person3.pno=com_per.pno and pname='ppp';
4 if sql %found then
5
return(abc);
6
else
7
return null;
8
end if;
9
end f2;
10 /
Function created.
SQL> begin
2
dbms_output.put_line('company-'||f2('ppp'));
3
end;
4
/
company-13
PL/SQL procedure successfully completed.
