Bulk Collect Clause With FETCH INTO Statement
In the previous tutorial we managed to compress multiple context switches into a single one by using PL/SQL Bulk Collect with SELECT-INTO statement. SELECT-INTO statement is a SQL standard query which means developer does not have much control over the execution of the statement.
If we talk about query performance, we cannot go beyond an extent with SELECT-INTO statement. Let’s see how we can overcome all these shortcomings of SELECT-INTO statement.
In this blog you will learn how to use Bulk Collect clause with FETCH-INTO statement of an explicit cursor. As FETCH statements are part of cursor life cycle thus a working knowledge of explicit cursor is required for the better understanding of this tutorial. For the same you can check out the tutorial on how to create an explicit cursor in Oracle database.
Why should we use Bulk Collect clause with FETCH INTO statement?
As mentioned in the previous blog whenever we use Bulk-Collect with SELECT-INTO statement the runtime engine uses implicit cursor to process the task. But if we use Bulk Collect with FETCH-INTO statement then the runtime engine will use the explicit cursor to process the task.
An explicit cursor always helps us in getting advance control over our standard SQL queries. For example with an explicit cursor we can control when to fetch the records or how many records we want to retrieve at once however this is not possible in case of SELECT-INTO statement.
What is the syntax of Bulk Collect with Fetch-Into statement?
Similar to SELECT-INTO statement, bulk collect clause works as an attribute to the FETCH-INTO statement. Here is the syntax
FETCH <cursor_name> BULK COLLECT INTO <plsql_collection>;
FETCH statements are part of explicit cursor. If you try to execute them without declaring their parent cursor then you will get an error. Also always remember that PL/SQL collections are the only supported structure for bulk collect.
Example: How to use PL/SQL Bulk Collect with FETCH-INTO statement in Oracle Database.
SET SERVEROUTPUT ON; DECLARE --Create an explicit cursor CURSOR exp_cur IS SELECT first_name FROM employees; --Declare collection for holding the data TYPE nt_fName IS TABLE OF VARCHAR2 (20); fname nt_fName; BEGIN OPEN exp_cur; LOOP FETCH exp_cur BULK COLLECT INTO fname; EXIT WHEN fname.count=0; --Print data FOR idx IN fname.FIRST.. fname.LAST LOOP DBMS_OUTPUT.PUT_LINE (idx||' '||fname(idx) ); END LOOP; END LOOP; CLOSE exp_cur; END; /
I have explained every single line of this entire code in detail in the video tutorial on my YouTube channel. You can watch this tutorial.
Since in this code we have used Bulk Collect clause with the fetch into statement it means that context switching will not be a problem. As we know bulk collect clause compresses multiple switches into a single one so no further performance bottle neck.
But does it mean this code is well optimized & needs no further optimization? Or for that matter Query performance will never be an issue for us in the future?
Stay tuned, to get the answers to all these questions as well as to learn how we can further optimize this PL/SQL program.