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

--取出集合;长度
declare
        type list_nested is table of varchar2(50) not null;
        v_all list_nested := list_nested('a','b','c','d','c','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','b','c','d','c','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','beijing','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','beijing','hunan');
    v_allB list_nested := list_nested('Java','beijing','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','beijing','hunan');
    v_allB list_nested := list_nested('Java','beijing','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','beijing','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','beijing','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','changan','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;
/