Bulk Binding

1.FORALL:打包进入SQL引擎

2.BULK COLLECT:打包返回PL/SQL引擎

● Bulk Binding FORALL: Example

create or replace procedure raise_salary(precent number) is
       type numlist is table of number
       index by binary_integer;
       id numlist;
begin
  id(1):=100;id(2):=102;
  id(3):=106;id(3):=110;
  
  forall i in id.first..id.last
  update employees
  set salary = ( 1 + percent/100)*salary
  where manager_id = id(i);
end;

● Using BULK COLLECT INTO with Cursors

create or replace procedure get_departments(loc number) is
cursor dept_csr is select * from departments where location_id =loc;
type dept_tabtype is
table of departments%rowtype;
depts dept_tabtype;
begin
  open dept_csr;
  fetch dept_csr bulk collect into depts;
  close dept_csr;
  for i in 1..depts.count loop
    dbms_output.put_line(depts(i).department_name||''||depts(i).department_name);
  end loop;
end;

●Using BULK COLLECT INTO with a RETURNING Clause

create or replace procedure raise_salary(rate number) is
  type emplist is table of number;
  type numlist is table of employees.salary%type
  index by binary_integer;
  emp_ids emplist:=emplist(100,101,102,104);
  new_sals numlist;
begin
  forall i in emp_ids.first..emp_ids.last
  update employees
  set commission_pct=rate*salary
  where employee_id = emp_ids(i)
  returning salary bulk collect into new_sals;
  
  for i in 1..new_sals.count loop
    dbms_output.put_line(i);
  end loop;
end;