Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP (conversión de fechas a caracteres y viceversa)

Pero antes de hablar de estas funciones, resulta conveniente conocer como se puede obtener desde PLSQL el valor de la fecha y el tiempo actual. Seguro que la gran mayoría de vosotros, si sois programadores PL/SQL, ya conocéis la clásica función SYSDATE, función que, sin duda, es la más empleada en este sentido. No obstante, la base de datos Oracle ofrece la posibilidad de utilizar otras funciones que proporcionan diferentes variantes del valor de la fecha y el tiempo actual y que veremos a continuación.

Las funciones PLSQL de las que hablo son:
FUNCION SQL        ZONA HORARIA      TIPO DE DATO DEVUELTO
CURRENT_DATE       Sesión            DATE
CURRENT_TIMESTAMP  Sesión            TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP     Sesión            TIMESTAMP
SYSDATE            Servidor de BBDD  DATE
SYSTIMESTAMP       Servidor de BBDD  TIMESTAMP WITH TIME ZONE

En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.
BEGIN
DBMS_OUTPUT.put_line (SYSDATE);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00
-000000000 00:00:00.437000000

En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.

También debéis daros cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.

Conversión de fechas a carácter

De igual manera que con la función TO_CHAR es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.

Veamos algunos ejemplos:

Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).
BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE));
  DBMS_OUTPUT.put_line (TO_CHAR (SYSTIMESTAMP));
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00

Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:
BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DD "de" Month "de" YYYY'));
END;
/

Martes   , 21 de Febrero    de 2012

En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:
BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DDth Month YYYY',
             'NLS_DATE_LANGUAGE=English'));
END;
/

Tuesday  , 21ST February  2012

En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.
BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'FMDay, DD "de" Month "de" YYYY'));
END;
/

Martes, 21 de Febrero de 2012

También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.

Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1

Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052

Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM

Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.

Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012

Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21

Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.

A continuación os dejo un ejemplo con varios usos de la función TO_DATE.
DECLARE
  l_date DATE;
BEGIN
  l_date := TO_DATE ('21-FEB-2012');
  l_date := TO_DATE ('022112', 'MMDDYY');
  l_date := TO_DATE ('21-Feb-12', 'DD-Mon-YY HH:MI:SS');
  l_date := TO_DATE ('Feb/21/12', 'Mon/DD/YY HH:MI:SS');
  l_date := TO_DATE ('Febrero.21.2012', 'Month.DD.YYYY HH:MI:SS');
END ;

Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.

Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":
SELECT TO_DATE ('21-02-2012', 'DD-MON-YYYY')
FROM dual;

Desde el punto de vista de la programación PL/SQL, conviene tener mucha precaución a la hora de utilizar las funciones TO_DATE o TO_TIMESTAMP, sobre todo si se utilizan sin especificar una máscara concreta (es decir, utilizarán la máscara que emplea la base de datos Oracle por defecto), ya que cambios posteriores del parámetro de base de datos NLS_DATE_FORMAT pueden provocar errores en nuestro programa.

Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL

De vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.

No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.

La consulta SQL era la siguiente:

SELECT COUNT(*)
INTO v_count
FROM tabla1 t1, tabla2 t2
WHERE t1.id = t2.id(+)
AND t2.otro_id = v_otro_id;

Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:

SELECT COUNT(*)
INTO v_count
FROM ...;

IF v_count > 0 THEN
ejecutar_algo();
ENF IF;

En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:

ejecutar_algo();

Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.

De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:

SELECT COUNT(*)
INTO v_count
FROM tabla1 t1, tabla2 t2
WHERE t1.id = t2.id
AND t2.otro_id = v_otro_id;

El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.

No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.

Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.