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;
|
|
No comments:
Post a Comment