为函数的返回值选择什么类型

Question

我想从一个函数返回查询结果:

SELECT Lec.univ_id,Count(Lec.lecturer_id) FROM D8_SUBJECT Sub
JOIN D8_SUBJ_LECT SubLect ON
Sub.subj_id = SubLect.subj_id
JOIN D8_LECTURER Lec ON
SubLect.LECTURER_ID  = Lec.LECTURER_ID 
WHERE Sub.subj_name = 'ИНФОРМАТИКА' AND univ_id BETWEEN 1 AND 50
Group BY Lec.univ_id;

其中为查询形式为(id Integer, count Integer)的行的结果。我试过这样做

CREATE OR REPLACE FUNCTION GetMaximum
(first_univer IN Integer,second_univer IN Integer,subj_name IN NVARCHAR(30)) 
RETURN  user_tables.num_rows%TYPE
AS
rf_cur sys_refcursor;
BEGIN
OPEN rf_cur for 
SELECT Lec.univ_id,Count(Lec.lecturer_id) 
FROM D8_SUBJECT Sub
JOIN D8_SUBJ_LECT SubLect 
ON Sub.subj_id = SubLect.subj_id   
JOIN D8_LECTURER Lec 
ON SubLect.LECTURER_ID  = Lec.LECTURER_ID 
WHERE Sub.subj_name = subj_name AND univ_id BETWEEN first_univer AND second_univer    
Group BY Lec.univ_id;
return rf_cur;
END GetMaximum;
/

，但它不编译。返回值

需要使用什么类型?

Answer 1

如果你返回refcursor，那么函数声明必须支持它:

CREATE OR REPLACE FUNCTION GetMaximum
(first_univer IN Integer,second_univer IN Integer,subj_name IN NVARCHAR(30)) 
RETURN sys_refcursor         --> this
AS
rf_cur sys_refcursor;        --> this
BEGIN
OPEN rf_cur for 
SELECT Lec.univ_id,Count(Lec.lecturer_id) cnt
FROM D8_SUBJECT Sub
<snip>
Group BY Lec.univ_id;
return rf_cur;             --> this
END GetMaximum;
/