oracle开发学习篇之集合运算符以及集合异常捕获

--取出集合;长度
declare type list_nested is table of varchar2(50) not null; v_all list_nested := list_nested('a','b','c','d','d'); begin dbms_output.put_line('list leng :' || cardinality(v_all)); end; /

--从集合中取出取消重复的元素
declare type list_nested is table of varchar2(50) not null; v_all list_nested := list_nested('a','d'); begin dbms_output.put_line('list leng :' || cardinality((set(v_all)))); end; /

--判断集合是否为空
declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','beijing','changan'); v_allB list_nested := list_nested('shanghai'); begin
    if v_allA is not empty then dbms_output.put_line('v_allA not null!'); end if; if v_allB is empty then dbms_output.put_line('v_allB is null!'); else dbms_output.put_line('v_allB not null!!'); end if; end; /


--判断字符是否存在
declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','changan'); v_allB list_nested := list_nested('shanghai'); v_str varchar2(20) := 'shanghai'; begin
    if v_str member of v_allA  then dbms_output.put_line('shanghai value is exists'); end if; end; /( --使用for循环遍历集合的每一个元素; 取出list中交集
declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','hunan'); v_allB list_nested := list_nested('Java','tianjing'); 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; /

--使用for循环遍历集合的每一个元素; 取出集合中所有的元素
declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','tianjing'); 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; / 判断集合是否为集合 declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','Java'); begin
    if v_allA is  A set then dbms_output.put_line('v_allA is list'); end if; end; /

declare type list_nested is table of varchar2(50) not null; v_allA varchar2(20) :=  'a'; begin
    if v_allA is  A set then dbms_output.put_line('v_allA is list'); end if; end; /



--判断B是否为A的子集合
declare type list_nested is table of varchar2(50) not null; v_allA list_nested := list_nested('shanghai','hunan','Java'); v_allB list_nested := list_nested('Java','beijing'); BEGIN
    if v_allB  submultiset v_allA then dbms_output.put_line('v_allB is v_allA submultiset'); end if; end; /

--集合的异常处理; --理解集合异常的缠身及处理操作; 所有异常捕获都能够使用others进行捕获;

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('The error collection is not initialized'); END; /




DECLARE type list_varray is varray(8) of varchar2(50); v_info list_varray := list_varray('shanghai','changan','facebook'); BEGIN dbms_output.put_line(v_info(5)); exception when subscript_beyond_count then dbms_output.put_line('索引值超过定义的元素个数!!'); end; /



DECLARE type list_varray is varray(8) of varchar2(50); v_info list_varray := list_varray('shanghai','facebook'); BEGIN dbms_output.put_line(v_info('1')); dbms_output.put_line(v_info('a')); exception when value_error then dbms_output.put_line('索引值类型错误'); end; /



declare type info_index is table of varchar2(100) index by PLS_INTEGER; v_info info_index; begin v_info(1) := 'fireof'; v_info(2) := 'firefox.com'; v_info(3) := 'www.firefox.com'; 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('data not found !!!'); end; /

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!