嵌套表

嵌套表(Nested Table)是Oracle中的一種集合數據類型，它允許在表中存儲多值屬性，類似于在表中嵌套另一個表。

嵌套表具有以下特點：

是Oracle對象關系特性的一部分

可以看作是一維數組，沒有最大元素數量限制

存儲在單獨的存儲表中，與主表有引用關系

可以包含對象類型或標量類型

-- 創建一個地址對象類型

CREATE TYPE address_type AS OBJECT (street VARCHAR2(50),city VARCHAR2(30),state VARCHAR2(2),zip VARCHAR2(10)
);
/

SELECT OBJECT_NAME, OBJECT_TYPE ?FROM USER_OBJECTS ?WHERE OBJECT_NAME = 'ADDRESS_TABLE_TYPE';?
-- 創建一個嵌套表類型，基于address_type

CREATE TYPE address_table_type AS TABLE OF address_type;
/CREATE TABLE customers (customer_id NUMBER,name VARCHAR2(50),addresses address_table_type
) NESTED TABLE addresses STORE AS customer_addresses;

-- 插入數據到嵌套表

INSERT INTO customers VALUES (1,?'John Smith',address_table_type(address_type('123 Main St', 'New York', 'NY', '10001'),address_type('456 Oak Ave', 'Boston', 'MA', '02134'))
);

-- 基本查詢
SELECT c.customer_id, c.name, a.street, a.city, a.state, a.zip FROM customers c, TABLE(c.addresses) a;

-- 使用別名簡化
SELECT c.customer_id, c.name, addr.* FROM customers c, TABLE(c.addresses) addr;

SELECT table_name, parent_table_name FROM user_nested_tables WHERE parent_table_name = 'CUSTOMERS';

-- 更新整個嵌套表

UPDATE customers
SET addresses = address_table_type(address_type('789 Pine Rd', 'Chicago', 'IL', '60601'),address_type('321 Elm St', 'Denver', 'CO', '80202')
)
WHERE customer_id = 1;

-- 更新嵌套表中的特定元素

UPDATE TABLE(SELECT addresses FROM customers WHERE customer_id = 1
) a
SET a.city = 'Los Angeles'
WHERE a.city = 'New York';

-- 刪除嵌套表中的特定元素

DELETE FROM TABLE(SELECT addresses FROM customers WHERE customer_id = 1
) a
WHERE a.city = 'Boston';

-- 在嵌套表存儲表上創建索引
CREATE INDEX idx_cust_addr_city ON customer_addresses (city);

--刪除嵌套存儲表上索引

DROP INDEX idx_cust_addr_city;

嵌套表存儲表會自動創建一個系統生成的NESTED_TABLE_ID列，用于與主表關聯。
-- 在嵌套表ID上創建索引,默認創建
CREATE INDEX idx_nested_table_id ON customer_addresses (NESTED_TABLE_ID,city);

NESTED_TABLE_ID實際上是與主表的行標識符(ROWID)相關聯，雖然基于行標識的概念，但不是直接存儲主表的物理ROWID
Oracle使用內部對象標識符(OID)來實現這種關聯

-- 查看嵌套表存儲表的結構(不顯示NESTED_TABLE_ID列)
DESCRIBE customer_addresses;

刪除嵌套表
-- 1. 先刪除或修改使用該類型的表列
ALTER TABLE employees DROP COLUMN phones;

-- 2. 再刪除嵌套表類型
DROP TYPE phone_nt;

-- 3. 最后刪除對象類型(如有)
DROP TYPE phone_type;

在PLSQL內使用嵌套表類型

使用 record 與 object 區別 ，record 不能使用構造函數初始化 只能先初始化為null

DECLARE-- 定義數字類型的嵌套表TYPE number_nt IS TABLE OF NUMBER;-- 定義字符串類型的嵌套表TYPE varchar2_nt IS TABLE OF VARCHAR2(100);-- 使用這些類型聲明變量v_numbers number_nt := number_nt(1, 3, 5, 7);v_names varchar2_nt := varchar2_nt('Alice', 'Bob', 'Charlie');
BEGINnull;
END;-- 首先創建對象類型 /或者在PLSQL內使用record
CREATE OR REPLACE TYPE emp_obj AS OBJECT (emp_id NUMBER,emp_name VARCHAR2(100),hire_date DATE
);
/DECLARETYPE emp_rec IS RECORD (emp_id ? ?NUMBER,emp_name ?VARCHAR2(100),hire_date DATE);TYPE emp_nt IS TABLE OF emp_rec;-- 使用顯式字段名初始化v_employees emp_nt := emp_nt();
BEGIN-- 先擴展空間v_employees.EXTEND(2);-- 使用字段名初始化v_employees(1).emp_id := 1;v_employees(1).emp_name := '張三';v_employees(1).hire_date := TO_DATE('2020-01-15', 'YYYY-MM-DD');v_employees(2).emp_id := 2;v_employees(2).emp_name := '李四';v_employees(2).hire_date := TO_DATE('2019-05-20', 'YYYY-MM-DD');-- 輸出邏輯...
END;
/

嵌套表初始化

顯示初始化

DECLARETYPE num_nt IS TABLE OF NUMBER;-- 方法1: 直接初始化v_nums1 num_nt := num_nt(10, 20, 30);-- 方法2: 先聲明后擴展v_nums2 num_nt := num_nt(); -- 空集合
BEGINv_nums2.EXTEND(3); -- 擴展3個元素v_nums2(1) := 100;v_nums2(2) := 200;v_nums2(3) := 300;
END;
/

BULK COLLECT INTO 初始化

DECLARETYPE emp_nt IS TABLE OF employees%ROWTYPE;v_emps emp_nt;
BEGIN-- 批量查詢填充SELECT * BULK COLLECT INTO v_emps?FROM employees?WHERE department_id = 10;-- 處理數據FOR i IN 1..v_emps.COUNT LOOPDBMS_OUTPUT.PUT_LINE(v_emps(i).last_name);END LOOP;
END;

函數返回值初始化

CREATE OR REPLACE FUNCTION get_department_emps(p_dept_id NUMBER
) RETURN emp_nt ISv_result emp_nt;
BEGINSELECT emp_obj(employee_id, last_name, hire_date)BULK COLLECT INTO v_resultFROM employeesWHERE department_id = p_dept_id;RETURN v_result;
END;
/-- 調用示例
DECLAREv_emps emp_nt;
BEGINv_emps := get_department_emps(60);-- 處理結果...
END;

out參數初始化

CREATE OR REPLACE PROCEDURE get_employees(p_dept_id IN NUMBER,p_emps OUT emp_nt
) IS
BEGIN-- 過程內部不需要初始化OUT參數SELECT emp_obj(employee_id, last_name, hire_date)BULK COLLECT INTO p_empsFROM employeesWHERE department_id = p_dept_id;
END;-- 調用時
DECLAREv_emps emp_nt; ?-- 調用前不需要初始化
BEGINget_employees(10, v_emps); ?-- 過程會處理初始化
END;

如果已經初始化 則覆蓋初始化內容