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 |
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 |
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 |
|
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)
|
|
|
Goto Forum:
Current Time: Thu May 16 09:52:06 CDT 2024
|