Description

EXECUTE mine (’11/09′,’P’)
EXECUTE mine (’12/09′,’f’)
EXECUTE mine (’01/10′,’T’)
You have entered an Invalid letter for the stored object. Try P, F or B. PL/SQL procedure successfully completed.
EXECUTE mine (’13/09′,’P’)
You have entered an Invalid FORMAT for the MONTH and YEAR. Try MM/YY. PL/SQL procedure successfully completed.
2) Write a stored Procedure called add_zip that will accept as Input THREE parameters for three columns in the table ZIPCODE (ZIP, CITY and STATE).It will firstly check whether entered ZIP already exists in the database and if YES – it will stop processing with the message.
If NOT — it will insert new row in the table ZIPCODE where other columns will use USER and
SYSDATE pseudo columns. Also it will use TWO Output parameters to display message SUCCESS or FAILURE and current # of rows in the table for the entered STATE. Then it will display ALL rows from that STATE. Use BIND variables to display your results. Undo your Insert, when Success happened. Here are the outputs:
Case 1:
PL/SQL procedure successfully completed.
FLAG
SUCCESS

ZIPNUM
2

SELECT * FROM zipcode
WHERE state = ‘MI’
ST
CREATED MODIF
ZIP CITY AT CREATED_BY MODIFIED_DATE
_DATE IED_BY
E

Ann ARISCH
48104 MI AMORRISO 03-AUG-99 24-NOV-99
Arbor ER
DBS501

18104 Chicago MI DBS501_093A40 12-NOV-09 12-NOV-09 _093A40

Rollback completed Case 2:
FLAG
FAILURE
ZIPNUM
1

This ZIPCODE 48104 is already in the Dataase. Try again. PL/SQL procedure successfully completed.
SELECT * FROM zipcode
ZIP CITY ST
AT
E CREATED_BY CREATED
_DATE MODIF
IED_BY MODIFIED_DATE
48104 Ann Arbor MI AMORRISO 03-AUG-99 ARISCH
ER 24-NOV-99
WHERE state = ‘MI’

A) After SELECT statement has been issued
10 rows selected.
B) After INPUT parameters ‘PETER’ and ‘PAN’ were provided PL/SQL procedure successfully completed.
MESSAGE

C) After INPUT parameters ‘IRENE’ and ‘WILLIG’ were provided PL/SQL procedure successfully completed.
MESSAGE