oracle集合类型应用举例

灵秀9533

--condition--
create table t1(id int);
create table t2(id int);
create table t3(id int);

create or replace package aa_test is

  -- Author  : tom
  -- Created : 2010-5-10 17:04:24
  -- Purpose :

  type tom_re is record(
    id t1.id%type);

  type tom_insert_tab is table of tom_re index by binary_integer;

  tom_insert_array tom_insert_tab;

  procedure tom_insert(iv_tom_insert_array in tom_re);

  procedure tom_print;

  procedure tom_print1;

  procedure tom_print2;
  
  procedure tom_print3;

end aa_test;




create or replace package body aa_test is

  procedure tom_insert(iv_tom_insert_array in tom_re) is
  begin
    insert into t3 (id) values (iv_tom_insert_array.id);
  end tom_insert;

  procedure tom_print is
    cursor cur_tom is
      select * from t1;
  begin
    open cur_tom;
    loop
      fetch cur_tom bulk collect
        into tom_insert_array; --这种情况，只循环一遍--   
   
      for i in 1 .. tom_insert_array.count loop
        dbms_output.put_line('----' || tom_insert_array(i).id);
      end loop;
   
      exit when cur_tom%notfound;
   
    end loop;
  
    close cur_tom;
    dbms_output.put_line('----' || tom_insert_array.count);
    tom_insert_array.delete; --清空数组--
    dbms_output.put_line('----' || tom_insert_array.count);
  end tom_print;

  procedure tom_print1 is
    cursor cur_tom is
      select * from t1;
  begin
    open cur_tom;
  
    fetch cur_tom bulk collect
      into tom_insert_array; --这种情况，只循环一遍--      
  
    for i in 1 .. tom_insert_array.count loop
      dbms_output.put_line('----' || tom_insert_array(i).id);
    end loop;
  
    close cur_tom;
  end tom_print1;

  procedure tom_print2 is
    cursor cur_tom is
      select * from t1;
    v_id integer;
  begin
    open cur_tom;
    loop
      fetch cur_tom
        into v_id; --循环多遍--
      exit when cur_tom%notfound;
      dbms_output.put_line('--v_id--' || v_id); --这种情况需要放exit后面--
    end loop;
  
    close cur_tom;
  end tom_print2;
  
  
  procedure tom_print3 is
    cursor cur_tom is
      select * from t1;
  begin
    open cur_tom;
  
    fetch cur_tom bulk collect
      into tom_insert_array; --这种情况，只循环一遍--      
  
    for i in 1 .. tom_insert_array.count loop
      tom_insert(tom_insert_array(i));
      dbms_output.put_line('--insert times:--'|| i);
    end loop;  
    close cur_tom;
   
  commit;
  end tom_print3;

end aa_test;