Using the IF clause in PL/SQL

Now that you have learned about the IF clause, how about building a PL/SQL block using an IF clause? In the following simulation, you are required to build a PL/SQL block that will calculate the sales commission for goods sold in the pet store. The sales commission is calculated as follows:

1. If the sale value is less than $100, the sales commission is calculated as 10% of the value of the sale.
2. If the sale value is more than $100 and less than $500, the sales commission is calculated as 15% of the value of the sale.
3. If the sale value is more than $500 and less than $1,000, the sales commission is calculated as 25% of the value of the sale.
4. If the sale value is more than $1,000, the sales commission is calculated as 25% of the value of the sale.

• Hint
  You will use the ACCEPT with PROMPT SQL*Plus command to accept values from the user. You will display the calculated commission by using DBMS_OUTPUT.PUT_LINE (string_value).
  The code for the IF clause required for the above calculation would be as follows:

IF v_sale_value < 100 Then
 v_commission_percent := 10;
ELSIF v_sale_value > 100  AND v_sale_value < 500 Then
 v_commission_percent := 15;
ELSIF v_sale_value > 500  AND v_sale_value < 1000 Then
 v_commission_percent := 20;
ELSE 
 v_commission_percent := 25;
END IF;

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_comYeah that much coffee or somebody else going on i'm not sure mission 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
  


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.

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.

Here is an example of a "PL/SQL block" using a CASE statement within the context of Oracle 19c. This example demonstrates a simple use case where the `CASE` statement determines an employee's bonus category based on their performance rating:

DECLARE
    v_employee_id   NUMBER := 101; -- Example employee ID
    v_performance   VARCHAR2(20); -- Variable to store performance category
    v_bonus         NUMBER;       -- Variable to store bonus amount
BEGIN
    -- Example performance rating
    v_performance := 'Excellent'; -- Assume fetched from a table

    -- Using CASE statement to determine bonus
    v_bonus := CASE 
                  WHEN v_performance = 'Excellent' THEN 1000
                  WHEN v_performance = 'Good' THEN 750
                  WHEN v_performance = 'Average' THEN 500
                  ELSE 0
               END;

    -- Output the result
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Performance: ' || v_performance);
    DBMS_OUTPUT.PUT_LINE('Bonus: $' || v_bonus);
END;
/

Explanation:

1. Declare Section:
   • Declares variables for employee ID, performance rating, and bonus amount.
2. Assign Performance Rating:
   • The performance rating (`v_performance`) is assigned directly in the example but could also be fetched from a database table.
3. CASE Statement:
   • The CASE statement evaluates the value of v_performance and assigns the corresponding bonus amount to v_bonus.
4. Output:
   • The DBMS_OUTPUT.PUT_LINE procedure displays the results in the SQL*Plus or SQL Developer output console.

Example Execution:
When the block is executed, assuming `v_performance` is `Excellent`, the output will be:

Employee ID: 101
Performance: Excellent
Bonus: $1000

This example can be modified to use table data, incorporate dynamic SQL, or integrate with more complex business logic.
In the next lesson, you will learn about loops within PL/SQL.