Week9 - Stored Procedures / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Create a procedure named 'findAmount' which takes 2 input parameters namely, personName is type of varchar2, entryTime is type of timestamp and 1 output parameter namely, travelAmount double precision. This procedure should find the amount for the travel_payment made by the person on time whose personName and entryTime is passed as an input paramter.

Hints:
Procedure name : findAmount
Parameters : personName(varchar2),entryTime(timestamp),travelAmount(double precision)

CREATE OR REPLACE PROCEDURE findAmount(
    personName IN VARCHAR2,
    entryTime IN TIMESTAMP,
    travelAmount OUT NUMBER
) AS
BEGIN
    SELECT amount INTO travelAmount
    FROM travel_payment
    WHERE entry_time = entryTime
    AND travel_card_id = (
      SELECT id
      FROM travel_card
      WHERE person_name = personName
    );

END;
/

2. Create a procedure named 'insertRoute' which has route_name as an input parameter with varchar as its datatype. This procedure should take the count of the existing table records(route table) and add 1 with that to generate the new route id.The newly generated id along with the route_name should be inserted into the route table.

Hints:
Procedure name : insertRoute
Parameters : route_name is type of varchar

CREATE OR REPLACE PROCEDURE insertRoute(
    route_name IN VARCHAR2
) AS
    new_route_id NUMBER;
BEGIN
    SELECT COUNT(*) + 1 INTO new_route_id FROM route;

    INSERT INTO route (id, route_name) VALUES (new_route_id, route_name);
    
    COMMIT;
END;
/