批量绑定

declare
  type id_table_type is table of number(6) index by binary_integer;
  type name_table_type is table of varchar2(10) index by binary_integer;
  id_table id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time number(10);
begin
  for i in 1 .. 5000 loop
    id_table(i) := i;
    name_table(i) := 'name' || to_char(i);
  end loop; 
  start_time := dbms_utility.get_time; 
  for i in 1 .. id_table.count loop
    insert into demo values(id_table(i),name_table(i));
  end loop; 
  end_time := dbms_utility.get_time;     
  dbms_output.put_line(to_char(((end_time - start_time)/100)));
end;     

 

 

declare
  type id_table_type is table of number(6) index by binary_integer;
  type name_table_type is table of varchar2(10) index by binary_integer;
  id_table id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time number(10);
begin
  for i in 1 .. 5000 loop
    id_table(i) := i;
    name_table(i) := 'name' || to_char(i);
  end loop; 
  start_time := dbms_utility.get_time; 
  forall i in 1 .. id_table.count
    insert into demo values(id_table(i),name_table(i)); 
  end_time := dbms_utility.get_time;     
  dbms_output.put_line(to_char(((end_time - start_time)/100)));
end;     

 

 

declare
  type id_table_type is table of number(6) index by binary_integer;
  id_table id_table_type;
begin
  for i in 1 .. 10 loop
    id_table(i):=11-i;
  end loop;
 
  forall i in 8 .. 10
    insert into demo (id) values(id_table(i));
end;   

 

 

declare
  type id_table_type is table of demo.id%type;
  id_table id_table_type;
begin
  id_table := id_table_type(1,null,3,null,5);
  forall i in indices of id_table
    delete from demo where id = id_table(i);
end;

 

 

--create table new_demo as select * from demo where 1 = 0;

 

 

declare
  type id_table_type is table of demo.id%type;
  id_table id_table_type;
  type name_table_type is table of demo.name%type;
  name_table name_table_type;
  type index_pointer_type is table of PLS_INTEGER;
  index_pointer index_pointer_type;
begin
  select * bulk collect into id_table,name_table from demo;
  index_pointer := index_pointer_type(8,10,12);
  forall i in values of index_pointer
    insert into new_demo values(id_table(i),name_table(i));
end;     

 

 

declare
  type dno_table_type is table of number(3);
  deno_table dno_table_type := dno_table_type(10,2);
begin
  forall i in 1 .. deno_table.count
   update emp set salary = salary * 1.1 where empno = deno_table(i);
  dbms_output.put_line(sql%bulk_rowcount(2));
end;    

 

 

declare
  type emp_table_type is table of emp%rowtype index by binary_integer;
  emp_table emp_table_type;
begin
  select * bulk collect into emp_table from emp where deptno = &no;
  for i in 1 .. emp_table.count loop
    dbms_output.put_line('雇员姓名:'||emp_table(i).ename);
  end loop;
end;

 

 

declare
  type name_table_type is table of emp.ename%type;
  ename_table name_table_type;
begin
  delete from emp where empno = &no returning ename bulk collect into ename_table;
  dbms_output.put_line('雇员名:');
  for i in 1 .. ename_table.count loop
    dbms_output.put_line(ename_table(i) || '      ');
  end loop;
  dbms_output.new_line;
end;