Oracle PL/SQL Interactive Workbook, 2/e

Test Your Thinking Solutions Chapter 5 Conditional Control: IF Statements

  1. Rewrite ch05_1a.sql. Instead of getting information from the user for the variable v_date, define its value with the help of the function SYSDATE. After it has been determined that a certain day falls on the weekend, check to see if the time is before or after noon. Display the time of the day together with the day.

    Answer: Your answer should look similar to the following. All changes are shown in bold letters.

    SET SERVEROUTPUT ON
    DECLARE
       v_day VARCHAR2(15);
       v_time VARCHAR(8); 
    BEGIN
       v_day := TO_CHAR(SYSDATE, 'fmDAY');
       v_time := TO_CHAR(SYSDATE, 'HH24:MI');
       
       IF v_day IN ('SATURDAY', 'SUNDAY') THEN
          DBMS_OUTPUT.PUT_LINE (v_day||', '||v_time); 
          IF v_time BETWEEN '12:01' AND '24:00' THEN
             DBMS_OUTPUT.PUT_LINE ('It''s afternoon'); 
          ELSE
             DBMS_OUTPUT.PUT_LINE ('It''s morning'); 
          END IF;
       END IF; 
    
       -- control resumes here
       DBMS_OUTPUT.PUT_LINE('Done');
    END;
    

    In this exercise, you remove variable v_date that was used to store date provided by a user. Instead, you add variable v_time to store the time of the day. You also modify the statement

        v_day := TO_CHAR(SYSDATE, 'fmDAY');
    

    so that DAY is prefixed by letters fm. This guarantees that extra spaces will be removed from the name of the day. Then, you add another statement that determines current time of the day and stores it in the variable v_time.

    Finally, you add an IF-THEN-ELSE statement that checks the time of the day and displays the appropriate message.

    Notice, how two single quotes are used in the second and third DBMS_OUTPUT.PUT_LINE statements. This allows you to use an apostrophe in your message.

    When run, this exercise produces the output shown below:

    SUNDAY, 16:19
    It's afternoon
    Done…
    
    PLSQL procedure successfully completed.
    
  2. Create a new script. For a given instructor, determine how many sections he or she is teaching. If the number is greater than or equal to 3, display a message saying that the instructor needs a vacation. Otherwise, display a message saying how many sections this instructor is teaching. Answer: Your answer should look similar to the following:
  3. SET SERVEROUTPUT ON
    DECLARE
       v_instructor_id NUMBER := &sv_instructor_id;
       v_total NUMBER;
    BEGIN
       SELECT COUNT(*)
         INTO v_total
         FROM section
        WHERE instructor_id = v_instructor_id;
       
       -- check if instructor teaches 3 or more sections    
       IF v_total >= 3 THEN
          DBMS_OUTPUT.PUT_LINE ('This instructor needs a vacation');
       ELSE
          DBMS_OUTPUT.PUT_LINE ('This instructor teaches '||v_total||
             ' sections');	
       END IF;
       -- control resumes here
       DBMS_OUTPUT.PUT_LINE ('Done');
    END;
    

    This script accepts a value for instructor's ID from a user. Next, it checks the number of sections taught by given instructor. This is accomplished with the help of the SELECT INTO statement. Next, it determines what message should be displayed on the screen with the help of IF-THEN-ELSE statement. If a particular instructor teaches 3 or more sections, the condition of the IF-THEN-ELSE statement evaluates to TRUE, and the message 'Instructor needs a vacation' is displayed to the user. In opposite case, the message stating how many sections instructor is teaching is displayed. Assume that value 101 was provided at the run-time. Then, the script produces the output shown below:

    Enter value for sv_instructor_id: 101
    old   2:    v_instructor_id NUMBER := &sv_instructor_id;
    new   2:    v_instructor_id NUMBER := 101;
    This instructor needs a vacation
    
    PLSQL procedure successfully completed.
    
  4. Execute the two PLSQL blocks below and explain why they produce different output for the same value of the variable v_num. Remember to issue the SET SERVEROUTPUT ON command before running this script.

    -- Block 1
    DECLARE
       v_num NUMBER := NULL; 
    BEGIN
       IF v_num > 0 THEN
          DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0');
       ELSE
          DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0');
       END IF;
    END;
    
    -- Block 2
    DECLARE
       v_num NUMBER := NULL; 
    BEGIN
       IF v_num > 0 THEN
          DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0');
       END IF;
       IF NOT (v_num > 0) THEN
          DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0');
       END IF;
    END;
    

    Answer: Consider outputs produced by the scripts above:

    -- Block1
    v_num is not greater than 0
    
    PLSQL procedure successfully completed.
    
    -- Block 2
    PLSQL procedure successfully completed.
    

    The outputs produced by Block 1 and Block 2 are different even though in both examples variable v_num has been defined as NULL.

    First, take a closer look at the IF-THEN-ELSE statement used in Block 1:

    IF v_num > 0 THEN
       DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0');
    ELSE
       DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0');
    END IF;
    

    The condition v_num > 0 evaluates to FALSE because NULL has been assigned to the variable v_num. As a result, the control is transferred to the ELSE part of the IF-THEN-ELSE statement. So, the message v_num is not greater than 0 is displayed on the screen.

    Second, take a closer look at the IF-THEN statements used in Block 2:

     
    IF v_num > 0 THEN
       DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0');
    END IF;
    IF NOT (v_num > 0) THEN 
       DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0');
    END IF;
    

    For both IF-THEN statements their conditions evaluate to FALSE, and as a result none of the messages are displayed on the screen.

Select a Chapter for Test Your Thinking Solutions

  1. Programming Concepts
  2. PLSQL Concepts
  3. General Programming Language Fundamentals
  4. SQL in PLSQL
  5. Conditional Control: IF Statements
  6. Conditional Control: CASE Statements
  7. Error Handling and Built-In Exceptions
  8. Iterative Control
  9. Introduction to Cursors
  10. Exceptions
  11. Exceptions: Advanced Concepts
  12. Procedures
  13. Functions
  14. Packages
  15. Advanced Cursors
  16. Stored Code
  17. Triggers
  18. Collections
  19. Records