Oracle® PL/SQL® Interactive Workbook, Second Edition By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix D. Answers to Test Your Thinking Sections
1)
Write a PL/SQL block
That includes declarations for the following variables:
A VARCHAR2 data type that can contain the string 'Introduction to Oracle PL/SQL'
A NUMBER that can be assigned 987654.55, but not 987654.567 or 9876543.55
A CONSTANT (you choose the correct data type) that is autoinitialized to the value '603D'
A BOOLEAN
A DATE data type autoinitialized to one week from today
In the body of the PL/SQL block, put a DBMS_OUTPUT.PUT_LINE message for each of the variables that received an autoinitialization value.
In a comment at the bottom of the PL/SQL block, state the value of your NUMBER data type.
A1:
Answer: Your answer should look similar to the following:
SET SERVEROUTPUT ON DECLARE -- A VARCHAR2 datatype that can contain the string -- 'Introduction to Oracle PL/SQL' v_descript VARCHAR2(35); -- A NUMBER that allows for the conditions: can be -- assigned 987654.55 but not 987654.567 -- or 9876543.55 v_number_test NUMBER(8,2); -- [a variable] auto initialized to the value '603D' v_location CONSTANT VARCHAR2(4) := '603D'; -- A BOOLEAN v_boolean_test BOOLEAN; -- A DATE datatype auto initialized to one week from -- today v_start_date DATE := TRUNC(SYSDATE) + 7; BEGIN DBMS_OUTPUT.PUT_LINE ('The location is: '||v_location||'.'); DBMS_OUTPUT.PUT_LINE ('The starting date is: '||v_start_date||'.'); END;
2)
Alter the PL/SQL block you created in Project 1 to conform to the following specs:
Remove the DBMS_OUTPUT.PUT_LINE messages.
In the body of the PL/SQL block, write a selection test (IF) that does the following (use a nested IF statement where appropriate):
Check whether the VARCHAR2 you created contains the course named 'Introduction to Underwater Basketweaving'.
If it does, then put a DBMS_OUTPUT.PUT_LINE message on the screen that says so.
If it does not, then test to see if the CONSTANT you created contains the room number 603D.
If it does, then put a DBMS_OUTPUT.PUT_LINE message on the screen that states the course name and the room number that you've reached in this logic.
If it does not, then put a DBMS_OUTPUT.PUT_LINE message on the screen that states that the course and location could not be determined.
Add a WHEN OTHERS EXCEPTION that puts a DBMS_ OUTPUT.PUT_LINE message on the screen that says that an error occurred.
A2:
Answer: Your answer should look similar to the following:
SET SERVEROUT ON DECLARE -- A VARCHAR2 datatype that can contain the string -- 'Introduction to Oracle PL/SQL' v_descript VARCHAR2(35); -- A NUMBER that allows for the conditions: can be -- assigned 987654.55 but not 987654.567 or -- 9876543.55 v_number_test NUMBER(8,2); -- [a variable] auto initialized to the value '603D' v_location CONSTANT VARCHAR2(4) := '603D'; -- A BOOLEAN v_boolean_test BOOLEAN; -- A DATE datatype auto initialized to one week from today v_start_date DATE := TRUNC(SYSDATE) + 7; BEGIN IF v_descript = 'Introduction to Underwater Basketweaving' THEN DBMS_OUTPUT.PUT_LINE ('This course is '||v_descript||'.'); ELSIF v_location = '603D' THEN -- No value has been assigned to v_descript IF v_descript IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE ('The course is '||v_descript ||'.'||' The location is '||v_location||'.'); ELSE DBMS_OUTPUT.PUT_LINE ('The course is unknown.'|| ' The location is '||v_location||'.'); END IF; ELSE DBMS_OUTPUT.PUT_LINE ('The course and location '|| 'could not be determined.'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('An error occurred.'); END;
