I am trying to create this stored procedure which should take customer no and email address as input. Then update the email address for that customer. if new email address is same as old then exception should be raised.
CREATE OR REPLACE PROCEDURE UpdateEmail
(CUSTOMERID IN CUSTOMER.CUSTOMERNO%TYPE,
N_EMAIL IN CUSTOMER.EMAIL%TYPE)
IS
DECLARE
e_same EXCEPTION;
V_ROWCOUNT NUMBER;
BEGIN
SELECT COUNT(EMAIL)
INTO V_ROWCOUNT
FROM CUSTOMER@FIT5148B
WHERE CUSTOMER.EMAIL = N_EMAIL
AND CUSTOMER.CUSTOMERNO = CUSTOMERID;
IF V_ROWCOUNT > 0
THEN RAISE e_same;
ELSE
UPDATE CUSTOMER
SET EMAIL = N_EMAIL
WHERE CUSTOMER.CUSTOMERNO = CUSTOMERID;
END IF;
EXCEPTION
WHEN e_same THEN dbms_output.put_line ('email address exist');
END;
/
But it is throwing error. Not sure if I am doing it right. I am using the latest Oracle SQL Developer.
Error(6,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.
Error(28,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
Declare
– Utsav