oracle开发学习篇之集合函数

集合函数;
declare 
        type list_nested is table of varchar2(100) not null;
        v_all list_nested := list_nested(changan,hubei,shanghai,beijing,Android,Java-Android);
BEGIN
        v_all.delete(1);
        for x in v_all.first .. v_all.last loop
            dbms_output.put_line(v_all(x));
        end loop;
        --dbms_output.put_line(‘list leng:‘ || v_all.count );
end;
/


declare 
        type list_nested is table of varchar2(100) not null;
        v_all list_nested := list_nested(changan,hubei,shanghai,beijing,Android,Java-Android);
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;
        dbms_output.put_line(list leng: || v_all.count );
end;
/



declare 
        type list_nested is table of varchar2(100) not null;
        v_all list_nested := list_nested(changan,hubei,shanghai,beijing,Android,Java-Android);
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;
/



declare
        type list_nested is table of varchar2(100) not null;
        v_all list_nested := list_nested(FireFox,firefox.com,www.firefox.com);
begin
        dbms_output.put_line(原始长度 || v_all.count);
        v_all.extend(2);
        dbms_output.put_line(扩充之后长度 || v_all.count);
        v_all(4) := www.google.com;
        v_all(5) := Google.com;
        for x in v_all.first  .. v_all.last loop
            dbms_output.put_line(v_all(x));
        end loop;
    end;
/

范例
    扩充集合长度,并使用已有内容填充
declare
        type list_nested is table of varchar2(100) not null;
        v_all list_nested := list_nested(FireFox,firefox.com,www.firefox.com);
begin
        dbms_output.put_line(原始长度 || v_all.count);
        v_all.extend(2 , 3);
        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;
/


范例
    集合函数count limit
declare
    type list_varray is  varray(20) of varchar2(100);
    v_info list_varray := list_varray(firefox.com,www.firefox.com,www.oracle.com);
begin
    dbms_output.put_line(数组集合的最大长度 || v_info.limit);
    dbms_output.put_line(数组集合的数据量 || v_info.count);
end;
/


declare
        type info_index is table of varchar2(20) index by PLS_INTEGER;
        v_info info_index;
        v_foot number;
begin
        v_info(1) := FireFox;
        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;
    end;
/ 


declare
        type info_index is table of varchar2(20) index by PLS_INTEGER;
        v_info info_index;
        v_foot number;
begin
        v_info(1) := FireFox;
        v_info(10) := Java;
        v_info(-10) := oracle;
        v_info(-20) := EJB;
        v_info(30) := Android;
        DBMS_OUTPUT.put_line(索引为10的下一个索引是: || v_info.next(10));
        dbms_output.put_line(索引为-10的上一个索引是: || v_info.prior(-10));
    end;
/ 


declare 
        type list_varray is varray(8) of varchar2(50);
        v_info list_varray :=  list_varray(FireFox,www.FireFox,www.Google.com,EJB);
begin
        dbms_output.put_line();
        for x in v_info.first .. v_info.last loop
                dbms_output.put_line(v_info(x));
        end loop;
    end;
/
declare 
        type list_varray is varray(8) of varchar2(100);
        v_info list_varray := list_varray(FireFox,www.firefox.com,oracle,oracle_username);
begin
        dbms_output.put_line(删除集合之前的数量: || v_info.count);
        v_info.trim; -- 删除一个数据;
        dbms_output.put_line(v_info.trim 删除集合之后的数据量 : || v_info.count);
        v_info.trim(2); -- 删除2个数据;
        dbms_output.put_line(v_info.trim(2) 删除集合之后的数据量 : || v_info.count);
        for x in v_info.first .. v_info.last loop
            dbms_output.put_line(v_info(x));
        end loop;
    end;
/

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。