Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure with two input parameters using cursor
stored procedure with two input parameters using cursor [message #41188] Sat, 07 December 2002 14:08 Go to next message
Sarah
Messages: 15
Registered: June 1999
Junior Member
I am stuck on a problem, hoping someone can help me out.

The question is as follows:

Use SQL*PLUS to create a server-side procedure named DISPLAY_SCHEDULE that receives a Northwoods University student ID and term ID as input variables , and then displays the student's name and current schedule.

The sample display out put should be as follows:
SQL> EXECUTE display_schedule(104, 6);
Schedule for Ruben Sanchez
***********************************************************
MIS 101 M-F 08:00 AM CR 101
MIS 441 M-F 09:00 AM BUS 105

I would need to have two input parameters and would need to retrieve data from multiple tables.

so far I have:

CREATE OR REPLACE PROCEDURE DISPLAY_SCHEDULE
(curr_s_id IN enrollment.s_id%TYPE,

curr_term_id IN course_section.term_Id%type) as

CURSOR course_cursor IS

SELECT call_id, day, time, bldg_code, room, s_last

FROM course, course_section, enrollment, faculty, location

WHERE course.course_id = course_section.course_id

AND coure_section.f_id = faculty.f_id

AND course_section.loc_id = location.loc_id

AND enrollment.c_sec_id = course_section.c_sec_id

AND enrollment.s_id = curr_s_id

AND course_section.term_id = curr_term_id;

course_row course_cursor%rowtype; as

begin

This proves to be an error, also I need help with the for loop within the statements.

I would appreciate any help urgently.

Thanks.
Re: stored procedure with two input parameters using cursor [message #41189 is a reply to message #41188] Sun, 08 December 2002 18:25 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This is hard to try and answer without knowing your table layouts, but I'll assume you have a student table that has the student name. I will also assume that the cursor definition you have is fine.

Delete the course_row variable. By using CURSOR FOR loops, we don't need to fetch into any variables.

...
begin
  for r in (select first_name || ' ' || last_name name
              from student
             where s_id = curr_s_id) loop
    dbms_output.put_line('Schedule for ' || r.name);
  end loop;

  for r in course_cursor loop
    dbms_output.put_line(r.call_id || ' ' || ...);
  end loop;
end;
Re: stored procedure with two input parameters using cursor [message #41290 is a reply to message #41188] Sat, 14 December 2002 03:48 Go to previous message
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
Senior Member
I would also use an implicit CURSOR FOR loop, rather than declare an explicit cursor and fetch from it. And, like Todd said, you need to make sure you have included the correct tables, like if the s_last comes from a student table, then include that table.

Also, don't include any unnecessary tables. For example, if you aren't using any columns from the faculty table, and you don't need to join to the faculty table to join with other tables, then leave it out. You also need to make sure that you have all of the columns you need. It looks like your sample output calls for a first name, but I don't see a column for that. Is there a column like s_first in some table? The code would also be easier to read, avoid ambiguous column names, and perhaps run faster, if you preface each column name with its corresponding table name, or an alias.

You can use the SHOW ERRORS command to display errors with their line numbers, when you compile your procedure. I noticed that you had an extra AS before the BEGIN. You also left the S out of COURSE in one place.

The code below is some partial suggested code. I did not preface column names with table names, only because I don't know which table which column comes from. However, I did make all the other corrections that I mentioned.

-- create procedure:
CREATE OR REPLACE PROCEDURE display_schedule
  (curr_s_id    IN enrollment.s_id%TYPE,
   curr_term_id IN course_section.term_Id%type) 
AS
BEGIN
  FOR r IN
    (SELECT call_id, day, time, bldg_code, room, s_last, s_first
     FROM   course, course_section, enrollment, location -- , student
     WHERE  course.course_id       = course_section.course_id
     AND    course_section.loc_id  = location.loc_id
     AND    enrollment.c_sec_id    = course_section.c_sec_id
     AND    enrollment.s_id        = curr_s_id
     AND    course_section.term_id = curr_term_id)
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Schedule for ' || r.s_first || ' ' || r.s_last);
    DBMS_OUTPUT.PUT_LINE ('***********************************************************');
    DBMS_OUTPUT.PUT_LINE (r.call_id 
                          || ' ' || r.day 
                          || ' ' || r.time 
                          || ' ' || r.bldg_code
                          || ' ' || r.room);
  END LOOP;
END display_schedule;
/
SHOW ERRORS

-- execute procedure:
SET SERVEROUTPUT ON
EXECUTE display_schedule (104, 6)
Previous Topic: dba won't allow dynamic sql DDL - opinion?
Next Topic: pl/sql
Goto Forum:
  


Current Time: Thu May 16 09:52:06 CDT 2024