What type to choose for the return value of the function-CodePudding

I want to return query results from a function:

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;

Where is the result of this query of a row of the form (id Integer, count Integer). I tried something like that

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;
/

but it don't compile. What type do I need to use for the return value

CodePudding user response：

If you're returning refcursor, then function declaration has to support it:

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;
/