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;
    /