PL/SQL bulk collect

Definition of PL/SQL bulk collect

PL/SQL provides different types of functionality to users; the bulk collection is one of the functionalities that is provided PL/SQL. Normally oracle provides the fetching functionality records in bulk instead of one by one. In which we use select statements that are used to fetch or retrieve multiple records in a single statement. PL/SQL bulk collection is used to improve the speed of data retrieval or we can say we can fetch records at high speed. Basically, bulk collection is used to reduce the switching time between the SQL and PL/SQL engine to fetch the records.

Syntax:

select colm name1, colm name2, colm name N bulk collect into specified bulk variable name from specified table name;
fetch specified cursor name bulk collect into specified bull variable name;

Explanation

In the above syntax, we use select statements with different parameters as follows.

• Colm name1: It is used to specify the column name that we need to print from the specified table.
• specified bulk variable name: It is used for a bulk variable name that depends on the user.
• specified table name: The specified table means the actual name that is created by the user.
• Collect and into: It is keywords used to implement the bulk collect.

After that, we use the fetch statement to collect records in a bulk manner as shown in the above syntax.

How bulk collect works in PL/SQL?

Now let’s see how bulk collect works in PL/SQL as follows.

Simply insert BULK COLLECT before the INTO keyword of your fetch operation, and then supply one or more collections after the INTO keyword to make use of bulk processing for queries.
The following are some details regarding how BULK COLLECT works:

• It works with associative arrays, nested tables, and VARRAYs, among other collections.
• Individual collections (one for each SELECT list expression) or a single collection of records can be fetched.
• The collection is emptied of all items if no rows are obtained.

Attributes of Bulk Collect

It uses the same attributes as the cursor and it has a % bulk rowcount. This attribute returns the number of rows from the table that means it shows the total number of counts. The word ‘n’ denotes the collection’s value sequence for which the row count is required.

Bulk Collect Operation

A developer will often use a cursor to obtain and analyses numerous rows of data one at a time, however, cursors have performance issues when working with huge numbers of rows. A cursor retrieves one row at a time, maintaining a consistent view until all rows have been obtained or the cursor is closed, as we’ve seen.
The fact that the database has two engines, the PL/SQL engine and the SQL engine, causes a performance concern. Because these engines have varied capabilities in various versions of the database, some functions are available in SQL but not in PL/SQL. When a cursor retrieves a row of data, it makes a “context switch” to the SQL engine, and the data is retrieved by the SQL component. The data is stored in memory by the SQL engine, and we return to the PL/SQL engine via another context switch.

The PL/SQL engine in the Oracle database may execute a bulk collection for you. A cursor loop in Oracle 10g and later may force the PL/SQL engine to bulk collect 100 rows at a time, allowing your code to handle rows without having to set up and run the bulk collect procedure. Bulk collecting 75 rows may not offer you much of again as a consequence of this performance boost in 10g, but bulk collecting huge numbers of rows (many hundreds) will still provide you with enhanced performance.
Data collection in bulk is simple. To begin, we must first identify the collection or collections that will be used in the bulk collection. After that, create a cursor to fetch the data, and then bulk gathers the data into collections.

Examples

Now let’s see different examples of Bulk Collect in PL/SQL for better understanding as follows. First, we need to create a new table by using the create table statement as follows.

create table student(stud_id number(10) not null, stud_name varchar2(30) not null, stud_city varchar2(30));

Explanation

By using the above statement we created a new table name as a student. The final output of the above statement we illustrated by using the below screenshot is as follows.

Now insert some records by using insert into the statement as follows.

insert into student(stud_id, stud_name, stud_city) values(101,'Jenny','Mumbai');
insert into student(stud_id, stud_name, stud_city) values(102,'Johan','Mumbai');
insert into student(stud_id, stud_name, stud_city) values(103,'Pooja','London');
insert into student(stud_id, stud_name, stud_city) values(104,'Sameer','London');
insert into student(stud_id, stud_name, stud_city) values(105,'Rohit', 'London');
select * from student;

Explanation

By using the above statement we inserted five records as shown in the below screenshot as follows.

Now everything is ready to perform the bulk collection as follows.

SET SERVEROUTPUT ON;
DECLARE
TYPE info_student IS TABLE OF VARCHAR2(30);
s_name info_student;
BEGIN
SELECT stud_name BULK COLLECT INTO s_name from student;
FOR idx IN 1..s_name.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(idx||'-'||s_name(idx));
END LOOP;
END;
/

Explanation

In the above example, we try to implement the bulk collect with INTO clause. Here we first set the serveroutput on as shown in the above example. In the declaration section, we created the collection name as info_student with varchar2 data types. In the next section is the execution section. We write the SQL statement with bulk collect keyword to fetch the records from the student table, here we fetch the stude_name from the student table and store it into the collection. After that, we write for a loop to display all records from the student table. In this example, we try to implement the select with into clause. The final output of the above statement we illustrated by using the below screenshot as follows.

Similarly, we can implement bulk collect with, fetch into, return into as per our requirement.

Conclusion

We hope from this article you learn PL/SQL Bulk Collect. We have learned the basic syntax of Bulk Collect and we also see different examples of Bulk Collect. From this article, we learned how and when we use PL/SQL Bulk Collect.

Recommended Articles

This is a guide to PL/SQL bulk collect. Here we discuss the Definition, syntax, How bulk collect works in PL/SQL? with Examples and code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL to_DATE
2. PL/SQL Cursor Loop
3. PL/SQL TRIM
4. PL/SQL Date Functions