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