源码-Oracle数据库管理-第十四章-记录与集合-Part 4（使用集合类型）

心无旁骛时，学习效率是最高的；

心猿意马时，肯定是学而不知所云。

--14.2.6 数据库中的嵌套表
--代码14.22 创建嵌套表
--创建一个嵌套表类型，用来存储员工的姓名
CREATE OR REPLACE TYPE tbl_emp_name AS TABLE OF VARCHAR2(20);
--创建一个使用嵌套列类型的列的表，用来保存部门和员工名称信息。
CREATE TABLE dept_and_emp(
   deptno NUMBER(2) primary key,   --部门编号
   dname VARCHAR2(14),               --部门名称
   loc VARCHAR2(13),                --部门地址
   emps   tbl_emp_name               --员工列表
)
NESTED TABLE emps STORE AS emps_nt;--嵌套表存储位置


--代码14.23 对包含嵌套表列的表执行DML语句
DECLARE
   --定义嵌套表变量，使用构造函数初始化了6个元素
   emp_list   tbl_emp_name
         := tbl_emp_name ('史密斯', '杰克', '马丁', '斯大林', '布什', '小平');
BEGIN
   --可以在INSERT语句中传入一个嵌套表实例
   INSERT INTO dept_and_emp
        VALUES (10,'行政部', '北京', emp_list);
   --也可以直接在INSERT语句中实例化嵌套表
   INSERT INTO dept_and_emp
        VALUES (20, '财务司','上海', tbl_emp_name ('李林', '张杰', '马新', '蔡文'));
   --对嵌套表进行更新，然后使用UPDATE语句将嵌套表实例更新回数据库
   emp_list(1) := '张三';
   emp_list(2) := '李四';
   emp_list(3) := '王五';
   emp_list(4) := '赵六';
   emp_list(5) := '丁七';
   emp_list(6) := '刘八';
   --使用更改过的emp_list更新嵌套表列
   UPDATE dept_and_emp
      SET emps = emp_list
    WHERE deptno = 10;
   --从数据库表中查询出嵌套表实例
   SELECT emps INTO emp_list FROM dept_and_emp WHERE deptno=10;      
   FOR v_index IN 1..emp_list.COUNT LOOP
      DBMS_OUTPUT.put_line(emp_list(v_index));
   END LOOP;      
   DBMS_OUTPUT.put_line('演示如何从其他表中插入嵌套表列的值:');
   --清除表中所有的数据
   DELETE FROM dept_and_emp;
   --使用INSERT SELECT语句，插入dept表中所有的记录，使用CAST和MULTISET将emp表中的ename作为嵌套表列的元素
   INSERT INTO dept_and_emp
     SELECT dept.*,
            CAST(MULTISET
                 (SELECT ename FROM emp WHERE emp.deptno = dept.deptno) AS
                 tbl_emp_name)
       FROM dept;
   --查询部门编号为20的记录
   SELECT emps INTO emp_list FROM dept_and_emp WHERE deptno =10;
   --输出部门编号为20的嵌套表的元素值
   FOR v_index IN 1 .. emp_list.COUNT LOOP
     DBMS_OUTPUT.put_line(emp_list(v_index));
   END LOOP;
END;



 SELECT d.deptno, d.dname, emp.*
   FROM dept_and_emp d, TABLE(d.emps) emp
  WHERE d.deptno = 10;

--14.2.7 定义变长数组
--代码14.24 定义并使用变长数组
DECLARE
  --定义一个变长数组，它最大保存10个元素，元素为VARCHAR2类型
  TYPE t_ename_list IS VARRAY(10) OF VARCHAR2(20);  
  --定义一个变长数组变量，并且使用构造函数对其进行初始化
  lst_ename t_ename_list:=t_ename_list('张三','李四','王五');
BEGIN
  --循环显示变长数组中的元素值
  FOR v_index IN 1..lst_ename.COUNT LOOP
     DBMS_OUTPUT.put_line(lst_ename(v_index));
  END LOOP;
END;


--14.2.8 操作变长数组
--代码14.25 操作变长数组
DECLARE  
       --定义变长数组类型
       TYPE t_dept_name IS VARRAY(10) OF VARCHAR2(20);  
       TYPE t_dept_no IS VARRAY(8) OF NUMBER;         
       --声明变长数组类型的变量
       varray_deptname_tab   t_dept_name := t_dept_name('行政部','管理部');  
       varray_deptno_tab   t_dept_no;         
    BEGIN  
       --检查变长数组是否被初始化，否则调用构造函数进行初始化
       IF varray_deptno_tab IS NULL THEN
          varray_deptno_tab:=t_dept_no(10,20,30,NULL,NULL,NULL);
       END IF;
       --对varray_deptname_tab进行扩展和收缩
       varray_deptname_tab.EXTEND(5);        --在原有的5个元素的基础上扩充2个元素，现在具有7个元素
       DBMS_OUTPUT.put_line('当前varray_deptname_tab个数:'||varray_deptname_tab.COUNT);
       varray_deptname_tab.TRIM;             --删除变长数组中的最后一个元素，现在有6个元素
       DBMS_OUTPUT.put_line('当前varray_deptname_tab个数:'||varray_deptname_tab.COUNT);       
       --varray_deptname_tab(7):='社会发展部';--抛出ORA-06533异常，下标越界 
       varray_deptname_tab.EXTEND;           --扩展一个元素
       varray_deptname_tab(7):='社会发展部'; --再次赋值，现在正常赋值
       DBMS_OUTPUT.put_line(varray_deptname_tab(7)); 
       --这行代码超过了变长数组最大长度，Oracle抛出ORA-06533异常        
       --varray_deptno_tab.EXTEND(5);         
END;  



--14.2.9 数据库中的变长数组
--代码14.26 创建并使用变长数组类型
--创建一个变长数组的类型empname_varray_type，用来存储员工信息

CREATE OR REPLACE TYPE force empname_varray_type IS VARRAY (20) OF VARCHAR2 (20);

CREATE TABLE dept_varray                  --创建部门数据表
(
   deptno NUMBER(2),                      --部门编号    
   dname VARCHAR2(20),                   --部门名称/--部门员工列表
   emplist empname_varray_type             
);

drop table dept_varray;


--代码14.27 操作数据库中的变长数组
DECLARE                                        --声明并初始化变长数组
   emp_list   empname_varray_type                          
                := empname_varray_type ('史密斯', '杰克', '汤姆', '丽沙', '简', '史太龙');
BEGIN
   INSERT INTO dept_varray
        VALUES (20, '维修组', emp_list);            --向表中插入变长数组数据
   INSERT INTO dept_varray                      --直接在INSERT语句中初始化变长数组数据
        VALUES (30, '机加工',
                empname_varray_type ('张3', '刘七', '赵五', '阿4', '阿五', '阿六'));
   SELECT emplist
     INTO emp_list
     FROM dept_varray
    WHERE deptno = 20;                          --使用SELECT语句从表中取出变长数组数据
   emp_list (1) := '杰克张';                          --更新变长数组数据的内容
   UPDATE dept_varray
      SET emplist = emp_list
    WHERE deptno = 20;                          --使用UPDATE语句更新变长数组数据
   DELETE FROM dept_varray
         WHERE deptno = 30;                     --删除记录并同时删除变长数组数据
END;


select * from dept_varray;