Oracle TYPE OBJECT详解
来源:互联网 发布:linux vi覆盖文件内容 编辑:程序博客网 时间:2024/05/16 10:42
======================================================
最近在自学PL/SQL高级编程,了解到对象类型(OBJECT TYPE)。
特意搜索了一下10G官方文档,下面不才基于此进行拓展:
=======================================================
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 storeobject-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 nullBEGIN-- 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 detailsEND;
4. PL/SQL如何处理未初始化的对象:
DECLARE emp employee_typ; -- emp is atomically nullBEGIN 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)
--建嵌套表类型
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
========================
- Oracle TYPE OBJECT详解
- Oracle TYPE OBJECT详解
- Oracle TYPE OBJECT详解
- 【转】Oracle Object type 对象类型详解
- Oracle TYPE OBJECT 用法
- Oracle TYPE OBJECT 用法
- Oracle TYPE OBJECT 用法
- oracle 对象类型 object type
- Oracle Object type 对象类型
- 【转】Oracle TYPE OBJECT 深入
- oracle type rowtype详解
- Object Type
- 【转】Oracle TYPE OBJECT 的成员函数应用
- oracle--v$lock type字段详解
- Business Object Type
- qemu-type-object-initialize
- python object和type
- oracle %type
- boost serialization 32位库与64位库间二进制数据读写
- 迷茫的大学------李开复
- 表格控件GridPanel
- 人脸数据库总结
- Sed的使用方法
- Oracle TYPE OBJECT详解
- Linux下passwd和shadow文件内容详解
- 大学入党申请书范文
- Java操作XML(使用org.w3c.dom)1/3
- 为多种屏幕尺寸编写移动Flash内容
- 常用网页播放器代码
- Java操作XML(使用org.w3c.dom)2/3
- 挺立在孤独与失败的废墟上-----俞敏洪
- 自定义圆形进度条ProgressBar的三种方式