5.plsql集合数据类型

扩展内容: plsql集合类型详解（varray，table）
一.概述：
1.集合：具有相同定义的元素的聚合，有以下三种
 （1）varray：指定最大长度--VARRAY(3) OF VARCHAR2(30);
 （2）关联数组：index by --table of varchar2(30) index by pls_integer;
 （3）嵌套表：引用表类型--table of cur_studnet_info%rowtype;
2.数组说明：
 （1）pl/sql 中没有数组的概念
 （2）但是可以用集合代替
二.集合
2.1varray单行多列 限定长度
  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，报错
    --v_string_array.extend;
    --v_string_array(4):='e';
    for i in v_string_array.first..v_string_array.last loop
      dbms_output.put_line(i||':'||v_string_array(i));
    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'
 
 实列：
 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';--递归
   --效果同varray，且不用手动扩容‘下标’
   for i in v_string_array.first..v_string_array.last loop
     dbms_output.put_line(i||':'||v_string_array(i));
   end loop;
  end;
  
2.3  嵌套表 多列多行--通常和游标一起使用
 declare
  cursor cur_emp_info is select * from emp;
  --声明table类型，无需追加index by。。。
  type emp_info_table is table of emp%rowtype;--of后面接记录类型，可自己定义
  v_emp_info_table emp_info_table;
 begin
   open cur_emp_info;
   fetch cur_emp_info bulk collect into v_emp_info_table LIMIT &数值;--limit取的条数 
   --bulk collect 会一次性将结果集绑定到一个集合变量中，并从sql应勤发送到plsql引擎，大大减少了上下文切换
   close cur_emp_info;
   for i in v_emp_info_table.first..v_emp_info_table.last loop
     dbms_output.put_line(i||':'||v_emp_info_table(i).empno);
     dbms_output.put_line(i||':'||v_emp_info_table(i).ename);
   end loop;
  exception
  when others then
    -- 游标一定要关闭（占内存）
    if cur_emp_info%isopen then
      close cur_emp_info;
    end if;
    -- 报错信息（可要可不要，一般记录至 "错误日志表"）
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
 end;

三.常见问题
3.1 delete 和 置 null 的区别
-- 结论
置 null: 清除数组元素，但 '不删除内存空间'
delete : 清除数组元素，并且 '删除内存空间'
-- 2.演示 "置 null"
v_string_array(0) := null;
-- 3.演示 "delete"
v_string_array.delete(0);

3.2 ORA-01403：未找到任何数据--delete处理过
1. 报错原因：数组遍历时，是按照下标顺序来的，若中间出现 
   '断层（该下标找不到数据）'，就会 '报错'
2. 解决办法：先判断（exists）
-- 解决办法：增加 if exists 判断 
    if v_string_array.exists(i) then
      dbms_output.put_line(i || ': ' || v_string_array(i));
    end if;
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