动态游标 强类型必须有返回值 弱类型没有返回值

DECLARE
  TYPE refcur_t IS REF CURSOR
  RETURN employees%ROWTYPE;
  refcur refcur_t;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN refcur FOR
  SELECT * FROM employees;
  LOOP
    FETCH refcur INTO v_emp;
    EXIT WHEN refcur%NOTFOUND;
    dbms_output.put_line(refcur%ROWCOUNT||' '||v_emp.name);
  END LOOP;
  CLOSE refcur;
END;  

/

DECLARE
  TYPE refcur_t IS REF CURSOR;
  refcur refcur_t;
  e_id NUMBER;
  e_name VARCHAR2(50);
BEGIN
  OPEN refcur FOR
  SELECT id,name FROM employees;
  FETCH refcur INTO e_id, e_name;
  WHILE refcur%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('#' || e_id || ':' || e_name);
    FETCH refcur INTO e_id, e_name;
  END LOOP;
  CLOSE refcur;
END;
/

DECLARE
  TYPE refcur_t IS REF CURSOR;
  refcur refcur_t;
  p_id NUMBER;
  p_name VARCHAR2(50);
  selection VARCHAR2(1) := UPPER(SUBSTR('&tab ',1,1));
BEGIN
  IF selection = 'E' THEN
    OPEN refcur FOR
      SELECT id, name
      FROM employees;
    DBMS_OUTPUT.PUT_LINE('===员工信息===');
  ELSIF selection = 'D' THEN
    OPEN refcur FOR
      SELECT department_id, department_name
      FROM departments;
    DBMS_OUTPUT.PUT_LINE('===部门信息===');
  ELSE
    DBMS_OUTPUT.PUT_LINE('请输入员工信息(E) 或 部门信息(D)');
    RETURN;
  END IF;
  FETCH refcur INTO p_id, p_name;
  WHILE refcur%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('#' || p_id || ':' || p_name);
    FETCH refcur INTO p_id, p_name;
  END LOOP;
  CLOSE refcur;
END;  
/