BULK COLLECT / LIMIT CLAUSE

BULK COLLECT:

Bulk collect can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database.

... BULK COLLECT INTO collection_name[, collection_name] ...

We can use BULK COLLECT keywords in any of the following clauses:
1.SELECT INTO
2.FETCH INTO
3.RETURNING INTO

SELECT...BULK COLLECT will not raise NO_DATA_FOUND if no rows are found.
Instead, we must check the contents of the collection to see if there is any data inside it.

The BULK COLLECT operation empties the collection referenced in the INTO clause before executing the query. If the query returns no rows, this collection's COUNT method will return 0.

Examples:

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
Begin
  select * bulk collect into my_test_collection 
  from source_daily;
  dbms_output.put_line(my_test_collection.count);
End;/

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
  Cursor test_cur is
    select * 
    from source_daily;
Begin  Open test_cur;
  Fetch test_cur
              bulk collect into my_test_collection;
  Dbms_output.put_line(my_test_collection.count);
End;/


LIMIT:

LIMIT clause for BULK COLLECT allows us to limit the number of rows fetched from the database.

FETCH cursor_name BULK COLLECT INTO ... [LIMIT rows];

where rows can be any literal, variable, or expression that evaluates to an integer.

LIMIT is very useful with BULK COLLECT, because it helps us to manage how much memory our program will be used to process the data.

[rows] this many number of rows that will be fetched into collections per single trip.

For every iteration the collections will be refreshed with the new fetched rows.
Once all the rows are done, the count on the collection will be zero and we can exit the fetch loop.

Example:

create table limit_test (rno number);

-- Populate test data
Begin
  For i in 1..10004
   loop
     insert into limit_test values (i);
   end loop;
  commit;
End;
/

Declare
  Type limit_test_t is table of limit_test%rowtype;
  limit_collection limit_test_t;
  Cursor c1 is select * from limit_test;
Begin 
  Open c1;
-- Bulk collect without any limit
  Fetch c1 bulk collect into limit_collection;
  Close c1;
  dbms_output.put_line(limit_collection.count);

-- Reopen the cursor & use limit
  open c1;
  loop
    fetch c1 bulk collect into limit_collection limit 30;
    exit when limit_collection.count=0;
    dbms_output.put_line(limit_collection.count);
  end loop;
End;/

RETURNING Clause : (With Bulk collect)

We can use the RETURNING clause within the DML (UPDATE/DELETE) to grab the modified info
by the statement into collections.

1.Execute the DML Statement and Grab the modified values into Collections Via Bulk Collect on the fly.
2. Use the variables in the code.

Note:

If the Update/Delete affects only one row, we can use the RETURNING Clause and grab the modified data into the Scalar variables.

If the Update/Delete affects multiple rows, we need to use BULK COLLECT and COLLECTION to grab the modified data using the RETURNING Clause.