Chapter 3 General Programming Language Fundamentals

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)

Write a PL/SQL block

  1. 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

  2. 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.

  3. 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:

  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 whether 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 number 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.

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; 


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net