Forum Stats

  • 3,781,292 Users
  • 2,254,499 Discussions
  • 7,879,633 Comments

Discussions

I need help with this question.

4138887
4138887 Member Posts: 7
edited Dec 14, 2019 3:04PM in SQL & PL/SQL

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

Tagged:

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 14, 2019 12:33PM

    You did not post CREATE TABLE LINE statement

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,379 Red Diamond
    edited Dec 14, 2019 3:04PM

    Hi,

    Thanks for posting (most of)  the CREATE TABLE statements.  Don't forget to post INSERT statements for a ;little sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data.

    See the Forum FAQ:

    In the code you posted, you never assign any values to the local variables shid, lname, direction or scheduled_arrival.

    Perhaps you meant to do something like

    lname := x.sname;

    inside the loop.