Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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;

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;