DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename scott.emp.ename%TYPE;
v_sal scott.emp.sal%TYPE;
-- cursor c1 is select ....
BEGIN
OPEN emp_cursor FOR SELECT t.ename,t.sal FROM scott.emp t WHERE t.deptno=10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename scott.emp.ename%TYPE;
v_sal scott.emp.sal%TYPE;
v_deptno scott.dept.deptno%type;
BEGIN
OPEN emp_cursor FOR
SELECT t.ename,t.sal FROM scott.emp t WHERE t.deptno=10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
open emp_cursor FOR select deptno from dept;
loop
fetch emp_cursor into v_deptno;
exit when emp_cursor%NOTFOUND;
dbms_output.put_line(v_deptno);
END LOOP;
close emp_cursor ;
END;
B、对象类型变量（REF obj_type）
代码：
CREATE OR REPLACE TYPE home_type AS OBJECT(
street VARCHAR2(50),
city VARCHAR2(20),
state VARCHAR2(20),
owner VARCHAR2(10)
);
CREATE TABLE homes OF home_type;
SELECT * FROM homes
INSERT INTO homes VALUES('dajie NO. 1 ','shenzhen','guangzhou','Lee');
INSERT INTO homes VALUES('dajie NO. 2 ','shenzhen','guangzhou','Wang');
INSERT INTO homes VALUES('dajie NO. 3 ','shenzhen','guangzhou', 'Zhang');
select ref(h) ,value(h) from homes h ;
-->ref（）查看oid
-->value()以对象的形式显示数据
CREATE TABLE person(
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(10),
addr REF home_type
);
INSERT INTO person SELECT 1,'Lee',ref(h) FROM homes h WHERE h.owner='Lee';
INSERT INTO person SELECT 2,'Zhang',ref(h) FROM homes h WHERE h.owner='Lee';
SELECT * FROM person;
select deref(addr) from person;
select id, name,deref(addr) from person;
delete from homes where owner='Lee';
select id, name,deref(addr) from person;
SELECT * FROM person;
update person set addr=null where addr is dangling