DBS

Helpers for DBS

Reset DB Tables

Templates

View

create view pilots as
select person.givenname, person.familyname, person.dateofbirth 
from person, flightstaff, rank
where person.personid = flightstaff.personid 
and flightstaff.rankid = rank.rankid 
and rank.name = 'Pilot';

Index

CREATE INDEX ix_person_familyname ON person(familyname);

TRIGGER

CREATE OR REPLACE TRIGGER delete_baggage
before delete on passengerlist
for each row
begin
 delete from baggage
 where flightID = :old.flightID
 and personID = :old.personID;
end;
/
select * from passengerlist join baggage using(personID,flightID)
order by personID, flightID;
select * from baggage order by personID, flightID;
delete from passengerlist where personID=5 and flightID=1;

Function

CREATE or REPLACE
FUNCTION number_of_cities
RETURN varchar
IS
    cities integer := -1;
BEGIN
    select count(distinct(name)) into cities from city;
    if cities = 0 then
    cities := -1;
    end if;
    return 'The number of cities= ' || cities || ' :)';
END;
/
-- Execution:
SELECT number_of_cities FROM DUAL
--OR
Set SERVEROUTPUT on;
begin 
DBMS_OUTPUT.PUT_LINE(number_of_cities);
end;
/

Procedure

create or replace procedure p_percentage_austrian_people
as
    x_rank number;
    x_name varchar(255);
begin
    -- loop for all ranks from 100 to 110
    for i in 100..110 loop
        select count(rankID) into x_rank from rank
        where rankID=i;
        if x_rank=0 then
            DBMS_OUTPUT.ENABLE;
            DBMS_OUTPUT.PUT_LINE('NO RANK AVAILABLE for ID: ' || i);
        else
            select name into x_name from rank where rankID=i;
            DBMS_OUTPUT.ENABLE;
            DBMS_OUTPUT.PUT_LINE('rankID = ' || i || ', name = ' ||
            x_name);
        end if;
    end loop;
end;
/
-- call prpcedure
exec p_percentage_austrian_people;

CURSOR:

DECLARE
-- Define cursor
CURSOR GET_RANK IS SELECT * FROM rank;
-- Define variale of the rowtype of the cursor
vResult GET_RANK%ROWTYPE;
BEGIN
-- Open cursor
OPEN GET_RANK;
LOOP
-- get next cursor row
FETCH GET_RANK INTO vResult;
-- exit when last row reached
EXIT WHEN GET_RANK%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vResult.name);
END LOOP;
-- Close cursor
CLOSE GET_RANK;
END;
/

CURSOR WITH FOR LOOP

set serveroutput on;

create or replace procedure 2ndrow
as
    counter int; -- counter variable
-- Define cursor
CURSOR GET_RANK IS select * from person order by personid;
BEGIN
    counter := 0; -- counts the number of records returned from the cursotr
    -- FOR implicitly opens cursor
    FOR vResult IN GET_RANK
    LOOP
        if mod(counter,2) = 1 then
            dbms_output.put_line ('PERSONID: '||vResult.personid||
             ' GIVENNAME: '||vResult.givenname||
             ' FAMILYNAME: '||vResult.familyname||
             ' DATEOFBIRTH: '||vResult.dateofbirth);
         end if;
         counter := counter + 1; -- increase the counter (data record has been handled)
    END LOOP;
END;
/

exec 2ndrow;

EXCEPTION

create or replace procedure  p_person_exceptiontest(fname in varchar)
as
    v_onePerson person%rowtype; -- holds one data record from table person
    e_wrongID exception; -- new exception type
begin
    Select * into v_onePerson from person where lower(familyname) = fname;
    if v_onePerson.personid < 100 then
       raise e_wrongID;
    end if;
    DBMS_OUTPUT.ENABLE;
    dbms_output.put_line ('PERSONID: '||v_onePerson.personid||
             ' GIVENNAME: '||v_onePerson.givenname||
             ' FAMILYNAME: '||v_onePerson.familyname||
             ' DATEOFBIRTH: '||v_onePerson.dateofbirth);
             
-- exception handling
    EXCEPTION
     -- here exception handling is done that way that known errors are handled
     -- totally in the exception part while all other errors are handed over to the
     -- place where the procedure has been called

    -- empty result for select
    WHEN NO_DATA_FOUND THEN
    raise_application_error(-20002, 'no person with given familyname available');
    -- more than 1 result for select
    WHEN TOO_MANY_ROWS THEN
    raise_application_error(-20002, 'more persons with the given familyname available');
    -- error thrown by hand (person with ID smaller 100)
    WHEN e_wrongID THEN
    raise_application_error(-20002, 'not allowed to select persons with an id below 100');
    -- all other errors that might occur
    WHEN OTHERS THEN
    raise_application_error(-20002, 'error not identified');
    RAISE; -- error is handed over to the place where procedure has been called
end;
/
-- call prpcedure
exec p_person_exceptiontest('janus');
exec p_person_exceptiontest('duck');
exec p_person_exceptiontest('wahl');

ROLLBACK

create or replace
procedure deleteFromPassengerlist(
    i_personid1 passengerlist.personID%type,
    i_personid2 passengerlist.personID%type,
    i_personid3 passengerlist.personID%type
)
is
    v_result passengerlist%rowtype;
BEGIN
    savepoint startProc;
    -- check if i_personidx already exist (optional)
    select * into v_result from passengerlist where personid = i_personid1
    and rownum = 1;
    select * into v_result from passengerlist where personid = i_personid2
    and rownum = 1;
    select * into v_result from passengerlist where personid = i_personid3
    and rownum = 1;

    -- now delete the goven passengers from the passengerlist
    -- if the passenger has baggage an error happens
    -- attention: if there is already a trigger on passengerlist (example 2)
    delete from passengerlist where personid = i_personid1;
    delete from passengerlist where personid = i_personid2;
    delete from passengerlist where personid = i_personid3;
    
    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;
/

BLOCK

declare
    -- declare variables
    x_flightID number;
    x_seatnumber number;
    x_numberofseats number;
begin
      -- set the flight id variable
      x_flightID := 16;
      -- how many seats are already booked on flight?
      select count(p1.seatnumber) into x_seatnumber
      from passengerlist p1
      where p1.flightID=x_flightID;
      -- calculate difference: seats in type of plane
      -- MINUS already booked seats
      select (p2.numberofseats-x_seatnumber) into x_numberofseats
      from flight f1, plane p1, planetype p2
      where f1.planeID=p1.planeID
      and p1.planetypeID=p2.planetypeID
      and f1.flightID=x_flightID;
      -- print
      DBMS_OUTPUT.ENABLE;
      DBMS_OUTPUT.PUT_LINE(x_numberofseats ||
      ' seats are still available for the flightID: ' ||
      x_flightID);
end;
/

Set SERVEROUTPUT on;

DECLARE
    id integer := 1;
    seats integer := 0;
BEGIN
    select s1.numberofseats - passengers into seats from 
        (select flight.flightid, planetype.numberofseats
        from flight, plane, planetype
        where flight.planeid = plane.planeid
        and plane.planetypeid = planetype.planetypeid) s1
    join
        (select flight.flightid, count(passengerlist.personid) as passengers
        from flight, passengerlist
        where flight.flightid = passengerlist.flightid
        group by flight.flightid) s2
    on s1.flightid = s2.flightid
    and s1.flightid = id;
    DBMS_OUTPUT.PUT_LINE( 'The available seats for flight No. ' || id || ' is the following: ' || seats || '');
END;
/