Bulk Binding

来源：互联网发布：吉他中国淘宝编辑：程序博客网时间：2024/06/05 09:13

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) iscursor dept_csr is select * from departments where location_id =loc;type dept_tabtype istable 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;