Oracle 物件關係型特性 (Object-Relational) 允許在關係表中直接建模複雜業務實體,支援抽象資料類型 (ADT)、VARRAY (可變長度陣列)、嵌套表、對象表與對象視圖。本文以 Oracle OCP/OCM 認證考點為切入點,通過完整 DDL/DML 範例,演示如何設計包含集合類型的表結構,並解析其存儲特性、查詢方式與效能考量。
-- 1. 基礎抽象類型 (可復用)
CREATE OR REPLACE TYPE goods_detail_typ AS OBJECT (
goods_id VARCHAR2(15),
goods_name VARCHAR2(100),
unit_price NUMBER(10,2),
MEMBER FUNCTION get_discount_price(p_discount NUMBER) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY goods_detail_typ AS
MEMBER FUNCTION get_discount_price(p_discount NUMBER) RETURN NUMBER IS
BEGIN
RETURN ROUND(unit_price * p_discount, 2);
END;
END;
/
-- 2. VARRAY 類型 (有序、有上限、內聯存儲)
CREATE OR REPLACE TYPE order_items_varray AS VARRAY(20) OF goods_detail_typ;
/
-- 3. 嵌套表類型 (無上限、單獨存儲段、支援集合操作)
CREATE OR REPLACE TYPE order_items_ntab AS TABLE OF goods_detail_typ;
/
-- 4. 訂單主表:包含 VARRAY 與嵌套表欄位
CREATE TABLE orders_obj (
order_id NUMBER PRIMARY KEY,
cust_id NUMBER,
order_date DATE DEFAULT SYSDATE,
items_varray order_items_varray, -- 內聯存儲 (小量明細)
items_ntab order_items_ntab -- 獨立存儲段 (大量明細)
)
NESTED TABLE items_ntab STORE AS order_items_ntab_seg; -- 指定嵌套表存儲段名
-- 方式一:VALUES 子句直接構造
INSERT INTO orders_obj VALUES (
1001, 888, SYSDATE,
order_items_varray(
goods_detail_typ('G001', '筆記本電腦', 5999),
goods_detail_typ('G002', '無線滑鼠', 199)
),
order_items_ntab(
goods_detail_typ('G003', '機械鍵盤', 899),
goods_detail_typ('G004', '27吋顯示器', 2499)
)
);
-- 方式二:SELECT 構造函數 (適用於從暫存表/外部表批量載入)
INSERT INTO orders_obj (order_id, cust_id, order_date, items_varray, items_ntab)
SELECT
1002, 999, SYSDATE,
CAST(MULTISET(SELECT goods_detail_typ(g.goods_id, g.goods_name, g.price)
FROM staging_order_items g WHERE g.order_id = 1002) AS order_items_varray),
CAST(MULTISET(SELECT goods_detail_typ(g.goods_id, g.goods_name, g.price)
FROM staging_order_items g WHERE g.order_id = 1002) AS order_items_ntab)
FROM dual;
-- 查詢某訂單的所有明細 (VARRAY)
SELECT o.order_id, v.*
FROM orders_obj o, TABLE(o.items_varray) v
WHERE o.order_id = 1001;
-- 查詢嵌套表明細並調用成員方法
SELECT o.order_id, n.goods_name, n.unit_price, n.get_discount_price(0.85) AS discount_price
FROM orders_obj o, TABLE(o.items_ntab) n
WHERE o.order_id = 1001;
-- 統計每個訂單的明細行數
SELECT o.order_id,
(SELECT COUNT(*) FROM TABLE(o.items_varray)) varray_cnt,
(SELECT COUNT(*) FROM TABLE(o.items_ntab)) ntab_cnt
FROM orders_obj o;
-- 向 VARRAY 追加元素 (需整體替換,VARRAY 不支援單元素 DML)
DECLARE
v_items order_items_varray;
BEGIN
SELECT items_varray INTO v_items FROM orders_obj WHERE order_id = 1001;
v_items.EXTEND;
v_items(v_items.LAST) := goods_detail_typ('G005', 'USB集線器', 89);
UPDATE orders_obj SET items_varray = v_items WHERE order_id = 1001;
END;
/
-- 向嵌套表插入/刪除單元素 (支援片段 DML)
INSERT INTO TABLE(SELECT items_ntab FROM orders_obj WHERE order_id = 1001)
VALUES (goods_detail_typ('G006', '網線', 29));
DELETE FROM TABLE(SELECT items_ntab FROM orders_obj WHERE order_id = 1001)
WHERE goods_id = 'G003';
| 特性 | VARRAY | 嵌套表 |
|---|---|---|
| 最大元素數 | 定義時固定 (如 20),不可超過 | 無上限 (受表空間限制) |
| 存儲方式 | 內聯 (Inline) 存儲於主表數據塊 | 獨立存儲段 (Out-of-line),類似子表 |
| 查詢展開 | 需 TABLE() 操作符,但無額外 JOIN | 同樣需 TABLE(),實際為 JOIN 存儲段 |
| 索引支援 | 不可直接建立索引 | 可在存儲段上建立索引 (NESTED_TABLE_ID + 元素欄位) |
| 適用場景 | 小量、固定上限、頻繁整體讀取的屬性集 (如電話號碼列表、標籤) | 大量、動態增長、需單獨查詢/索引/分區的明細集合 (如訂單明細、日誌條目) |
NESTED TABLE ... STORE AS ... 子句必寫;VARRAY 無需指定存儲子句。CAST(MULTISET(...)) 是批量構造標準寫法。MEMBER FUNCTION 有返回值,MEMBER PROCEDURE 無返回值;調用時需實例化對象。INSERT/DELETE INTO TABLE(subquery) 片段操作。