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.
Top comments (0)