oracle集合三嵌套表(Nested Table)学习

嵌套表

嵌套表(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');
BEGIN
  null;
END;

-- 首先创建对象类型 /或者在PLSQL内使用record
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  hire_date DATE
);
/

DECLARE
  TYPE 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;
/

嵌套表初始化

显示初始化

DECLARE
  TYPE num_nt IS TABLE OF NUMBER;
  -- 方法1: 直接初始化
  v_nums1 num_nt := num_nt(10, 20, 30);
  
  -- 方法2: 先声明后扩展
  v_nums2 num_nt := num_nt(); -- 空集合
BEGIN
  v_nums2.EXTEND(3); -- 扩展3个元素
  v_nums2(1) := 100;
  v_nums2(2) := 200;
  v_nums2(3) := 300;
END;
/

BULK COLLECT INTO 初始化

DECLARE
  TYPE 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 LOOP
    DBMS_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 IS
  v_result emp_nt;
BEGIN
  SELECT emp_obj(employee_id, last_name, hire_date)
  BULK COLLECT INTO v_result
  FROM employees
  WHERE department_id = p_dept_id;
  
  RETURN v_result;
END;
/

-- 调用示例
DECLARE
  v_emps emp_nt;
BEGIN
  v_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_emps
  FROM employees
  WHERE department_id = p_dept_id;
END;

-- 调用时
DECLARE
  v_emps emp_nt;  -- 调用前不需要初始化
BEGIN
  get_employees(10, v_emps);  -- 过程会处理初始化
END;

如果已经初始化 则覆盖初始化内容