您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 基礎進階：結構化數據類型與類型化表完整指南

編輯：DB2 教學

前言：結構化類型——將複雜數據作為獨立單元處理

IBM DB2 UDB for Linux/UNIX/Windows 支援結構化數據類型：一種用戶定義類型，包含一系列屬性，每屬性有數據類型。屬性專門描述類型實例特性。例：定義 address_t，city 可為其屬性。結構化類型能將地址等數據作為獨立單元或單一數據項處理，無需將每項（屬性）存入單獨列。

常見兩種儲存方式：

普通表中一或多列使用結構化類型作為數據類型（清單 1、2）
表（類型化表）中的行，列根據結構化類型屬性定義。表用結構化類型創建，無需在表定義中指定單獨列（清單 3）

結構化數據類型核心特性

可作普通表列類型、整個表/視圖類型、另一結構化類型屬性
用作表類型時稱類型化表
創建結構化類型列表同樣方式，必須為每列指定數據類型，結構化類型列指定類型名
繼承：結構化類型可包含子類型，重用所有屬性並加入自身特定屬性。子類型繼承屬性的類型稱超類型。基於同一超類型的子類型集合稱類型層次結構，頂端超類型稱根類型
CREATE TYPE 創建，DROP 刪除
創建時 DB2 自動創建：構造函數（同類型名、無參數、返回所有屬性為空的實例）、每屬性的 mutator 方法（調用並指定新值返回更新屬性的新實例）、每屬性的 observer 方法（返回實例的屬性值）
調用 mutator/observer 方法使用雙點 .. 運算符

類型化表

用用戶定義結構化類型定義的表
以行形式儲存結構化類型實例，每屬性存單獨列；屬性名與類型變成列名與類型
類似結構化類型，類型化表可屬表層次結構：根表、超表、子表
CREATE TABLE 創建，DROP 刪除（被刪除者不可擁有子類型）。DROP TABLE HIERARCHY <root-table-name> 刪除整個表層次結構

完整實戰範例：從結構化類型列到類型化表層次結構

使用 DB2 附帶 SAMPLE 資料庫，僅需資料庫連接。

範例 1：普通表中的結構化類型列

創建 CLIENTS 表：CLIENT_ID 系統生成標識列、CLIENT_LNAME/FNAME 姓名、ADDRESS 類型 Address_t 結構化類型列（習慣用 _t 後綴標識結構化類型）。

CONNECT TO SAMPLE;
CREATE TYPE address_t AS (street VARCHAR(12), city VARCHAR(12), province VARCHAR(12), postal_code CHAR(6)) MODE DB2SQL;
CREATE TABLE clients (client_id INTEGER GENERATED ALWAYS AS IDENTITY, client_lname VARCHAR(12), client_fname VARCHAR(12), address address_t);

檢索結構化類型數據需轉換為標量值：創建 FROM SQL 轉換函數，用轉換組 關聯。創建標量函數 ADDRESS_TRANSFORM，輸入 Address_t 返回 VARCHAR(42)，函數體用 observer 方法 (..) 檢索屬性並連接 (||) 成郵寄地址。創建轉換組 DB2_PROGRAM（DB2 預設尋找的組名）。

CREATE FUNCTION address_transform (addr address_t) RETURNS VARCHAR(42) LANGUAGE SQL
  RETURN addr..street || ', ' || addr..city || ', ' || addr..province || ' ' || addr..postal_code;
CREATE TRANSFORM FOR address_t DB2_PROGRAM (FROM SQL WITH FUNCTION address_transform);

插入數據：address_t() 調用構造函數創建空實例，雙點調用 mutator 設置各屬性。查詢指定 ADDRESS 列檢索郵寄地址。

INSERT INTO clients (client_lname, client_fname, address) VALUES ('Nicholson', 'James',
  address_t() ..street('20 Indian Rd') ..city('Toronto') ..province('Ontario') ..postal_code('M6T2R1'));
SELECT client_id, client_fname, client_lname, address FROM clients;
-- CLIENT_ID CLIENT_FNAME CLIENT_LNAME ADDRESS
-- 21 James Nicholson 20 Indian Rd, Toronto, Ontario M6T2R1

檢索/更新單一屬性

調用 observer 方法獲取城市：SELECT client_id, client_lname, address..city AS city FROM clients。更新城市用 mutator：UPDATE clients SET address..city = 'Oakville' WHERE client_id = 21。（清單 2）

範例 2：類型化表層次結構與繼承演示

創建三個結構化類型：Emp_t（根類型，含先前 Address_t）、Salesperson_t、Engineer_t（後兩者為子類型）。REF USING INTEGER 指定 INTEGER 作為 REFERENCE 類型（系統類型，類型化表對象標識符類型，每類型化表必有對象標識符作第一列）。

CREATE TYPE emp_t AS (empno INTEGER, lname VARCHAR(12), fname VARCHAR(12), deptno CHAR(4), salary DECIMAL(7,2), address address_t) REF USING INTEGER MODE DB2SQL;
CREATE TYPE salesperson_t UNDER emp_t AS (commission DECIMAL(7,2)) MODE DB2SQL;
CREATE TYPE engineer_t UNDER emp_t AS (perf_bonus DECIMAL(7,2), recog_award DECIMAL(7,2)) MODE DB2SQL;

創建類型化表：超表 EMP，對象標識符列 OID 用戶生成（插入後不可修改）。INHERIT SELECT PRIVILEGES 保留超表 SELECT 權限的用戶/組獲同等權限。

CREATE TABLE emp OF emp_t (REF IS oid USER GENERATED);
CREATE TABLE salesperson OF salesperson_t UNDER emp INHERIT SELECT PRIVILEGES;
CREATE TABLE engineer OF engineer_t UNDER emp INHERIT SELECT PRIVILEGES;

插入數據：VALUES 含 address_t() 調用構造函數、雙點 mutator 設置地址、OID 列用 casting 函數（同結構化類型名 Engineer_t/Salesperson_t）強制轉換為 REFERENCE 類型。

INSERT INTO engineer (oid, empno, lname, fname, deptno, salary, perf_bonus, recog_award, address)
  VALUES (engineer_t(1), 42, 'Kidman', 'Jennifer', 'Z004', 65000.00, 4000.00, 2000.00,
    address_t() ..street('7 Dorval Rd') ..city('Markham') ..province('Ontario') ..postal_code('L6G2R1'));
INSERT INTO salesperson (oid, empno, lname, fname, deptno, salary, commission, address)
  VALUES (salesperson_t(2), 69, 'Theron', 'Maggie', 'C012', 49000.00, 15000.00,
    address_t() ..street('7 River St') ..city('Ottawa') ..province('Ontario') ..postal_code('K9G6R2'));

查詢三表顯示子表繼承超表列（含 OID）：

SELECT * FROM emp;
-- OID EMPNO LNAME FNAME DEPTNO SALARY ADDRESS
-- 1 42 Kidman Jennifer Z004 65000.00 7 Dorval Rd, Mark...
-- 2 69 Theron Maggie C012 49000.00 7 River St, Ottaw...

SELECT oid, empno, lname, deptno, salary, perf_bonus, recog_award, address FROM engineer;
-- 1 42 Kidman Z004 65000.00 4000.00 2000.00 7 Dorva...

SELECT oid, empno, lname, deptno, salary, commission, address FROM salesperson;
-- 2 69 Theron C012 49000.00 15000.00 7 River St, Ottawa...