11_Oracle集合(复合)数据类型使用详解

关于数据类型的分类，请参考博文《04_开发基础》

本文对复合数据类型(记录类型,集合类型)的详细用法进行归档：

-------------------操纵单行记录，单行多列-------------------------------

--使用用记录类型
Declare
  TYPE dept_rec IS Record( --定义记录类型
    deptno number(2),
    dname  varchar2(14),
    loc    varchar2(13));
  dept_norow dept_rec;     --根据定义，声明自定义的记录类型
  dept_row   dept%rowtype; --根据表自定义的记录类型
Begin
  --赋值
  dept_norow.deptno := 99;
  dept_norow.dname  := '测试部门99';
  dept_norow.loc    := '测试部门99';
  --增加
  INSERT INTO dept VALUES dept_norow; --字段个数，顺序，类型都要一致
  --更新
  update dept set ROW = dept_norow WHERE DEPTNO = 99;
  --查询 For..Loop..
  For dept_norow in (select * from dept where deptno = 10) Loop
    --业务操作
    null;
  End loop;
  --
  commit;
END;

-------------操纵多行记录-------------------------------------
--1. 使用索引表类型,多行多列
DECLARE
  --定义 TYPE type_name IS TABLE OF element_data_type [NOT NULL] 
  --     INDEX BY [PLS_INITGER|BINARY_INITGER|VARCHAR2(SIZE)]
  --常见定义方式参考
  TYPE idx_birthday IS TABLE OF DATE INDEX BY PLS_INTEGER;
  TYPE idx_deptno IS TABLE OF dept.deptno%TYPE NOT NULL INDEX BY PLS_INTEGER;
  TYPE idx_dept_table IS TABLE OF dept%ROWTYPE INDEX BY dept.dname%TYPE;
    v_dept idx_dept_table;
  CURSOR dept_cur IS SELECT * FROM dept;
BEGIN
  FOR v_cur IN dept_cur LOOP
    v_dept(v_cur.dname) := v_cur;
    DBMS_OUTPUT.put_line(v_dept(v_cur.dname).loc);
  END LOOP;
END;

--2. 使用嵌套表类型,多行多列
Declare
  --定义 TYPE type_tab_name IS TABLE OF element_type [NOT NULL];
  TYPE emp_name_table IS TABLE OF VARCHAR2(20); --员工名字嵌套表
  TYPE deptno_table IS TABLE OF NUMBER(2); --部门编号嵌套表
  deptno_info   deptno_table;
  emp_name_info emp_name_table := emp_name_table('张三', '李四'); --使用前必须初始化
Begin
  --访问嵌套表元素,记得有下标
  DBMS_OUTPUT.put_line('员工1' || emp_name_info(1));
  DBMS_OUTPUT.put_line('员工2' || emp_name_info(2));
  --通过IS NULL判断是否被初始化
  IF deptno_info IS NULL THEN
    deptno_info := deptno_table();
  END IF;
  --扩展deptno_info元素
  deptno_info.EXTEND(5);
  FOR i IN 1..5 LOOP  --下标从1开始
    deptno_info(i) := i + 10;
  END LOOP;
  --显示个数
  DBMS_OUTPUT.put_line('部门数：' || deptno_info.COUNT);
END;

--3. 使用变长数组类型,多行多列
DECLARE
  --定义 TYPE type_name IS {VARRAY |VARYING ARRAY} (size_limit) 
  --     OF element_type [NOT NULL]; 
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16); --定义变长数组
  TYPE empno_type IS VARRAY(10) OF NUMBER(4);     --定义变长数组
  project_list projectlist:=projectlist('张三','李四','王五','赵六');--使用前必须初始化
  empno_list empno_type;
BEGIN
  DBMS_OUTPUT.put_line(project_list(1)); --输出第1个元素值
  project_list.EXTEND;--扩展至第五个元素
  project_list(5) := '孙七';
  empno_list := empno_type(100,200,300,400,NULL,NULL,NULL);--使用前必须初始化
  empno_list(5) := 500;--为第五个元素赋值
  DBMS_OUTPUT.put_line(empno_list(5));
  --嵌套表和变长数组常用方法
  empno_list.EXTEND(10); --扩展集合长度至10个
  IF empno_list.EXISTS(10)THEN --查找第10个元素是否存在 TRUE/FALSE
    NULL;
  ELSE
    DBMS_OUTPUT.put_line('第10个元素是不存在');
  END IF;
  DBMS_OUTPUT.put_line(empno_list.COUNT);      --查找元素个数
  DBMS_OUTPUT.put_line(empno_list.LIMIT);      --查找元素最大上限数
  DBMS_OUTPUT.put_line(empno_list.FIRST);      --第一个元素下标
  DBMS_OUTPUT.put_line(empno_list.LAST);       --最后一个元素下标
  DBMS_OUTPUT.put_line(empno_list.PRIOR(40));  --第40个元素上一个值
  DBMS_OUTPUT.put_line(empno_list.NEXT(40));  --第40个元素下一个值
  empno_list.TRIM;   --TRIM从集合末端删除一个元素，Trim(n)从集合末端删除n个元素

  empno_list.DELETE; --DELETE删除所有元素,DELETE(n)删除第n个元素,DELETE(m,n)删除从m至n的元素

END;

-----------------集合类型可以用批量方法处理-------------------------
--批量处理好处：编写PL/SQL代码时，PL/SQL引擎会与SQL引擎交互，频繁的交互会大大降低效率
--批量处理方法1 FORALL
DECLARE
  CURSOR cur IS
    SELECT * FROM dept;
  TYPE rec IS TABLE OF dept%ROWTYPE;
  TYPE rec_array IS array(100) OF dept%ROWTYPE;
  recs rec;
BEGIN
  OPEN cur;
  WHILE (TRUE) LOOP
    FETCH cur BULK COLLECT
      INTO recs LIMIT 100;
    FORALL i IN 1 .. recs.COUNT  --FORALL不是循环语句，不用loop..end loop
      INSERT INTO t VALUES recs (i);
    COMMIT;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

--批量处理方法2 BULK COLLECT
--它可以在SELECT-INTO ,FETCH-INTO,RETURNING-INTO中使用
DECLARE
  TYPE numtab IS TABLE OF emp.empno%TYPE;
  TYPE nametab IS TABLE OF emp.ename%TYPE;
  nums  numtab;
  names nametab;
BEGIN
  SELECT empno, ename BULK COLLECT INTO nums, names FROM emp;
  FOR i IN 1..nums.COUNT LOOP
    DBMS_OUTPUT.put_line(nums(i)||'-'||names(i));
  END LOOP;
END;
--完