Updating oracle views
PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor.When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.
I can simplify this procedure to nothing more than the code in Listing 2.
Code Listing 2: Simplified increase_salary procedure without FOR loop PROCEDURE increase_salary ( department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS BEGIN UPDATE employees emp SET emp.salary = emp.salary emp.salary * increase_salary.increase_pct_in WHERE emp.department_id = increase_salary.department_id_in; END increase_salary; Now there is just a single context switch to execute one UPDATE statement. Of course, in most real-world scenarios, life—and code—is not so simple.
Code Listing 4: Bulk processing for the increase_salary procedure 1 CREATE OR REPLACE PROCEDURE increase_salary ( 2 department_id_in IN employees.department_id%TYPE, 3 increase_pct_in IN NUMBER) 4 IS 5 TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE 6 INDEX BY PLS_INTEGER; 7 l_employee_ids employee_ids_t; 8 l_eligible_ids employee_ids_t; 9 10 l_eligible BOOLEAN; 11 BEGIN 12 SELECT employee_id 13 BULK COLLECT INTO l_employee_ids 14 FROM employees 15 WHERE department_id = increase_salary.department_id_in; 16 17 FOR indx IN 1 .. COUNT 18 LOOP 19 check_eligibility (l_employee_ids (indx), 20 increase_pct_in, 21 l_eligible); 22 23 IF l_eligible 24 THEN 25 l_eligible_ids (l_eligible_ids.
COUNT 1) := 26 l_employee_ids (indx); 27 END IF; 28 END LOOP; 29 30 FORALL indx IN 1 .. COUNT 31 UPDATE employees emp 32 SET emp.salary = 33 emp.salary 34 emp.salary * increase_salary.increase_pct_in 35 WHERE emp.employee_id = l_eligible_ids (indx); 36 END increase_salary; Declare a new nested table type and two collection variables based on this type.