Clausula WITH Oracle 12C

Saludos,

La clausula WITH nos ayuda a simplificar los SQL complejos "SQL Complejos con WITH", en la versión 12C se incluye el poder incluir funciones y procedimiento dentro de la consulta cuya definición sea en linea durante la ejecución del SQL.

Exclusiones WITH
No pueden ser creados dentro de programación  PL/SQL o bloque anonimos.

Clausula WITH Function
Clausula para la definición de una función con el comando with

Declaracion.

WITH   FUNCTION with_function(p_valor IN NUMBER) RETURN NUMBER IS   BEGIN     RETURN p_valor;   END; SELECT with_function(level) FROM   dual WHERE  rownum = 1 /​ WITH_FUNCTION(LEVEL) --------------------                    0 1 row selected.

En este ejemplo vemos que declaramos la función with_function dentro de la clausula WITH y lo invocamos en el SQL.

Clausula WITH Procedure
Dentro del mismo With podemos realizar declaraciones de procedimiento que pueden o no estar siendo invocados.

Declaracion.

WITH   --Procedimiento   PROCEDURE with_procedure(p_valor IN NUMBER) IS   BEGIN     DBMS_OUTPUT.put_line('p_valor=' || p_valor);   END;   --Funcion   FUNCTION with_function(p_valor IN NUMBER) RETURN NUMBER IS   BEGIN     with_procedure(p_valor);     RETURN p_valor;   END; SELECT with_function(level) FROM   dual WHERE  rownum = 1 / WITH_FUNCTION(LEVEL) --------------------                    0 1 row selected. DBMS_OUTPUT p_valor=0

Como se ve en este ejemplo tenemos una función with_function y un procedimiento with_procedure. La funcion with_function llama al procedimiento with_procdure.


Clausula WITH SQL, Procedure y Function
Vamos a realizar algo mas complejo, vamos introducir un WITH SQL llamado with_sql el cual lo invocaremos desde el SQL principal.

Declaracion.

WITH   --Procedimiento   PROCEDURE with_procedure(p_valor IN NUMBER) IS   BEGIN     DBMS_OUTPUT.put_line('p_valor=' || p_valor);   END;   --Funcion   FUNCTION with_function(p_valor IN NUMBER) RETURN NUMBER IS   BEGIN     with_procedure(p_valor);     RETURN p_valor +1;   END;   --SQL   with_sql as   (     select rownum fila, sysdate+rownum  fecha  from dual connect by level <=5   ) SELECT a.fecha, a.fila,with_function(a.fila) resultado FROM   with_sql  a / FECHA          FILA  RESULTADO -------- ---------- ---------- 08/05/18          1          2 09/05/18          2          3 10/05/18          3          4 11/05/18          4          5 12/05/18          5          6 5 rows selected. ​DBMS_OUTPUT p_valor=1 p_valor=2 p_valor=3 p_valor=4 p_valor=5

Que es mejor, crear un PL/SQL o Definir en linea con WITH?
Todo esto dependerá la reutilización del código o su funcionalidad, por ejemplo si vamos a crear una funcion que solo va a funcionar en en un SQL específico no hay necesidad de crear un objeto Function y llenarnos de objetos. En este caso podemos usar la definición de la función dentro del WITH.

En cuestión de rendimiento la definición en linea tiene mejor desempeño. Veamos.

Para este ejemplo crearemos primero un función normal_function para poder hacer el comparativo con la with_function.

Creación de funcion PLSQL.

--Creacion de funcion PLSQL para probar CREATE OR REPLACE FUNCTION normal_function(p_valor IN NUMBER) RETURN NUMBER IS BEGIN   RETURN p_valor+1; END; /​

Script para comprar el rendimiento del WITH_FUNCTION VS NORMAL_FUNCTION. Para ello tomaremos los tiempos de procesador para comparar el tiempo de ejecución.

Comparativo WITH_FUNCTION VS NORMAL_FUNCTION.

DECLARE   l_time    PLS_INTEGER;   l_cpu     PLS_INTEGER;     l_sql     VARCHAR2(32767);   l_cursor  SYS_REFCURSOR;     TYPE t_tab IS TABLE OF NUMBER;   l_tab t_tab; BEGIN   l_time := DBMS_UTILITY.get_time;   l_cpu  := DBMS_UTILITY.get_cpu_time;   l_sql := 'WITH               FUNCTION with_function(p_valor IN NUMBER) RETURN NUMBER IS               BEGIN                 RETURN p_valor;               END;             SELECT with_function(level)             FROM   dual connect by level <1000000';               OPEN l_cursor FOR l_sql;   FETCH l_cursor   BULK COLLECT INTO l_tab;   CLOSE l_cursor;     DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||                        'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||                        'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');   l_time := DBMS_UTILITY.get_time;   l_cpu  := DBMS_UTILITY.get_cpu_time;   l_sql := 'SELECT normal_function(level)             FROM   dual connect by level<1000000 ';               OPEN l_cursor FOR l_sql;   FETCH l_cursor   BULK COLLECT INTO l_tab;   CLOSE l_cursor;     DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||                        'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||                        'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '); END; / WITH_FUNCTION  : Time=129 hsecs CPU Time=108 hsecs NORMAL_FUNCTION: Time=212 hsecs CPU Time=210 hsecs

Vemos que WITH_FUNCTION tomo casi la mitad del tiempo que NORMAL_FUNCTION.