Consider the following entities and their relationship.
Train(t_no, t_name)
Passenger (p_no, p_name, addr, age)
Relationship between Train and Passenger is many-to-many with descriptive attribute date, seat_no and amt.
Constraints : primary key, foreign key,primary key for third table (t_no, p_no, date),t_name and p_name should not be null,amt 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 passenger details of given train on ‘1 July 2014’.
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select pname, addr, age, tpdate from train, passenger, trainpassenger where train.tno=trainpassenger.tno and passenger.pno=trainpassenger.pno;
2 c c1%rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('pname'||' '||' addr'||' '||' age');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 if(c.tpdate=n) then
10 dbms_output.put_line(c.pname||' '||c.addr||' '||c.age);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('1 july 14');
3 end;
4 /
pname addr age
lokesh daund 18
PL/SQL procedure successfully completed.
2)Write a trigger that restricts insertion of train_passenger detail having date less than current date. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on trainpassenger
2 for each row
3 declare
4 d1 varchar(15);
5 d2 varchar(15);
6 begin
7 d1:=to_char(:new.tpdate,'dd-mm-yyyy');
8 d2:=to_char(sysdate,'dd-mm-yyyy');
9 if(d1<d2) then
10 raise_application_error(-20001,'date should be greater than current date.');
11 end if;
12 end;
13 /
Trigger created.
SQL> insert into trainpassenger values(102,8,'02 feb 1997',14,900);
insert into trainpassenger values(102,8,'02 feb 1997',14,900)
*
ERROR at line 1:
ORA-20001: date should be greater than current date.
ORA-06512: at "SCOTT.T1", line 8
ORA-04088: error during execution of trigger 'SCOTT.T1
Train(t_no, t_name)
Passenger (p_no, p_name, addr, age)
Relationship between Train and Passenger is many-to-many with descriptive attribute date, seat_no and amt.
Constraints : primary key, foreign key,primary key for third table (t_no, p_no, date),t_name and p_name should not be null,amt 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 passenger details of given train on ‘1 July 2014’.
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select pname, addr, age, tpdate from train, passenger, trainpassenger where train.tno=trainpassenger.tno and passenger.pno=trainpassenger.pno;
2 c c1%rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('pname'||' '||' addr'||' '||' age');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 if(c.tpdate=n) then
10 dbms_output.put_line(c.pname||' '||c.addr||' '||c.age);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('1 july 14');
3 end;
4 /
pname addr age
lokesh daund 18
PL/SQL procedure successfully completed.
2)Write a trigger that restricts insertion of train_passenger detail having date less than current date. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on trainpassenger
2 for each row
3 declare
4 d1 varchar(15);
5 d2 varchar(15);
6 begin
7 d1:=to_char(:new.tpdate,'dd-mm-yyyy');
8 d2:=to_char(sysdate,'dd-mm-yyyy');
9 if(d1<d2) then
10 raise_application_error(-20001,'date should be greater than current date.');
11 end if;
12 end;
13 /
Trigger created.
SQL> insert into trainpassenger values(102,8,'02 feb 1997',14,900);
insert into trainpassenger values(102,8,'02 feb 1997',14,900)
*
ERROR at line 1:
ORA-20001: date should be greater than current date.
ORA-06512: at "SCOTT.T1", line 8
ORA-04088: error during execution of trigger 'SCOTT.T1
