Oracle PL SQL Tutorial day 9
Conditional statements are at the heart of any programming or scripting language. In Oracle PL/SQL, conditional logic allows developers to control the flow of execution based on logical decisions. These constructs enable your PL/SQL programs to respond dynamically to various data and operational states, making your code more powerful, flexible, and intelligent.
In this comprehensive guide, we will explore:
1. Introduction to Conditional Statements
2. Types of Conditional Statements in PL/SQL
3. The IF-THEN Statement
4. IF-THEN-ELSE Statement
5. IF-THEN-ELSIF-ELSE Statement
6. The CASE Statement (Simple and Searched)
7. Nested Conditional Statements
8. Examples Using HR Schema
9. Best Practices
10. Conclusion
1. Introduction to Conditional Statements
----------------------------------------
In PL/SQL, conditional statements evaluate Boolean expressions and execute code blocks based on whether the condition is true or false. These are essential when dealing with decision-making scenarios, such as validations, status checks, and branching workflows.
2. Types of Conditional Statements in PL/SQL
--------------------------------------------
Oracle PL/SQL supports the following types of conditional statements:
- IF-THEN
- IF-THEN-ELSE
- IF-THEN-ELSIF
- CASE (Simple and Searched)
Each serves a specific purpose depending on how many conditions you need to evaluate and the nature of those conditions.
3. The IF-THEN Statement
------------------------
This is the most basic form. It executes the block of code if the condition is true.
Syntax:
IF condition THEN
-- statements
END IF;
Example:
DECLARE
v_salary NUMBER := 3000;
BEGIN
IF v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is below average.');
END IF;
END;
In above example code we are checking the value and print a message if condition is true.
If the condition false it will not execute the code inside the condition.
We put only one statement but multiple statement inside if condition can be added. It could be any statement like insert,update, delete, any procedure or function calling etc.
4. IF-THEN-ELSE Statement
-------------------------
This statement provides an alternative block of code if the condition is false.
Syntax:
IF condition THEN
-- statements if true
ELSE
-- statements if false
END IF;
Example:
DECLARE
v_salary NUMBER := 6000;
BEGIN
IF v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is below average.');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is average or above.');
END IF;
END;
Here we try to get results if condition true otherwise the Else part execute always.
5. IF-THEN-ELSIF-ELSE Statement
-------------------------------
This is used when you have multiple conditions to evaluate sequentially.
Syntax:
IF condition1 THEN
-- block1
ELSIF condition2 THEN
-- block2
ELSE
-- default block
END IF;
Example:
DECLARE
v_salary NUMBER := 8000;
BEGIN
IF v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is below average.');
ELSIF v_salary BETWEEN 5000 AND 7000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is average.');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is above average.');
END IF;
END;
Here we try to compare multiple values and get the results if both condition false then ELSE part will execute always.
6. The CASE Statement
The case statement is another alternative of IF conditions. In this case expression remain same but comparison change or check many times. The case can be used in oracle SQL as well. Let's look some example beliw
a. Simple CASE
Compares a single expression against a list of values.
Syntax:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE result_default
END;
Example:
DECLARE
v_grade CHAR(1) := 'B';
BEGIN
CASE v_grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Average');
ELSE DBMS_OUTPUT.PUT_LINE('Fail');
END CASE;
END;
b. Searched CASE
Evaluates multiple Boolean expressions.
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END;
Example:
DECLARE
v_score NUMBER := 72;
BEGIN
CASE
WHEN v_score >= 90 THEN DBMS_OUTPUT.PUT_LINE('Grade: A');
WHEN v_score >= 75 THEN DBMS_OUTPUT.PUT_LINE('Grade: B');
WHEN v_score >= 60 THEN DBMS_OUTPUT.PUT_LINE('Grade: C');
ELSE DBMS_OUTPUT.PUT_LINE('Grade: F');
END CASE;
END;
7. Nested Conditional Statements
You can nest IF or CASE statements inside one another when complex decision logic is required.
Example:
DECLARE
v_dept_id NUMBER := 60;
v_job_id VARCHAR2(20) := 'IT_PROG';
BEGIN
IF v_dept_id = 60 THEN
IF v_job_id = 'IT_PROG' THEN
DBMS_OUTPUT.PUT_LINE('IT Programmer in IT department');
ELSE
DBMS_OUTPUT.PUT_LINE('Other job in IT department');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Other department');
END IF;
END;
8. Examples Using HR Schema
lets have a look with data and tables.
a. Determine Employee Status Based on Salary
DECLARE
v_emp_id employees.employee_id%TYPE := 105;
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id;
IF v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Employee salary is below standard.');
ELSIF v_salary <= 10000 THEN
DBMS_OUTPUT.PUT_LINE('Employee salary is standard.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee salary is above standard.');
END IF;
END;
b. Assign Bonus Based on Department
DECLARE
v_emp_id employees.employee_id%TYPE := 108;
v_dept_id employees.department_id%TYPE;
v_bonus NUMBER;
BEGIN
SELECT department_id INTO v_dept_id FROM employees WHERE employee_id = v_emp_id;
CASE v_dept_id
WHEN 60 THEN v_bonus := 500;
WHEN 80 THEN v_bonus := 1000;
ELSE v_bonus := 300;
END CASE;
DBMS_OUTPUT.PUT_LINE('Assigned bonus: ' || v_bonus);
END;
c. Determine Location Message
DECLARE
v_country_id locations.country_id%TYPE;
BEGIN
SELECT country_id INTO v_country_id FROM locations WHERE location_id = 1700;
CASE
WHEN v_country_id = 'US' THEN
DBMS_OUTPUT.PUT_LINE('Location is in the United States');
WHEN v_country_id = 'UK' THEN
DBMS_OUTPUT.PUT_LINE('Location is in the United Kingdom');
ELSE
DBMS_OUTPUT.PUT_LINE('Other location');
END CASE;
END;
9. Best Practices
- - Keep it simple: Avoid overly complex nested conditions unless necessary.
- - Use CASE for clarity: Prefer CASE when evaluating multiple values against a single expression.
- - Declare variables properly: Always declare and initialize variables used in conditions.
- - Handle NULLs: Remember that NULL comparisons (e.g., v_var = NULL) are always false. Use IS NULL or IS NOT NULL.
- - Comment your logic: Especially for nested or multi-branch logic.
- - Combine with exceptions: Use conditional checks to validate input before executing critical operations.
10. Conclusion
Conditional statements in Oracle PL/SQL are foundational to writing effective and intelligent logic in your database programs. They allow you to control flow, make decisions based on runtime conditions, and handle various input and operational scenarios gracefully.
From basic IF-THEN constructs to advanced CASE statements, mastering conditional logic will make your PL/SQL code robust, readable, and efficient. The real-world examples using the HR schema should help solidify your understanding and demonstrate how these techniques apply to production-level database programming.
0 Comments