PLSQL总结——21.批处理

/*
create table student
(
  id number,
  name varchar2(20)
);
*/

declare

  type student_t is record(
    id   number,
    name varchar2(20)); --student结构体
  type student_nt is table of student_t index by pls_integer; --student索引表

  v_student_t   student_t; --student结构体变量
  v_student_nt  student_nt; --student索引表变量，插入用
  v_student_nt2 student_nt; --student索引表变量，隐式提取
  v_student_nt3 student_nt; --student索引表变量，显示提取

  /*输出studnet数组*/
  procedure show_array(x            number,
                       y            number,
                       p_student_nt student_nt) is
  
  begin
    for i in x .. y
    loop
      dbms_output.put_line(p_student_nt(i)
                           .id || ',' || p_student_nt(i).name);
    end loop;
  end show_array;

begin
  v_student_t.id := 1;
  v_student_t.name := 'a';
  v_student_nt(1) := v_student_t;

  v_student_t.id := 2;
  v_student_t.name := 'b';
  v_student_nt(2) := v_student_t;

  v_student_t.id := 3;
  v_student_t.name := 'c';
  v_student_nt(3) := v_student_t;

  v_student_t.id := 4;
  v_student_t.name := 'd';
  v_student_nt(4) := v_student_t;

  /*forall方式1，数组是要紧凑的*/
  forall rec in v_student_nt.first .. v_student_nt.last
    insert into student values v_student_nt (rec);
  /*forall方式2，indices of，数组不需要紧凑的*/
  /*  
      forall rec in indices of v_student_nt between 1 and 4
      insert into student values v_student_nt (rec);
  */

  /*隐式bulk collect*/
  select * bulk collect into v_student_nt2 from student;
  /*输出v_student_nt2*/
  show_array(v_student_nt2.first, v_student_nt2.last, v_student_nt2);

  dbms_output.put_line('-------------------------------');

  declare
    cursor c1 is
      select * from student;
  begin
    open c1;
    loop
      fetch c1 bulk collect
        into v_student_nt3;
      exit when c1%notfound;
    end loop;
    close c1;
  end;
  /*输出v_student_nt3*/
  show_array(v_student_nt2.first, v_student_nt2.last, v_student_nt3);

end;