/*============================================================
示例. REF游标
============================================================*/
--显示雇员表中20号部门的雇员信息和部门表中部分部门信息
DECLARE
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
--声明两个记录变量
v_emp_record emp%ROWTYPE;
v_dept_record dept%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM emp WHERE deptno= 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.ename||'的雇佣日期是'
||v_emp_record.hiredate);
END LOOP;
--将同一个游标变量对应到另一个SELECT语句
OPEN c1 FOR SELECT * FROM dept WHERE deptno IN(10,20);
LOOP
FETCH c1 INTO v_dept_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept_record.deptno||'表示'
||v_dept_record.dname);
END LOOP;
CLOSE c1;
END;