Lab 5.1 Exercise Answers | Oracle PL[s]SQL by Example

This section gives you some suggested answers to the questions in Lab 5.1, 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 here and what the effects are from any different answers you may come up with.

5.1.1 Answers

What output was printed on the screen (for both dates)?

A1:

Answer: The first output produced for the date is 09-JAN-2002. The second output produced for the date is 13-JAN-2002.

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

When the value of 09-JAN-2002 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-2002   old 2: v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');   new 2: v_date DATE := TO_DATE('13-JAN-2002', 'DD-MON-YYYY');   13-JAN-02 falls on weekend   Done...   PL/SQL procedure successfully completed.

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. So, '13-JAN-2002 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.

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

A2:

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

The second date, 13-JAN-2002, is a Sunday. Because Sunday falls on a weekend, the condition evaluates to TRUE, and the message '13-JAN-2002 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-2002' for v_date.

What output was printed on the screen? Why?

A1:

Answer: Your script should look similar to the following. Changes are shown in bold letters .

 -- ch05_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-2002   old 2: v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');   new 2: v_date DATE := TO_DATE('13-JAN-2002', '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 given date falls on the weekend, and control is passed to the last DBMS_ OUTPUT.PUT_LINE statement.

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

A2:

Answer: Your script should look similar to the following. Changes are shown in bold letters.

  -- ch05_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;

Both days, Saturday and Sunday, are the only days of the week that start with the letter '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.

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.

A3:

Answer: Your script should look similar to the following. Changes are shown in bold letters.

 -- ch05_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;

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

5.1.2 Answers

a)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there are 15 students enrolled in section 1 of course number 25?
A1:	Answer: If there are 15 or more students 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.

b)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there are 3 students enrolled in section 1 of course number 25?
A2:	Answer: If there are 3 students 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 on 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.

c)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there is no section 1 for course number 25?
A3:	Answer: If there is no section 1 for course number 25, the ELSE part of the IF-THEN-ELSE statement will be executed. So the second DBMS_OUTPUT.PUT_LINE statement will be 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.

d)	How would you change this script so that both course and section numbers are provided by a user?
A4:	Answer: Two additional variables must be declared and initialized with the help of the substitution variables as follows. Your script should look similar to this script. Changes are shown in bold letters. -- ch05_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;
e)	How would you change this script so that if there are less than 15 students enrolled in section 1 of course number 25, a message indicating how many students can still be enrolled is displayed?
A5:	Answer: Your script should look similar to this script. Changes are shown in bold letters. -- ch05_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.