Cláusula WITH FUNCTION (mejoras en la versión 12c de la base de datos Oracle)

Arquitectura de la version 12c de bases de datos Oracle

Uso de funciones PLSQL dentro de una sentencia SELECT

Ya hace bastante tiempo que los programadores de bases de datos Oracle podemos llamar a nuestras propias funciones PL/SQL desde una sentencia SQL. Por ejemplo, supongamos que definimos la función PORCENTAJE que realiza una serie de cálculos para devolver un porcentaje. La función puede ser algo tan sencillo como:

FUNCTION porcentaje (
  val1 IN NUMBER,
  val2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  RETURN (val1*100/(val1+val2));
END;

Utilizar dicha function dentro de una sentencia SELECT es tan sencillo como:

SELECT porcentaje (salario_base, salario_variable)
FROM empleados;

Esta aproximación, seguro que conocida por la mayoría que estáis leyendo este artículo, requiere que la función PL/SQL se haya almacenado en la base de datos Oracle, implicando un cambio de motores de ejecución desde el motor de ejecución SQL, que ejecuta la SELECT, al motor de ejecución PL/SQL, que ejecuta la función.

Ahora, con la base de datos Oracle 12c, es posible definir funciones y procedimientos PL/SQL dentro de la cláusula WITH de una SELECT y utilizarla dentro de la propia consulta. Por lo tanto, está nueva funcionalidad nos permitiría consolidar la función PORCENTAJE y la consulta SELECT mostradas anteriormente en una sola sentencia SQL:

WITH
  FUNCTION porcentaje (
    val1 IN NUMBER,
    val2 IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  RETURN (val1*100/(val1+val2));
  END;
SELECT porcentaje (salario_base, salario_variable)
FROM empleados;

No obstante muchos os estaréis preguntando: ¿pero qué ventaja puede tener copiar la lógica de una función PL/SQL dentro de una sentencia SELECT? Sobre todo si la función ya está almacenada en la base de datos Oracle y puede ser reutilizada por todo aquel que tenga los permisos adecuados.

La respuesta es bien sencilla, el rendimiento de la sentencia SELECT mejorará. El motivo es que cuando llamamos a la función PL/SQL desde la sentencia SELECT, según hemos mencionado anteriormente, se produce un cambio de motor de ejecución desde el motor SQL al PL/SQL, y este cambio afectará al rendimiento global de la sentencia SELECT. Mover el código de la función a la propia sentencia SELECT significa que este cambio de motor no se produce.

Además, la posibilidad de definir funciones PLSQL dentro de sentencias SELECT también es muy útil en aquellas bases de datos que son de solo lectura, es decir, donde no tenemos permisos para crear dichas funciones dentro de la propia base de datos. Y es que aunque no podamos crear funciones PL/SQL, no tendremos problemas en incluir la función dentro de la cláusula WITH de la sentencia SELECT.

Referencias a constantes en un paquete PL/SQL desde SQL

Aunque en versiones de la base de datos Oracle anteriores a la 12c ya era posible llamar desde una sentencia SQL a una función incluida en un paquete PL/SQL, lo que no era posible era hacer referencia a una constante declarada dentro de un paquete PLSQL (a menos que dicha sentencia SQL fuera ejecutada dentro del propio paquete).

Veamos un ejemplo concreto. Supongamos que creamos el siguiente paquete PL/SQL:

CREATE OR REPLACE PACKAGE mi_pack_plsql
IS
  nombre_empresa CONSTANT VARCHAR2(50) := 'Mi empresa PLSQL';
END;

Y ahora ejecutamos la siguiente sentencia SELECT:

SELECT mi_pack_plsql.nombre_empresa
FROM dual;

Ejecución que generará el siguiente error de la base de datos Oracle:

ORA-06553: PLS-221: 'NOMBRE_EMPRESA' no es un procedimiento o no se ha definido

Lógicamente existe una forma muy sencilla de solucionar este problema y que consiste en definir una función dentro del paquete PL/SQL:

CREATE OR REPLACE PACKAGE mi_pack_plsql
IS
  c_nombre_empresa CONSTANT VARCHAR2(50) := 'Mi empresa PLSQL';

  FUNCTION  nombre_empresa
  RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY mi_pack_plsql
IS
  FUNCTION  nombre_empresa
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN c_nombre_empresa;
  END;
END;

Y ahora nuestra sentencia SELECT funcionará sin problemas:

SELECT mi_pack_plsql.nombre_empresa
FROM dual;

NOMBRE_EMPRESA
————————————————
Mi empresa PLSQL

Con la versión 12c de la base de datos Oracle ya no es necesario definir la función dentro del paquete PL/SQL, sino que nos bastará con crear la función dentro de la cláusula WITH de la sentencia SELECT:

CREATE OR REPLACE PACKAGE mi_pack_plsql
IS
  nombre_empresa CONSTANT VARCHAR2(50) := 'Mi empresa PLSQL';
END;

WITH
  FUNCTION nombre_empresa
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN mi_pack_plsql.nombre_empresa;
  END;
SELECT nombre_empresa
FROM dual;

Para terminar solo indicaros que en mi opinión, la funcionalidad de poder utilizar la cláusula WITH FUNCTION dentro de una sentencia SELECT es bastante útil. No obstante antes de utilizarla, nos deberemos preguntar si dicha función vamos a necesitarla en múltiples sitios de nuestra aplicación, en cuyo caso deberemos decidir si la mejora en rendimiento conseguida al utilizar la cláusula WITH FUNCTION, compensa el esfuerzo de tener que copiar y pegar la función en diferentes lugares del nuestro código PL/SQL.