源码-PL/SQL从入门到精通-第八章-记录与集合-Part 3_is table of定义的出来的表可以直接查询吗-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/51956309
索引表、嵌套表、可变数组、FORALL、BULK COLLECT...，这一章的新概念真够多的，不过，相比Java的集合类，其用法感觉不那么复杂。
--代码8.18 在数据表中使用嵌套表示例
DROP TYPE empname_type;
CREATE TYPE empname_type_02 IS TABLE OF VARCHAR2(20);
--1.创建嵌套表类型
CREATE TYPE empname_type IS TABLE OF VARCHAR2(20);
/
--2.创建数据表时指定嵌套表列，同时要使用STORE AS指定嵌套表的存储表
CREATE TABLE dept_nested(deptno NUMBER(2), --部门编号
                         dname VARCHAR2(20), --部门名称
                         emplist empname_type --部门员工列表
                         ) NESTED TABLE emplist STORE AS empname_table;

--代码8.19 操作嵌套表列数据
DECLARE
  emp_list empname_type := empname_type('史密斯',
                                        '杰克',
                                        '马丁',
                                        '斯大林',
                                        '布什',
                                        '小平');
BEGIN
  --可以在INSERT语句中传入一个嵌套表实例
  INSERT INTO dept_nested
  VALUES
    (10, '国务院', emp_list);
  --也可以直接在INSERT语句中实例化嵌套表
  INSERT INTO dept_nested
  VALUES
    (20, '财务司', empname_type('李林', '张杰', '马新', '蔡文'));
  --从数据库表中查询出嵌套表实例
  SELECT emplist
  INTO   emp_list
  FROM   dept_nested
  WHERE  deptno = 10;
  --对嵌套表进行更新，然后使用UPDATE语句将嵌套表实例更新回数据库
  emp_list(1) := '少校';
  emp_list(2) := '大校';
  emp_list(3) := '中校';
  emp_list(4) := '学校';
  emp_list(5) := '无效';
  emp_list(6) := '药效';
  --使用更改过的emp_list更新嵌套表列
  UPDATE dept_nested
  SET    emplist = emp_list
  WHERE  deptno = 10;
END;


SELECT *
FROM   dept_nested;

--代码8.20 变长数组初始化示例
DECLARE
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16); --定义项目列表变长数组
  TYPE empno_type IS VARRAY(10) OF NUMBER(4); --定义员工编号变长数组
  --声明变长数组类型的变量，并使用构造函数进行初始化
  project_list projectlist := projectlist('网站', 'ERP', 'CRM', 'CMS');
  empno_list   empno_type; --声明变长数组类型的变量
BEGIN
  DBMS_OUTPUT.put_line(project_list(3)); --输出第3个元素的值
  project_list.EXTEND; --扩展到第5个元素
  project_list(5) := 'WORKFLOW'; --为第5个元素赋值
  empno_list :=  --构造empno_list
   empno_type(7011,
                           7012,
                           7013,
                           7014,
                           NULL,
                           NULL,
                           NULL,
                           NULL,
                           NULL,
                           NULL);
  empno_list(9) := 8011; --为第9个元素赋初值
  DBMS_OUTPUT.put_line(empno_list(9)); --输出第9个元素的值
END;

--代码8.21 创建并使用变长数组类型
--创建一个变长数组的类型empname_varray_type，用来存储员工信息
CREATE OR REPLACE TYPE empname_varray_type IS VARRAY(20) OF VARCHAR2(20);
CREATE TABLE dept_varray --创建部门数据表
(deptno NUMBER(2), --部门编号    
 dname VARCHAR2(20), --部门名称
 emplist empname_varray_type --部门员工列表
 );

--代码8.22 操作变长数组列
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('张三', '刘七', '赵五', '阿四', '阿五', '阿六'));
  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;

--代码8.23 Exists方法使用示例
DECLARE
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16); --定义项目列表变长数组
  project_list projectlist := projectlist('网站', 'ERP', 'CRM', 'CMS');
BEGIN
  IF project_list.EXISTS(5) --判断一个不存在的元素值
  THEN
    --如果存在，则输出元素值
    DBMS_OUTPUT.put_line('元素存在，其值为：' || project_list(5));
  ELSE
    DBMS_OUTPUT.put_line('元素不存在'); --如果不存在，显示元素不存在    
  END IF;
END;


--代码8.24 Count方法使用示例
DECLARE
  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
  deptno_info := deptno_table(); --构造一个不包含任何元素的嵌套表
  deptno_info.EXTEND(5); --扩展5个元素
  DBMS_OUTPUT.PUT_LINE('deptno_info的元素个数为：' || deptno_info.COUNT);
  DBMS_OUTPUT.PUT_LINE('emp_name_info的元素个数为：' || emp_name_info.COUNT);
END;

--代码8.25 Limit方法使用示例
DECLARE
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16); --定义项目列表变长数组
  project_list projectlist := projectlist('网站', 'ERP', 'CRM', 'CMS');
BEGIN
  DBMS_OUTPUT.put_line('变长数组的上限值为：' || project_list.LIMIT);
  project_list.EXTEND(8);
  DBMS_OUTPUT.put_line('变长数组的当前个数为：' || project_list.COUNT);
END;

--代码8.26 First和Last方法
DECLARE
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16); --定义项目列表变长数组
  project_list projectlist := projectlist('网站', 'ERP', 'CRM', 'CMS');
BEGIN
  DBMS_OUTPUT.put_line('project_list的第1个元素下标：' || project_list.FIRST); --查看第1个元素的下标
  project_list.EXTEND(8); --扩展8个元素
  DBMS_OUTPUT.put_line('project_list的最后一个元素的下标：' || project_list.LAST); --查看最后1个元素的下标
END;

--代码8.27 Prior和Next示例
DECLARE
  TYPE idx_table IS TABLE OF VARCHAR(12) INDEX BY PLS_INTEGER; --定义索引表类型
  v_emp idx_table; --定义索引表变量
  i     PLS_INTEGER; --定义循环控制变量
BEGIN
  v_emp(1) := '史密斯'; --随机的为索引表赋值
  v_emp(20) := '克拉克';
  v_emp(40) := '史瑞克';
  v_emp(-10) := '杰瑞';
  --获取集合中第-10个元素的下一个值
  DBMS_OUTPUT.put_line('第-10个元素的下一个值：' || v_emp(v_emp.NEXT(-10)));
  --获取集合中第40个元素的上一个值
  DBMS_OUTPUT.put_line('第40个元素的上一个值：' || v_emp(v_emp.PRIOR(40)));
  i := v_emp.FIRST; --定位到第1个元素的下标
  WHILE i IS NOT NULL --开始循环直到下标为NULL
  LOOP
    --输出元素的值
    DBMS_OUTPUT.put_line('v_emp(' || i || ')=' || v_emp(i));
    i := v_emp.NEXT(i); --向下移动循环指针，指向下一个下标
  END LOOP;
END;

--代码8.28 Extend使用示例
DECLARE
  TYPE courselist IS TABLE OF VARCHAR2(10); --定义嵌套表
  --定义课程嵌套表变量
  courses courselist;
  i       PLS_INTEGER;
BEGIN
  courses := courselist('生物', '物理', '化学'); --初始化元素
  courses.DELETE(3); --删除第3个元素
  courses.EXTEND; --追加一个新的NULL元素
  courses(4) := '英语';
  courses.EXTEND(5, 1); --把第1个元素拷贝5份添加到末尾  
  i := courses.FIRST;
  WHILE i IS NOT NULL
  LOOP
    --循环显示结果值
    DBMS_OUTPUT.PUT_LINE('courses(' || i || ')=' || courses(i));
    i := courses.NEXT(i);
  END LOOP;
END;

--代码8.29 Trim使用示例
DECLARE
  TYPE courselist IS TABLE OF VARCHAR2(10); --定义嵌套表
  --定义课程嵌套表变量
  courses courselist;
  i       PLS_INTEGER;
BEGIN
  courses := courselist('生物', '物理', '化学', '音乐', '数学', '地理'); --初始化元素
  courses.TRIM(2); --删除集合末尾的2个元素
  DBMS_OUTPUT.PUT_LINE('当前的元素个数：' || courses.COUNT); --显示元素个数
  courses.EXTEND; --扩展1个元素   
  courses(courses.COUNT) := '语文'; --为最后1个元素赋值
  courses.TRIM; --删除集合末尾的最后1个元素 
  i := courses.FIRST;
  WHILE i IS NOT NULL
  LOOP
    --循环显示结果值
    DBMS_OUTPUT.PUT_LINE('courses(' || i || ')=' || courses(i));
    i := courses.NEXT(i);
  END LOOP;
END;

--代码8.30 Delete使用示例
DECLARE
  TYPE courselist IS TABLE OF VARCHAR2(10); --定义嵌套表
  --定义课程嵌套表变量
  courses courselist;
  i       PLS_INTEGER;
BEGIN
  courses := courselist('生物', '物理', '化学', '音乐', '数学', '地理'); --初始化元素
  courses.DELETE(2); --删除第2个元素
  DBMS_OUTPUT.PUT_LINE('当前的元素个数：' || courses.COUNT); --显示元素个数
  courses.EXTEND; --扩展1个元素  
  DBMS_OUTPUT.PUT_LINE('当前的元素个数：' || courses.COUNT); --显示元素个数    
  courses(courses.LAST) := '语文'; --为最后1个元素赋值
  courses.DELETE(4, courses.COUNT); --删除集合末尾的最后1个元素 
  i := courses.FIRST;
  WHILE i IS NOT NULL
  LOOP
    --循环显示结果值
    DBMS_OUTPUT.PUT_LINE('courses(' || i || ')=' || courses(i));
    i := courses.NEXT(i);
  END LOOP;
END;


--代码8.31 集合异常示例
DECLARE
  TYPE numlist IS TABLE OF NUMBER;
  nums numlist; --一个空的嵌套表
BEGIN
  nums(1) := 1; --未构造就使用表元素，将触发：ORA-06531:引用未初始化的收集
  nums := numlist(1, 2); --初始化嵌套表
  nums(NULL) := 3; --使用NULL索引键，将触发：ORA-06502:PL/SQL:数字或值错误:NULL索引表键值
  nums(0) := 3; --访问不存在的下标，将触发：ORA-06532:下标超出限制
  nums(3) := 3; --下标超过最大元素个数，将触发：ORA-06532:下标超出限制
  nums.DELETE(1); --删除第1个元素
  IF nums(1) = 1
  THEN
    NULL;
    --因为第1个元素已被删除，再访问将触发：ORA-01403: 未找到任何数据
  END IF;
END;

--代码8.32 Forall语句示例
DECLARE
  TYPE dept_type IS VARRAY(20) OF NUMBER; --定义嵌套表变量  
  depts dept_type := dept_type(10, 30, 70); --实例化嵌套表，分配3个元素
BEGIN
  FOR i IN depts.FIRST .. depts.LAST --循环嵌套表元素 
  LOOP
    DELETE FROM emp
    WHERE  deptno = depts(i); --向SQL引擎发送SQL命令执行SQL操作
  END LOOP;
END;

select * from emp;

DECLARE
  TYPE dept_type IS VARRAY(20) OF NUMBER; --定义嵌套表变量
  depts dept_type := dept_type(10, 30, 70); --实例化嵌套表，分配3个元素   
BEGIN
  FORALL i IN depts.FIRST .. depts.LAST --循环嵌套表元素
    DELETE FROM emp
    WHERE  deptno = depts(i); --向SQL引擎发送SQL命令执行SQL操作
  FOR i IN 1 .. depts.COUNT
  LOOP
    DBMS_OUTPUT.put_line('部门编号' || depts(i) || '的删除操作受影响的行为：' ||
                         SQL%BULK_ROWCOUNT(i));
  END LOOP;
END;


--代码8.33 Bulk collect语句示例
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; --从emp表中查出员工编号和名称，批量插入到集合
  FOR i IN 1 .. nums.COUNT --循环显示集合内容
  LOOP
    DBMS_OUTPUT.put('num(' || i || ')=' || nums(i) || '   ');
    DBMS_OUTPUT.put_line('names(' || i || ')=' || names(i));
  END LOOP;
END;