[ PL/SQL TIPS ] Excepciones en UPDATE/DELETE cuando no existen filas

Muchas veces se puede presentar el caso en el que dentro de un Sotred Procedure es necesario tratar las excepciones que ocurren al usar operaciones como UPDATE o DELETE en PL/SQL y no retornan resultado alguno. Mas precisamente, cuando las condiciones de busqueda de la/s fila/s no devuelve resultado.

Trataré de explicar una posible solución que en mi opinion es simple de entender e implementar, aunque pueden surgir mejoras o soluciones totalmente distintas.



Recursos

CREATE TABLE JOBS
(
     JOB_ID        VARCHAR2(10) NOT NULL,
     JOB_TITLE     VARCHAR2(35) NOT NULL,
     MIN_SALARY    NUMBER(6),
     MAX_SALARY    NUMBER(6)
);

INSERT INTO JOBS VALUES ('FINC', 'Financial', 9000, 12000);
INSERT INTO JOBS VALUES ('DEV', 'Developer', 10000, 15000);
INSERT INTO JOBS VALUES ('ANST', 'Analyst', 10000, 13000);
INSERT INTO JOBS VALUES ('ACCT', 'Accountant', 12000, 18000);
INSERT INTO JOBS VALUES ('LWYR', 'Lawyer', 15000, 20000);

Solución

Para la practica se puede usar el esquema HR de la base de datos Oracle... o bien, usar los Recursos previamente ofrecidos en este artículo.

La solución queda planteada en el siguiente código:

CREATE OR REPLACE PROCEDURE DELETE_JOB
(
    p_jobid          IN    JOBS.JOB_ID%TYPE,
    x_return_code    OUT   NUMBER,
    x_return_message OUT   VARCHAR2
) AS
    NO_ROWS_FOUND        EXCEPTION;
BEGIN

  BEGIN
    DELETE FROM JOBS
      WHERE JOB_ID = UPPER(p_jobid);
     
    IF SQL%NOTFOUND THEN
        RAISE NO_ROWS_FOUND;
    ELSE
        x_return_code := 0;
        x_return_message := 'Ejecucion Exitosa';
    END IF;
  END;
 
  EXCEPTION
    WHEN NO_ROWS_FOUND THEN
        x_return_code := 1;
        x_return_message := 'No se ha encontrado resultados';

    WHEN OTHERS THEN
        x_return_code := SQLCODE;
        x_return_message := 'Error Inesperado: ' || SQLERRM;   
END;

Como se observa, al principio se declara una variable de tipo EXCEPTION que es por la cual se tratará la excepción.

Luego, se crea un bloque BEGIN ... END para registrar los resultados de las posibles Querys que se ejecuten dentro de este bloque. Antes de finalizar dicho bloque se contstruye una sentencia de control IF THEN ELSE para comprobar si el resultado de la ejecución de las querys trajo resultados o no haciendo uso de la condición SQL%NOTFOUND (o SQL sin resultados a modo de lectura).

Y finalmente un simple RAISE dispara la excepción para ser tratada según corresponda.