oracle 集合二 可变数组（VARRAY）学习

数据结构  数组

与传统数组相比，oracle 的可变数组指的是逻辑可变，物理不变
传统数组初始化时需要指定长度，即使脏内存未被清理，此时该内存也已经可以使用，也就是说初始化即分配内存
可变数组初始化也需要指定长度，但并不会分配所有内存，而是先预分配部分，后边自动扩容，直到达到定义大小上限
传统数组内存一定是连续的，可变数组也是如此，因可变数组并不分配所有内存，如果发生在连续的内存不足以满足扩容请求则发生数组复制，即寻找一块可以容纳的连续空间进行复制

能在PL/SQL外定义使用，也可在PL/SQL内定义使用，也可在PL/SQL内使用外部定义的数组

语法

PLSQL外

CREATE [ OR REPLACE ] TYPE type_name is VARRAY(max_size) OF element_datatype [ NOT NULL ];

PLSQL中

DECLARE
  TYPE Node IS VARRAY(8) OF NUMBER;
  v_my_nodes Node := Node();
BEGIN
  v_my_nodes.EXTEND(3);
  v_my_nodes(1) := 10;
  v_my_nodes(2) := 20;
  v_my_nodes(3) := 30;
  
  FOR i IN 1..v_my_nodes.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('节点 ' || i || ': ' || v_my_nodes(i));
  END LOOP;
END;
/

不支持的类型

LONG, LONG RAW (已过时)
CLOB, NCLOB (在VARRAY中不支持)
某些特定于PL/SQL的类型(如PLS_INTEGER)在SQL范围不可用

两种定义方式的区别

特性                    SQL级别定义 (CREATE TYPE)        PL/SQL内部定义
作用域                  整个数据库                        仅当前PL/SQL块
是否可持久化             是                                否
是否可用于表列定义       是                                否
是否可被其他程序引用     是                                否
是否需要权限            需要CREATE TYPE权限                不需要

基本使用

在 PLSQL外
 

-- 1. 创建VARRAY类型
CREATE OR REPLACE TYPE address_type AS OBJECT (
    street VARCHAR2(100),
    city VARCHAR2(50),
    zip_code VARCHAR2(10)
);
/
CREATE OR REPLACE TYPE address_array AS VARRAY(2) OF address_type;
/

-- 2. 创建包含VARRAY列的表
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    addresses address_array
);

-- 3. 插入数据
INSERT INTO employees VALUES (
    101,
    '王五',
    address_array(
        address_type('中山路100号', '北京市', '100001'),
        address_type('科技园路200号', '深圳市', '518000')
    )
);

-- 4. 查询数据
SELECT e.emp_name, a.street, a.city, a.zip_code
FROM employees e, TABLE(e.addresses) a
WHERE e.emp_id = 101;

在PLSQL内

DECLARE
  TYPE Node IS VARRAY(8) OF NUMBER;
  v_my_nodes Node := Node();
BEGIN
  v_my_nodes.EXTEND(3);
  v_my_nodes(1) := 10;
  v_my_nodes(2) := 20;
  v_my_nodes(3) := 30;
  
  FOR i IN 1..v_my_nodes.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('节点 ' || i || ': ' || v_my_nodes(i));
  END LOOP;
END;
/

在PLSQL外定义，在PLSQL内使用

CREATE OR REPLACE TYPE phone_list_type AS VARRAY(3) OF VARCHAR2(20);
/
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    phones phone_list_type
);

DECLARE
    v_phones phone_list_type := phone_list_type();
BEGIN
    -- 扩展并添加更多电话号码
    v_phones.EXTEND;
    v_phones(1) := '15100151000';
    DBMS_OUTPUT.PUT_LINE(v_phones(1));
    -- 插入数据
    INSERT INTO customers VALUES (
        2,
        '李四',
        v_phones
    );
    COMMIT;
END;
/

查询数据

SELECT c.customer_id, c.name, p.COLUMN_VALUE AS phone_number FROM customers c, TABLE(c.phones) p;

核心时间复杂度

操作					时间复杂度			说明
随机访问(v_array(i))	    O(1)				通过索引直接访问
尾部追加(EXTEND+赋值)	O(1)				当未达到LIMIT时
尾部删除(TRIM)			O(1)				从末尾移除元素
非尾部插入/删除			O(n)				需要移动后续元素
查找特定元素			    O(n)				需要线性扫描

是否需要初始化            需要
是否空间预分配            需要

存储影响

行内存储（Inline Storage）：

当 VARRAY 数据较小（通常 < 4KB）时，与表数据一起存储

访问速度：快（O(1) 随机访问）

更新代价：高（整个 VARRAY 被视为一个单元）

行外存储（Out-of-line Storage）：

较大 VARRAY 存储在单独的 LOB 中

访问速度：较慢（需要额外 I/O）

部分更新：可能更高效（Oracle 11g+支持部分更新）

集合通用方法

方法/函数			语法示例				描述					返回值类型
COUNT				varray_name.COUNT		返回当前元素数量		    NUMBER
LIMIT				varray_name.LIMIT		返回VARRAY的最大容量 	NUMBER
FIRST				varray_name.FIRST		返回第一个元素的索引	    NUMBER
LAST				varray_name.LAST		返回最后一个元素的索引	NUMBER
PRIOR				varray_name.PRIOR(n)	返回索引n的前一个索引  	NUMBER
NEXT				varray_name.NEXT(n)		返回索引n的后一个索引 	NUMBER
EXISTS				varray_name.EXISTS(n)	检查索引n是否存在		BOOLEAN

修改操作函数

方法/函数			语法示例				描述					备注
EXTEND				varray_name.EXTEND		添加1个null元素	
					varray_name.EXTEND(n)	添加n个null元素	
					varray_name.EXTEND(n,i)	添加n个元素，复制第i个元素的值	
TRIM				varray_name.TRIM		移除最后1个元素	
					varray_name.TRIM(n)		移除最后n个元素	
DELETE				varray_name.DELETE	    删除所有元素				11g+
					varray_name.DELETE(n)	删除第n个元素			11g+
					varray_name.DELETE(m,n)	删除m到n的元素			11g+

构造函数

语法				示例							描述
type_name()			phone_list_type()				创建空VARRAY
type_name(e1,e2...)	phone_list_type('123','456')	创建并初始化