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
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.
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.
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.
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.
Vemos que WITH_FUNCTION tomo casi la mitad del tiempo que NORMAL_FUNCTION.
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.
