oracle中object和type,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的声明和初始化对象

对象的类型可以代表任何真实世界的实体。例如，一个对象的类型可以代表一个学生，银行帐户，电脑屏幕上

，合理数量，或数据结构，如队列，堆栈，或列表。

CREATEORREPLACETYPE address_typASOBJECT (

street          VARCHAR2(30),

city            VARCHAR2(20),

state           CHAR(2),

postal_code     VARCHAR2(6)

);

CREATEORREPLACETYPE employee_typASOBJECT(

employee_id       NUMBER(6),

first_name        VARCHAR2(20),

last_name         VARCHAR2(25),

email             VARCHAR2(25),

phone_number      VARCHAR2(25),

hire_date         DATE,

job_id            VARCHAR2(25),

salary            NUMBER(8,2),

commission_pct    NUMBER(2,2),

manager_id        NUMBER(6),

department_id     NUMBER(4),

address           address_typ

MAP MEMBER FUNCTIONget_idnoRETURNNUMBER,

MEMBER PROCEDUREdisplay_address(SELFINOUTNOCOPY employee_typ)

);

--

创建对象体

CREATETYPE BODY employee_typAS

MAP MEMBER FUNCTIONget_idnoRETURNNUMBERIS

BEGIN

RETURNemployee_id;

END;

MEMBER PROCEDUREdisplay_address ( SELFINOUTNOCOPY employee_typ )IS

BEGIN

DBMS_OUTPUT.PUT_LINE(first_name || ' '|| last_name);

DBMS_OUTPUT.PUT_LINE(address.street);

DBMS_OUTPUT.PUT_LINE(address.city || ', '|| address.state ||' '||

address.postal_code);

END;

END;

--

持久化对象

CREATETABLEemployee_tabOFemployee_typ;

CREATETYPE  emp_typastableofemployee_typ;

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

DECLARE

emp employee_typ; -- emp is atomically null

BEGIN

-- call the constructor for employee_typ

emp := employee_typ(315, 'Francis','Logan','FLOGAN',

'555.777.2222', to_date('2012-12-24','yyyy-mm-dd'),'SA_MAN', 11000, .15, 101, 110,

address_typ('376 Mission','San Francisco','CA','94222'));

DBMS_OUTPUT.PUT_LINE(emp.first_name || ' '|| emp.last_name);-- display details

emp.display_address();  -- call object method to display details

END;

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

DECLARE

emp employee_typ; -- emp is atomically null

BEGIN

IF emp ISNULLTHENDBMS_OUTPUT.PUT_LINE('emp is NULL #1');ENDIF;

IF emp.employee_id ISNULLTHEN

DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #1');

ENDIF;

emp.employee_id := 330;

IF emp ISNULLTHENDBMS_OUTPUT.PUT_LINE('emp is NULL #2');ENDIF;

IF emp.employee_id ISNULLTHEN

DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #2');

ENDIF;

emp := employee_typ(NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,

address_typ(NULL,NULL,NULL,NULL));

-- emp := NULL; -- this would have made the following IF statement TRUE

IF emp ISNULLTHENDBMS_OUTPUT.PUT_LINE('emp is NULL #3');ENDIF;

IF emp.employee_id ISNULLTHEN

DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #3');

ENDIF;

EXCEPTION

WHENACCESS_INTO_NULLTHEN

DBMS_OUTPUT.PUT_LINE('Cannot assign value to NULL object');

END;

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

5.1.调用对象构造器和方法(Calling Object Constructors and Methods)

DECLARE

emp employee_typ;

BEGIN

INSERTINTOemployee_tabVALUES(employee_typ(310,'Evers','Boston','EBOSTON',

'555.111.2222', to_date('2012-12-24','yyyy-mm-dd'),'SA_REP', 9000, .15, 101, 110,

address_typ('123 Main','San Francisco','CA','94111')) );

INSERTINTOemployee_tabVALUES(employee_typ(320,'Martha','Dunn','MDUNN',

'555.111.3333', to_date('2012-11-5','yyyy-mm-dd'),'AC_MGR', 12500, 0, 101, 110,

address_typ('123 Broadway','Redwood City','CA','94065')) );

END;

5.2  更新和删除对象：

DECLARE

emp employee_typ;

BEGIN

INSERTINTOemployee_tabVALUES(employee_typ(370,'Robert','Myers','RMYERS',

'555.111.2277', to_date('2012-3-7','yyyy-mm-dd'),'SA_REP', 8800, .12, 101, 110,

address_typ('540 Fillmore','San Francisco','CA','94011')) );

UPDATEemployee_tab eSETe.address.street ='1040 California'

WHEREe.employee_id = 370;

DELETEFROMemployee_tab eWHEREe.employee_id = 310;

END;

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

DECLARE

emp           employee_typ;

emp_ref   REF employee_typ;

emp_name      VARCHAR2(50);

BEGIN

SELECTREF(e)INTOemp_refFROMemployee_tab eWHEREe.employee_id = 370;

-- the following assignment raises an error, not allowed in PL/SQL

-- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;

-- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements

SELECTDEREF(emp_ref)INTOempFROMDUAL;-- use dummy table DUAL

emp_name := emp.first_name || ' '|| emp.last_name;

DBMS_OUTPUT.PUT_LINE(emp_name);

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:

CREATETYPE person_typASOBJECT (nameVARCHAR2(25), age NUMBER);

CREATETYPE hobbies_varASVARRAY(10)OFVARCHAR2(25);

CREATEORREPLACEPACKAGE teams

AUTHID CURRENT_USERAS

PROCEDUREcreate_table (tab_name VARCHAR2);

PROCEDUREinsert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);

PROCEDUREprint_table (tab_name VARCHAR2);

END;

CREATEORREPLACEPACKAGE BODY teamsAS

PROCEDUREcreate_table (tab_name VARCHAR2)IS

BEGIN

EXECUTEIMMEDIATE'CREATE TABLE '|| tab_name ||

' (pers person_typ, hobbs hobbies_var)';

END;

PROCEDUREinsert_row (

tab_name VARCHAR2,

p person_typ,

h hobbies_var) IS

BEGIN

EXECUTEIMMEDIATE'INSERT INTO '|| tab_name ||

' VALUES (:1, :2)'USING p, h;

END;

PROCEDUREprint_table (tab_name VARCHAR2)IS

TYPE  refcurtyp ISREFCURSOR;

v_cur refcurtyp;

p     person_typ;

h     hobbies_var;

BEGIN

OPENv_curFOR'SELECT pers, hobbs FROM '|| tab_name;

LOOP

FETCHv_curINTOp, h;

EXIT WHENv_cur%NOTFOUND;

-- print attributes of 'p' and elements of 'h'

DBMS_OUTPUT.PUT_LINE('Name: '|| p.name||' - Age: '|| p.age);

FORiINh.FIRST..h.LAST

LOOP

DBMS_OUTPUT.PUT_LINE('Hobby('|| i ||'): '|| h(i));

ENDLOOP;

ENDLOOP;

CLOSEv_cur;

END;

END;

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

DECLARE

team_name VARCHAR2(15);

BEGIN

team_name := 'Notables';

TEAMS.create_table(team_name);

TEAMS.insert_row(team_name, person_typ('John', 31),

hobbies_var('skiing','coin collecting','tennis'));

TEAMS.insert_row(team_name, person_typ('Mary', 28),

hobbies_var('golf','quilting','rock climbing','fencing'));

TEAMS.print_table(team_name);

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