Question 23

Explanation

Correct option: They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

Breakdown of the Query

1. INSERT ALL

• This is an Oracle-specific syntax used to insert rows into multiple tables based on conditions.

• It's often referred to as conditional multitable insert.

• It’s different from a regular INSERT INTO because it can conditionally insert into different tables using WHEN ... THEN.

2. WHEN ... THEN INTO ...

These clauses define the conditions that determine which table the row should be inserted into.

• WHEN order_total < 10000 THEN INTO small_orders
  → If the order_total is less than 10,000, insert the row into the small_orders table.

• WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
  → If the order_total is between 10,001 and 19,999, insert it into medium_orders.

• WHEN order_total > 20000 THEN INTO large_orders
  → If the order_total is greater than 20,000, insert into large_orders.

Note: There’s no ELSE clause, so if a row doesn’t match any condition, it is not inserted anywhere.

3. SELECT ... FROM orders

• This part is a subquery that retrieves the rows to evaluate against the WHEN conditions.

• It selects:

  • order_id

  • order_total

  • customer_id

• These are the columns that will be inserted into the matching table(s), depending on the WHEN clauses.

The SQL statement in Exhibit 1 uses Oracle's INSERT ALL syntax with conditional WHEN clauses, which is used for conditional multitable inserts.

How Conditional INSERT ALL Works in Oracle:

• Each row returned by the SELECT is evaluated independently against each WHEN condition.

• The evaluation is not exclusive (i.e., it's not like IF...ELSE IF...ELSE logic).

• A row may match multiple WHEN conditions (if allowed by the logic).

• Oracle evaluates all WHEN clauses independently, and multiple inserts can happen for the same row if multiple conditions are true.

Why the Others Are Incorrect:

• "Evaluated by the first WHEN clause only" →  False: Oracle doesn't stop after the first match.

• "Else clause is required" →  False: The ELSE clause is optional; if no WHEN matches, the row is just not inserted.

• "Evaluated only if the first fails" →  Also incorrect because all WHEN clauses are always considered.

From Oracle Docs: “All WHEN clauses are evaluated for each row returned by the subquery. If more than one WHEN condition evaluates to true, then multiple INTO clauses can be executed for the same row.”

The Oracle WHEN keyword is used in two ways. One use is in the CASE statement to pick among given values. The other use of WHEN is in a conditional INSERT statement.

Using WHEN in the CASE statement
The CASE statement operates like a series of IF statements, only using the key word WHEN.

Example Usage:
An example of WHEN used in a CASE statement that reports a temperature range:

CASE
WHEN n_temperature = 10 THEN v_status := 'very low';
WHEN n_temperature < 20 THEN v_status := 'low';
WHEN n_temperature = 50 THEN v_status := 'medium';
WHEN n_temperature > 80 THEN v_status := 'high';
ELSE v_status := 'very high';
END CASE;

Using WHEN in a conditional INSERT statement

There are two modes for conditional insert: ALL and FIRST.

If you specify ALL, the default value, then the database evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the database executes the corresponding INTO clause list.

√  They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

Example Usage:

INSERT
WHEN MOD(deptno,2)=0 THEN
INTO even_employees (empno, ename)
VALUES (empno, ename)
 
WHEN MOD(deptno,2)=1 THEN
INTO uneven_employees (empno, ename)
VALUES (empno, ename)
 
ELSE
INTO unknow_employees (empno, ename)
VALUES (empno, ename)
SELECT empno, ename, deptno FROM emp;