plsql 集合类型详解（varray、table）

1 概述

1. 集合：具有相同定义的元素的聚合，有以下三种
   (1) varray: 指定最大长度 -- varray(3) of varchar2(30);
   (2) 关联数组: index by -- table of varchar2(30) index by pls_integer;
   (3) 嵌套表: 引用表类型 -- table of cur_student_info%rowtype;

2. 数组说明
   (1) pl/sql 中没有数组（Array）的概念
   (2) 但可用 '集合' 替代

2 集合

2.1 varray 单行多列 限定长度

type <varray_name> is varray(size) of <date_type> [not null];

type string_array is varray(3) of varchar2(30);
-- string_array: 类型名
-- varray(3)   : 可变数组，设置 最大长度 3
-- varchar2(30): 数据类型 varchar2

示例：数组赋值并输出

declare
  type string_array is varray(3) of varchar2(30); -- 最大长度是 3
  v_string_array string_array;
begin
  -- 初始化数组
  v_string_array := string_array('a', 'b');

  -- 手动扩充空间（麻烦，很少用，替代方案在后面）
  v_string_array.extend;
  v_string_array(3) := 'c';

  -- 下标越界 4 > 3 = varray(3)
  -- v_string_array.extend;
  -- v_string_array(4) := 'd';

  for i in v_string_array.first .. v_string_array.last loop
    dbms_output.put_line(i || ': ' ||
                         v_string_array(i)); -- 下标从 1 开始
  end loop;
end;

输出结果：

1: a
2: b
3: c

2.2 关联数组 单行多列 index by

type <type_name> is table of date_type [not null]
index by [pls_integer | binary_integer];

index by : 创建一个主键索引，以便引用记录表变量中的特定行
           说白了，就是 '替换 集合.extend'

binary_integer: 由 Oracle 来执行，不会出现溢出，但是执行速度较慢
pls_integer: 由 CPU 来运算，因此会出现溢出，但其执行速度较快，数据范围：-2^31 ~ 2^31 - 1

使用原则：优先使用 'pls_integer'，除非批次处理业务量超过其 '数据范围'，才使用 'binary_integer'

• 扩展：plsql binary_integer、pls_integer、simple_integer 区别

declare
  type string_array is table of varchar2(30) index by pls_integer;
  v_string_array string_array;
begin
  -- 写法 1：
  -- v_string_array(1) := 'a';
  -- v_string_array(2) := 'b';
  -- v_string_array(3) := 'c';

  -- 写法 2：常用，因为咱没必要去考虑 "下标"
  v_string_array(v_string_array.count) := 'a'; --> 0
  v_string_array(v_string_array.count) := 'b'; --> 1
  v_string_array(v_string_array.count) := 'c'; --> 递归

  -- 效果同 varry，且不用再 '手动扩容'，体验感更佳
  for i in v_string_array.first .. v_string_array.last loop
    dbms_output.put_line(i || ': ' || 
                         v_string_array(i)); -- 下标从 0 开始
  end loop;
end;

输出结果：效果同 varray，但无需 手动扩展下标

0: a
1: b
2: c

2.3 嵌套表 多行多列

基础数据：

create table scott.student_info (
  sno   number(3),
  sname varchar2(30),
  sex   varchar2(2)
);

insert into scott.student_info(sno, sname, sex) values(1, '瑶瑶', '女');
insert into scott.student_info(sno, sname, sex) values(2, '优优', '男');
insert into scott.student_info(sno, sname, sex) values(3, '阿梦', '女');
commit;

示例：输出表 student_info 中所有 sno <=3 的记录

declare
  cursor cur_student_info is
    select si.sno,
           si.sname,
           si.sex
      from scott.student_info si
     where si.sno <= 3;

  -- 声明 table 类型，无需 追加 index by ...
  type student_info_table is table of cur_student_info%rowtype;
  v_student_info_table student_info_table;
begin
  open cur_student_info;

  fetch cur_student_info bulk collect
    into v_student_info_table;

  close cur_student_info;

  -- 输出结果
  for i in v_student_info_table.first .. v_student_info_table.last loop
    dbms_output.put_line('学号：' || v_student_info_table(i).sno || ', ' ||
                         '姓名：' || v_student_info_table(i).sname || ', ' ||
                         '性别：' || v_student_info_table(i).sex);
  end loop;

exception
  when others then
    -- 游标一定要关闭（占内存）
    if cur_student_info%isopen then
      close cur_student_info;
    end if;
    -- 报错信息（可要可不要，一般记录至 "错误日志表"）
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

输出结果：

学号：1, 姓名：瑶瑶, 性别：女
学号：2, 姓名：优优, 性别：男
学号：3, 姓名：阿梦, 性别：女

3 常见问题

3.1 delete 和 置 null 的区别

-- 结论
置 null: 清除数组元素，但 '不删除内存空间'
delete : 清除数组元素，并且 '删除内存空间'

示例：

declare
  type string_array is table of varchar2(30) index by pls_integer;
  v_string_array string_array;
begin
  -- 1.数组赋值
  v_string_array(v_string_array.count) := 'b1';
  v_string_array(v_string_array.count) := 'b2';
  v_string_array(v_string_array.count) := 'b3';

  -- 2.演示 "置 null"
  dbms_output.put_line('原数组长度：' || v_string_array.count);
  v_string_array(0) := null;
  dbms_output.put_line('置 null 后的长度：' || v_string_array.count);

  for i in v_string_array.first .. v_string_array.last loop
    dbms_output.put_line(i || ' : ' || v_string_array(i));
  end loop;

  dbms_output.new_line();

  -- 3.演示 "delete"
  v_string_array.delete(0);

  dbms_output.put_line('delete 后的长度：' || v_string_array.count);

  for j in v_string_array.first .. v_string_array.last loop
    dbms_output.put_line(j || ' : ' || v_string_array(j));
  end loop;
end;

输出结果：（null：值为空，但空间还在，delete 则都没了）

原数组长度：3
置 null 后的长度：3
0 : 
1 : b2
2 : b3

delete 后的长度：2
1 : b2
2 : b3

3.2 ORA-01403：未找到任何数据

1. 报错原因：数组遍历时，是按照下标顺序来的，若中间出现 '断层（该下标找不到数据）'，就会 '报错'
2. 解决办法：先判断（exists）

示例：

declare
  type string_array is table of varchar2(30) index by pls_integer;

  v_string_array string_array;
begin
  v_string_array(v_string_array.count) := 'b1'; -- 因为 数组为空，故而 v_string_array.count = 0
  v_string_array(v_string_array.count) := 'b2';
  v_string_array(v_string_array.count) := 'b3';

  v_string_array.delete(1); -- 删除 'b2'

  for i in v_string_array.first .. v_string_array.last loop
  
    -- 解决办法：增加 if exists 判断 
    -- if v_string_array.exists(i) then
        dbms_output.put_line(i || ': ' || v_string_array(i));
    -- end if;
  end loop;
end;

添加 if exists 之前：报错，之后：结果如下：

0: b1
2: b3

3.3 数组属性和函数

属性/函数	描述
count	返回集合中元素的个数
delete	删除集合中 所有 的元素及 extend
delete(x)	删除元素下标为 x 的元素（对 varry 非法）
delete(x, y)	删除元素下标从 x 到 y 的元素（对 varry 非法）
trim	从集合末端开始删除一个元素（对 index by 非法）
trim(x)	从集合末端开始删除 x 个元素 （对 index by 非法）
exists(x)	如果集合元素 x 已经 初始化(extend) ，则返回 true，否则返回 false
extend	在集合 末尾 添加一个元素（对 index by 非法）
extend(x)	在集合 末尾 添加 x个元素 （对 index by 非法）
extend(x, n)	在集合 末尾 添加元素 x 个下标为n 的 副本（对 index by 非法）
first	返回集合中第一个元素的下标号，对 varry 集合 始终 返回 1（除非 未初始化 则为 空）
last	返回集合中最后一个元素的下标号，对 varry 集合 值始终 等于 count （除非 未初始化 则为 空）
limit	返回 varry 集合的最大的元素个数，对 index by 无效
next(x)	返回在第 x 个元素之后紧挨着它的元素下标(x+1)，若 x 是最后一个元素，则返回 null
prior(x)	返回在第x个元素之前紧挨着它的 元素下标(x-1)，如果 x 是第一个元素，则返回 null