logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

Oracle PL/SQL开发基础（第十八弹：使用集合方法）

 张小龙net馆藏 2017-10-17

在PL/SQL提供的集合方法中，EXISTS，COUNT，LIMIT，FIRST，LAST，PRIOR，NEXT是函数；EXTEND，TRIM，DELETE是过程。EXISTS，PRIOR，NEXT，TRIM，EXTEND，DELETE对应的参数是集合的下标索引。

集合方法只能在PL/SQL中使用，不能在SQL语句中使用。

只有EXISTS能用于空集合，如果在空集合上调用其他方法，PL/SQL就会抛出异常COLLECTION_IS_NULL。

EXISTS

EXISTS方法用于判断集合中指定的元素是否存在。存在则返回True，否则返回False。
使用这个方法主要用于在访问一个未分配值的下标元素时，避免Oracle弹出NO DATA FOUND这样的错误。
当传递的下标越界时，EXISTS会返回False，而不会引发下标超出界限的异常。

使用如：

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

COUNT

COUNT方法能返回集合中包含的元素个数，该函数在判断集合的当前元素个数时非常有用，因为集合的当前大小不总是能够确定，特别是对于嵌套表和索引表来说。

对于变长数组来说，COUNT值和LAST方法值恒等，但对于嵌套表来说，正常情况下COUNT值会和LAST值相等。但是，当我们从嵌套表中间删除一个元素时，COUNT值就会比LAST值小。
在计算元素的个数时，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); --5
   DBMS_OUTPUT.PUT_LINE('emp_name_info的元素个数为：'||emp_name_info.COUNT); --2   
END; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

LIMIT

LIMIT方法用于返回集合元素的最大个数，对于变长数组来说，因为其元素个数固定，可以返回变长数组所允许的最大元素个数。而对于嵌套表和索引表来说，由于其元素个数没有限制，所以调用该方法将总是返回NULL。

使用如：

DECLARE
   TYPE projectlist IS VARRAY (50) OF VARCHAR2 (16);   --定义项目列表变长数组
   project_list   projectlist := projectlist ('网站', 'ERP', 'CRM', 'CMS');
BEGIN
   DBMS_OUTPUT.put_line ('变长数组的上限值为：' || project_list.LIMIT); -- 50
   project_list.EXTEND(8);
   DBMS_OUTPUT.put_line ('变长数组的当前个数为：' || project_list.COUNT); -- 12
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

FIRST和LAST方法

FIRST和LAST分别返回集合中第一个和最后一个元素的索引数字，而不是该元素的值。如果集合为空，则FIRST和LAST将返回NULL。
使用如：

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个元素的下标，输出1
   project_list.EXTEND (8);                            --扩展8个元素
   DBMS_OUTPUT.put_line (   'project_list的最后一个元素的下标：'
                         || project_list.LAST
                        );                             --查看最后1个元素的下标，输出12
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

PRIOR和NEXT方法

PRIOR会返回集合中特定索引值参数的元素的前一个索引值，NEXT会返回集合中的特定索引值参数所指向的元素的下一个索引值。如果特定的元素没有前一个或后一个值，那么PRIOR或NEXT就会返回NULL值。

PRIOR和NEXT通常用来使用循环遍历所有的元素值，这种遍历方法比通过固定的下标索引更加可靠，因为在循环过程中，有些元素可能被插入或删除。特别是索引表，因为它的下标索引可能是不连续的。
使用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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

EXTEND方法

EXTEND可以为嵌套表和变长数组扩展元素，不能用于索引表，主要有三种使用形式：
- EXTEND：在集合末端添加一个空元素。
- EXTEND(n)：在集合末端添加n个空元素。
- EXTEND(n,i)：把第i个元素复制n份，添加到集合的末端。

如果一个集合未使用构造语法进行初始化，是不能使用EXTEND进行扩展的。如果嵌套表或变长数组添加了NOT NULL约束，也不能使用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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

TRIM方法

TRIM方法用来从嵌套表或变长数组的尾端删除元素：
- TRIM：从集合末端删除一个元素。
- TRIM(n)：从集合末端删除n个元素。

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

DELETE方法

DELETE用于从索引表和嵌套表中删除一个或多个元素：
- DELETE：删除集合中所有元素。
- DELETE(n)：从以数字做主键的所有表或者嵌套表中删除第n个元素。如果索引表有一个字符串键，对应该键值得元素就会被珊瑚。如果n为空，DELETE(n)不会做任何事情。
- DELETE(m,n)：从索引表或嵌套表中把索引范围m到n的所有元素删除。如果m大于n或m和n中有一个为空，那么DELETE(m,n)就不做任何事情。

由于变长数组的元素个数固定，因此在变长数组上使用DELETE是非法的。

在内部，如果DELETE发现被删除的元素不存在，将只是简单的忽略它，并不会抛出异常，PL/SQL会为被删除的元素保留一个占位符（也就是说标记删除），以便可以重新为被删除的元素赋值。

但是COUNT方法会忽略掉已标记为删除的元素，可以通过FIRST、LAST、NEXT、PRIOR来获取被删除元素的详细信息，如：

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);                               --删除集合第4个到最后一个元素 
   i:=courses.FIRST; 
   WHILE i IS NOT NULL LOOP                                        --循环显示结果值
      DBMS_OUTPUT.PUT_LINE('courses('||i||')='||courses(i));
      i:=courses.NEXT(i);
   END LOOP;
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： 张小龙net馆藏 > 《Oracle》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多