Oracle 数据库 ref 数据类型怎么用

忘是亡心i 2022-04-01 07:11 313阅读 0赞

转

# oracle中对象表，ref及deref的一个实例。 #

2014年04月15日 21:51:27 [zml19910422][] 阅读数：543 标签： [oracle][][ref][][deref][] 更多

个人分类： [sql/plsql基础知识][sql_plsql]

转自：[http://blog.csdn.net/strikers1982/article/details/5488088][http_blog.csdn.net_strikers1982_article_details_5488088]

ORACLE在关系数据库外，融入了面向对象的元素，比如可以创建type，type之间可以继承，type可以带构造函数、排序函数、各种各样的成员函数、存储过程等等。

对象表是指该表的一行就是一个对象，有一个OID(object ID)，对象表之间没有主外键关联的概念，为了体现这层关系，oracle中用了ref对象来实现。

下面例子，创建一个地址类型，一个人员类型，人员有地址属性，所以在人员类型中设置一个ref address来确定指向他所在地址的指针。

\--创建地址类型

create type address as object(  
  street varchar2(35),  
  city varchar2(15),  
  state char(2),  
  zip\_code integer  
);

create table addresses of address; --创建地址对象表

\--创建人员类型  
create type person as object(  
  first\_name varchar2(15),  
  last\_name varchar2(15),  
  birthday date,  
  home\_address ref address, --指向对应的地址，该地址应该在另外一个对象表中的一行  
  phone\_number varchar2(15)  
);

CREATE TABLE persons of person; --创建人员对象表

\--插入一个地址

insert into addresses values(address('nanhai','shenzhen','gd','518054'));

insert into addresses values(address('shennan','shenzhen','gd','518057'));

\--插入一个人员，注意这里的home\_address部分是如何插入一个ref address的。  
insert into persons values(person('shitou','haha',to\_date('1982-07-05','yyyy-mm-dd'),  
 (select ref(a) from addresses a where street='nanhai'),  
        '1355555555'));

\--也可以用下面的过程来插入一个人员记录

declare  
  addref ref address ;  
begin  
  select ref(a) into addref from addresses a where street='nanhai';  
  insert into persons  
    values (person('shitou','haha',to\_date('1982-07-05','yyyy-mm-dd'),  
                     addref,'1355555555'));  
  commit;  
end;

\--查询某人的地址信息  
select first\_name,deref(home\_address) from persons;

\--修改地址  
update persons set home\_address=(select ref(a) from addresses a where street='shennan');

\--删除某个人员

delete from persons where first\_name='shitou';

\--删除某个地址的相关人员记录  
delete from persons where home\_address=(select ref(a) from addresses a where street='nanhai');

[https://blog.csdn.net/zml19910422/article/details/23789675][https_blog.csdn.net_zml19910422_article_details_23789675]

[https://blog.csdn.net/zml19910422/article/details/30478899][https_blog.csdn.net_zml19910422_article_details_30478899]

### 转Oracle TYPE OBJECT详解 ###

2014年06月13日 13:24:58 [zml19910422][] 阅读数：532

转自：http://blog.csdn.net/indexman/article/details/8435426

======================================================

最近在自学PL/SQL高级编程，了解到对象类型（OBJECT TYPE）。

特意搜索了一下10G官方文档，下面不才基于此进行拓展：

=======================================================

**1. 介绍**  
Object-oriented programming is especially suited for building reusable components and complex

applications.

尤其适合于构建可重用的部件和复杂的应用程序的面向对象的编程。

In PL/SQL, object-oriented programming is based on object types.

在PL / SQL，面向对象的程序设计是基于对象类型。

They let you model real-world objects, separate interfaces and implementation details, and store  
object-oriented data persistently in the database.  
他们坚持让你模拟现实世界的对象，单独的接口和实现细节，面向对象的数据和存储在数据库中。  
  
2. PL / SQL的声明和初始化对象  
对象的类型可以代表任何真实世界的实体。例如，一个对象的类型可以代表一个学生，银行帐户，电脑屏幕上  
  
，合理数量，或数据结构，如队列，堆栈，或列表。  
  
**\[sql\]** [view plain][][copy][view plain]

1.  CREATE OR REPLACE TYPE address\_typ AS OBJECT (  
2.         street          VARCHAR2(30),                       
3.         city            VARCHAR2(20),  
4.         state           CHAR(2),                              
5.         postal\_code     VARCHAR2(6)    
6.          );

**\[sql\]** [view plain][][copy][view plain]

1.  CREATE OR REPLACE TYPE employee\_typ AS OBJECT(  
2.         employee\_id       NUMBER(6),  
3.         first\_name        VARCHAR2(20),  
4.         last\_name         VARCHAR2(25),  
5.         email             VARCHAR2(25),  
6.         phone\_number      VARCHAR2(25),  
7.         hire\_date         DATE,  
8.         job\_id            VARCHAR2(25),                                                     
9.         salary            NUMBER(8,2),                
10.        commission\_pct    NUMBER(2,2),                                                     
11.        manager\_id        NUMBER(6),                                                     
12.        department\_id     NUMBER(4),                                                     
13.        address           address\_typ                                                     
14.        MAP MEMBER FUNCTION get\_idno RETURN NUMBER,                                                     
15.        MEMBER PROCEDURE display\_address(SELF IN OUT NOCOPY employee\_typ)  
16.        );

\--创建对象体  
**\[sql\]** [view plain][][copy][view plain]

1.  CREATE TYPE BODY employee\_typ AS  
2.    MAP MEMBER FUNCTION get\_idno RETURN NUMBER IS  
3.    BEGIN  
4.      RETURN employee\_id;  
5.    END;  
6.    MEMBER PROCEDURE display\_address ( SELF IN OUT NOCOPY employee\_typ ) IS  
7.    BEGIN  
8.      DBMS\_OUTPUT.PUT\_LINE(first\_name || ' '  || last\_name);  
9.      DBMS\_OUTPUT.PUT\_LINE(address.street);  
10.     DBMS\_OUTPUT.PUT\_LINE(address.city || ', '  || address.state || ' ' ||  
11.                          address.postal\_code);     
12.   END;  
13. END;

\--持久化对象  
**\[sql\]** [view plain][][copy][view plain]

1.  CREATE TABLE employee\_tab OF employee\_typ;  
2.  CREATE TYPE  emp\_typ as table of employee\_typ;

**3.  在PL/SQL块中声明对象：**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.    emp employee\_typ; -- emp is atomically null  
3.  BEGIN  
4.  \-- call the constructor for employee\_typ  
5.    emp := employee\_typ(315, 'Francis', 'Logan', 'FLOGAN',  
6.          '555.777.2222', to\_date('2012-12-24', 'yyyy-mm-dd'), 'SA\_MAN', 11000, .15, 101, 110,  
7.           address\_typ('376 Mission', 'San Francisco', 'CA', '94222'));  
8.    DBMS\_OUTPUT.PUT\_LINE(emp.first\_name || ' ' || emp.last\_name); -- display details  
9.    emp.display\_address();  -- call object method to display details  
10. END;

**4.  PL/SQL如何处理未初始化的对象：**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.    emp employee\_typ; -- emp is atomically null  
3.  BEGIN  
4.    IF emp IS NULL THEN DBMS\_OUTPUT.PUT\_LINE('emp is NULL \#1'); END IF;  
5.    IF emp.employee\_id IS NULL THEN  
6.       DBMS\_OUTPUT.PUT\_LINE('emp.employee\_id is NULL \#1');  
7.    END IF;  
8.    emp.employee\_id := 330;  
9.    IF emp IS NULL THEN DBMS\_OUTPUT.PUT\_LINE('emp is NULL \#2'); END IF;  
10.   IF emp.employee\_id IS NULL THEN  
11.     DBMS\_OUTPUT.PUT\_LINE('emp.employee\_id is NULL \#2');  
12.   END IF;  
13.   emp := employee\_typ(NULL, NULL, NULL, NULL,  
14.         NULL, NULL, NULL, NULL, NULL, NULL, NULL,  
15.          address\_typ(NULL, NULL, NULL, NULL));  
16.   -- emp := NULL; -- this would have made the following IF statement TRUE  
17.   IF emp IS NULL THEN DBMS\_OUTPUT.PUT\_LINE('emp is NULL \#3'); END IF;  
18.   IF emp.employee\_id IS NULL THEN  
19.     DBMS\_OUTPUT.PUT\_LINE('emp.employee\_id is NULL \#3');  
20.   END IF;  
21. EXCEPTION  
22.    WHEN ACCESS\_INTO\_NULL THEN  
23.      DBMS\_OUTPUT.PUT\_LINE('Cannot assign value to NULL object');  
24. END;

**5. 在PL/SQL中操纵对象：**

**5.1.调用对象构造器和方法（Calling Object Constructors and Methods）**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.    emp employee\_typ;  
3.  BEGIN  
4.    INSERT INTO employee\_tab VALUES (employee\_typ(310, 'Evers', 'Boston', 'EBOSTON',  
5.     '555.111.2222', to\_date('2012-12-24', 'yyyy-mm-dd'), 'SA\_REP', 9000, .15, 101, 110,  
6.      address\_typ('123 Main', 'San Francisco', 'CA', '94111')) );  
7.    INSERT INTO employee\_tab VALUES (employee\_typ(320, 'Martha', 'Dunn', 'MDUNN',  
8.      '555.111.3333', to\_date('2012-11-5', 'yyyy-mm-dd'), 'AC\_MGR', 12500, 0, 101, 110,  
9.      address\_typ('123 Broadway', 'Redwood City', 'CA', '94065')) );  
10. END;

**5.2  更新和删除对象：**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.    emp employee\_typ;  
3.  BEGIN  
4.    INSERT INTO employee\_tab VALUES (employee\_typ(370, 'Robert', 'Myers', 'RMYERS',  
5.     '555.111.2277', to\_date('2012-3-7', 'yyyy-mm-dd'), 'SA\_REP', 8800, .12, 101, 110,  
6.      address\_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) );  
7.    UPDATE employee\_tab e SET e.address.street = '1040 California'  
8.       WHERE e.employee\_id = 370;  
9.    DELETE FROM employee\_tab e WHERE e.employee\_id = 310;  
10. END;

**6.  通过REF修饰符操纵对象：**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.    emp           employee\_typ;  
3.    emp\_ref   REF employee\_typ;  
4.    emp\_name      VARCHAR2(50);  
5.  BEGIN  
6.    SELECT REF(e) INTO emp\_ref FROM employee\_tab e WHERE e.employee\_id = 370;  
7.  \-- the following assignment raises an error, not allowed in PL/SQL  
8.  \-- emp\_name := emp\_ref.first\_name || ' ' || emp\_ref.last\_name;  
9.  \-- emp := DEREF(emp\_ref); not allowed, cannot use DEREF in procedural statements  
10.   SELECT DEREF(emp\_ref) INTO emp FROM DUAL; -- use dummy table DUAL  
11.   emp\_name := emp.first\_name || ' ' || emp.last\_name;  
12.   DBMS\_OUTPUT.PUT\_LINE(emp\_name);  
13. END;

**7. 定义相当于PL/SQL集合类型的SQL类型（Defining SQL Types Equivalent to PL/SQL Collection Types）**

7.1 定义嵌套表：  
  
\--建嵌套表类型  
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- define type  
\--建对象类型  
CREATE TYPE student AS OBJECT (  -- create object  
   id\_num  INTEGER(4),  
   name    VARCHAR2(25),  
   address VARCHAR2(35),  
   status  CHAR(2),  
   courses CourseList);  -- declare nested table as attribute  
\--建立嵌套表类型表  
CREATE TABLE sophomores of student  
  NESTED TABLE courses STORE AS courses\_nt;  
\--插入数据  
insert into sophomores   
values(1,'dylan','CARL STREET','ACTIVE',  
       CourseList('MATH1020')  
      );  
\--查询  
SELECT  a.\*, b.\*   
from  sophomores a, TABLE(a.courses) b;   
  
select /\*+ nested\_table\_get\_refs \*/ \*   
from courses\_nt t;  
  
  
**7.2 定义数组：**  
\-- 声明数组类型（Each project has a 16-character code name）  
\-- We will store up to 50 projects at a time in a database column.  
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);  
\--创建表  
CREATE TABLE dept\_projects (  -- create database table  
   dept\_id  NUMBER(2),  
   name     VARCHAR2(15),  
   budget   NUMBER(11,2),  
\-- Each department can have up to 50 projects.  
   projects ProjectList);  
  
\--插入数据：  
  INSERT INTO dept\_projects  
    VALUES(60, 'Security', 750400,  
      ProjectList('New Badges', 'Track Computers', 'Check Exits'));  
  
  
**8.  在动态SQL中使用对象：**  
8.1 定义对象类型person\_typ和数组类型hobbies\_var,并创建报TEAMS:  
  
**\[sql\]** [view plain][][copy][view plain]

1.  CREATE TYPE person\_typ AS OBJECT (name VARCHAR2(25), age NUMBER);  
2.    
3.  CREATE TYPE hobbies\_var AS VARRAY(10) OF VARCHAR2(25);  
4.    
5.  CREATE OR REPLACE PACKAGE teams  
6.     AUTHID CURRENT\_USER AS  
7.     PROCEDURE create\_table (tab\_name VARCHAR2);  
8.     PROCEDURE insert\_row (tab\_name VARCHAR2, p person\_typ, h hobbies\_var);  
9.     PROCEDURE print\_table (tab\_name VARCHAR2);  
10. END;  
11.   
12. CREATE OR REPLACE PACKAGE BODY teams AS  
13.    PROCEDURE create\_table (tab\_name VARCHAR2) IS  
14.    BEGIN  
15.       EXECUTE IMMEDIATE 'CREATE TABLE ' || tab\_name ||  
16.                         ' (pers person\_typ, hobbs hobbies\_var)';  
17.    END;  
18.    PROCEDURE insert\_row (  
19.       tab\_name VARCHAR2,  
20.       p person\_typ,  
21.       h hobbies\_var) IS  
22.    BEGIN  
23.       EXECUTE IMMEDIATE 'INSERT INTO ' || tab\_name ||  
24.          ' VALUES (:1, :2)' USING p, h;  
25.    END;  
26.    PROCEDURE print\_table (tab\_name VARCHAR2) IS  
27.       TYPE  refcurtyp IS REF CURSOR;  
28.       v\_cur refcurtyp;  
29.       p     person\_typ;  
30.       h     hobbies\_var;  
31.    BEGIN  
32.       OPEN v\_cur FOR 'SELECT pers, hobbs FROM ' || tab\_name;  
33.       LOOP  
34.          FETCH v\_cur INTO p, h;  
35.          EXIT WHEN v\_cur%NOTFOUND;  
36.          -- print attributes of 'p' and elements of 'h'  
37.          DBMS\_OUTPUT.PUT\_LINE('Name: ' || p.name || ' - Age: ' || p.age);  
38.          FOR i IN h.FIRST..h.LAST  
39.          LOOP  
40.            DBMS\_OUTPUT.PUT\_LINE('Hobby(' || i || '): ' || h(i));  
41.          END LOOP;  
42.       END LOOP;  
43.       CLOSE v\_cur;  
44.    END;  
45. END;

**8.2 调用TEAMS包中的存储过程：**

**\[sql\]** [view plain][][copy][view plain]

1.  DECLARE  
2.     team\_name VARCHAR2(15);  
3.  BEGIN  
4.     team\_name := 'Notables';  
5.     TEAMS.create\_table(team\_name);  
6.     TEAMS.insert\_row(team\_name, person\_typ('John', 31),  
7.        hobbies\_var('skiing', 'coin collecting', 'tennis'));  
8.     TEAMS.insert\_row(team\_name, person\_typ('Mary', 28),  
9.        hobbies\_var('golf', 'quilting', 'rock climbing', 'fencing'));  
10.    TEAMS.print\_table(team\_name);  
11. END;

=================================================  
output:  
Name: John - Age: 31  
Hobby(1): skiing  
Hobby(2): coin collecting  
Hobby(3): tennis  
Name: Mary - Age: 28  
Hobby(1): golf  
Hobby(2): quilting  
Hobby(3): rock climbing  
Hobby(4): fencing

PL/SQL 过程已成功完成。

========================

**Powered  By  Dylan**

========================

相关参考：

[Oracle中对象表，ref及deref的一个实例。][Oracle_ref_deref]

[Oracle AUTHID DEFINER与AUTHID CURRENT\_USER介绍][Oracle AUTHID DEFINER_AUTHID CURRENT_USER]

[https://blog.csdn.net/zml19910422/article/details/30478899][https_blog.csdn.net_zml19910422_article_details_30478899]

# oracle 数据库 ref 数据类型怎么用 #

最佳答案![20181229172324263.png][]

![20181229172411132][]573767859   
知道合伙人互联网行家 推荐于2018-04-12

Oracle在关系数据库外，融入了面向对象的元素，比如可以创建type，type之间可以继承，type可以带构造函数、排序函数、各种各样的成员函数、存储过程等等。  
　　对象表是指该表的一行就是一个对象，有一个OID(object ID)，对象表之间没有主外键关联的概念，为了体现这层关系，oracle中用了ref对象来实现。  
　　下面例子，创建一个地址类型，一个人员类型，人员有地址属性，所以在人员类型中设置一个ref address来确定指向他所在地址的指针。  
　　--创建地址类型  
　　create type address as object(  
　　street varchar2(35),  
　　city varchar2(15),  
　　state char(2),  
　　zip\_code integer  
　　);  
　　create table addresses of address; --创建地址对象表--创建人员类型  
　　create type person as object(  
　　first\_name varchar2(15),  
　　last\_name varchar2(15),  
　　birthday date,  
　　home\_address ref address, --指向对应的地址，该地址应该在另外一个对象表中的一行phone\_number varchar2(15)  
　　);  
　　CREATE TABLE persons of person; --创建人员对象表--插入一个地址  
　　insert into addresses values(address('nanhai','shenzhen','gd','518054'));insert into addresses values(address('shennan','shenzhen','gd','518057'));--插入一个人员，注意这里的home\_address部分是如何插入一个ref address的。  
　　insert into persons values(person('shitou','haha',to\_date('1982-07-05','yyyy-mm-dd'),(select ref(a) from addresses a where street='nanhai'),'1355555555'));  
　　--也可以用下面的过程来插入一个人员记录  
　　declare  
　　addref ref address ;  
　　begin  
　　select ref(a) into addref from addresses a where street='nanhai';insert into persons  
　　values (person('shitou','haha',to\_date('1982-07-05','yyyy-mm-dd'),addref,'1355555555'));  
　　commit;  
　　end;  
　　--查询某人的地址信息  
　　select first\_name,deref(home\_address) from persons;--修改地址  
　　update persons set home\_address=(select ref(a) from addresses a where street='shennan');--删除某个人员  
　　delete from persons where first\_name='shitou';--删除某个地址的相关人员记录  
　　delete from persons where home\_address=(select ref(a) from addresses a where street='nanhai');

[https://zhidao.baidu.com/question/923336789869065979.html][https_zhidao.baidu.com_question_923336789869065979.html]

[zml19910422]: https://me.csdn.net/zml19910422
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[ref]: http://so.csdn.net/so/search/s.do?q=ref&t=blog
[deref]: http://so.csdn.net/so/search/s.do?q=deref&t=blog
[sql_plsql]: https://blog.csdn.net/zml19910422/article/category/2331953
[http_blog.csdn.net_strikers1982_article_details_5488088]: http://blog.csdn.net/strikers1982/article/details/5488088
[https_blog.csdn.net_zml19910422_article_details_23789675]: https://blog.csdn.net/zml19910422/article/details/23789675
[https_blog.csdn.net_zml19910422_article_details_30478899]: https://blog.csdn.net/zml19910422/article/details/30478899
[view plain]: http://blog.csdn.net/indexman/article/details/8435426#
[Oracle_ref_deref]: http://blog.csdn.net/zml19910422/article/details/23789675
[Oracle AUTHID DEFINER_AUTHID CURRENT_USER]: http://blog.csdn.net/zml19910422/article/details/30479767
[20181229172324263.png]: /images/20220401/07088c8c05764f09ba785560c1c66adc.png
[20181229172411132]: /images/20220401/cc2b1d641a5f4aa68f88dda9559969c0.png
[https_zhidao.baidu.com_question_923336789869065979.html]: https://zhidao.baidu.com/question/923336789869065979.html