萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 認證實戰:構建包含 VARRAY 與嵌套表的對象關係型表

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;

查詢集合欄位 (TABLE 操作符展開)

-- 查詢某訂單的所有明細 (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;

DML 維護集合元素

-- 向 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 + 元素欄位)
適用場景小量、固定上限、頻繁整體讀取的屬性集 (如電話號碼列表、標籤)大量、動態增長、需單獨查詢/索引/分區的明細集合 (如訂單明細、日誌條目)

認證考點聚焦

  1. 語法細節:NESTED TABLE ... STORE AS ... 子句必寫;VARRAY 無需指定存儲子句。
  2. 構造函數:類型名同名函數,參數對應屬性順序;CAST(MULTISET(...)) 是批量構造標準寫法。
  3. 成員方法:MEMBER FUNCTION 有返回值,MEMBER PROCEDURE 無返回值;調用時需實例化對象。
  4. DML 差異:VARRAY 需整體讀出修改回寫;嵌套表支援 INSERT/DELETE INTO TABLE(subquery) 片段操作。
  5. 異構遷移:Oracle 物件類型無法直接遷移至 PostgreSQL/MySQL,需展開為關係模型 (1:N 子表) 或 JSONB 文檔。
copyright © 萬盛學電腦網 all rights reserved