Control Structures  «Prev 

PL/SQL IF clause

  1. Connect to the database, using PETSTORE as the user name, GREATPETS as the password, and MYDB as the host string. Click the OK button to continue.
  2. To display the result of the PL/SQL block on the screen, set the server output on.
  3. Use the ACCEPT with PROMPT SQL*Plus command to accept values from the user to a variable. Name this variable av_sale_value. Prompt the user with the message Please enter the sale value.
  4. Next, begin the main block with the DECLARE statement.
  5. Next, declare a variable of type NUMBER and call it v_sale_value. Assign it the value with the av_sale_value variable.
  6. Next, declare a variable of type NUMBER and call it v_commission_percent.
  7. Next, declare a variable of type NUMBER and call it v_commission.
  8. Start the executable section of the block.
  9. Build the IF clause to calculate the sales commission.
  10. Calculate the commission and assign it to v_commission.
  11. Convert v_commission to a string by using the TO_CHAR function and display it using the dbms_output.put_line package procedure.
  12. End the block.
  13. Execute your block.
  14. SQL*Plus now displays the result of the compilation of your block.

The IF-THEN-ELSIF Combination

This last form of the IF statement comes in handy when you have to implement logic that has many alternatives; it is not an either/or situation. The IF-ELSIF formulation provides a way to handle multiple conditions within a single IF statement. In general, you should use ELSIF with mutually exclusive alternatives (i.e., only one condition can be TRUE for any execution of the IF statement). The general format for this variation of IF is: 

IF condition-1
THEN
statements-1
ELSIF condition-N
THEN
statements-N
[ELSE
else_statements]
END IF;
IF

Beginning Oracle PL/SQL
Warning: Be very careful to use ELSIF, not ELSEIF. The inadvertent use of ELSEIF is a fairly common syntax error. ELSE IF (two words) does not work either.

CASE statement

Logically speaking, the IF-THEN-ELSIF construct is one way to implement CASE statement functionality in PL/SQL. Of course, if you are using Oracle9i Database onwards, you are probably better off actually using a CASE statement. Each ELSIF clause must have a THEN after its condition. Only the ELSE keyword does not need the THEN keyword. The ELSE clause in the IF-ELSIF is the otherwise of the statement. If none of the conditions evaluate to TRUE, the statements in the ELSE clause are executed. But the ELSE clause is optional. You can code an IF-ELSIF that has only IF and ELSIF clauses. In such a case, if none of the conditions are TRUE, no statements inside the IF block are executed.

Oracle Database PL/SQL Programming