Problem 1: Create a PL/SQL procedure that given the name of a station, print out
information of all schedules passing this station, including schedule ID, name of the line of that schedule, direction (1 or 2), and arrival time of at the station.
Please handle the special case when the input name does not match any station name and print an error message saying no such station.
**MY ATTEMPT**
set serveroutput on;
create or replace procedure name_station (s_name in varchar)
AS
CURSOR c1 is select sname
from line, station, schedule, schedule_station
where line.lid=schedule.lid
and schedule.shid=schedule_station.shid
and station.sid=schedule_station.sid
and sname=s_name;
shid int;
lname varchar(30);
direction int;
scheduled_arrival interval day to second;
begin
for x in c1 loop
dbms_output.put_line('Schedule ID ' ||SHID||' Name of the line '||lname||' Direction is '||direction||' arrival time '||scheduled_arrival);
end loop;
if c1%rowcount=0 then
dbms_output.put_line('No such station');
end if;
close c1;
end;
/
set serveroutput on;
exec name_station('greenbelt');
exec name_station('maryland’);
**THE RESULT**
Procedure NAME_STATION compiled
Schedule ID Name of the line Direction is arrival time
Schedule ID Name of the line Direction is arrival time
Schedule ID Name of the line Direction is arrival time
Schedule ID Name of the line Direction is arrival time
Error starting at line : 26 in command -
BEGIN name_station('greenbelt'); END;
Error report -
ORA-01001: invalid cursor
ORA-06512: at "IS633SANDRAM1.NAME_STATION", line 17
ORA-06512: at line 1
01001. 00000 - "invalid cursor"
*Cause:
*Action:
**TABLES USED**
create table station
(sid int, --- station id, unique
sname varchar(30), --- station name
address varchar(100), --- address of station
status int, --- 1 is open, 0 is closed
primary key(sid));
create table schedule
(
shid int, --- schedule id
lid int, --- line id
direction int, --- 1 means the train travels in increasing order of seq column of line_station, 2 means in decreasing order.
primary key (shid),
foreign key (lid) references line
);
create table schedule_station
(shid int, --- schedule id
sid int, --- station id
scheduled_arrival interval day to second, --- time scheduled to arrive at the station, only keep hour and minutes, e.g., interval '8:30:00.00' hour to second means 8:30 am,
primary key (shid, sid),
foreign key(shid) references schedule,
foreign key(sid) references station);