1)登录SQLPLUS,用户为SCOTT,利用该用户提供的表EMP

C:\Users\Administrator>sqlplus scott/scott

2)编写一个FUNCTION,返回值为游标变量

CREATE OR REPLACE FUNCTION GETEMPCURSOR(PRM_CHOICE IN NUMBER)
RETURN  SYS_REFCURSOR
IS
  EMPCURSOR SYS_REFCURSOR;
BEGIN
  IF PRM_CHOICE = 1 THEN
    OPEN EMPCURSOR FOR SELECT * FROM EMP WHERE DEPTNO = 10;
  ELSIF   PRM_CHOICE =2 THEN
    OPEN EMPCURSOR FOR SELECT * FROM EMP WHERE DEPTNO = 20;
  ELSIF  PRM_CHOICE =3 THEN
    OPEN EMPCURSOR FOR SELECT * FROM EMP WHERE DEPTNO = 30;
  ELSE
    OPEN EMPCURSOR FOR SELECT * FROM EMP;
  END IF;
  RETURN EMPCURSOR;
END;

3)编写一个PLSQL块,调用上面的FUNCTION得到返回的游标变量

DECLARE
  EMPCURSOR SYS_REFCURSOR;
  REC_EMP EMP%ROWTYPE;
BEGIN
  EMPCURSOR := GETEMPCURSOR(10);
  IF EMPCURSOR%ISOPEN THEN
    LOOP
        FETCH EMPCURSOR INTO REC_EMP;
        EXIT WHEN EMPCURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(''||'==>'||REC_EMP.ENAME);
    END LOOP;
  END IF;
  CLOSE EMPCURSOR;
END;