Karina M Karina M - 10 days ago 7
SQL Question

Create a stored procedure that updates the personal info of an employee

I have done the following to create a stored procedure that updates the personal information of an employee in the EMPLOYEE table:

CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE
(
THE_EMPLOYEEID IN NUMBER,
NEW_LASTNAME IN VARCHAR2,
NEW_FIRSTNAME IN VARCHAR2,
NEW_TITLE IN VARCHAR2,
NEW_REPORTSTO IN NUMBER,
NEW_BIRTHDATE IN DATE,
NEW_HIREDATE IN DATE,
NEW_ADDRESS IN VARCHAR2,
NEW_CITY IN VARCHAR2,
NEW_STATE IN VARCHAR2,
NEW_COUNTRY VARCHAR2,
NEW_POSTALCODE VARCHAR2,
NEW_PHONE VARCHAR2,
NEW_FAX VARCHAR2,
NEW_EMAIL VARCHAR2
)
AS
BEGIN
UPDATE EMPLOYEE
SET LASTNAME =
CASE NEW_LASTNAME
WHEN NULL THEN
LASTNAME
ELSE NEW_LASTNAME
END,
FIRSTNAME =
CASE NEW_FIRSTNAME
WHEN NULL THEN
FIRSTNAME
ELSE NEW_FIRSTNAME
END,
TITLE = NEW_TITLE,
REPORTSTO = NEW_REPORTSTO,
BIRTHDATE = NEW_BIRTHDATE,
HIREDATE = NEW_HIREDATE,
ADDRESS = NEW_ADDRESS,
CITY = NEW_CITY,
STATE = NEW_STATE,
COUNTRY = NEW_COUNTRY,
POSTALCODE = NEW_POSTALCODE,
PHONE = NEW_PHONE,
FAX = NEW_FAX,
EMAIL = NEW_EMAIL
WHERE EMPLOYEEID = THE_EMPLOYEEID;
END UPDATE_EMPLOYEE;


EMPLOYEEID, LASTNAME, and FIRSTNAME are not nullable in the table EMPLOYEE.

I get this message:

Connecting to the database ChinookDB.
ORA-01407: cannot update ("CHINOOK"."EMPLOYEE"."LASTNAME") to NULL
ORA-06512: at "CHINOOK.UPDATE_EMPLOYEE", line 21
ORA-06512: at line 34
Process exited.
Disconnecting from the database ChinookDB.


Can someone please inform me on what I am doing wrong?

Answer

I assume that you want to ignore the new_firstname and new_lastname parameters if a NULL value is passed in. If so, your case statements are wrong

CASE NEW_LASTNAME 
        WHEN NULL THEN 
          LASTNAME
        ELSE NEW_LASTNAME
        END,

will always return new_lastname. No value, including NULL is ever equal to NULL so when null will always evaluate to false. You could do something like

CASE WHEN new_lastname IS NULL
     THEN lastname
     ELSE new_lastname
 END

or more simply

COALESCE( new_lastname, lastname )

Of course, you have the same problem with the new_firstname expression.