How to Use IF Statements in Oracle Database 23ai PL/SQL

Conditional logic is one of the most essential elements of programming. In Oracle Database 23ai, PL/SQL supports multiple forms of IF statements that help developers execute code based on specific conditions. Whether you are writing a simple check or handling complex business rules, IF statements allow you to add decision-making capabilities to your PL/SQL blocks. In this Oracle tutorial, you will learn how to use IF statements in PL/SQL with clear explanations, real working examples, and some integration with Oracle Database 23ai features such as JSON data handling and vector-based logic.

Basic Syntax of IF Statement in PL/SQL

The simplest form of conditional logic in PL/SQL uses the IF...THEN structure. This checks a condition and executes the code only if it is true.

DECLARE
   v_salary NUMBER := 4000;
BEGIN
   IF v_salary < 5000 THEN
      DBMS_OUTPUT.PUT_LINE('Salary is below the threshold');
   END IF;
END;
/

Result:

Salary is below the threshold

In this example, the condition v_salary < 5000 is true, so the message is printed. If it were false, nothing would execute inside the block.

Using IF...THEN...ELSE in PL/SQL

The IF...THEN...ELSE form lets you handle both true and false outcomes.

DECLARE
   v_score NUMBER := 75;
BEGIN
   IF v_score >= 80 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: A');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Grade: B or below');
   END IF;
END;
/

Result:

Grade: B or below

This structure ensures that one branch of logic always runs, no matter the condition.

IF...ELSIF...ELSE for Multiple Conditions

When there are more than two possible outcomes, you can use ELSIF.

DECLARE
   v_marks NUMBER := 92;
BEGIN
   IF v_marks >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Excellent');
   ELSIF v_marks >= 75 THEN
      DBMS_OUTPUT.PUT_LINE('Very Good');
   ELSIF v_marks >= 50 THEN
      DBMS_OUTPUT.PUT_LINE('Average');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Fail');
   END IF;
END;
/

Result:

Excellent

Here, the program evaluates conditions in sequence and executes the first block that matches.

Nested IF Statements

PL/SQL also allows nesting one IF inside another. This is useful for hierarchical checks.

DECLARE
   v_department VARCHAR2(20) := 'IT';
   v_experience NUMBER := 6;
BEGIN
   IF v_department = 'IT' THEN
      IF v_experience > 5 THEN
         DBMS_OUTPUT.PUT_LINE('Eligible for senior IT role');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Eligible for junior IT role');
      END IF;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Non-IT department');
   END IF;
END;
/

Result:

Eligible for senior IT role

Nested conditions provide fine-grained control over complex scenarios.

IF Statements with Database Queries

In real-world applications, conditions are often based on database data.

DECLARE
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

   IF v_salary > 10000 THEN
      DBMS_OUTPUT.PUT_LINE('High salary employee');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Regular salary employee');
   END IF;
END;
/

This example shows how IF can integrate directly with queries to decide program flow.

Using IF Statements with JSON Data in Oracle 23ai

Oracle Database 23ai enhances JSON handling. You can check values inside JSON documents using IF in PL/SQL.

DECLARE
   v_customer JSON_OBJECT_T;
BEGIN
   v_customer := JSON_OBJECT_T('{"id":101, "status":"active", "loyaltyLevel": "gold"}');

   IF v_customer.get_String('status') = 'active' THEN
      DBMS_OUTPUT.PUT_LINE('Customer is active');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Customer is inactive');
   END IF;
END;
/

Result:

Customer is active

This demonstrates how IF works seamlessly with JSON functions in Oracle 23ai, which is vital in modern applications.

IF with Vectors in Oracle 23ai

Oracle 23ai introduced vector data types for AI-driven applications. You can use IF to control logic after performing vector similarity searches.

DECLARE
   v_similarity NUMBER := 0.85;  -- Assume result from vector search
BEGIN
   IF v_similarity > 0.80 THEN
      DBMS_OUTPUT.PUT_LINE('Vector is highly similar');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Vector is not very similar');
   END IF;
END;
/

Result:

Vector is highly similar

This is a simplified demonstration of how IF statements can be applied when dealing with AI-powered vector operations in Oracle 23ai.

Common Errors with IF Statements

1. Missing END IF – Every IF must be closed with END IF;.
2. Boolean Expressions Only – Conditions must evaluate to TRUE or FALSE.
3. Order of Conditions – In ELSIF, Oracle stops evaluating once a condition is true, so order matters.

Practical Example: Employee Bonus Logic

Let’s create a more practical scenario combining all forms.

DECLARE
   v_salary NUMBER := 8500;
   v_years  NUMBER := 7;
BEGIN
   IF v_salary >= 10000 THEN
      DBMS_OUTPUT.PUT_LINE('No bonus for salary above 10000');
   ELSIF v_salary >= 8000 AND v_years > 5 THEN
      DBMS_OUTPUT.PUT_LINE('Bonus: 15% of salary');
   ELSIF v_salary >= 8000 THEN
      DBMS_OUTPUT.PUT_LINE('Bonus: 10% of salary');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Bonus: 20% of salary');
   END IF;
END;
/

Result:

Bonus: 15% of salary

This example demonstrates real-life business rules implemented with nested and multiple conditions.

Conclusion

The IF statement is a cornerstone of PL/SQL programming in Oracle Database 23ai. It helps developers control program flow based on conditions, making applications dynamic and responsive. From simple checks to complex decision trees, IF works with basic values, query results, JSON data, and even AI-driven vector operations introduced in 23ai. By mastering IF statements, you can build smarter PL/SQL programs that handle diverse real-world scenarios efficiently.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows