Oracle BULK COLLECT

Oracle BULK COLLECT is a SQL statement in the Oracle Specific category. Oracle: fetches multiple rows into a PL/SQL collection in one operation. Much faster than row-by-row FETCH. The syntax is SELECT column BULK COLLECT INTO collection FROM table;. It returns bulk collected data. A typical example: DECLARE TYPE name_list IS TABLE OF employees.name%TYPE; names name_list; BEGIN SELECT name BULK COLLECT INTO names FROM employees WHERE department_id = 20; FOR i IN 1..names.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Employee: ' || names(i)); END LOOP; END; / -- BULK COLLECT with LIMIT (pagination): DECLARE CURSOR emp_cur IS SELECT * FROM employees; TYPE emp_tab IS TABLE OF employees%ROWTYPE; emp_batch emp_tab; BEGIN OPEN emp_cur; LOOP FETCH emp_cur BULK COLLECT INTO emp_batch LIMIT 100; EXIT… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).