源码-PL/SQL从入门到精通-第八章-记录与集合-Part 3

来源：互联网发布：五级网络啥时候上市编辑：程序博客网时间：2024/06/10 16:59
索引表、嵌套表、可变数组、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;
0 0