Explain BULK COLLECT in PL/SQL

How it works

BULK COLLECT INTO fetches an entire result set (or a FORALL DML's RETURNING clause) into a collection in a single round trip between the PL/SQL and SQL engines, instead of looping and fetching one row at a time. It's the main lever for cutting context-switch overhead when processing many rows — at the cost of holding the whole result set in PGA memory, unless it's paired with LIMIT.

DECLARE
  TYPE t_name_tab IS TABLE OF employees.last_name%TYPE;
  v_names t_name_tab;
BEGIN
  SELECT last_name BULK COLLECT INTO v_names
  FROM employees WHERE department_id = 90;

  FOR v_i IN 1..v_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_names(v_i));
  END LOOP;
END;
/

Related Questions