Chapter 5 Conditional Control: IF Statements

Team-Fly

---

	Oracle® PL/SQL® Interactive Workbook, Second Edition By Benjamin Rosenzweig, Elena Silvestrova
	Table of Contents
	Appendix D.  Answers to Test Your Thinking Sections

---

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.
A1:	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 that 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 following output: 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.
A2:	Answer: Your answer should look similar to the following: 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 three or more sections, the condition of the IF-THEN-ELSE statement evaluates to TRUE, and the message 'This instructor needs a vacation' is displayed to the user. In the opposite case, the message stating how many sections instructor is teaching is displayed. Assume that value 101 was provided at the runtime. Then the script produces the following output: 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.
3)	Execute the two PL/SQL 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;
A3:	Answer: Consider outputs produced by the preceding scripts: -- 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.

---

Team-Fly

Top