oracle object_type,Oracle TYPE OBJECT详解 | 学步园

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

最近在自学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的声明和初始化对象

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

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

CREATE OR REPLACE TYPE address_typ AS OBJECT (

street VARCHAR2(30),

city VARCHAR2(20),

state CHAR(2),

postal_code VARCHAR2(6)

);

CREATE OR REPLACE TYPE employee_typ AS OBJECT(

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 FUNCTION get_idno RETURN NUMBER,

MEMBER PROCEDURE display_address(SELF IN OUT NOCOPY employee_typ)

);

--创建对象体CREATE TYPE BODY employee_typ AS

MAP MEMBER FUNCTION get_idno RETURN NUMBER IS

BEGIN

RETURN employee_id;

END;

MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY 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;

--持久化对象CREATE TABLE employee_tab OF employee_typ;

CREATE TYPE emp_typ as table of employee_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 IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #1'); END IF;

IF emp.employee_id IS NULL THEN

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

END IF;

emp.employee_id := 330;

IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #2'); END IF;

IF emp.employee_id IS NULL THEN

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

END IF;

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 IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #3'); END IF;

IF emp.employee_id IS NULL THEN

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

END IF;

EXCEPTION

WHEN ACCESS_INTO_NULL THEN

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

INSERT INTO employee_tab VALUES (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')) );

INSERT INTO employee_tab VALUES (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

INSERT INTO employee_tab VALUES (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')) );

UPDATE employee_tab e SET e.address.street = '1040 California'

WHERE e.employee_id = 370;

DELETE FROM employee_tab e WHERE e.employee_id = 310;

END;

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

DECLARE

emp employee_typ;

emp_ref REF employee_typ;

emp_name VARCHAR2(50);

BEGIN

SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.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

SELECT DEREF(emp_ref) INTO emp FROM DUAL; -- 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:

CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);

CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);

CREATE OR REPLACE PACKAGE teams

AUTHID CURRENT_USER AS

PROCEDURE create_table (tab_name VARCHAR2);

PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);

PROCEDURE print_table (tab_name VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY teams AS

PROCEDURE create_table (tab_name VARCHAR2) IS

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||

' (pers person_typ, hobbs hobbies_var)';

END;

PROCEDURE insert_row (

tab_name VARCHAR2,

p person_typ,

h hobbies_var) IS

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||

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

END;

PROCEDURE print_table (tab_name VARCHAR2) IS

TYPE refcurtyp IS REF CURSOR;

v_cur refcurtyp;

p person_typ;

h hobbies_var;

BEGIN

OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;

LOOP

FETCH v_cur INTO p, h;

EXIT WHEN v_cur%NOTFOUND;

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

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

FOR i IN h.FIRST..h.LAST

LOOP

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

END LOOP;

END LOOP;

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

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

Powered  By  Dylan

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