RDBMS-Slip20

Consider the following entities and their relationship.
Drug(d_no, d_name, company, price)
Medical _Store(m_no,m_name,m_city,ph_no)
Relationship between Drug and Medical_Store is many-to-many with descriptive attribute quantity.Constraints:primary key, foreign key,m_name and d_name should not be null,m_city can be pune or pimpri
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:



 1)Write a function which will count total number of drugs available in given medical store.
SQL> set serveroutput on
SQL> create or replace function fun1(nodrg in varchar) return number as nofdrg number;
  2  begin
  3  select count(quantity) into nofdrg from drug, medicalstore, drugmedicalstore where drug.dno=drugmedicalstore.dno and medicalstore.mno=drugmedicalstore.mno;
  4  if sql %found then
  5  return(nofdrg);
  6  else
  7  return null;
  8  end if;
  9  end fun1;
 10  /


Function created.
SQL> begin
  2  dbms_output.put_line('no of drug- '||fun1('matrix'));
  3  end;
  4  /

no of drug- 5
PL/SQL procedure successfully completed.


2)Write a cursor which will display medical store wise drug details with available quantity.

SQL> set serveroutput on
SQL> declare
  2  cursor c2 is select mname, dname, company, price from drug, medicalstore, drugmedicalstore where drug.dno=drugmedicalstore.dno and medicalstore.mno=drugmedicalstore.mno order by mname;
  3  c c2%rowtype;
  4  begin
  5  open c2;
  6  loop
  7  fetch c2 into c;
  8  exit when c2%notfound;
  9  dbms_output.put_line(c.mname||' '||c.dname||' '||c.company||' '||c.price);
 10  end loop;
 11  close c2;
 12  end;
 13  /


cronicle syndron sector 5452
matrix cojka sector 1895
mediplus santin mankind 9824
new strom insepton malform 6545
nitro acen mankind 3652

PL/SQL procedure successfully completed.