Oracle PL/SQL - PL SQL Statement FORALL command

Introduction

FORALL command builds a set of SQL statements and executes all of them at once.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  www . j a va 2 s  .  c om

SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7499, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 20);
SQL>
SQL> declare
  2       type number_nt is table of NUMBER;
  3       v_deptNo_nt number_nt:=number_nt(10,20);
  4  begin
  5       forall i in v_deptNo_nt.first()..v_deptNo_nt.last()
  6          update emp
  7             set sal=sal+10
  8          where deptNo=v_deptNo_nt(i);
  9  end;
 10  /

PL/SQL procedure successfully completed.
SQL>