1：定义REF动态游标类型
TYPE <类型名> IS REF CURSOR
RETURN <返回类型>;
声明REF动态游标
<游标名> <类型名>
打开REF动态游标
OPEN <游标名> FOR <查询语句>
2：REF动态游标的分类
主要分为两类，强类型和弱类型的，强类型的为带有RETURN语句的REF动态游标，弱类型为不带有RETURN语句的REF动态游标
强类型REF动态游标实例：
DECLARE
TYPE refcur_t IS REF CORSOR
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;
弱类型REF动态游标实例一：
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;
弱类型REF动态游标实例二：根据用户的输入查询部门表或者员工信息表
DECLARE
TYPE refcur_t IS REF CURSOR;
refcur refcur_t;
pid 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('====员工信息======');
elseif 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;