ORACLE Database Queryes: Ejemplo de Sentencia MERGE.

Hoy daremos un ejemplo de como podemos utilizar la sentencia MERGE en ORACLE. Resolveremos un problema utilizando el esquema HR que ya viene creado en la Base da Datos Oracle XE 11G.

Requisitos:

• Tener instalada la Base de Datos y tener acceso a ella, de preferencia rol de DBA.
• Haber desbloqueado el Usuario HR (ALTER USER HR ACCOUNT UNLOCK;).
• Tener permisos de UPDATE.
• Tener un IDE para administrar la Base de Datos (Toad, SQLDeveloper, etc.), esto no es requerido al no haber opción esta el sqlplus.

Problema a resolver:

Escriba una sentencia de actualización que incremente en 7% el salario de los empleados (EMPLOYEES.SALARY) si se encuentran cumpliendo años de laborar en la empresa.

Asegurarse que el incremento no exceda los $1,000. Todos recibirán incremento excepto los que cumplen 2 años  o menos. Debe resolver este problema utilizando una única sentencia de actualización y no un bloque de código PL/SQL.

Nota:  hire_date = Fecha de contratación.

Análizamos el problema:

Entonces, tomando el problema en cuestión, podemos definir lo que se quiere:

1. Vamos a incrementar el Salario en la tabla HR.EMPLOYEES un 0.07%
2. Solamente a los que tengan más de dos años de laborar en la empresa.
3. El incremento no debe ser mayor a $1000.

Ideas que se me ocurren para solucionar:

1. Podríamos usar un CORRELATED UPDATE con subqueryes (código engorroso a mi juicio)
2. Usamos un MERGE, donde en el SELECT ya vaya la data preparada para actualizarse, siempre usaríamos un subquery.
3. Lastimosamente, como lo tenemos que hacer con una sola sentencia no es posible usar un bloque PLSQL. Pero definitivamente un bloque anónimo sería una buena opción.
4. ¿Se les ocurre otra forma, respetando las reglas del problema?

Resolviendo:

1. Hagamos primero el SELECT que utilizaremos con las validaciones.

SELECT EMP.EMPLOYEE_ID
      ,EMP.SALARY
      ,(EMP.SALARY *0.07) INCREASE
 FROM HR.EMPLOYEES EMP
 WHERE MONTHS_BETWEEN(SYSDATE,HIRE_DATE) > 24

Entendiendo el código:
Necesitamos los 3 campos: EMPLOYEE_ID, SALARY y el INCREASE (es el incremento de SALARIO, multiplicamos el salario por 0.07 que es el equivalente al 7%).
En las condiciones usamos MONTHS_BETWEEN, sabiendo que los años tienen 12 meses y queremos solamente aquellos empleados que tienen más de dos años (osea 24 meses).

2. Lo siguiente será aplicar la condición de que el incremento no sea mayor que $1000, para ello recurrimos a convertir nuestro primer query en un subquery y agregar un query principal que solamente tendrá los valores que necesitamos.

SELECT NSAL.EMPLOYEE_ID,
               CASE
                WHEN NSAL.INCREASE < 1000
                  THEN
                    (NSAL.SALARY + NSAL.INCREASE)
                  ELSE
                    (NSAL.SALARY + 1000)
              END NEW_SALARY
 FROM
              (SELECT EMP.EMPLOYEE_ID
                              ,EMP.SALARY
                             ,(EMP.SALARY *0.07) INCREASE
                FROM HR.EMPLOYEES EMP
                WHERE     MONTHS_BETWEEN(SYSDATE,HIRE_DATE) > 24) NSAL;

Entendiendo el código:
Conservamos el EMPLOYEE_ID, que es nuestra llave. Hacemos el incremente de salario tomando en cuenta la condición que no sea mayor a 1000, los que tengan más de 1000 de incremento solamente les sumaremos 1000, y los que sea menor será el nuevo salario sumando el salario anterior más la porción a incrementar.
Usamos como tabla base nuestro primer query, que ya nos había filtrado los datos de los empleados que tenían más de dos años de laborar en la empresa.

3. Para finalizar, usaremos la sentencia MERGE que nos permitirá actualizar la tabla EMPLOYEES utilizando los valores ya filtrados y tomando como llaves los EMPLOYEE_ID que necesitamos.

MERGE INTO HR.EMPLOYEES EMUSING (SELECT NSAL.EMPLOYEE_ID,               CASE                WHEN NSAL.INCREASE < 1000                  THEN                    (NSAL.SALARY + NSAL.INCREASE)                  ELSE                    (NSAL.SALARY + 1000)              END NEW_SALARY            FROM              (SELECT EMP.EMPLOYEE_ID                              ,EMP.SALARY                             ,(EMP.SALARY *0.07) INCREASE                FROM HR.EMPLOYEES EMP                WHERE     MONTHS_BETWEEN(SYSDATE,HIRE_DATE) > 24) NSAL        ) SALON (EM.EMPLOYEE_ID = SAL.EMPLOYEE_ID)WHEN MATCHED THEN  UPDATE SET  EM.SALARY = SAL.NEW_SALARY;

Entendiendo el código:
Dentro de nuestro USING del MERGE tenemos nuestro Query completo, que ya tiene filtrada la data que necesitamos.
El MERGE se hace directo a la tabla EMPLOYEES, usando la llave EMPLOYEE_ID para relacionar (como se puede ver en la sentencia ON).
Cuando los EMPLOYEE_ID hacen match, entonces se actualiza el campo SALARY de EMPLOYEES con el nuevo valor de salario que está dentro de nuestro query.

Como recomendación, en vez de usar SYSDATE usa una fecha cualquiera que te permita segmentar los datos que están en HR.EMPLOYEES  ya que los HIRE_DATES que allí están son antiguos entonces terminará por actualizar todos los empleados a esta fecha. Para mis pruebas usé TO_DATE('14/01/2007','DD/MM/YYYY'), esta fecha me permitió solamente actualizar 25 registros.

Espero que le sirva a alguien este ejemplo, cualquier comentario, corrección o sugerencia puedes comentarnos y será un placer apoyarte.