| Lesson 3 | Using the IF clause |
| Objective | Build a PL/SQL block with the IF clause. |
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:
- If the sale value is less than $100, the sales commission is calculated as 10% of the value of the sale.
- 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.
- 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.
- 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;