Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Mesa, AZ
    Posts
    6

    Question Unanswered: Procedure returning multiple records

    I have a SELECT statement that returns two records. When I put this into a PROCEDURE it expects the INTO command. So when I add that command with all the related variables it returns the error "exact fetch returns more than the requested number of rows". I know that you can have multiple variables returned from a PROCEDURE, but how does it handle multiple rows/records?

    Here is the regular SELECT statement:

    SELECT course.call_id, cs.c_sec_day, TO_CHAR(cs.c_sec_time, 'HH:MI AM'), loc.bldg_code, loc.room
    FROM student s, course, course_section cs, enrollment en, location loc
    WHERE s.s_id = 5
    AND cs.term_id = 6
    AND s.s_id = en.s_id
    AND en.c_sec_id = cs.c_sec_id
    AND cs.course_id = course.course_id
    AND cs.loc_id = loc.loc_id;

    And here is the PROCEDURE:
    CREATE OR REPLACE PROCEDURE display_schedule
    (stu_s_id IN NUMBER,
    stu_course_id IN NUMBER)
    IS
    stu_f_name varchar(30);
    stu_l_name varchar(30);
    stu_call_id course.call_id%TYPE;
    stu_c_sec_day course_section.c_sec_day%TYPE;
    stu_c_sec_time DATE;
    stu_bldg_code location.bldg_code%TYPE;
    stu_room location.room%TYPE;
    BEGIN

    SELECT s_first, s_last
    INTO stu_f_name, stu_l_name
    FROM student
    WHERE s_id = stu_s_id;

    SELECT course.call_id, cs.c_sec_day, TO_CHAR(cs.c_sec_time, 'HH:MI AM'), loc.bldg_code, loc.room
    INTO stu_call_id, stu_c_sec_day, stu_c_sec_time, stu_bldg_code, stu_room
    FROM student s, course, course_section cs, enrollment en, location loc
    WHERE s.s_id = stu_s_id
    AND cs.term_id = stu_course_id
    AND s.s_id = en.s_id
    AND en.c_sec_id = cs.c_sec_id
    AND cs.course_id = course.course_id
    AND cs.loc_id = loc.loc_id;

    DBMS_OUTPUT.PUT_LINE('Schedule for ' || stu_f_name || ' ' || stu_l_name || ':');
    DBMS_OUTPUT.PUT_LINE('**************************** ********************');

    END;
    /

    The output from the SELECT statement looks like this:
    CALL_ID C_SEC_DAY TO_CHAR( BLDG_CODE ROOM
    ---------- ---------- -------- ---------- ------
    MIS 101 MTWRF 08:00 AM CR 101
    MIS 441 MTWRF 09:00 AM BUS 105

    ...but I want the output to look like this through a PROCEDURE:
    Schedule for StudentFirst StudentLast
    ************************************************
    MIS 101 MTWRF 08:00 AM CR 101
    MIS 441 MTWRF 09:00 AM BUS 105


    Any ideas?

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    You need to define a cursor to pull back more than one row.

    Ex:

    Code:
    CREATE OR REPLACE PROCEDURE display_schedule
    (stu_s_id IN NUMBER,
    stu_course_id IN NUMBER)
    IS
    cursor c_test is 
    SELECT course.call_id, cs.c_sec_day, TO_CHAR(cs.c_sec_time, 'HH:MI AM'), loc.bldg_code, loc.room
    FROM student s, course, course_section cs, enrollment en, location loc
    WHERE s.s_id = 5
    AND cs.term_id = 6
    AND s.s_id = en.s_id
    AND en.c_sec_id = cs.c_sec_id
    AND cs.course_id = course.course_id
    AND cs.loc_id = loc.loc_id;
    
    BEGIN
    FOR r_test in c_test LOOP
    dbms_output.put_line(r_test.call_id || ' ' || r_test.c_sec_day || ' ' || r_test.c_sec_time || ' ' || r_test.bldg_code || ' ' || r_test.room);
    END LOOP;
    
    END;
    Basically just declare the cursor in the declare part, and use the FOR LOOP syntax. There are other syntaxes with cursors, but I prefer this one.

    Hope this helps. Any more questions let me know.

  3. #3
    Join Date
    Feb 2004
    Location
    Mesa, AZ
    Posts
    6
    Originally posted by ss659
    Thank you sooooo much. That worked perfectly. However, I did need to change where the date was formatted and the changes are below...just incase anyone else needed this. The date format needed to the in the FOR loop instead of the select statement. Otherwise it was saying that 'C_SEC_TIME' needed to be declared.

    Code:
    CREATE OR REPLACE PROCEDURE display_schedule
    (stu_s_id IN NUMBER,
    stu_course_id IN NUMBER)
    IS
    cursor c_test is 
    SELECT course.call_id, cs.c_sec_day, cs.c_sec_time, loc.bldg_code, loc.room
    FROM student s, course, course_section cs, enrollment en, location loc
    WHERE s.s_id = 5
    AND cs.term_id = 6
    AND s.s_id = en.s_id
    AND en.c_sec_id = cs.c_sec_id
    AND cs.course_id = course.course_id
    AND cs.loc_id = loc.loc_id;
    
    BEGIN
    FOR r_test in c_test LOOP
    dbms_output.put_line(r_test.call_id || ' ' || r_test.c_sec_day || ' ' || TO_CHAR(r_test.c_sec_time, 'HH:MI AM') || ' ' || r_test.bldg_code || ' ' || r_test.room);
    END LOOP;
    
    END;
    Thanks again!

    O.

    Last edited by owen22; 02-15-04 at 23:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •