**FORALL**: FORALL index IN lower..upper DML_statement;

**BULK COLLECT**: SELECT columns BULK COLLECT INTO collection FROM table;

**EXECUTE IMMEDIATE**: EXECUTE IMMEDIATE sql_string [INTO variable] [USING bind_values];

**Associative Array**: TYPE name IS TABLE OF type INDEX BY key_type;

**Nested Table**: TYPE name IS TABLE OF type;

**VARRAY**: TYPE name IS VARRAY(size) OF type;

**Pipelined Function**: FUNCTION name RETURN collection_type PIPELINED AS BEGIN ... PIPE ROW(value); ... RETURN; END;

**PRAGMA**: PRAGMA AUTONOMOUS_TRANSACTION; (at procedure/function level)

FORALL: FORALL index IN lower..upper DML_statement; (bulk DML)

BULK COLLECT: SELECT columns BULK COLLECT INTO collection FROM table; (bulk SELECT)

EXECUTE IMMEDIATE: EXECUTE IMMEDIATE sql_string [INTO variable] [USING bind_values]; (dynamic SQL)

Collections: TYPE name IS TABLE OF type INDEX BY key_type; (associative array)

Pipelined: FUNCTION name RETURN collection_type PIPELINED AS BEGIN ... PIPE ROW(value); END;

PRAGMA AUTONOMOUS_TRANSACTION: Creates independent transaction (for logging)

LIMIT: Use FETCH FIRST n ROWS ONLY with BULK COLLECT to control batch size

Bind variables: Always use :1, :2 in EXECUTE IMMEDIATE to prevent SQL injection

FORALL, BULK COLLECT, EXECUTE IMMEDIATE, pipelined functions, PRAGMA AUTONOMOUS_TRANSACTION, collections (associative arrays, nested tables, VARRAYs)

Table variables, temp tables, sp_executesql for dynamic SQL, table-valued functions (not pipelined), no PRAGMA

FOREACH for arrays, EXECUTE for dynamic SQL, RETURN NEXT for set-returning functions (similar to pipelined), no PRAGMA

Limited array support, PREPARE/EXECUTE for dynamic SQL, no pipelined functions, no PRAGMA

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE