通过函数返回记录集: CREATE OR REPLACE FUNCTION gg(s INT,e INT) RETURN SYS_REFCURSOR AS r_c SYS_REFCURSOR; BEGIN OPEN r_c FOR SELECT ename,empno FROM (SELECT ename,empno, ROW_NUMBER() OVER(ORDER BY ENAME) RN FROM SCOTT.EMP T) WHERE RN >= S AND RN <= E; RETURN r_c; END; /
DECLARE r_c SYS_REFCURSOR; v_c1 VARCHAR2(10); v_c2 VARCHAR2(10); BEGIN r_c:=gg(2,6);--返回第二到第六条 LOOP FETCH r_c INTO v_c1,v_c2; EXIT WHEN r_c%NOTFOUND; dbms_output.put_Line(v_c1||'-'||v_c2); END LOOP; CLOSE r_c; END; /
通过函数返回记录集:
CREATE OR REPLACE FUNCTION gg(s INT,e INT) RETURN SYS_REFCURSOR AS
r_c SYS_REFCURSOR;
BEGIN
OPEN r_c FOR
SELECT ename,empno
FROM (SELECT ename,empno, ROW_NUMBER() OVER(ORDER BY ENAME) RN FROM SCOTT.EMP T)
WHERE RN >= S
AND RN <= E;
RETURN r_c;
END;
/
DECLARE
r_c SYS_REFCURSOR;
v_c1 VARCHAR2(10);
v_c2 VARCHAR2(10);
BEGIN
r_c:=gg(2,6);--返回第二到第六条
LOOP
FETCH r_c INTO v_c1,v_c2;
EXIT WHEN r_c%NOTFOUND;
dbms_output.put_Line(v_c1||'-'||v_c2);
END LOOP;
CLOSE r_c;
END;
/