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_allBv_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

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

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 ;

/

运行结果:
此数据已经被删除!

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值