我想從函式回傳查詢結果：
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;
/
但它不編譯。我需要為回傳值使用什么型別
uj5u.com熱心網友回復：
如果您要回傳 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;
/
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/336360.html