Chapter 12 Procedures

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;