PL/SQL expressions are evaluated by the primary SQL engine, not the PL/SQL engine itself. Understanding this mechanism helps you diagnose unexpected behavior and write expressions correctly.
How it works
When PL/SQL encounters an expression — for example, in an IF statement:
IF expression THEN ...It sends the following query to the primary SQL engine:
SELECT expressionPL/SQL replaces each variable name in the expression with a positional parameter. This means the execution plan for the SELECT is prepared once and reused for all subsequent evaluations with different variable values.
On the first use of an expression, PL/SQL runs the equivalent of a PREPARE statement. For example, given two integer variables x and y:
IF x < y THEN ...The first execution is equivalent to:
PREPARE statement_name(integer, integer) AS SELECT $1 < $2;On every subsequent execution of the IF statement, the prepared plan is reused with the current values of x and y.
These implementation details are not important for day-to-day PL/SQL development, but are useful when troubleshooting expression evaluation issues.