oracle数组使用

create or replace procedure p_add_find_rel(v_city_id IN NUMBER,
                                           vo_errcode OUT VARCHAR2,
                                           vo_errmsg OUT VARCHAR2)

 IS  

       --定义一个record类型

       TYPE agent_type is record(
       object_id     tmp_find_rel.object_id%TYPE,
       object_type   tmp_find_rel.object_type%TYPE,
       find_id       tmp_find_rel.find_id%TYPE,
       find_val      tmp_find_rel.find_val%TYPE,
       staff_id      tmp_find_rel.staff_id%TYPE,
       agt_tml       tmp_find_rel.agt_tml%TYPE

       );

       --定义一个数组serv_array 类型

       type serv_array is table of agent_type index by binary_integer;

       --申明一个数组变量。

       var_serv serv_array;
       v_area_id  NUMBER(10);
       --v_city_id  NUMBER(5) := '3';
       v_area_count NUMBER ;
       --v_city_count NUMBER;

begin

  --一次性查出所有记录，放到申明的数组变量中去。

  SELECT * BULK COLLECT INTO var_serv FROM tmp_find_rel t ORDER BY t.object_id ASC ;

  --循环遍历该数据

  FOR i IN 1..var_serv.count LOOP
          SELECT /*+parallel(t,8)*/COUNT(*) INTO v_area_count FROM xsqd.v_channel_js_temp@qd3_js t WHERE t.channel_id = var_serv(i).object_id ;
          IF v_area_count <> 0 THEN  
            SELECT /*+parallel(t,8)*/ DISTINCT area INTO v_area_id FROM xsqd.v_channel_js_temp@qd3_js t WHERE t.channel_id = var_serv(i).object_id ;
            insert INTO tp_object_find_rel(object_id,object_type,find_id,find_val,city_id,area_id,state,state_date,create_staff,create_date,End_Date,begin_date,source_type)
                               values(var_serv(i).object_id,var_serv(i).object_type,var_serv(i).find_id,var_serv(i).find_val,v_city_id,v_area_id,12,SYSDATE,'add',SYSDATE,to_date('3000-01-01','yyyy-mm-dd'),to_date('2000-01-01','yyyy-mm-dd'),'up');
            DELETE FROM tmp_find_rel t where t.object_id = var_serv(i).object_id AND t.object_type = var_serv(i).object_type AND
            t.find_id = var_serv(i).find_id AND t.find_val = var_serv(i).find_val ;
            COMMIT ;
          END if ;   
  END LOOP ;
  vo_errcode := '0';
  vo_errmsg  :='ok';
  exception
  when others then
  vo_errcode   := sqlcode;
  vo_errmsg    := substr(sqlerrm, 1, 250);
end p_add_find_rel;