RDBMS-Slip29

Consider the following entities and their relationship.                              
Company (c_no, c_name, c_city, c_share_value)
Person (p_no, p_name, p_city, p_ph_no)  
Relationship between Company and Person is many-to-many with descriptive attribute no_of_shares.
Constraints:primary key, foreign key,c_name and p_name should not be null,no_of_shares should be greater than zero.                                    
                                     
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display names of person who are shareholder of the given company.
SQL> set serveroutput on
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select cname, pname from company, person, companyperson where company.cno=companyper
son.cno and person.pno=companyperson.pno;
  2  c c1%rowtype;
  3  begin
  4  open c1;
  5  dbms_output.put_line('cname'||' '||'pname');
  6  loop
  7  fetch c1 into c;
  8  exit when c1%notfound;
  9  if(c.cname=n) then
 10  dbms_output.put_line(c.cname||' '||c.pname);
 11  end if;
 12  end loop;
 13  close c1;
 14  end;
 15  /

Procedure created.
SQL> begin
  2  p1('microsoft');
  3  end;
  4  /

cname pname
microsoft harsh

PL/SQL procedure successfully completed.

2)Write a trigger which get activated when share value of company become less than Rs. 10. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t33 before insert or update on company
  2  for each row
  3  begin
  4  if(:new.sharevalue<10) then
  5  raise_application_error(-20001,'Share value must be greater than Rs.10.');
  6  end if;
  7  end;
  8  /


Trigger created.

SQL> insert into company values(107,'tcs','daund', 5);
insert into company values(107,'tcs','daund', 5)

            *
ERROR at line 1:
ORA-20001: Share value must be greater than Rs.10.
ORA-06512: at "SCOTT.T33", line 3
ORA-04088: error during execution of trigger 'SCOTT.T33'