Home > Articles > Data > Oracle

Conditional Control: IF Statements in Oracle PL/SQL

  • Print
  • + Share This
  • 💬 Discuss
PL/SQL has three types of conditional control: IF, ELSIF, and CASE statements. This chapter explores the first two types and shows you how they can be nested inside one another.

CHAPTER OBJECTIVES

In this chapter, you will learn about

  • IF statements
  • ELSIF statements
  • Nested IF statements

In almost every program you write, you need to make decisions. For example, if it is the end of the fiscal year, bonuses must be distributed to the employees based on their salaries. To compute employee bonuses, a program needs a conditional control. In other words, it needs to employ a selection structure.

Conditional control allows you to control the program's flow of the execution based on a condition. In programming terms, this means that the statements in the program are not executed sequentially. Rather, one group of statements or another is executed, depending on how the condition is evaluated.

PL/SQL has three types of conditional control: IF, ELSIF, and CASE statements. This chapter explores the first two types and shows you how they can be nested inside one another. CASE statements are discussed in the next chapter.

Lab 4.1 IF Statements

LAB OBJECTIVES

After completing this lab, you will be able to

  • Use the IF-THEN statement
  • Use the IF-THEN-ELSE statement

An IF statement has two forms: IF-THEN and IF-THEN-ELSE. An IF-THEN statement allows you to specify only one group of actions to take. In other words, this group of actions is taken only when a condition evaluates to TRUE. An IF-THEN-ELSE statement allows you to specify two groups of actions. The second group of actions is taken when a condition evaluates to FALSE or NULL.

IF-THEN STATEMENTS

An IF-THEN statement is the most basic kind of a conditional control; it has the following structure:

IF CONDITION THEN
   STATEMENT 1;
   ...
   STATEMENT N;
END IF;

The reserved word IF marks the beginning of the IF statement. Statements 1 through N are a sequence of executable statements that consist of one or more standard programming structures. The word CONDITION between the keywords IF and THEN determines whether these statements are executed. END IF is a reserved phrase that indicates the end of the IF-THEN construct.

Figure 4.1 shows this flow of logic.

Figure 4.1

Figure 4.1 IF-THEN statement

When an IF-THEN statement is executed, a condition is evaluated to either TRUE or FALSE. If the condition evaluates to TRUE, control is passed to the first executable statement of the IF-THEN construct. If the condition evaluates to FALSE, control is passed to the first executable statement after the END IF statement.

Consider the following example. Two numeric values are stored in the variables v_num1 and v_num2. You need to arrange their values so that the smaller value is always stored in v_num1 and the larger value is always stored in v_num2.

For Example

DECLARE
   v_num1 NUMBER := 5;
   v_num2 NUMBER := 3;
   v_temp NUMBER;
BEGIN
   -- if v_num1 is greater than v_num2 rearrange their values
   IF v_num1 > v_num2 THEN
      v_temp := v_num1;
      v_num1 := v_num2;
      v_num2 := v_temp;
   END IF;

   -- display the values of v_num1 and v_num2
   DBMS_OUTPUT.PUT_LINE ('v_num1 = '||v_num1);
   DBMS_OUTPUT.PUT_LINE ('v_num2 = '||v_num2);
END;

In this example, condition v_num1 > v_num2 evaluates to TRUE because 5 is greater than 3. Next, the values are rearranged so that 3 is assigned to v_num1 and 5 is assigned to v_num2. This is done with the help of the third variable, v_temp, which is used for temporary storage.

This example produces the following output:

v_num1 = 3
v_num2 = 5

PL/SQL procedure successfully completed.

IF-THEN-ELSE STATEMENT

An IF-THEN statement specifies the sequence of statements to execute only if the condition evaluates to TRUE. When this condition evaluates to FALSE, there is no special action to take, except to proceed with execution of the program.

An IF-THEN-ELSE statement enables you to specify two groups of statements. One group of statements is executed when the condition evaluates to TRUE. Another group of statements is executed when the condition evaluates to FALSE. This is indicated as follows:

IF CONDITION THEN
   STATEMENT 1;
ELSE
   STATEMENT 2;
END IF;
STATEMENT 3;

When CONDITION evaluates to TRUE, control is passed to STATEMENT 1; when CONDITION evaluates to FALSE, control is passed to STATEMENT 2. After the IF-THEN-ELSE construct has completed, STATEMENT 3 is executed. Figure 4.2 illustrates this flow of logic.

Figure 4.2

Figure 4.2 IF-THEN-ELSE statement

NULL CONDITION

In some cases, a condition used in an IF statement can be evaluated to NULL instead of TRUE or FALSE. For the IF-THEN construct, the statements are not executed if an associated condition evaluates to NULL. Next, control is passed to the first executable statement after END IF. For the IF-THEN-ELSE construct, the statements specified after the keyword ELSE are executed if an associated condition evaluates to NULL.

For Example

DECLARE
   v_num1 NUMBER := 0;
   v_num2 NUMBER;
BEGIN
   IF v_num1 = v_num2 THEN
      DBMS_OUTPUT.PUT_LINE ('v_num1 = v_num2');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('v_num1 != v_num2');
  END IF;
END;

This example produces the following output:

v_num1 != v_num2

PL/SQL procedure successfully completed.

The condition

v_num1 = v_num2

is evaluated to NULL because a value is not assigned to the variable v_num2. Therefore, variable v_num2 is NULL. Notice that the IF-THEN-ELSE construct is behaving as if the condition evaluated to FALSE, and the second DBMS_ OUTPUT.PUT_LINE statement is executed.

Lab 4.1 Exercises

This section provides exercises and suggested answers, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers and what the effects are of any different answers you may come up with.

4.1.1 Use the IF-THEN Statement

In this exercise, you use the IF-THEN statement to test whether the date provided by the user falls on the weekend (in other words, if the day is Saturday or Sunday).

Create the following PL/SQL script:

-- ch04_1a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
   v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
   v_day  VARCHAR2(15);
BEGIN
   v_day := RTRIM(TO_CHAR(v_date, 'DAY'));

   IF v_day IN ('SATURDAY', 'SUNDAY') THEN
      DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
   END IF;

   --- control resumes here
   DBMS_OUTPUT.PUT_LINE ('Done...');
END;

To test this script fully, execute it twice. For the first run, enter 09-JAN-2008, and for the second run, enter 13-JAN-2008. Execute the script, and then answer the following questions:

  1. What output is printed on the screen (for both dates)?

    ANSWER: The first output produced for the date is 09-JAN-2008. The second output produced for the date is 13-JAN-2008.

    Enter value for sv_user_date: 09-JAN-2008
    old   2:    v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
    new   2:    v_date DATE := TO_DATE('09-JAN-2008', 'DD-MON-YYYY');
    Done...
    
    PL/SQL procedure successfully completed.

    When the value of 09-JAN-2008 is entered for v_date, the day of the week is determined for the variable v_day with the help of the functions TO_CHAR and RTRIM. Next, the following condition is evaluated:

    v_day IN ('SATURDAY', 'SUNDAY')

    Because the value of v_day is 'WEDNESDAY', the condition evaluates to FALSE. Then, control is passed to the first executable statement after END IF. As a result, Done... is displayed on the screen:

    Enter value for sv_user_date: 13-JAN-2008
    old   2:    v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
    new   2:    v_date DATE := TO_DATE('13-JAN-2008', 'DD-MON-YYYY');
    13-JAN-08 falls on weekend
    Done...
    
    PL/SQL procedure successfully completed.

    As in the previous run, the value of v_day is derived from the value of v_date. Next, the condition of the IF-THEN statement is evaluated. Because it evaluates to TRUE, the statement after the keyword THEN is executed. Therefore, 13-JAN-2008 falls on weekend is displayed on the screen. Next, control is passed to the last DBMS_OUTPUT.PUT_LINE statement, and Done... is displayed on the screen.

  2. Explain why the output produced for the two dates is different.

    ANSWER: The first date, 09-JAN-2008, is a Wednesday. As a result, the condition v_day IN ('SATURDAY,' 'SUNDAY') does not evaluate to TRUE. Therefore, control is transferred to the statement after END IF, and Done... is displayed on the screen.

    The second date, 13-JAN-2008, is a Sunday. Because Sunday falls on a weekend, the condition evaluates to TRUE, and the message 13-JAN-2008 falls on weekend is displayed on the screen. Next, the last DBMS_OUTPUT.PUT_LINE statement is executed, and Done... is displayed on the screen.

    Remove the RTRIM function from the assignment statement for v_day as follows:

    v_day := TO_CHAR(v_date, 'DAY');

    Run the script again, entering 13-JAN-2008 for v_date.

  3. What output is printed on the screen? Why?

    ANSWER: The script should look similar to the following. Changes are shown in bold.

    -- ch04_1b.sql, version 2.0
    SET SERVEROUTPUT ON
    DECLARE
       v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
       v_day  VARCHAR2(15);
    BEGIN
       v_day := TO_CHAR(v_date, 'DAY');
    
       IF v_day IN ('SATURDAY', 'SUNDAY') THEN
          DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
       END IF;
    
       -- control resumes here
       DBMS_OUTPUT.PUT_LINE ('Done...');
    END;

    This script produces the following output:

    Enter value for sv_user_date: 13-JAN-2008
    old   2:    v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
    new   2:    v_date DATE := TO_DATE('13-JAN-2008', 'DD-MON-YYYY');
    Done...
    
    PL/SQL procedure successfully completed.

    In the original example, the variable v_day is calculated with the help of the statement RTRIM(TO_CHAR(v_date, 'DAY')). First, the function TO_CHAR returns the day of the week, padded with blanks. The size of the value retrieved by the function TO_CHAR is always 9 bytes. Next, the RTRIM function removes trailing spaces.

    In the statement

    v_day := TO_CHAR(v_date, 'DAY')

    the TO_CHAR function is used without the RTRIM function. Therefore, trailing blanks are not removed after the day of the week has been derived. As a result, the condition of the IF-THEN statement evaluates to FALSE even though the given date falls on the weekend, and control is passed to the last DBMS_ OUTPUT.PUT_LINE statement.

  4. Rewrite this script using the LIKE operator instead of the IN operator so that it produces the same results for the dates specified earlier.

    ANSWER: The script should look similar to the following. Changes are shown in bold.

    -- ch04_1c.sql, version 3.0
    SET SERVEROUTPUT ON
    DECLARE
       v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
       v_day  VARCHAR2(15);
    BEGIN
       v_day := RTRIM(TO_CHAR(v_date, 'DAY'));
    
       IF v_day LIKE 'S%' THEN
          DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
       END IF;
    
       -- control resumes here
       DBMS_OUTPUT.PUT_LINE ('Done...');
    END;

    Saturday and Sunday are the only days of the week that start with S. As a result, there is no need to spell out the names of the days or specify any additional letters for the LIKE operator.

  5. Rewrite this script using the IF-THEN-ELSE construct. If the date specified does not fall on the weekend, display a message to the user saying so.

    ANSWER: The script should look similar to the following. Changes are shown in bold.

    -- ch04_1d.sql, version 4.0
    SET SERVEROUTPUT ON
    DECLARE
       v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
       v_day  VARCHAR2(15);
    BEGIN
       v_day := RTRIM(TO_CHAR(v_date, 'DAY'));
    
       IF v_day IN ('SATURDAY', 'SUNDAY') THEN
          DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
       ELSE
          
          DBMS_OUTPUT.PUT_LINE
             
             (v_date||' does not fall on the weekend');
       END IF;
    
       -- control resumes here
       DBMS_OUTPUT.PUT_LINE('Done...');
    END;

    To modify the script, the ELSE part was added to the IF statement. The rest of the script has not been changed.

4.1.2 Use the IF-THEN-ELSE Statement

In this exercise, you use the IF-THEN-ELSE statement to check how many students are enrolled in course number 25, section 1. If 15 or more students are enrolled, section 1 of course number 25 is full. Otherwise, section 1 of course number 25 is not full, and more students can register for it. In both cases, a message should be displayed to the user, indicating whether section 1 is full. Try to answer the questions before you run the script. After you have answered the questions, run the script and check your answers. Note that the SELECT INTO statement uses the ANSI 1999 SQL standard.

Create the following PL/SQL script:

-- ch04_2a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
   v_total NUMBER;
BEGIN
   SELECT COUNT(*)
     INTO v_total
     FROM enrollment e
     JOIN section s USING (section_id)
    WHERE s.course_no = 25
      AND s.section_no = 1;

   -- check if section 1 of course 25 is full
   IF v_total >= 15 THEN
      DBMS_OUTPUT.PUT_LINE
         ('Section 1 of course 25 is full');
   ELSE
      DBMS_OUTPUT.PUT_LINE
         ('Section 1 of course 25 is not full');
   END IF;
   -- control resumes here
END;

Notice that the SELECT INTO statement uses an equijoin. The join condition is listed in the JOIN clause, indicating columns that are part of the primary key and foreign key constraints. In this example, column SECTION_ID of the ENROLLMENT table has a foreign key constraint defined on it. This constraint references column SECTION_ID of the SECTION table, which, in turn, has a primary key constraint defined on it.

Try to answer the following questions, and then execute the script:

  1. What DBMS_OUTPUT.PUT_LINE statement is displayed if 15 students are enrolled in section 1 of course number 25?

    ANSWER: If 15 or more students are enrolled in section 1 of course number 25, the first DBMS_OUTPUT.PUT_LINE statement is displayed on the screen.

    The condition

    v_total >= 15

    evaluates to TRUE, and as a result, the statement

    DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is full');

    is executed.

  2. What DBMS_OUTPUT.PUT_LINE statement is displayed if three students are enrolled in section 1 of course number 25?

    ANSWER: If three students are enrolled in section 1 of course number 25, the second DBMS_OUTPUT.PUT_LINE statement is displayed on the screen.

    The condition

    v_total >= 15

    evaluates to FALSE, and the ELSE part of the IF-THEN-ELSE statement is executed. As a result, the statement

    DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is not full');

    is executed.

  3. What DBMS_OUTPUT.PUT_LINE statement is displayed if there is no section 1 for course number 25?

    ANSWER: If there is no section 1 for course number 25, the ELSE part of the IF-THEN-ELSE statement is executed. So the second DBMS_OUTPUT.PUT_LINE statement is displayed on the screen.

    The COUNT function used in the SELECT statement:

    SELECT COUNT(*)
      INTO v_total
      FROM enrollment e
      JOIN section s USING (section_id)
     WHERE s.course_no = 25
       AND s.section_no = 1;

    returns 0. The condition of the IF-THEN-ELSE statement evaluates to FALSE. Therefore, the ELSE part of the IF-THEN-ELSE statement is executed, and the second DBMS_OUTPUT.PUT_LINE statement is displayed on the screen.

  4. How would you change this script so that the user provides both course and section numbers?

    ANSWER: Two additional variables must be declared and initialized with the help of the substitution variables as follows. The script should look similar to the following. Changes are shown in bold.

    -- ch04_2b.sql, version 2.0
    SET SERVEROUTPUT ON
    DECLARE
       v_total      NUMBER;
       v_course_no  CHAR(6) := '&sv_course_no';
       v_section_no NUMBER  := &sv_section_no;
    BEGIN
       SELECT COUNT(*)
          INTO v_total
          FROM enrollment e
          JOIN section s USING (section_id)
       WHERE s.course_no  = v_course_no
          AND s.section_no = v_section_no;
    
       -- check if a specific section of a course is full
       IF v_total >= 15 THEN
          DBMS_OUTPUT.PUT_LINE
             ('Section 1 of course 25 is full');
       ELSE
          DBMS_OUTPUT.PUT_LINE
             ('Section 1 of course 25 is not full');
       END IF;
       -- control resumes here
    END;
  5. How would you change this script so that if fewer than 15 students are enrolled in section 1 of course number 25, a message appears indicating how many students can still enroll?

    ANSWER: The script should look similar to the following. Changes are shown in bold.

    -- ch04_2c.sql, version 3.0
    SET SERVEROUTPUT ON
    DECLARE
       v_total    NUMBER;
       v_students NUMBER;
    BEGIN
       SELECT COUNT(*)
         INTO v_total
         FROM enrollment e
         JOIN section s USING (section_id)
        WHERE s.course_no = 25
          AND s.section_no = 1;
    
       -- check if section 1 of course 25 is full
       IF v_total >= 15 THEN
          DBMS_OUTPUT.PUT_LINE
             ('Section 1 of course 25 is full');
       ELSE
          v_students := 15 - v_total;
          DBMS_OUTPUT.PUT_LINE (v_students||
             ' students can still enroll into section 1'||
             ' of course 25');
       END IF;
       -- control resumes here
    END;

    Notice that if the IF-THEN-ELSE statement evaluates to FALSE, the statements associated with the ELSE part are executed. In this case, the value of the variable v_total is subtracted from 15. The result of this operation indicates how many more students can enroll in section 1 of course number 25.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus