While doing programming in PL/SQL following are the common scenario of data manipulation
1. selecting multiple records from the cursor or table (BULK COLLECT)
2. Inserting multiple record in the database(BULK BIND )
3. Updating the multiple records to the cursor latest row fetched from the cursor (CURRENT OF )
1. selecting multiple records from the cursor or table (BULK COLLECT)
Concept of Context Switching
=======================
All most every PL/SQL developer writes the SQL and PL/SQL statement in code. The SQL statement are executed by the SQL engine and PL/SQL statement are executed by the PL/SQL engine. When PL/SQL engine encounter the SQL statement then it pass the control to the SQL engine and again control come backs to PL/SQL engine when PL/SQL statement encounters.
This is called as context switching.
Following is the procedure which will accept the departmentId and salary percentage increase and gives to each employee in the provided department
The following procedure use the cursor (FOR LOOP cursor ) to fetch the employee for the provided departmentId and then update the employee table with increased salary
increase_salary procedure with FOR loop
PROCEDURE increase_salary (
department_id_in IN employees.department_id%TYPE,
increase_pct_in IN NUMBER)
IS
BEGIN
FOR employee_rec
IN (SELECT employee_id
FROM employees
WHERE department_id =
increase_salary.department_id_in)
LOOP
UPDATE employees emp
SET emp.salary = emp.salary +
emp.salary * increase_salary.increase_pct_in
WHERE emp.employee_id = employee_rec.employee_id;
END LOOP;
END increase_salary;
Suppose there are 100 employees in department 15. When I execute this block,
BEGIN
increase_salary (15, .10);
END;
When we are executing the above procedure there will be 100 context switching between SQL and PL/SQL engine this is row by row switching which is performance overhead.
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;
Single context switch to execute update statement all work is done in single context switch. By default the update statement is BULK update.
In Real time the code is not that much simple need to perform sever data manipulation operation then updating the data .Suppose that, for example, in the case of the increase_salary procedure, I need to check employees for eligibility for the increase in salary and if they are ineligible, send an e-mail notification. My procedure might then look like below.
PROCEDURE increase_salary (
department_id_in IN employees.department_id%TYPE,
increase_pct_in IN NUMBER)
IS
l_eligible BOOLEAN;
BEGIN
FOR employee_rec
IN (SELECT employee_id
FROM employees
WHERE department_id =
increase_salary.department_id_in)
LOOP
check_eligibility (employee_rec.employee_id,
increase_pct_in,
l_eligible);
IF l_eligible
THEN
UPDATE employees emp
SET emp.salary =
emp.salary
+ emp.salary
* increase_salary.increase_pct_in
WHERE emp.employee_id = employee_rec.employee_id;
END IF;
END LOOP;
END increase_salary;
Now no longer everything in single context switch.
Bulk Processing in PL/SQL
The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID.
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 .. l_employee_ids.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 .. l_eligible_ids.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;
The highlighted green part is the BULK select which will fetch all employee id in l_employee_ids
collection .
The code snippet highlighted in yellow FORALL BULK update
Rather than move back and forth between PL/SQL and SQL Engine the FORALL handles all the updates and passes them to the sql engine in single context switch.
IMPORTANT THINK to know when starting to use the advantage of the BULK COLLECT
Trade of the BULK COLLECT run faster consume more memory .
There are two types of memory SGA (System Global Area) and PGA(Program Global Area)
the SGA is shared for all the session connected to the database. whie PGA is alloacted for each session . Memory for collection is stored in the PGA.
Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.
The PL/SQL made the life of developer easy by by introducing the LIMIT clause on the BULK COLLECT to control amount of memory used.
Due to LIMIT it will fetch the number_record specified in the limit_in parameter at a time .PL/sQL resuse the same limit_in and same memory for subsequent fetch. Even though the table size grows the PGA consumption will be constant.
When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes to decide whether to terminate your loop and data processing.
1. selecting multiple records from the cursor or table (BULK COLLECT)
2. Inserting multiple record in the database(BULK BIND )
3. Updating the multiple records to the cursor latest row fetched from the cursor (CURRENT OF )
1. selecting multiple records from the cursor or table (BULK COLLECT)
Concept of Context Switching
=======================
All most every PL/SQL developer writes the SQL and PL/SQL statement in code. The SQL statement are executed by the SQL engine and PL/SQL statement are executed by the PL/SQL engine. When PL/SQL engine encounter the SQL statement then it pass the control to the SQL engine and again control come backs to PL/SQL engine when PL/SQL statement encounters.
This is called as context switching.
Following is the procedure which will accept the departmentId and salary percentage increase and gives to each employee in the provided department
The following procedure use the cursor (FOR LOOP cursor ) to fetch the employee for the provided departmentId and then update the employee table with increased salary
increase_salary procedure with FOR loop
PROCEDURE increase_salary (
department_id_in IN employees.department_id%TYPE,
increase_pct_in IN NUMBER)
IS
BEGIN
FOR employee_rec
IN (SELECT employee_id
FROM employees
WHERE department_id =
increase_salary.department_id_in)
LOOP
UPDATE employees emp
SET emp.salary = emp.salary +
emp.salary * increase_salary.increase_pct_in
WHERE emp.employee_id = employee_rec.employee_id;
END LOOP;
END increase_salary;
Suppose there are 100 employees in department 15. When I execute this block,
BEGIN
increase_salary (15, .10);
END;
When we are executing the above procedure there will be 100 context switching between SQL and PL/SQL engine this is row by row switching which is performance overhead.
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;
Single context switch to execute update statement all work is done in single context switch. By default the update statement is BULK update.
In Real time the code is not that much simple need to perform sever data manipulation operation then updating the data .Suppose that, for example, in the case of the increase_salary procedure, I need to check employees for eligibility for the increase in salary and if they are ineligible, send an e-mail notification. My procedure might then look like below.
PROCEDURE increase_salary (
department_id_in IN employees.department_id%TYPE,
increase_pct_in IN NUMBER)
IS
l_eligible BOOLEAN;
BEGIN
FOR employee_rec
IN (SELECT employee_id
FROM employees
WHERE department_id =
increase_salary.department_id_in)
LOOP
check_eligibility (employee_rec.employee_id,
increase_pct_in,
l_eligible);
IF l_eligible
THEN
UPDATE employees emp
SET emp.salary =
emp.salary
+ emp.salary
* increase_salary.increase_pct_in
WHERE emp.employee_id = employee_rec.employee_id;
END IF;
END LOOP;
END increase_salary;
Now no longer everything in single context switch.
Bulk Processing in PL/SQL
The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID.
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 .. l_employee_ids.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 .. l_eligible_ids.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;
The highlighted green part is the BULK select which will fetch all employee id in l_employee_ids
collection .
The code snippet highlighted in yellow FORALL BULK update
Rather than move back and forth between PL/SQL and SQL Engine the FORALL handles all the updates and passes them to the sql engine in single context switch.
IMPORTANT THINK to know when starting to use the advantage of the BULK COLLECT
Trade of the BULK COLLECT run faster consume more memory .
There are two types of memory SGA (System Global Area) and PGA(Program Global Area)
the SGA is shared for all the session connected to the database. whie PGA is alloacted for each session . Memory for collection is stored in the PGA.
Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.
The PL/SQL made the life of developer easy by by introducing the LIMIT clause on the BULK COLLECT to control amount of memory used.
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT limit_in;
Due to LIMIT it will fetch the number_record specified in the limit_in parameter at a time .PL/sQL resuse the same limit_in and same memory for subsequent fetch. Even though the table size grows the PGA consumption will be constant.
When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes to decide whether to terminate your loop and data processing.
EXIT WHEN l_table_with_227_rows.COUNT = 0;
Generally, you should keep all of the following in mind when working with BULK COLLECT: 1. The collection is always filled sequentially, starting from index value 1. 2. It is always safe (that is, you will never raise a NO_DATA_FOUND exception) to iterate through a collection from 1 to collection .COUNT when it has been filled with BULK COLLECT. 3. The collection is empty when no rows are fetched. 4. Always check the contents of the collection (with the COUNT method) to see if there are more rows to process. 5. Ignore the values returned by the cursor attributes, especially %NOTFOUND.
