How do I output a cursor when I pass in a string using a stored procedure?

Question

Hey guys, I'm working on a stored procedure, but I'm stuck on how to output the cursor below. I want it to take any course I give it, and spit back out the count, the course number, and the course description. Here's what I have so far:

EXEC STUDENT_COUNT_COURSE('CS 101');

CALLS

CREATE OR REPLACE PROCEDURE STUDENT_COUNT_COURSE (p_CrsNum IN COURSE.COURSENUM%TYPE)
IS
  cursor cursor1 IS
          SELECT     CourseDesc.courseNum, CourseDesc.courseDesc, COUNT(DISTINCT Student.studentID) as "Count",
          FROM         Course INNER JOIN
                      CourseDesc ON Course.courseNum = CourseDesc.courseNum INNER JOIN
                      Grades ON Course.courseID = Grades.courseID INNER JOIN
                      Student ON Grades.studentID = Student.studentID
          WHERE     (CourseDesc.courseNum = p_CrsNum)
          GROUP BY CourseDesc.courseNum, CourseDesc.courseDesc;
begin          
for c in cursor1
loop
dbms_output.putline('There are COUNT students in' || c.courseNum ||', '|| c.dourseDesc);
//still working on count...
end loop;
end;

Answer 1

You need a cursor variable to hold the resultset, which you can output as a procedure OUT parameter but returning it through a function is the usual way to handle things. Like this:

CREATE OR REPLACE FUNCTION STUDENT_COUNT_COURSE 
    (p_CrsNum IN COURSE.COURSENUM%TYPE)
    RETURN sys_refcursor
IS
    rc sys_refcursor;
BEGIN
    open rc for 
          SELECT     CourseDesc.courseNum
                     , CourseDesc.courseDesc
                     , COUNT(DISTINCT Student.studentID)
          FROM         Course INNER JOIN
                      CourseDesc ON Course.courseNum = CourseDesc.courseNum INNER JOIN
                      Grades ON Course.courseID = Grades.courseID INNER JOIN
                      Student ON Grades.studentID = Student.studentID
          WHERE     (CourseDesc.courseNum = p_CrsNum)
          GROUP BY CourseDesc.courseNum, CourseDesc.courseDesc;

    return rc;
end; 

Answer 2

I think that what you may want is something like this (just reading a cursor and send it to the standard output)

LOOP 
 FETCH cursor1
  INTO  couse_num, course_name, num_students;
EXIT WHEN v_cursor%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(couse_num|| ' , ' || course_name|| ' , ' || num_students);
END LOOP;
CLOSE cursor1;

but first you have to create the cursor (the way posted by APC)

Answer 3

I feel stupid... I was missing an underscore with my dbms_output.put_line and had an extra comma at the end of my select statement.

Here's the solution I used:

CREATE OR REPLACE PROCEDURE STUDENT_COUNT_COURSE(p_CrsNum IN COURSE.COURSENUM%TYPE)
AS
  cursor cursor1 is SELECT CourseDesc.courseNum, 
                    CourseDesc.courseDesc, 
                    COUNT(DISTINCT Student.studentID) as "Count"
          FROM      Course INNER JOIN
                    CourseDesc ON Course.courseNum = CourseDesc.courseNum INNER JOIN
                    Grades ON Course.courseID = Grades.courseID INNER JOIN
                    Student ON Grades.studentID = Student.studentID
          WHERE     (CourseDesc.courseNum = p_CrsNum)
          GROUP BY CourseDesc.courseNum, CourseDesc.courseDesc;
BEGIN          
    FOR c IN cursor1
    LOOP
        IF C."Count"=1 THEN
           DBMS_OUTPUT.PUT_LINE('There is '||c."Count"||' student in ' || c.courseNum ||', '||c.CourseDesc);
        ELSE
            DBMS_OUTPUT.PUT_LINE('There are '||c."Count"||' students in ' || c.courseNum ||', '||c.CourseDesc);
        END IF;
    END LOOP;
END;