Create and Populate Table

CREATE TABLE employees 
( 
   employee_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   department_id INTEGER, 
   salary        NUMBER 
) 

BEGIN 
   FOR indx IN 1 .. 1000 
   LOOP 
      INSERT INTO employees (department_id , salary) 
           VALUES (MOD (indx, 10) * 10, indx * 100); 
   END LOOP; 
 
   COMMIT; 
END; 

Ah the Elegance and Simplicity of SQL!

UPDATE employees  
   SET salary = 10000  
 WHERE department_id = 50

Row-by-row Updating in PL/SQL

CREATE OR REPLACE PROCEDURE raise_across_dept (  
   dept_in    IN employees.department_id%TYPE,  
   raise_in   IN employees.salary%TYPE)   
IS  
   /*   
     One step with cursor FOR loop:  
     The kind of code that is likely to cause  
     Snapshot too old and Rollback segment too small errors  
   */  
  
   CURSOR emp_cur  
   IS  
      SELECT employee_id, salary  
        FROM employees  
       WHERE department_id = dept_in;  
BEGIN  
   FOR rec IN emp_cur  
   LOOP  
      UPDATE employees  
         SET salary = rec.salary  
       WHERE employee_id = rec.employee_id;  
   END LOOP;  
 
   COMMIT; 
END; 

Row-by-row Incremental Commit Processing

CREATE OR REPLACE PROCEDURE raise_across_dept (  
   dept_in           IN employees.department_id%TYPE,  
   raise_in          IN employees.salary%TYPE,  
   commit_after_in   IN PLS_INTEGER)  
IS  
  /*  
     Traditional incremental commit logic.  
     This will not help at all, though, with Snapshot too old errors.  
   */  
   l_counter   PLS_INTEGER;  
  
   CURSOR emp_cur  
   IS  
      SELECT employee_id, salary  
        FROM employees  
       WHERE department_id = dept_in;  
BEGIN  
   l_counter := 1;  
  
   FOR rec IN emp_cur  
   LOOP  
      prepare_raise_value (raise_in, rec.employee_id, rec.salary);  
  
      UPDATE employees  
         SET salary = rec.salary  
       WHERE employee_id = rec.employee_id;  
  
      IF l_counter >= commit_after_in  
      THEN  
         COMMIT;  
         l_counter := 1;  
      ELSE  
         l_counter := l_counter + 1;  
      END IF;  
   END LOOP;  
  
   COMMIT;  
END; 

So Use Bulk Processing

CREATE OR REPLACE PROCEDURE raise_across_dept (  
   dept_in           IN employees.department_id%TYPE,  
   raise_in          IN employees.salary%TYPE,  
   commit_after_in   IN PLS_INTEGER)  
IS  
  /*  
    Use of BULK COLLECT could bypass Snapshot too old problem  
    Use of FORALL could hit the Rollback segment too small problem FASTER  
  */   
   TYPE emp_info_rt IS RECORD ( 
       employee_id employees.employee_id%TYPE 
     , salary employees.salary%TYPE);  
  
   TYPE emp_info_tt IS TABLE OF emp_info_rt  
      INDEX BY PLS_INTEGER;  
  
   l_emp_info emp_info_tt;  
BEGIN  
   SELECT employee_id, salary  
     BULK COLLECT INTO l_emp_info 
     FROM employees  
    WHERE department_id = dept_in;  
  
   FORALL indx IN l_emp_info.FIRST .. l_emp_info.LAST  
      UPDATE employees  
         SET salary = l_emp_info (indx).salary 
       WHERE employee_id = l_emp_info (indx).employee_id;  
  
   COMMIT;  
END; 

Use FORALL IN Clause for Limiting Statements

CREATE OR REPLACE PROCEDURE raise_across_dept (   
   dept_in           IN employees.department_id%TYPE,   
   raise_in          IN employees.salary%TYPE,   
   limit_bulk_in     IN PLS_INTEGER DEFAULT 10000,   
   commit_after_in   IN PLS_INTEGER DEFAULT 1000)   
IS   
   failure_in_forall   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);  
  
   l_last         PLS_INTEGER;   
   l_start        PLS_INTEGER;   
   l_end          PLS_INTEGER;   
   
   TYPE emp_info_rt IS RECORD (  
       employee_id employees.employee_id%TYPE  
     , salary employees.salary%TYPE);   
   
   TYPE emp_info_tt IS TABLE OF emp_info_rt   
      INDEX BY PLS_INTEGER;   
   
   l_emp_info emp_info_tt;   
   
   CURSOR emps_in_dept_cur   
   IS   
      SELECT employee_id, salary FROM employees   
       WHERE department_id = dept_in;   
BEGIN   
   OPEN emps_in_dept_cur;   
   
   LOOP   
      FETCH emps_in_dept_cur   
         BULK COLLECT INTO l_emp_info   
         LIMIT limit_bulk_in;   
   
      EXIT WHEN l_emp_info.COUNT = 0;   
   
      l_start := 1;   
      l_last := l_emp_info.COUNT;    
   
      LOOP   
         EXIT WHEN l_start > l_last;   
  
         l_end := LEAST (l_start + commit_after_in - 1, l_last);   
   
         BEGIN   
            DBMS_OUTPUT.PUT_LINE ('FORALL start-end: ' || l_start || '-' || l_end);  
  
            FORALL indx IN l_start .. l_end SAVE EXCEPTIONS   
               UPDATE employees   
                  SET salary = l_emp_info (indx) .salary  
                WHERE employee_id = l_emp_info (indx).employee_id;   
         EXCEPTION   
            WHEN failure_in_forall   
            THEN   
               /* Write errors out to your REAL error log.    
                  Here I simply display the information. */   
               DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   
               DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT || ' rows.');   
   
               FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT   
               LOOP   
                  DBMS_OUTPUT.put_line (   
                        'Error '   
                     || indx   
                     || ' occurred on index '   
                     || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX   
                     || '  with error code '   
                     || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);   
               END LOOP;   
         END;   
   
         COMMIT;   
         l_start := l_end + 1;   
      END LOOP;   
   
      COMMIT;   
   END LOOP;   
END; 

BEGIN 
   raise_across_dept (  
      dept_in           => 50,  
      raise_in          => 10000,  
      limit_bulk_in     => 100,  
      commit_after_in   => 25); 
END;