CREATE OR REPLACE PROCEDURE get_itinerary(givenresid IN INT)
IS
CURSOR cpn IS SELECT pname FROM passenger INNER JOIN flight_reservation ON flight_reservation.pid = passenger.pid WHERE flight_reservation.rid = givenresid;
CURSOR ctp IS SELECT total FROM flight_reservation WHERE flight_reservation.rid = givenresid;
CURSOR coc IS SELECT origin_apcode FROM flight_reservation WHERE flight_reservation.rid = givenresid;
CURSOR cdc IS SELECT destination_apcode FROM flight_reservation WHERE flight_reservation.rid = givenresid;
opn VARCHAR(50);
otp NUMBER;
ooc CHAR(3);
odc CHAR(3);
BEGIN
OPEN cpn;
OPEN ctp;
OPEN coc;
OPEN cdc;
FETCH cpn INTO opn;
FETCH ctp INTO otp;
FETCH coc INTO ooc;
FETCH cdc INTO odc;
DBMS_OUTPUT.PUT_LINE('Passenger name: ' || opn);
DBMS_OUTPUT.PUT_LINE('Total cost: ' || otp);
DBMS_OUTPUT.PUT_LINE('Originating airport: ' || ooc);
DBMS_OUTPUT.PUT_LINE('Destination airport: ' || odc);
CLOSE cpn;
CLOSE ctp;
CLOSE coc;
CLOSE cdc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Task 1: Reservation does not exist. Please try again.');
END;