Bulk collection of PL / SQL

Bulk collection of PL / SQL

Defining bulk collection PL / SQL

PL / SQL provides different types of functions to users; the bulk collection is one of the functionalities that PL / SQL provides. Typically, Oracle provides the functionality to search for records in bulk rather than one by one. In which we use select statements that are used to find or retrieve multiple records in a single statement. PL / SQL bulk collection is used to improve data retrieval speed or we can say we can search logs at high speed. Basically, bulk collection is used to reduce the switching time between the SQL engine and PL / SQL to retrieve 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 name of the column that we need to print from the specified table.
  • Bulk Variable Name Specified – Used for a bulk variable name that is user dependent.
  • specified table name: the specified table means the actual name created by the user.
  • Collect and Enter – These are the keywords used to implement bulk collection.

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

How does bulk collection work in PL / SQL?

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

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

  • It works with associative arrays, nested tables, and VARRAY, among other collections.
  • You can retrieve individual collections (one for each SELECT list expression) or a single collection of records.
  • The collection is flushed of all items if no rows are returned.

Bulk Collect Attributes

It uses the same attributes as the cursor and has a massive% row count. This attribute returns the number of rows in the table, which means that it shows the total number of counts. The word ‘n’ denotes the sequence of values ​​in the collection for which the row count is required.

Mass collection operation

A developer will often use a cursor to obtain and analyze numerous rows of data one at a time; however, cursors have performance issues when working with a large number of rows. A cursor retrieves one row at a time, maintaining a consistent view until all rows have been fetched or the cursor has been closed, as we have seen.
The fact that the database has two engines, the PL / SQL engine and the SQL engine, causes performance problems. 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 performs a “context switch” to the SQL engine and the SQL component retrieves the data. The SQL engine stores the data in memory and we return to the PL / SQL engine through another context switch.

The Oracle database PL / SQL engine can perform a bulk collection for you. A cursor loop in Oracle 10g and later can force the PL / SQL engine to collect 100 rows at a time, allowing your code to handle rows without having to configure and run the bulk collect procedure. Bulk harvesting of 75 rows may not get you much new as a result of this 10g performance increase, but bulk harvesting of large numbers of rows (many hundreds) will still give you improved performance.
Bulk data collection is easy. To get started, we must first identify the collection or collections to be used in the bulk collection. After that you create a cursor to get the data and then bulk collect it into collections.

Examples of

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

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

Explanation

Using the above statement, we create a new table name as a student. The end result of the above statement that we illustrate using the screenshot below is as follows.

PL SQL bulk collection 1

Now insert some records using insert in declaration 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

Using the above declaration, we insert five records as shown in the following screenshot as follows.

PL SQL bulk collection 2

Now everything is ready to do 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 example above, we tried to implement bulk collection with the INTO clause. Here we first configure the server output as shown in the example above. In the declaration section, we create the collection name as info_student with varchar2 data types. In the next section is the execution section. We write the SQL statement with the bulk collection keyword to get the records from the student table, here we get the stude_name from the student table and store it in the collection. After that we write a loop to show all the records from the student table. In this example, we are trying to implement the select with into clause. The final result of the above statement we illustrate using the screenshot below as follows.

PL SQL Bulk Collect 3

Similarly, we can implement bulk collection with, retrieve, and return as per our requirement.

Conclution

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

Recommended Articles

This is a guide to bulk collection of PL / SQL. Here we discuss the Definition, the syntax, How does bulk collection work in PL / SQL? with examples and code implementation. You can also take a look at the following articles to learn more:

  1. PL / SQL until el_DATE
  2. PL / SQL cursor loop
  3. TRIM PL / SQL
  4. PL / SQL date functions

Subsequent PL / SQL bulk collection appeared first on EDUCBA.

Leave a Comment

x