Bulk Collect can be used with both SELECT INTO and FETCH statements in PL/SQL. Each of these methods has its specific use case for bulk fetching data from the database into PL/SQL collections.

1. Bulk Collect with SELECT INTO

The SELECT INTO statement is used when you need to fetch all the data in one go into a collection. This method is efficient for bulk data retrieval when you know that the dataset is small or manageable in memory.

Syntax

SELECT column1, column2, ...
BULK COLLECT INTO collection_variable
FROM table_name
WHERE condition;

Example

Let's demonstrate Bulk Collect with SELECT INTO using the same employees table created earlier.

Step 1: Creating the Employees Table

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);

INSERT INTO employees VALUES (1, 'John', 'Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO employees VALUES (3, 'Mike', 'Johnson', 55000);
INSERT INTO employees VALUES (4, 'Emily', 'Davis', 70000);
INSERT INTO employees VALUES (5, 'Chris', 'Brown', 45000);

COMMIT;

Step 2: Bulk Collect with SELECT INTO

DECLARE
    TYPE employee_tab IS TABLE OF employees%ROWTYPE;
    employees_data employee_tab;  -- Collection to hold employee data

BEGIN
    -- Bulk Collect using SELECT INTO to fetch all records from the employees table
    SELECT * BULK COLLECT INTO employees_data FROM employees;

    -- Loop through the collection and display the data
    FOR i IN 1 .. employees_data.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || employees_data(i).employee_id ||
                             ', Name: ' || employees_data(i).first_name || ' ' || employees_data(i).last_name ||
                             ', Salary: ' || employees_data(i).salary);
    END LOOP;
END;
/

Explanation

Collection Declaration: A collection type employee_tab is defined, which holds multiple rows of employee data.

Bulk Collect: The SELECT * BULK COLLECT INTO retrieves all the data from the employees table and stores it into the employees_data collection.

Processing the Collection: A FOR loop is used to iterate through the employees_data collection and print each employee's details.

Output

The output will be the same as previously shown:

ID: 1, Name: John Doe, Salary: 50000
ID: 2, Name: Jane Smith, Salary: 60000
ID: 3, Name: Mike Johnson, Salary: 55000
ID: 4, Name: Emily Davis, Salary: 70000
ID: 5, Name: Chris Brown, Salary: 45000

2. Bulk Collect with Cursors (FETCH INTO)

If the dataset is too large to be fetched in one go (e.g., memory issues), you can use a cursor with FETCH and BULK COLLECT INTO to retrieve smaller chunks of data repeatedly. This is known as bulk fetching in batches.

Syntax

OPEN cursor_name FOR
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

LOOP
    FETCH cursor_name BULK COLLECT INTO collection_variable LIMIT n;  -- n is the batch size
    EXIT WHEN collection_variable.COUNT = 0;

    -- Process the collection
END LOOP;

CLOSE cursor_name;

Example: Fetching in Batches

Here’s an example demonstrating how to use Bulk Collect with FETCH INTO to retrieve data in batches.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary FROM employees;

    TYPE employee_tab IS TABLE OF employees%ROWTYPE;
    employees_data employee_tab;  -- Collection to hold the fetched data

    batch_size CONSTANT NUMBER := 2;  -- Batch size (limit the number of rows fetched at a time)

BEGIN
    OPEN emp_cursor;

    LOOP
        -- Fetch rows in batches of 'batch_size' into the collection
        FETCH emp_cursor BULK COLLECT INTO employees_data LIMIT batch_size;

        -- Exit the loop if no more rows are fetched
        EXIT WHEN employees_data.COUNT = 0;

        -- Process the batch of data
        FOR i IN 1 .. employees_data.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('ID: ' || employees_data(i).employee_id ||
                                 ', Name: ' || employees_data(i).first_name || ' ' || employees_data(i).last_name ||
                                 ', Salary: ' || employees_data(i).salary);
        END LOOP;
    END LOOP;

    CLOSE emp_cursor;
END;
/

Explanation

Cursor Declaration: A cursor emp_cursor is declared to retrieve employee data from the employees table.

Batch Size: A constant batch_size is declared, which specifies how many rows to fetch at a time (in this case, 2).

Loop and Fetch: The cursor is opened, and the FETCH BULK COLLECT INTO statement fetches a batch of rows (as per LIMIT batch_size) into the employees_data collection.

Exit Condition: The loop exits when no more rows are fetched (i.e., employees_data.COUNT = 0).

Processing Each Batch: A FOR loop processes the fetched rows in each batch.

Close the Cursor: After the data is processed, the cursor is closed.

Output

Since the batch_size is set to 2, it will fetch and display 2 rows at a time:

ID: 1, Name: John Doe, Salary: 50000
ID: 2, Name: Jane Smith, Salary: 60000
ID: 3, Name: Mike Johnson, Salary: 55000
ID: 4, Name: Emily Davis, Salary: 70000
ID: 5, Name: Chris Brown, Salary: 45000

Each time, the data is fetched in batches of 2 records, until all records are retrieved and displayed.

Conclusion

Bulk Collect with SELECT INTO: This method is efficient when you want to fetch all the data at once and load it into a collection for processing. It is suitable for scenarios where the data size is manageable in memory.

Bulk Collect with FETCH INTO (using cursors): This method is used for fetching data in smaller batches, which is useful when dealing with large datasets that may cause memory overflow or when you want to process data incrementally.

Both methods improve performance by minimizing context switches between the SQL engine and PL/SQL engine, making them highly effective for data retrieval in PL/SQL programs.