Oracle12C--集合(三十)

知识点的梳理：

• 集合数据类型可以像一张数据表一样，向里面保存多行数据；
• 记录类型使用IS RECORD定义，可以由用户自己定义内部的组成；
• 索引表类似于程序语言中的数组，可以直接通过下标进行指定行数据的访问；
• 可变数据可以保存复杂的数据，它可以使用顺序索引进行数据的访问；
• 使用FORALL语句可以将多条要执行的SQL一起绑定执行；
• 通过BULK COLLECT语句可以批量接收数据； 
• 集合运算符
  • 集合运算符只与嵌套表和可变数组一起使用；
    • 
  • 示例1：验证CARDINALITY运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; BEGIN DBMS_OUTPUT.put_line('集合长度：' || CARDINALITY(v_all)) ; END ; /

运行结果： 集合长度：7

• 示例2：验证CARDINALITY运算符，使用SET运算符取消重复数据

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; BEGIN DBMS_OUTPUT.put_line('集合长度：' || CARDINALITY(SET(v_all))) ; END ; /

运行结果： 集合长度：5 分析： 利用Set运算符将集合中的重复数据取消后再计算集合长度；

• 示例3：验证EMPTY运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested () ; BEGIN IF v_allA IS NOT EMPTY THEN DBMS_OUTPUT.put_line('v_allA不是一个空集合！') ; END IF ; IF v_allB IS EMPTY THEN DBMS_OUTPUT.put_line('v_allB是一个空集合！') ; END IF ; END ; /

运行结果： v_allA不是一个空集合！ v_allB是一个空集合！

• 示例4：使用MEMBER OF运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all                list_nested := list_nested ('mldn','beijing','lixinghua') ; v_str                VARCHAR2(10) := 'mldn' ; BEGIN IF v_str MEMBER OF v_all THEN DBMS_OUTPUT.put_line('mldn字符串存在。') ; END IF ; END ; /

运行结果： mldn字符串存在。

• 示例5：验证MULTISET EXCEPT运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('bing','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET EXCEPT v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; /

运行结果： bing 分析： 本SQL使用MULTISET EXCEPT运算符，以第一个集合为参考， 将第二个集合中与第一个集合相同的部分删除，最终新的集合里只有一个bing的字符串

• 示例6：验证MULTISET INTERSECT运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET INTERSECT v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; /

运行结果： beijing java

• 示例7：验证MULTISET UNION运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET UNION v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; /

运行结果： mldn beijing java beijing java

• 示例8：验证SET运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; BEGIN IF v_allA IS A SET THEN DBMS_OUTPUT.put_line('v_allA是一个集合。') ; END IF ; END ; /

运行结果： v_allA是一个集合。

• 示例9：验证SUBMULTISET运算符

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('mldn','java') ; BEGIN IF v_allB SUBMULTISET v_allA THEN DBMS_OUTPUT.put_line('v_allB是v_allA的一个子集合。') ; END IF ; END ; /

运行结果： v_allB是v_allA的一个子集合。

• 集合函数
  • 常见的集合函数

    

    

  • 示例1：使用COUNT函数取得集合中的元素个数

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN DBMS_OUTPUT.put_line('集合长度：' || v_all.COUNT) ; END ; /

运行结果： 集合长度：5

• 示例2：使用DELETE函数删除一个数据

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','微软','lixinghua','android','java') ; BEGIN v_all.DELETE(1) ; -- 删除指定索引的数据 FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; /

运行结果： 微软 lixinghua android java

• 示例3：DELETE()函数删除一个范围的数据

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all                list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN v_all.DELETE(1,3) ;        -- 删除指定范围的数据 FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; /

运行结果： android java

• 示例4：判断某一数据是否存在

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN IF v_all.EXISTS(1) THEN DBMS_OUTPUT.put_line('索引为1的数据存在。') ; END IF ; IF NOT v_all.EXISTS(10) THEN DBMS_OUTPUT.put_line('索引为10的数据不存在。') ; END IF ; END ; /

运行结果： 索引为1的数据存在。 索引为10的数据不存在。

• 示例5：扩充集合长度

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all        list_nested := list_nested ('bing','微软','lixinghua') ; BEGIN v_all.EXTEND(2) ;        -- 集合扩充2个长度 v_all(4) := 'android' ; v_all(5) := 'java' ; DBMS_OUTPUT.put_line('集合长度：' || v_all.COUNT) ; FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; /

运行结果： 集合长度：5 bing 微软 lixinghua android java

• 示例6：扩充集合长度，并使用已有内容填充

DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('oracle','微软','lixinghua') ; BEGIN v_all.EXTEND(2,1) ; -- 集合扩充2个长度，使用原始集合的第1个数据填充 DBMS_OUTPUT.put_line('集合长度：' || v_all.COUNT) ; FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; /

运行结果： 集合长度：5 oracle 微软 lixinghua oracle oracle

• 示例7：使用LIMIT取得集合的最高下标

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android','java') ; BEGIN DBMS_OUTPUT.put_line('数组集合的最大长度：' || v_info.LIMIT) ; DBMS_OUTPUT.put_line('数组集合的数据量：' || v_info.COUNT) ; END ; /

运行结果： 数组集合的最大长度：8 数组集合的数据量：3

• 示例8：验证NEXT函数

DECLARE TYPE info_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER ; v_info info_index ; v_foot NUMBER ; BEGIN v_info (1) := 'MLDN' ; v_info (10) := 'JAVA' ; v_info (-10) := 'Oracle' ; v_info (-20) := 'EJB' ; v_info (30) := 'Android' ; v_foot := v_info.FIRST ; -- 取得集合的第一个索引值 WHILE(v_info.EXISTS(v_foot)) LOOP -- 判断此索引数据是否存在 DBMS_OUTPUT.put_line('v_info(' || v_foot || ') = ' || v_info(v_foot)) ; v_foot := v_info.NEXT(v_foot) ; -- 取得下一个索引值 END LOOP ; DBMS_OUTPUT.put_line('索引为10的下一个索引是：' || v_info.NEXT(10)) ; DBMS_OUTPUT.put_line('索引为-10的上一个索引是：' || v_info.PRIOR(-10)) ; END ; /

运行结果： v_info(-20) = EJB v_info(-10) = Oracle v_info(1) = MLDN v_info(10) = JAVA v_info(30) = Android 索引为10的下一个索引是：30 索引为-10的上一个索引是：-20

• 示例9：验证TRIM函数

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android','java','oracle','ejb') ; BEGIN DBMS_OUTPUT.put_line('删除集合之前的数据量：' || v_info.COUNT) ; v_info.TRIM ; -- 删除1个集合的数据，还剩下4个数据 DBMS_OUTPUT.put_line('v_info.TRIM删除集合数据之后的数据量：' || v_info.COUNT) ; v_info.TRIM(2) ; -- 删除2个数据之后还剩下2个数据 DBMS_OUTPUT.put_line('v_info.TRIM(2)删除集合数据之后的数据量：' || v_info.COUNT) ; END ; /

运行结果： 删除集合之前的数据量：5 v_info.TRIM删除集合数据之后的数据量：4 v_info.TRIM(2)删除集合数据之后的数据量：2

• 处理集合异常
  • 在进行集合的操作时，可能会出现诸如未初始化就直接使用集合，没有指定索引的集合元素等异常问题；
  • 常见集合异常

    

  • 示例1：处理集合未初始化异常

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray ; -- 此集合没有初始化 BEGIN v_info(0) := 10 ; -- 此时集合未初始化，所以产生异常 EXCEPTION WHEN COLLECTION_IS_NULL THEN DBMS_OUTPUT.put_line('集合未初始化，无法使用！') ; END ; /

运行结果： 集合未初始化，无法使用！

• 示例2：处理访问索引超过集合长度异常

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android') ; BEGIN DBMS_OUTPUT.put_line(v_info(3)) ; -- 没有此索引数据 EXCEPTION WHEN SUBSCRIPT_BEYOND_COUNT THEN DBMS_OUTPUT.put_line('索引值超过定义的元素个数！') ; END ; /

运行结果： 索引值超过定义的元素个数！

• 示例3：处理访问索引超过集合最大定义长度异常

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android') ; BEGIN DBMS_OUTPUT.put_line(v_info(30)) ; -- 索引下标超过最大范围 EXCEPTION WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN DBMS_OUTPUT.put_line('索引值超过定义集合类型的最大元素个数！') ; END ; /

运行结果： 索引值超过定义集合类型的最大元素个数！

• 示例4：设置错误的索引数据

DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('bing','android') ; -- 此集合没有初始化 BEGIN DBMS_OUTPUT.put_line(v_info('1')) ; -- 1可以自动变为数字（PLS_INTEGER） DBMS_OUTPUT.put_line(v_info('a')) ; -- a无法自动转换为数字 EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.put_line('索引值类型错误！') ; END ; /

运行结果： bing 索引值类型错误！

• 示例5：处理索引表集合中访问已删除数据集合异常

DECLARE TYPE info_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER ; v_info info_index ; BEGIN v_info(1) := 'mldn' ; v_info(2) := 'android' ; v_info(3) := 'java' ; v_info.DELETE(1) ; -- 删除数据 DBMS_OUTPUT.put_line(v_info(1)) ; -- 此元素已经被删除，无法访问 DBMS_OUTPUT.put_line(v_info(2)) ; DBMS_OUTPUT.put_line(v_info(3)) ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('此数据已经被删除！') ; END ; /

运行结果： 此数据已经被删除！