Chapter 12 Procedures

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 procedure with no parameters. The procedure will let you know if the current day is a weekend or a weekday. Additionally, it will let you know the user name and current time. It will also let you know how many valid and invalid procedures are in the database.

A1:

Answer: Your answer should look similar to the following:

 CREATE OR REPLACE PROCEDURE current_status  AS     v_day_type CHAR(1);     v_user VARCHAR2(30);     v_valid NUMBER;     v_invalid NUMBER;  BEGIN     SELECT SUBSTR(TO_CHAR(sysdate, 'DAY'), 0, 1)       INTO v_day_type       FROM dual;     IF v_day_type = 'S' THEN        DBMS_OUTPUT.PUT_LINE ('Today is a weekend.');     ELSE        DBMS_OUTPUT.PUT_LINE ('Today is a weekday.');     END IF;     --    DBMS_OUTPUT.PUT_LINE('The time is: '||        TO_CHAR(sysdate, 'HH:MI AM'));     --    SELECT user       INTO v_user       FROM dual;     DBMS_OUTPUT.PUT_LINE ('The current user is '||v_user);     --    SELECT NVL(COUNT(*), 0)       INTO v_valid       FROM user_objects      WHERE status = 'VALID'        AND object_type = 'PROCEDURE';     DBMS_OUTPUT.PUT_LINE        ('There are '||v_valid||' valid procedures.');     --    SELECT NVL(COUNT(*), 0)       INTO v_invalid       FROM user_objects      WHERE status = 'INVALID'        AND object_type = 'PROCEDURE';     DBMS_OUTPUT.PUT_LINE        ('There are '||v_invalid||' invalid procedures.');  END;  SET SERVEROUTPUT ON  EXEC current_status; 
2)

Write a procedure that takes in a zipcode, city, and state and inserts the values into the zipcode table. There should be a check to see if the zipcode is already in the database. If it is, an exception will be raised and an error message will be displayed. Write an anonymous block that uses the procedure and inserts your zipcode.

A2:

Answer: Your answer should look similar to the following:

 CREATE OR REPLACE PROCEDURE insert_zip    (I_ZIPCODE IN zipcode.zip%TYPE,     I_CITY    IN zipcode.city%TYPE,     I_STATE   IN zipcode.state%TYPE)  AS     v_zipcode zipcode.zip%TYPE;     v_city zipcode.city%TYPE;     v_state zipcode.state%TYPE;     v_dummy zipcode.zip%TYPE;  BEGIN     v_zipcode := i_zipcode;     v_city := i_city;     v_state := i_state;  --    SELECT zip       INTO v_dummy       FROM zipcode      WHERE zip = v_zipcode;  --    DBMS_OUTPUT.PUT_LINE('The zipcode '||v_zipcode||        ' is already in the database and cannot be'||        ' reinserted.');  -- EXCEPTION     WHEN NO_DATA_FOUND THEN       INSERT INTO ZIPCODE       VALUES (v_zipcode, v_city, v_state, user, sysdate,               user, sysdate);     WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE ('There was an unknown error '||           'in insert_zip.');  END;  SET SERVEROUTPUT ON  BEGIN    insert_zip (10035, 'No Where', 'ZZ');  END;  BEGIN    insert_zip (99999, 'No Where', 'ZZ');  END;  ROLLBACK; 


    Team-Fly    
    Top
     



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

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