Week10 - Exception Handling / Assess

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

1. Create a procedure named "insert_route" which has key_id and route_name as the input parameter and status as output parameter. This procedure insert values into the table route. The values are passed as the input parameter to the procedure. Note: If the key_id value does not exists in the table, then set the status parameter as Inserted successfully. If the key_id value already exists in the table then you should handle the exception or warning by displaying the statusas Sorry duplicate key is not allowed. Hints: Procedure name: insert_route Input parameters: key_id(int) , route_name (varchar2) Output parameters: status varchar2

create or replace procedure insert_route(key_id in number, route_name in varchar2, status out varchar2)
is
begin
    insert into route
    values(key_id, route_name);
    status := 'Inserted successfully';
exception
    when dup_val_on_index then
        status := 'Sorry duplicate key is not allowed';
end;
/

2. Create a procedure named "select_metro_trains" which has routeId as the input parameter and status as the output parameter. This procedure displays the id from the table metro_train where routeId passed as the input parameter. Note: If there is a chance of returning the multiple rows then you should handle the exception or warning by displaying the status SORRY PROCEDURE WONT RETURN MULTIPLE ROWS. Hints: Procedure name: select_metro_trains Input parameters: routeId (id)

create or replace procedure select_metro_trains (routeId in varchar, status out varchar)
is
mt int;
begin
select id into mt from metro_train where route_id  = routeId;
status:=mt;
EXCEPTION 
when too_many_rows then 
status:= 'SORRY PROCEDURE WONT RETURN MULTIPLE ROWS';
END;
/