You may need to update a table's particular fields or all fields if their values are null only else there is no need to update any field. Below is the example procedure for the same purpose written on HR.Employees table:
CREATE OR REPLACE PROCEDURE HR.UpdateEmpIfNull (
PN_EMPLOYEE_ID IN NUMBER,
PV_FIRST_NAME IN VARCHAR2,
PV_LAST_NAME IN VARCHAR2,
PV_EMAIL IN VARCHAR2,
PV_PHONE_NUMBER IN VARCHAR2,
PD_HIRE_DATE IN DATE,
PV_JOB_iD IN VARCHAR2,
PN_SALARY IN NUMBER,
PV_Success OUT VARCHAR2)
IS
n_length NUMBER;
n_length2 NUMBER;
BEGIN
SELECT LENGTH( FIRST_NAME
|| LAST_NAME
|| EMAIL
|| PHONE_NUMBER
|| HIRE_DATE
|| JOB_ID
|| SALARY)
INTO n_length
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;
UPDATE HR.EMPLOYEES
SET FIRST_NAME = DECODE (FIRST_NAME, NULL, PV_FIRST_NAME, FIRST_NAME),
LAST_NAME = DECODE (LAST_NAME, NULL, pv_LAST_NAME, LAST_NAME),
EMAIL = DECODE (EMAIL, NULL, pv_EMAIL, EMAIL),
PHONE_NUMBER =
DECODE (PHONE_NUMBER, NULL, pv_PHONE_NUMBER, PHONE_NUMBER),
HIRE_DATE = DECODE (HIRE_DATE, NULL, pD_HIRE_DATE, HIRE_DATE),
JOB_ID = DECODE (JOB_ID, NULL, pV_JOB_ID, JOB_ID),
SALARY = DECODE (SALARY, NULL, pN_SALARY, SALARY)
WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;
/* Using below query to determine if the update was successful, we can not use here sql%rowcount or sql%found to determine because if employee id is correct then it will always show a successful update but we need to determine if any null field value has updated or not */
SELECT LENGTH( FIRST_NAME
|| LAST_NAME
|| EMAIL
|| PHONE_NUMBER
|| HIRE_DATE
|| JOB_ID
|| SALARY)
INTO n_length2
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;
IF n_length2 > n_length
THEN
--- Save Changes.
COMMIT;
Pv_Success := 'Y';
ELSE
ROLLBACK;
pv_success := 'N';
END IF;
EXCEPTION
WHEN OTHERS
THEN
Pv_Success := 'N';
ROLLBACK;
END;
/
