Oracle PL/SQL Interactive Workbook, 2/e

Test Your Thinking Solutions Chapter 3 General Programming Language Fundamentals

1. Write a PL/SQL block

   1. That includes declarations for the following variables:
      • A VARCHAR2 datatype 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 datatype) that is auto-initialized to the value '603D'
      • A BOOLEAN
      • A DATE datatype auto-initialized to one week from today
   2. In the body of the PL/SQL block, put a DBMS_OUTPUT.PUT_LINE message for each of the variables that received an auto initialization value.
   3. In a comment at the bottom of the PL/SQL block, state the value of your number datatype.

   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 above to conform to the following specs.

   1. Remove the DBMS_OUTPUT.PUT_LINE messages.
   2. In the body of the PL/SQL block, write a selection test (IF) that does the following - use a nested if statement where appropriate.
      1. Check if the VARCHAR2 you created contains the course named 'Introduction to Underwater Basketweaving'.
      2. If it does, then put a DBMS_OUTPUT.PUT_LINE message on the screen that says so.
      3. If it does not, then test to see if the CONSTANT you created contains the room number 603D.
      4. If it does, then put a DBMS_OUTPUT.PUT_LINE message on the screen that states the course name and the room name that you've reached in this logic.
      5. 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.
   3. Add a WHEN OTHERS EXCEPTION that puts a DBMS_OUTPUT.PUT_LINE message on the screen that says that an error occurred.

   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;
   

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