Updating oracle views

My procedure might then look like the version in Listing 3.

This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.FIRST; BEGIN WHILE (indx IS NOT NULL) LOOP DBMS_OUTPUT.put_line (l_names (indx)); indx := l_names.NEXT (indx); END LOOP; END; END; / Given that PL/SQL is so tightly integrated with the SQL language, you might be wondering why special features would be needed to improve the performance of SQL statements inside PL/SQL.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.

Leave a Reply