知识点的梳理:
- 集合数据类型可以像一张数据表一样,向里面保存多行数据;
- 记录类型使用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 ; / | 运行结果: |
- 示例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 ; / | 运行结果: |
- 示例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 ; / | 运行结果: |
- 示例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 将第二个集合中与第一个集合相同的部分删除,最终新的集合里只有一个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 ; / | 运行结果: |
- 示例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 ; / | 运行结果: |
-
集合函数
-
常见的集合函数
- 示例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 ; / | 运行结果: |
- 示例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 ; / | 运行结果: |