oracle开发学习篇之集合函数

集合函数; declare type list_nested is table of varchar2(100) @H_404_18@not null; v_all list_nested :@H_404_18@= list_nested('changan','hubei','shanghai','beijing','Android','Java-Android'); BEGIN v_all.delete(1); for x @H_404_18@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; @H_404_18@/


declare type list_nested is table of varchar2(100) @H_404_18@not null; v_all list_nested :@H_404_18@= list_nested('changan','Java-Android'); BEGIN v_all.delete(1,3); --指定删除索引范围的数据;
        for x @H_404_18@in v_all.first .. v_all.last loop dbms_output.put_line(v_all(x)); end loop; dbms_output.put_line('list leng:' @H_404_18@|| v_all.count ); end; @H_404_18@/



declare type list_nested is table of varchar2(100) @H_404_18@not null; v_all list_nested :@H_404_18@= list_nested('changan','Java-Android'); BEGIN
        if v_all.@H_404_18@EXISTS(1) then dbms_output.put_line('索引为1的数据存在'); end if; if @H_404_18@not v_all.@H_404_18@EXISTS(10) then dbms_output.put_line('索引为10的数据不存在'); end if; end; @H_404_18@/



declare type list_nested is table of varchar2(100) @H_404_18@not null; v_all list_nested :@H_404_18@= list_nested('FireFox','firefox.com','www.firefox.com'); begin dbms_output.put_line('原始长度' @H_404_18@|| v_all.count); v_all.extend(2); dbms_output.put_line('扩充之后长度' @H_404_18@|| v_all.count); v_all(4) :@H_404_18@= 'www.google.com'; v_all(5) :@H_404_18@= 'Google.com'; for x @H_404_18@in v_all.first .. v_all.last loop dbms_output.put_line(v_all(x)); end loop; end; @H_404_18@/ 范例 扩充集合长度，并使用已有内容填充 declare type list_nested is table of varchar2(100) @H_404_18@not null; v_all list_nested :@H_404_18@= list_nested('FireFox','www.firefox.com'); begin dbms_output.put_line('原始长度' @H_404_18@|| v_all.count); v_all.extend(2,3); dbms_output.put_line('扩充之后长度' @H_404_18@|| v_all.count); for x @H_404_18@in v_all.first .. v_all.last loop dbms_output.put_line(v_all(x)); end loop; end; @H_404_18@/ 范例 集合函数count limit declare type list_varray is  varray(20) of varchar2(100); v_info list_varray :@H_404_18@= list_varray('firefox.com','www.firefox.com','www.oracle.com'); begin dbms_output.put_line('数组集合的最大长度' @H_404_18@|| v_info.limit); dbms_output.put_line('数组集合的数据量' @H_404_18@|| v_info.count); end; @H_404_18@/


declare type info_index is table of varchar2(20) index by PLS_INTEGER; v_info info_index; v_foot number; begin v_info(1) :@H_404_18@= 'FireFox'; v_info(10) :@H_404_18@= 'Java'; v_info(@H_404_18@-10) :@H_404_18@= 'oracle'; v_info(@H_404_18@-20) :@H_404_18@= 'EJB'; v_info(30) :@H_404_18@= 'Android'; v_foot :@H_404_18@= v_info.first; while (v_info.@H_404_18@exists(v_foot)) loop dbms_output.put_line('v_info (' @H_404_18@|| v_foot @H_404_18@||') = ' @H_404_18@|| v_info(v_foot); v_foot :@H_404_18@= v_info.next(v_foot) ; end loop; end; @H_404_18@/ 


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


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

猜你在找的Oracle相关文章
