DBS

TEMPLATES
PRESENTATIONS FOLDER
EXERCISES FOLDER
BEST ORACLE DOCUMENTATION
search for: 'ORA-XXXXX on:dba-oracle.com' in google.com
RESET DB TABLES
SEBASTIAN'S SOLUTION

SAMPLE EXAM

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
-- 1) --------------------------------------------------
--Function
--Baggage on a flight is free up to 20kg. If a passenger has excess baggage, 2.50€ is charged
-- for each kg above the limit. Write a function that returns the value that is to be 
--charged for a specific person (personID) on a specific flight (flightID). Return value -1 
--if person is not existing on the flight.

create or replace function charge_baggage (
	i_flightID passengerlist.flightID%type,
	i_personID passengerlist.personID%type)
return number
is
  v_count integer;
  v_weight integer;
begin
	select count(*) into v_count
	from passengerlist
	where flightID = i_flightID
	and personID = i_personID;
	
	if v_count < 1 then
		return -1;
	end if;
	
	select nvl(sum(weight),0) into v_weight from baggage
	where flightID=i_flightID and personid=i_personid;

	v_weight := v_weight - 20;
	if v_weight>0 then
		v_weight:=v_weight*2.5;
	else 
		v_weight:=0;
	end if; 
	
	return (v_weight);
end;
/

select charge_baggage(45,333) from dual;
select charge_baggage(45,300) from dual;
select charge_baggage(45,301) from dual;




-- 2) --------------------------------------------------
--Add the following view to your database:
create view passenger as
select personID, flightID, givenname, familyname, seatnumber, 
       coalesce(sum(weight),0) totalweight
from person join passengerlist using(personID)
            left join baggage using(personID, flightID)
group by personID, flightID, givenname, familyname, seatnumber;
--Use this view to add passengers to a flight. If an error happens, everything that
--has been done so far concerning the insert statement should be rolled back and the
--error message is shown.
--Throw an error if one of the following situations happens:
--1.FlightID not existing
--2.Person name not existing (or not unique)
--3.Person already existing in passengerlist
--4.Seatnumber already occupied
--In case inserting was ok a select from the view should show the inserted passenger.

create or replace trigger trigger_passenger
instead of insert on passenger
for each row
declare
  v_help integer;
  v_personID person.personID%type;

begin
  
  -- check if flight is existing
  begin
  	select flightID into v_help from flight 
  	where flightID = :new.flightID;
  exception
    when NO_DATA_FOUND then
    	raise_application_error(-20001,'flightID not existing');
  end;
  
  -- check if person is existing and unique
  begin
  	select personID into v_personID
  	from person 
  	where familyname = :new.familyname
  	and givenname = :new.givenname;
  exception
    when NO_DATA_FOUND or TOO_MANY_ROWS then
    	raise_application_error(-20002,'person not existing or not unique');
  end;
  
	-- check if person is already booked on flight
	select count(*) into v_help
	from passengerlist 
	where personID = v_personID
	and flightID = :new.flightID;
	
	if v_help > 0 then
    	raise_application_error(-20003,'person already booked on this flight');
  end if;
 
	-- check if seat is available
	select count(*) into v_help
	from passengerlist 
	where upper(seatnumber) = upper(:new.seatnumber)
	and flightID = :new.flightID;
	

	if v_help > 0 then
    	raise_application_error(-20004,'seat not available');
  end if;

  insert into passengerlist values(:new.flightID, v_personID, :new.seatnumber);

end;
/

-- Testcases
insert into passenger(flightID, givenname, familyname, seatnumber) values(2,'Herbert','Maier','6A');  -- flightID not existing
insert into passenger(flightID, givenname, familyname, seatnumber) values(1,'Max','Maier','6A'); -- person not existing
insert into passenger(flightID, givenname, familyname, seatnumber) values(1,'Herbert','Maier','6A'); -- person already passenger
insert into passenger(flightID, givenname, familyname, seatnumber) values(16,'Herbert','Maier','1'); -- seatnumber in use
insert into passenger(flightID, givenname, familyname, seatnumber) values(16,'Herbert','Maier','15');  -- is ok (no error)


-- 3) --------------------------------------------------
--Write a procedure that prints a list with all flights (flightNumber, name of departure airport, number of passengers) 
--with more than x passengers (x is an input parameter to the procedure). Mark all lines with a * at the beginning that start from ‘Wien-Schwechat’.
/*Example of output:
flight AB9264 departing from Amsterdam Flughafen Schipol with 5 passengers
flight NE3343 departing from Prag with 5 passengers
* flight OS1467 departing from Wien-Schwechat with 4 passengers
flight AB4741 departing from Madrid Borajas with 5 passengers
*/


create or replace procedure print_flight_list (i_minPassNumber integer)
is
	cursor c_flights is
		select flightNumber, name, count(*) passengerNumber 
		from flight join passengerlist using(flightID) join airport on airportdeparture=airportID
		group by flightNumber, name having count(*) > i_minPassNumber;
	v_praefix varchar(2);
begin
  for v_result in c_flights loop
  	if v_result.name = 'Wien-Schwechat' then
  		v_praefix := '* ';
  	else
  	  v_praefix := '';
  	end if;
  	dbms_output.put_line(v_praefix||
  	                     'flight '||v_result.flightNumber||
  	                     ' departing from '||v_result.name||
  	                     ' with '||v_result.passengerNumber||' passengers');
  end loop;
end;
/

exec print_flight_list(3);

ELSŐ VIZSGA

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
--FELADAT: nem lehet több foglalás mint ahány hely van a gépen
CREATE OR REPLACE TRIGGER insert_seat
  --trigger event before insert happens on crew, this trigger ensures that crew does not exceed the limits
  BEFORE insert on passengerlist
  for each row   
  declare
    v_occupied number;
    v_max number;
    v_planeid plane.planeid%type;
    no_seats_left exception;
  BEGIN 
    --calculate number of already occupied seats on given flight
    select count(seatnumber), planeid into v_occupied, v_planeid from passengerlist, flight where flight.flightid=passengerlist.flightid and passengerlist.flightid=:new.flightid group by planeid;
    --calculate number of seats on planetype
    select numberofseats into v_max from planetype, plane where planetype.planetypeid=plane.planetypeid and planeid=v_planeid;
    
    if v_max <= v_occupied then
      raise no_seats_left;
    end if;
    
    exception
    when no_seats_left then
    raise_application_error(-20001, 'Flight is already full!');
end;
/
insert into passengerlist values(1, 1, '9a');
--FELADAT:
--Packagebe 1 procedure meg egy function, mind2 public.
--procedure: FLight id alapján kiírja a járat adatait, meg az utasokat névvel és országgal.
--left join kell h azt is kiírja akinél nincs ország.

--package interface
create or replace package pa_exam as
  -- global variables for storing the flightid and personid
  g_flightID flight.flightID%TYPE;
  g_personID person.personID%TYPE;
  
  procedure list_passengers (i_flightid flight.flightid%type default g_flightID);
  function get_totalweight(i_flightid flight.flightid%type default g_flightID, i_personid person.personid%type default g_personID) return number;
  end;
/

-- package body
create or replace package body pa_exam as

 procedure list_passengers (i_flightid flight.flightid%type default g_flightID) AS 
    e_flightid_not_existing exception; -- exception if flightid not existing
    e_flightid_not_given exception; -- exception if flightid has not been given as parameter

    CURSOR cur_f (i_flightid flight.flightid%TYPE) --cursor with parameters
    IS
       --select query for passengers of a specific flight and their respective country names
       select familyname, givenname, name from person, country, passport, passengerlist 
       where person.personid=passport.personid and passport.countryid = country.countryid and person.personid=passengerlist.personid and flightid=i_flightid;
       
    v_cnt int; --for counting cursor loops
    --variables for select into statement
    v_fnumber flight.flightnumber%type;
    v_name airport.name%type;
    v_deptime flight.departuretime%type;
  BEGIN
    if i_flightid IS NULL then
    raise e_flightid_not_given;
    end if;

    -- store flightID and personID for later use as default value
    g_flightID := i_flightid; --flight id is copied 
    
    --display flightname, departurename, departuretime
    select flightnumber, name, departuretime into v_fnumber, v_name, v_deptime from flight, airport where airportdeparture=airportid and flightid=g_flightID;
    dbms_output.put_line (v_fnumber || ': ' || v_name  || ' - ' || v_deptime); --display flightnumber data before cursor loop
   
    --start execute lines for cursor
    v_cnt:=0;
    for res in cur_f(g_flightID) 
    loop
      v_cnt:=v_cnt+1;
      dbms_output.put_line('-- ' || res.familyname || ' ' || res.givenname  || ': ' || res.name);
      end loop;
    -- start exception handling
    exception 
    when e_flightid_not_given then
    dbms_output.put_line ('flightid not existing or not unique');
    when others then
    dbms_output.put_line ('error not handled yet');
    raise;
  END;

--here goes the function
function get_totalweight(i_flightid flight.flightid%type default g_flightID, i_personid person.personid%type default g_personID) 
return number 
    as
    e_flight_not_existing exception;
    e_person_not_existing exception;
    e_person_not_booked exception;
    v_count number;
    v_result number;
    
    BEGIN
    g_flightID := i_flightid; --flight id is copied 
    g_personid := i_personid; --person id is copied     
    -- check if flight existing
    if i_flightid IS NULL then
    raise e_flight_not_existing;
    end if;
    -- check if flight existing
    if i_personid IS NULL then
    raise e_person_not_existing;
    end if; 
    -- check if person is booked on flight
    select count(personID) into v_count from passengerlist
    where personID = g_personID
    and flightID = g_flightID;
    if v_count = 0 then
    raise e_person_not_booked;
    end if;
    
   --select statement for summing up the baggages
   select sum(weight) into v_result from person, baggage, flight 
   where person.personid=baggage.personid and baggage.flightid=flight.flightid and flight.flightid=g_flightID and person.personid=g_personid;
   return v_result;
   
   -- start exception handling
   EXCEPTION
   when e_flight_not_existing then
   raise_application_error(-20003, 'FlightID could not be found!');
   when e_person_not_existing then
   raise_application_error(-20004, 'Person could not be found!');
   when e_person_not_booked then
   raise_application_error(-20005, 'Person not booked on flight!');
   
   END;
END;
/
set serveroutput on;
-- test procedure
exec pa_exam.list_passengers(1);

--test functions in 2 different scenarios
begin
dbms_output.put_line (pa_exam.get_totalweight(1, 5));
end;
/

begin
dbms_output.put_line (pa_exam.get_totalweight());
end;
/

EXERCISE 3

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
--FELADAT:
--1.) Write a PL/SQL procedure with a select statement of your
--choice that prints out the results for each second data record only.
--Use cursor for this (not rownum as we had it in exercise 1) 

Set SERVEROUTPUT on;
--Exercise 03 Part 1 Task 1
--for loop (implicit), cursor is assigned to variable, no need to declare it in for loop
--implicit cursos for every second row
CREATE OR REPLACE PROCEDURE getRow IS 
v_cnt int;
  CURSOR cur_per 
  IS
     SELECT * FROM person;
BEGIN
  v_cnt:=0;
  for v_person in cur_per 
  loop 
  if mod(v_cnt,2)=1 then
    dbms_output.put_line(v_person.givenname || ' ' ||v_person.familyname || ' ' || v_cnt);
    end if;
    v_cnt:=v_cnt+1;
    end loop;
END;
/
execute getRow;

--explicit cursor for every second row
CREATE OR REPLACE PROCEDURE getRow3 IS 
  CURSOR cur_per 
  IS
     SELECT * FROM person;
     v_person cur_per%ROWTYPE;
     v_cnt int;
BEGIN
  v_cnt:=0;
  open cur_per;
  loop
  fetch cur_per into v_person;
  exit when cur_per%NOTFOUND;
    if mod(v_cnt,2)=1 then 
    dbms_output.put_line(v_person.givenname || ' ' ||v_person.familyname);
    end if;
    v_cnt:=v_cnt+1;    
    end loop;
  close cur_per;
--  DBMS_OUTPUT.PUT_LINE(v_person);
END;
/
execute getRow3;

--FELADAT:
--2.) Have given the following SELECT statement: (6)  
--Select * from person where lower(familyname) = fname;  
--Write a PL/SQL procedure with one parameter fname holding the familyname of the person
--which prints the result of the select statement. Do not use a cursor for this,
--in case there are 2 or more results write an error message.
--Use exception handling for this and for all others errors that might occur.
--Find a special situation (of your choice) and define an own error for it by using RAISE.
--Further explain when RAISE_APPLICATION_ERROR can be used;

Exercise 03 Part 1 Task 2
--Passing a parameter (IN) to a procedure without cursor using rowtype
CREATE OR REPLACE PROCEDURE holdName (fname varchar) AS 
     res person%ROWTYPE;
BEGIN
  Select * into res
  from person where lower(familyname) = fname;
    dbms_output.put_line(res.givenname || ' ' ||res.familyname);
    exception 
    when TOO_MANY_ROWS then
    dbms_output.put_line ('A SELECT...INTO can return only one row'); 
END;
/
execute holdName('bauer');

--FELADAT:
--Write a PL/SQL package that has a public procedure,
--a public function as well as one or more private functions or procedures.   
--1.) The public procedure lists all passengers and their baggage that belong to a specific flight.
--The flight is handed over to the procedure by a parameter flightID.
--The result is to be printed in the following form: 
--flight: OS1467
--1. Tom Brown: <baggage amount: 3  -  total weight: 27,4kg>
--     baggage 1: 10,5kg
--     baggage 2: 13,6kg
--     baggage 3: 3,3kg 
--2. Joe Zawinul: <baggage amount: 1  -  total weight: 19,8kg>
--     baggage 1: 19,8kg …. 
--Take care of the following features: 
--The flightID has to be stored in an internal, global variable
-- If the puplic procedure is called without a parameter (flugID is missing) the internal stored (=the last used) flightID is used.  
--2.) The public function returns (5)
-- In case no parameter is given: the last used flightID
-- In case an integer is given: The flightID which is the next, second, previous, … flight from the current flightID at the same departure airport.
--E.g. 1 means the next flight, -1 the previous flight, 2 the second flight from the current, …  
--Take care of the following features:
-- Use exception handling for wrong results and return -1 if an error occurs
--(e.g. if no internal flight number stored yet or if there is no flight with offset x at the same airport).  
--3.) Use at least one private function or procedure
--that helps to calculate internal things
--(e.g. a private procedure with parameter personID and flightID that works off all baggage of a specific person).

--Exercise 3 Part 2 
--package interface
create or replace package pa_ue4p2 as
  -- global variable for storing the flightNumber
  v_flightNumber flight.flightNumber%type;
  procedure list_passengers (i_flightNumber flight.flightnumber%type default v_flightNumber);
  function get_flightNumber(i_offset int default NULL) return flight.flightnumber%type;
  end;
/

-- package body
create or replace package body pa_ue4p2 as
  --public procedure for baggages
  procedure list_baggage(v_pid passengerlist.personid%TYPE, v_flid passengerlist.flightid%TYPE) AS 
    cursor cur_bag is
       --select query for weight of baggage for a specific person on a specific flight
       select weight from baggage where personid=v_pid and flightid=v_flid;
       v_cnt int;
  begin
    v_cnt := 0;
    --implicit cursor, no need to open cursor
    for v_baggage in cur_bag --cursor results into v_baggage variable
    loop
      v_cnt:=v_cnt+1;    
      dbms_output.put_line('.     baggage' || v_cnt || ': ' || v_baggage.weight || 'kg');
      end loop;
  end;

  -- public procedure for passengers
  procedure list_passengers(i_flightNumber flight.flightnumber%TYPE) AS 
    e_flightnumber_not_existing exception; -- exception if flightnumber not existing
    e_flightnumber_not_given exception; -- exception if flightnumber has not been given as parameter

    CURSOR cur_f (i_flightid flight.flightid%TYPE) --cursor with parameters
    IS
       --select query for cummulated baggage/person/flight
       select personid, person.givenname, person.familyname, count(*) amount, coalesce(sum(weight),0) weight from person 
       join passengerlist on person.personid=passengerlist.personid 
       join baggage on baggage.personid=person.personid where flightid=i_flightid 
       group by personid, givenname, familyname;
  
    v_cnt int; --for counting cursor loops
    v_flightid flight.flightID%type; -- variable holding the flightID
    v_help int; -- help variable used for storing individual results
  BEGIN
    --parameter flightNumber missing and global variable v_flightNumber not set yet
    if i_flightNumber IS NULL then
    raise e_flightnumber_not_given;
    end if;

    -- store flightID and flightNumber in variables for later use as default value
    select flightid into v_flightid from flight where flightnumber = i_flightNumber;
    v_flightNumber := i_flightNumber; --flight number is copied 
    dbms_output.put_line ('flight: '||v_flightNumber); --display flightnumber before cursor loop
    --start execute lines for cursor
    v_cnt:=0;
    for res in cur_f(v_flightid) 
    loop
      v_cnt:=v_cnt+1;
      dbms_output.put_line(v_cnt || '. ' || res.givenname || ' ' || res.familyname  || ': <baggage amount: ' ||  res.amount || ' - total weight: ' || res.weight || 'kg>');
      list_baggage(res.personid,v_flightid); --calling procedure list_baggage and hand over parameters: person id from cursor, flightid
      end loop;
    -- start exception handling
    exception 
    when e_flightnumber_not_given then
    dbms_output.put_line ('flightNumber not existing or not unique');
    when others then
    dbms_output.put_line ('error not handled yet');
    raise;
  END;
  
  FUNCTION get_flightNumber(i_offset int default NULL)
  return flight.flightnumber%type
  as
  e_flightnumber_not_given exception; -- exception in case no flightnumber is given
  v_fln flight.flightnumber%type; -- flightID belonging to the given flightNumber
  begin
  -- check if there is a flightNumber given (global variable)
  if v_flightNumber IS NULL then
  raise e_flightnumber_not_given;
  end if;
  -- check if there is an offset given (as parameter)
  if i_offset IS NULL then
  return v_flightNumber;
  else
  -- this select statement does all the calculation to find the flightnumber
  -- with the given offset. It is also possible to use one or more cursors instead
  with subsel as (
  select rownum as rownumber, flightnumber, departuretime from 
  (select distinct flightnumber, departuretime from flight where airportdeparture IN (select distinct airportdeparture from flight where flightnumber = v_flightNumber ) order by departuretime)
  )
  select flightnumber into v_fln from subsel where rownumber = (select rownumber+i_offset from subsel where flightnumber = v_flightNumber );
  return v_fln;
  end if;
  -- start exception handling
  exception
  -- error thrown by hand (no flightnumber available)
  WHEN e_flightnumber_not_given THEN
  dbms_output.put_line ('you must enter a flightNumber');
  return '-1';
  -- empty result for select
  WHEN NO_DATA_FOUND THEN
  dbms_output.put_line ('no flight with the given offset available');
  return '-1';
  -- all other errors
  WHEN OTHERS THEN
  dbms_output.put_line ('error not handled yet');
  return '-1';
  end;
end;
/
set serveroutput on;
-- test procedure
exec pa_ue4p2.list_passengers('OS1467');

--test functions in 2 different scenarios
begin
dbms_output.put_line (pa_ue4p2.get_flightNumber);
end;
/
begin
dbms_output.put_line (pa_ue4p2.get_flightNumber(1));
end;

EXERCISE 4

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
Set SERVEROUTPUT on;
----Exercise 04 Part 1 Task 1
--Procedure that deletes persons from the passengerlist, 
--At least two deletes within the procedure should work, a further delete gives an error
--Catch the error in the exception part and rollback all previous deletes.

CREATE OR REPLACE PROCEDURE deleteFromPassengerlist(i_pid1 passengerlist.personid%TYPE, i_pid2 passengerlist.personid%TYPE, i_pid3 passengerlist.personid%TYPE) is  
  v_result passengerlist.personid%TYPE;
BEGIN
  --create savepoint
  savepoint startProc;
  delete from passengerlist where personid = i_pid1;
  delete from passengerlist where personid = i_pid2;
  delete from passengerlist where personid = i_pid3;
  
  --exception handling in case of error and rollback
  exception
  when NO_DATA_FOUND then
    dbms_output.put_line('at least one of the given personIDs not existing');
    rollback to startProc;
    
  when others then
    if SQLCODE = -2292 then -- integrity constraint violated
      dbms_output.put_line('must delete baggage first');
      rollback to startProc;
    else
      raise;
    end if;  
END;
/
exec deleteFromPassengerlist(5,6,19);

--Exercise 04, Part 1 Task 2
--Trigger that ensures - in case of a cancellation of a passenger -
--that all pieces of luggage of this passenger will be deleted.
CREATE OR REPLACE TRIGGER delete_baggage 
  --trigger event when delete happens on passengerlist, this trigger ensures that baggages will be deleted before
  BEFORE delete on passengerlist 
  for each row   
  BEGIN 
    delete from baggage where flightID = :old.flightID and personID = :old.personID;
  END; 
 /
 
delete from passengerlist where personID=5 and flightID=1;


--Exercise 04 Part 1 Task 3
--trigger that checks on assigning personnel to a flight that no more than 1 pilot (or chefpilot), 
--not more than 2 copilots, and no more than 6 stewards/stewardesses are on the flight.
CREATE OR REPLACE TRIGGER insert_crew
  --trigger event before insert happens on crew, this trigger ensures that crew does not exceed the limits
  BEFORE insert on crew
  for each row   
  declare
    v_count int; --for counting the number of staff assigned to a flight
    v_acronym rank.acronym%type;
  BEGIN 
  -- find the rank of the person that is beeing added to the crew
  select acronym into v_acronym from flightstaff join rank using(rankid) where personid=:new.personid;
  
  case
  -- find the number of pilots and chefpilots already assigned to the flight
  when v_acronym='Pl' or v_acronym='Cp' then
    select count(*) into v_count from flightstaff join crew using(personid) join rank using(rankid) where flightid=:new.flightid and acronym in ('Pl', 'Cp');
    if (v_count>=1) then 
      raise_application_error(-20101,'not more than 1 pilot / chefpilot on a flight allowed');
    end if;
    
      -- find the number of co-pilots already assigned to the flight, maximum is 2
  when v_acronym='Co' then
    select count(*) into v_count from flightstaff join crew using(personid) join rank using(rankid) where flightid=:new.flightid and acronym='Co';
    if (v_count>=2) then 
      raise_application_error(-20101,'not more than 2 co-pilot on a flight allowed');
    end if;
    
  -- find the number of stewardesses already assigned to the flight
  when v_acronym='Bp' then
    select count(*) into v_count from flightstaff join crew using(personid) join rank using(rankid) where flightid=:new.flightid and acronym in ('Bp');
    if (v_count>=6) then 
      raise_application_error(-20101,'not more than 6 steward / stewardesses on a flight allowed');
    end if;
  end case;
  END; 
 /


--Exercise 04 Part 1 Task 4
--Write a database trigger that logs all (attempts of) changes to the passengerlist in an extra table 
-(table has to be created before using the trigger and stores passenger full name, flightID, timestamp). 
--Logging must not be affected by errors or rolled back transactions.

create table passengerlog (
  id integer primary key,
  gname varchar(50),
  fname varchar(50),
  flightID integer,
  changeType char,
  time_stamp timestamp
  );

create sequence seq_passengerlog;

create or replace procedure write_passengerlog(i_personID integer, i_flightID integer, i_changeType char) as
-- start a transaction that is independant from the others
-- is valid for this procedure only and allows to store data
-- to the passengerlog table even if outside things are rolled back
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_systimestamp timestamp;
  v_cur_seq_passengerlog integer;
  v_person person%rowtype;
  begin
    select systimestamp into v_systimestamp from dual;
    select seq_passengerlog.nextval into v_cur_seq_passengerlog from dual;
    select * into v_person from person where personID = i_personID;
    insert into passengerlog values(v_cur_seq_passengerlog,
    v_person.givenname,
    v_person.familyname,
    i_flightID,
    i_changeType,
    v_systimestamp);
    dbms_output.put_line('new entry to passengerlog: id='
    ||v_cur_seq_passengerlog||' '||v_person.givenname||' '
    ||v_person.familyname||' flightID='||i_flightID
    ||' '||i_changeType||' '||v_systimestamp );
    commit; -- commits only the autonomous transaction (= this procedure)
end;
/

create or replace trigger trigger_passengerlog
  before insert or update or delete on passengerlist
  for each row
  declare
  begin
    IF INSERTING THEN
      write_passengerlog(:new.personID, :new.flightID, 'I');
      ELSIF UPDATING THEN
      write_passengerlog(:old.personID, :old.flightID, 'O');
      write_passengerlog(:new.personID, :new.flightID, 'N');
      ELSE
      write_passengerlog(:old.personID, :old.flightID, 'D');
    END IF;
end;
/

--Example 4 Part 2, Task1
/*Write a PL/SQL function that cancels a flight for a passenger. 
For that purpose use IN parameters for holding the passenger’s family name, the passenger’s given name, and the flight number. 
Calculate and print the cancellation fee, that is calculated by Fee = Duration of flight * 120
Show the functionality for the best case and for all possible errors
*/
create or replace function passenger_cancel (i_fname person.familyname%TYPE, i_gname person.givenname%TYPE, i_flightNumber flight.flightnumber%TYPE)
  return number as
  v_help number;
  v_flightID number;
  v_personID number;
begin
  begin
    --find out the flight id based on flight number
    select flightID into v_flightID from flight where flightNumber = i_flightNumber;
    exception
    when NO_DATA_FOUND or TOO_MANY_ROWS then
    DBMS_OUTPUT.PUT_LINE('FlightNumber '||i_flightNumber||' not existing or not unique');
    return 0;
  end;
  begin
    --find out the person id based on name and flight id
    select personid into v_personID from passengerlist join person using(personid) where givenname = i_gname and familyname = i.fname and flightid=v_flightid;
    exception
    when NO_DATA_FOUND or TOO_MANY_ROWS then
    DBMS_OUTPUT.PUT_LINE('Passenger '||i_gname||' '||i_fname||' not existing for flightNumber '||i_flightNumber);
    return 0;
  end;
  -- can be omitted if trigger already exists that removes baggage
  --delete baggage on a specific flight for a specific person
  delete from baggage where personID=v_personID and flightID=v_flightID;
  --delete person from passengerlist on a specific flight
  delete from passengerlist where personID=v_personID and flightID=v_flightID;

  --calculation fee
  select duration*120 into v_help from flight where flightID = v_flightID;
  return v_help;
end;
/
--test function
begin
DBMS_OUTPUT.PUT_LINE(passenger_cancel('Maier','Herbert','OS1467'));
end;
/

--Exercise 4, Part 2, Task 2
/*Write a PL/SQL procedure, that cancels a flight, so all passengers and all baggage must be rescheduled to a different flight. 
The one and only parameter is the flightID. Inside the procedure, create a new flight with proper departure and destination airport and using the same plane type. 
Reschedule all passengers to that flight.
*/
set serveroutput on;
-- create sequence
create sequence seq_flight_flightID start with 46;
-- procedure
create or replace procedure proc_cancelFlight_short (i_flightID flight.flightID%type)
is
  v_oldFlight flight%rowType;
  v_newPlaneID plane.planeID%type;
  begin
    -- read data record for given flight
    begin
      select * into v_oldFlight from flight where flightID = i_flightID;
      exception
      when NO_DATA_FOUND then -- flightID not existing
      raise_application_error(-20100, 'flightID not existing');
    end;
    -- look for another plane of the same planetype
    begin
      select planeID into v_newplaneID from plane 
      where planeTypeID = (select planeTypeID from flight join plane using(planeID)join planetype using(planeTypeID) 
      where flightID = i_flightID) and planeID <> (select planeID from flight where flightID = i_flightID) and rownum=1;
      exception
      when NO_DATA_FOUND then -- no new airplane available
      raise_application_error(-20101, 'no new airplane available');
    end;
  -- insert new flight with new flightID (assumption is one hour later)
  insert into flight(flightID, planeID, gateID, airportdeparture, airportdestination, departuretime, flightnumber, duration)
  values(seq_flight_flightID.nextval, v_newplaneID, v_oldFlight.GateID, v_oldFlight.airportdeparture, v_oldFlight.airportdestination,
  v_oldFlight.departuretime+1/24, v_oldFlight.flightnumber, v_oldFlight.duration);
  
  insert into passengerlist(flightID, personID, seatnumber)
  select seq_flight_flightID.currval, personID, seatnumber from passengerlist where flightID = i_flightID;
  
  update baggage set flightID = seq_flight_flightID.currval where flightID = i_flightID;
  delete from passengerlist where flightID = i_flightID;
  update crew set flightID=seq_flight_flightID.currval where flightID = i_flightID;
  delete from flight where flightID = i_flightID;
end;
/

--Exercise 4, Part 2, Task 3
--Write a PL/SQL function that returns the use to capacity (=occupancy rate = percentage of booked seats) for a specific flight.

create or replace function booked_capacity (i_flightNumber flight.flightNumber%type) 
return number is
  v_flightID flight.flightID%type;
  v_no_seats integer;
  v_no_passengers integer;
  begin
    begin
      select flightID into v_flightID
      from flight
      where flightNumber = i_flightNumber;
      exception
      when NO_DATA_FOUND or TOO_MANY_ROWS then
      DBMS_OUTPUT.PUT_LINE('FlightNumber '||i_flightNumber||' not existing or not unique');
      return -1;
    end;
    
    begin
      select numberofseats into v_no_seats
      from flight join plane using(planeID)
      join planetype using(planetypeID)
      where flightNumber = i_flightNumber;
      exception
      when NO_DATA_FOUND or TOO_MANY_ROWS then
      DBMS_OUTPUT.PUT_LINE('cannot calculate total amount of seats for flightNumber '||i_flightNumber);
      return -1;
    end;
    
  select count(*) into v_no_passengers from passengerlist where flightID = v_flightID;
  DBMS_OUTPUT.PUT_LINE(v_no_passengers||' of '||v_no_seats||' seats are booked for flight '||i_flightNumber);
  return round((v_no_passengers / v_no_seats) * 100,2);
end;
/
begin
  DBMS_OUTPUT.PUT_LINE(booked_capacity('OS1467'));
end;
/

SEBASTIAN'S

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
SET SERVEROUTPUT ON;

--1)

create or replace trigger check_passenger before insert
on passengerlist
for each row
declare
v_max number; --maximum number of seats
v_booked number; --booked seats
no_seats_left exception;
begin
--getting max number of seats:
select pt.numberofseats into v_max from planetype pt
join plane pl on pl.planetypeID = pt.planetypeID
join flight fl on fl.planeID = pl.planeID
where fl.flightID = :new.flightID;

--getting booked seats:
select count(personID) into v_booked from passengerlist
where flightID = :new.flightID;

--if no space, do error message:
if v_booked >= v_max then
raise no_seats_left;
end if;
exception
when no_seats_left then
raise_application_error(-20001, 'Flight is already full!');
end;
/

insert into passengerlist values(1, 1, '9a');

 

--2)

CREATE OR REPLACE PACKAGE display_flight
AS
g_flightID flight.flightID%Type;
g_personID person.personID%Type;
PROCEDURE list_flight (g_flightID IN flight.flightID%Type);
FUNCTION get_baggage (g_flightID IN flight.flightID%Type, g_personID IN person.personID%Type) RETURN number;
flight_id_missing exception;
END display_flight;
/

--body:
CREATE OR REPLACE PACKAGE BODY display_flight
AS 

--main procedure:
PROCEDURE list_flight (g_flightID IN flight.flightID%Type)
AS
v_flightnumber flight.flightnumber%Type;
v_airportdeparture airport.name%Type;
v_departuretime flight.departuretime%Type;
v_baggageAmount Number;
v_baggageWeight baggage.weight%Type;
v_count number;
CURSOR get_person is
select ps.givenname, ps.familyname, co.name as Country from passengerlist pl
join person ps on pl.personID = ps.personID
left join passport pa on ps.personID = pa.personID
left join country co on pa.countryID = co.countryID
where pl.flightID = g_flightID;
BEGIN
--first check for flightID:
select count(flightID) into v_count
from flight
where flightID = g_flightID;

--raise error if not found:
if v_count = 0 then
raise flight_id_missing;
end if;

--flight data:
SELECT flightnumber INTO v_flightnumber FROM flight
WHERE flightID = g_flightID;
SELECT ap.name INTO v_airportdeparture FROM flight fl
join airport ap on fl.airportdeparture = ap.airportID
WHERE fl.flightID = g_flightID;
select departuretime into v_departuretime from flight
WHERE flightID = g_flightID;

--output flight data:
DBMS_OUTPUT.PUT_LINE(v_flightnumber || ': ' || v_airportdeparture || ' - ' || v_departuretime);

FOR vResult IN get_person LOOP
DBMS_OUTPUT.PUT_LINE('-- ' || vResult.givenname || ' ' || vResult.familyname || ': ' || upper(vResult.Country));
END LOOP;

EXCEPTION
when flight_id_missing then
raise_application_error(-20002, 'FlightID could not be found!'); 
END;

FUNCTION get_baggage (g_flightID IN flight.flightID%Type, g_personID IN person.personID%type)
RETURN number
AS
v_result number;
flight_not_existing exception;
person_not_existing exception;
person_not_on_flight exception;
v_count number;
BEGIN
--check flight:
select count(flightID) into v_count from flight
where flightID = g_flightID;
if v_count = 0 then
raise flight_not_existing;
end if;
--check person:
select count(personID) into v_count from person
where personID = g_personID;
if v_count = 0 then
raise person_not_existing;
end if;
--check flight:
select count(personID) into v_count from passengerlist
where personID = g_personID
and flightID = g_flightID;
if v_count = 0 then
raise person_not_on_flight;
end if;

select sum(weight) into v_result from baggage where personID = g_personID and flightID = g_flightID;
RETURN v_result;
EXCEPTION
when flight_not_existing then
raise_application_error(-20003, 'FlightID could not be found!');
when person_not_existing then
raise_application_error(-20004, 'Person could not be found!');
when person_not_on_flight then
raise_application_error(-20005, 'Person not booked on flight!');
END;

END;
/