oracle 数组操作

1、在countries表中插入数据，在插入之前进行检查，如果表中已经存在，则不插入重复数据。

declare
       type arr_type is varray(29) of varchar(100); --固定维数的数组
       cn_names arr_type := arr_type('澳大利亚','新西兰','巴布亚新几内亚','文莱','新喀里多尼亚','比利时','英国','丹麦','芬兰','希腊','爱尔兰',
       '意大利','卢森堡','马耳他','挪威','瑞士','葡萄牙','德国','瑞典','法国','荷兰','西班牙','奥地利','斐济','瓦努阿图',
       '美国','加拿大','日本','新加坡');
       en_names arr_type := arr_type('Australia','New Zealand','Papua New Guinea','Brunei Darussalam','New Caledonia','Belgium','United Kingdom',
       'Denmark','Finland','Greece','Ireland','Italy','Luxembourg','Malta','Norway','Switzerland','Portugal','Germany',
       'Sweden','France','Netherlands','Spain','Austria','Fiji','Vanuatu','United States','Canada','Japan','Singapore');
       v_row number;
begin
     for i in 1..cn_names.count loop
         select id into v_row from countries where name_cn = cn_names(i);
         if sql%notfound then
            insert into countries (id,name,name_cn,time_stamp) values (countries_seq.nextval,en_names(i),cn_names(i),sysdate);
         end if;
     end loop;

2、使用数组操作和使用游标操作的区别

declare --使用游标
       cursor cur is select * from employees;
       rr employees%rowtype;
begin
     open cur;
     loop
         exit when cur%notfound;
         fetch cur into rr;
         dbms_output.put_line(rr.username);
     end loop;
     close cur;
end;

declare --使用数组
       type arr is table of employees%rowtype index by binary_integer; --可变数组
       v_arr arr;
begin
     select * bulk collect into v_arr from employees; --bulk collect将数据一起赋给v_arr,加快速度，这里不用会提示错误
     for i in 1..v_arr.count loop
         dbms_output.put_line(v_arr(i).username);
     end loop;
end;

用数组的运行速度比用游标的速度快些！

 

可参阅：http://fxz-2008.javaeye.com/blog/469766

http://hi.baidu.com/%CE%D2%B0%AE%D5%C5%D3%B1/blog/item/54ccb5016391c4dd277fb593.html