对象类型 + 游标

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