Oracle TYPE OBJECT详解

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

最近遇到问题，了解到对象类型（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

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

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

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

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

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

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

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

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

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

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

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 过程已成功完成。