BULK COLLECT in PL/SQL

Certainly! Here’s a simple example to demonstrate the use of BULK COLLECT in PL/SQL.

What is Bulk Collect?

BULK COLLECT is a method used to fetch multiple rows from a SQL query into a PL/SQL collection in a single operation. This reduces context switches between the SQL and PL/SQL engines, making the process more efficient, especially for large datasets.

Simple Example of Bulk Collect

In this example, we'll create a table, insert some data into it, and then use BULK COLLECT to fetch the data into a collection.

Step 1: Create a Sample Table

First, we create a sample table called employees.

CREATE TABLE employees (
employee_id NUMBER,
employee_name VARCHAR2(50)
);

INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe');
INSERT INTO employees (employee_id, employee_name) VALUES (2, 'Jane Smith');
INSERT INTO employees (employee_id, employee_name) VALUES (3, 'Sam Wilson');
INSERT INTO employees (employee_id, employee_name) VALUES (4, 'Sara Brown');
INSERT INTO employees (employee_id, employee_name) VALUES (5, 'Mike Johnson');

COMMIT;

Step 2: PL/SQL Block Using Bulk Collect

Now, we will use BULK COLLECT to fetch all employee names into a collection.

DECLARE
TYPE emp_name_table IS TABLE OF VARCHAR2(50); -- Define a collection type
emp_names emp_name_table; -- Declare a variable of that type
BEGIN
-- Bulk collect employee names into the collection
SELECT employee_name BULK COLLECT INTO emp_names
FROM employees;

-- Print the names
FOR i IN 1..emp_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_names(i));
END LOOP;

END;
/

Explanation

1. Define a Collection Type: We define a collection type called emp_name_table to hold employee names.

2. BULK COLLECT INTO: The SELECT statement fetches all employee_name values from the employees table and collects them into the emp_names collection in one go.

3. Loop to Display Results: We loop through the collection using COUNT to get the total number of entries and print each employee name.

Output

If you run the above PL/SQL block, you will see output like this:

Employee Name: John Doe
Employee Name: Jane Smith
Employee Name: Sam Wilson
Employee Name: Sara Brown
Employee Name: Mike Johnson

Key Points

Efficiency: Using BULK COLLECT reduces the number of context switches between SQL and PL/SQL, making it more efficient for processing large sets of data.

Handling Large Data: It's particularly useful when you're working with large datasets, as it minimizes the overhead of individual row processing.

This example illustrates the basic use of BULK COLLECT to efficiently gather multiple rows into a PL/SQL collection.