当我使用存储过程传入​​字符串时，如何输出游标？

Question

嗨，大家好，我正在研究一个存储过程，但我被困在如何输出下面的光标。我希望它能够采取任何课程，然后吐出点数，课程编号和课程描述。这是我到目前为止有：

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

我觉得很蠢......我在dbms_output.put_line上丢失了一个下划线，并且在我的select语句结尾处有一个额外的逗号。

这是我使用的解决方案：

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; 

Answer 2

需要使用游标变量来保存结果集，它可以作为一个过程OUT参数，而是通过一个函数返回它的输出是通常的处理事物的方式。就像这样：

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 3

我认为你可能想是这样的（只是阅读光标，并将其发送到标准输出）

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; 

但首先你必须创建光标（ APC发布的方式）