RDS-10

Slip 10

Consider the following entities and their relationships.       

Gym (Name, city, charges, scheme)

Member (ID, Name, phoneNo, address)

Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

SQL> create table gym(gno int primary key,gname varchar(20),city varchar(20),charges int,scheme varchar(20));

SQL> create table member(mid int primary key,mname varchar(20),phone int,addr varchar(20),gno int);

SQL> insert into gym values(101,'aaa','pune',1000,'xyz');

SQL> insert into member values(001,'mmm',1122334455,'goa',101);

1)Write a function which will accept member id and scheme from user and display charges paid by that member.

SQL>  set serveroutput on

SQL> create or replace function f3(abc in varchar) return number as xyz number;

  2   begin

  3  select gym.charges into xyz from gym,member where gym.gno=member.gno and mid='2';

  4   if sql %found then

  5  return(xyz);

  6   else

  7  return null;

  8  end if;

  9  end f3;

 10   /

Function created.

SQL> begin

  2  dbms_output.put_line('gym-'||f3 ('2'));

  3  end;

  4  /

gym-2000

PL/SQL procedure successfully completed.

2)Write a trigger which will fire before insert or update on Gym having charges less than  1000. (Raise user defined exception and give appropriate message)

 

SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on gym
  2  for each row
  3  begin
  4  if(:new.charges<=1000)then
  5  raise_application_error(-20001,'charges>1000');
  6  end if;
  7  end;
  8  /

Trigger created


SQL> insert into gym values(104,'ddd','pune',-1000,'mmm');
insert into gym values(104,'ddd','pune',-1000,'mmm')
            *
ERROR at line 1:
ORA-20001: charges>0
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T1'.