How Do I Use Conditional Logic in PL/SQL

 Introduction

Using conditional logic in PL/SQL allows you to make decisions and control the flow of your code based on specific conditions. This is especially useful in Oracle APEX when you want to perform different actions depending on the values entered by users or the results of queries. PL/SQL provides structures like IF-THEN, IF-THEN-ELSE, and CASE statements, which help you write flexible and dynamic logic that responds to real-time data and user input.

In Oracle APEX, using conditional logic in PL/SQL allows you to control how your code behaves based on certain values or conditions. This helps your application respond differently depending on user input, query results, or system states. You can use IF, IF-THEN-ELSE, ELSIF, and CASE statements to manage multiple outcomes in a single PL/SQL block.

Step 1: Where to Use Conditional Logic
Conditional logic in PL/SQL can be used in:

• Page processes (after submit, before header, etc.)

• Validations

• Dynamic actions (server-side)

• Computations

• PL/SQL function bodies

• Procedures and functions in SQL Workshop

Step 2: Basic IF-THEN Statement
Use IF when you want to perform a block of code only when a condition is true.

DECLARE
  v_total NUMBER := 10;
BEGIN
  IF v_total > 5 THEN
    APEX_DEBUG.MESSAGE('Total is greater than 5');
  END IF;
END;

Step 3: Using IF-THEN-ELSE
Use ELSE when you want to do something when the condition is not true.

DECLARE
  v_status VARCHAR2(10) := 'CLOSED';
BEGIN
  IF v_status = 'OPEN' THEN
    APEX_DEBUG.MESSAGE('Status is OPEN');
  ELSE
    APEX_DEBUG.MESSAGE('Status is not OPEN');
  END IF;
END;

Step 4: Using ELSIF for Multiple Conditions
Use ELSIF to check more than one condition.

DECLARE
  v_score NUMBER := 85;
BEGIN
  IF v_score >= 90 THEN
    APEX_DEBUG.MESSAGE('Grade: A');
  ELSIF v_score >= 80 THEN
    APEX_DEBUG.MESSAGE('Grade: B');
  ELSE
    APEX_DEBUG.MESSAGE('Grade: C or lower');
  END IF;
END;

Step 5: Using CASE Statements
CASE statements are useful when checking multiple fixed values.

DECLARE
  v_day VARCHAR2(10) := 'MON';
BEGIN
  CASE v_day
    WHEN 'MON' THEN APEX_DEBUG.MESSAGE('Start of the week');
    WHEN 'FRI' THEN APEX_DEBUG.MESSAGE('End of the week');
    ELSE APEX_DEBUG.MESSAGE('Midweek');
  END CASE;
END;

Step 6: Using Conditional Logic with Page Items
You can reference APEX page items using the : syntax and apply logic accordingly.

BEGIN
  IF :P1_ROLE = 'ADMIN' THEN
    :P1_ACCESS := 'FULL';
  ELSE
    :P1_ACCESS := 'LIMITED';
  END IF;
END;

Best Practices

• Keep your conditions clear and readable.

• Always include an ELSE or DEFAULT branch for complete logic.

• Use APEX_DEBUG.MESSAGE for debugging or DBMS_OUTPUT.PUT_LINE in SQL Workshop.

• Keep conditions simple and use nested logic only when necessary.

• Use page item values carefully and check for NULL where needed.

Using conditional logic in PL/SQL is essential for building responsive and flexible Oracle APEX applications. It allows you to control how data is processed, displayed, and managed based on your business rules.

Conclusion
Conditional logic in PL/SQL gives your Oracle APEX applications the ability to react intelligently to different scenarios. By using IF statements and CASE expressions, you can guide your application’s behavior, validate data, handle exceptions, and manage different outcomes with ease. Learning how to use these control structures effectively is key to building smart and reliable PL/SQL code in any Oracle APEX project.

ADDITIONAL EXAMPLE

Conditional statements control program flow based on specific conditions.

DECLARE

    v_salary NUMBER;

BEGIN

    SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    IF v_salary > 50000 THEN

        :P1_STATUS := 'High Salary';

    ELSE

        :P1_STATUS := 'Standard Salary';

    END IF;

END;

This example updates a page item (P1_STATUS) based on the salary amount.