Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL

Las fechas son un tipo de datos del PL/SQL considerablemente más complejo que un tipo carácter o un tipo numérico. Una fecha o momento de tiempo está compuesto de múltiples campos (año, mes, día, hora, minutos, etcétera) y, además, existen un buen número de normas para determinar si una fecha es válida o no (los años bisiestos, los cambios de hora, etcétera). Como consecuencia de todo esto, en PLSQL resulta habitual tener que:

• Declarar constantes y variables de tipo fecha o tiempo.
• Utilizar funciones para modificar dichas variables y mostrarlas en el formato deseado por el usuario.
• Manipular fechas y tiempos para realizar cálculos variados.

Este artículo será el primero de una serie en los que explicaré todo lo que un programador PL/SQL necesita conocer para trabajar con los diferentes tipos de datos asociados con fechas y momentos de tiempo (DATE, TIMESTAMP e INTERVAL).

Manejo de excepciones en PL/SQL (excepciones definidas por el usuario)

Como continuación del anterior artículo dedicado al manejo de las excepciones PLSQL predefinidas, ahora voy a hablar de las excepciones definidas por el usuario.

PLSQL permite al usuario definir sus propias excepciones. Estas excepciones deben ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE. Este tipo de excepciones deben ser declaradas en el segmento DECLARE. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub-bloques que comprende.

Cambios en los parámetros de la base de datos Oracle, cuándo tienen lugar

Existe la creencia un tanto generalizada de que cuando realizamos un cambio en los parámetros de la base de datos Oracle (utilizando el comando SQL ALTER SYSTEM SET ...) mientras la instancia de la base de datos está operativa y si los cambios los realizamos utilizando el fichero de parámetros almacenado (SPFILE), estos cambios no tienen lugar hasta que la base de datos se reinicia. Esta idea es falsa y equivocada, ya que el comando SQL ALTER SYSTEM dispone del parámetro SCOPE para especificar en que momento deben tener efecto los cambios.

El parámetro SCOPE puede tomar los valores MEMORY, SPFILE, or BOTH. Si le asignamos el valor MEMORY el cambio aplicará de forma inmediata siempre que estemos cambiando un parámetro que no requiera el reinicio de la base de datos Oracle para que el cambio sea efectivo. Si le asignamos el valor SPFILE entonces únicamente estaremos modificando el fichero de parámetros almacenado y el cambio tendrá lugar en el siguiente reinicio de la base de datos Oracle. Pero si utilizamos el valor BOTH, entonces el cambio será inmediato y, además, cambiaremos el valor en el fichero de parámetros almacenado (SPFILE).

La cláusula PIVOT: Cómo girar columnas en SQL y PL/SQL

Una tarea que puede resultarnos útil en determinadas circunstancias es conseguir girar (en inglés pivot) los resultados entregados por una consulta SQL o PLSQL. Por ejemplo, supongamos que escribimos la siguiente consulta para calcular el precio medio de los productos por almacen:

SQL> SELECT almacen, producto, AVG(precio) pmedio
2  FROM  productos
3  GROUP BY almacen, producto
4  /

ALMACEN PRODUCTO PMEDIO
------- -------- ------
Ba      ABC123       95
Ba      DEF456       75
Ba      XYZ987      160
Ma      ABC123      100
Ma      XYZ987      150
Va      DEF456       80

6 rows selected.       

Uso de ediciones para actualizar la base de datos

Desde mi punto de vista, la funcionalidad Edition-Based Redefinition, cuya traducción directa sería "redefinición basada en ediciones", se trata de la característica más interesante de la release 2 de la base de datos Oracle 11g. En pocas palabras se trata de la posibilidad de actualizar nuestra aplicación PL/SQL sin tener que poner en modo restringido la base de datos, es decir, a partir de esta versión de la base de datos Oracle es posible realizar online la actualización de nuestra aplicación. Explicado de esta manera tan sencilla la mencionada funcionalidad suena como si se ratase de un juego de niños, pero en realidad es algo cuya verdadera dimensión resulta difícil de medir por sus enormes implicaciones.

Cómo usar la utilidad de trazado del SQL de Oracle

La utilidad de trazado del SQL de las bases de datos Oracle nos permite analizar el rendimiento de un determinado programa PL/SQL. Esta funcionalidad nos va a permitir obtener información acerca del rendimiento de todas las sentencias SQL que se ejecuten durante la ejecución del programa PLSQL.

Para utilizar la herramienta de trazado del PL/SQL de Oracle deberemos seguir cinco pasos:

1) Inicializar los parámetros relativos a esta funcionalidad SQL.
2) Activar la traza SQL.
3) Ejecutar la aplicación que queremos analizar y desactivar la traza cuando termine.
4) Formatear el fichero producido por la traza SQL con el comando TKPROF.
5) Interpretar la salida del comando TKPROF y, si es necesario, optimizar nuestro programa PLSQL.

Memoria PGA frente a espacio de almacenamiento temporal en las bases de datos Oracle

Recientemente he recibido en este blogs sobre programación PL/SQL algunas preguntas referentes a las diferencias entre el tipo de operaciones que la base de datos Oracle realiza en la memoria del área global de programa (PGA - Program Global Area) frente a las que realiza en el espacio del almacenamiento temporal (TEMP space). Primero os comentaré que las versiones antiguas de las bases de datos Oracle solían utilizar los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE para controlar cuanta memoria PGA era posible utilizar antes de empezar a tener que usar el espacio de disco temporal (TEMP).

No obstante en las nuevas versiones de las bases de datos Oracle, la gestión de la memoria PGA se realiza de forma automática y mucho más dinámica.

Manejo de excepciones en PL/SQL (excepciones predefinidas)

Los errores que se producen durante la ejecución de un bloque de código PL/SQL pueden ser manejados a gusto del programador, es decir, si durante la ejecución de una sentencia PLSQL se produce un error, podemos hacer que el programa realice unas acciones u otras dependiendo del tipo de error que se haya generado, esto es algo parecido a lo que se puede hacer cuando programamos en C++ o Java. Para conseguir esto debemos añadir dentro del bloque de código PL/SQL una sección para tratamiento de las excepciones.

Existen dos tipos de excepciones:

• Excepciones predefinidas.
• Excepciones definidas por el usuario.

En este artículo voy a hablar sólo de las excepciones predefinidas.

Tuning y constraints (o restricciones en la base de datos Oracle)

En este artículo continuaré con el caso de tuning PL/SQL planteado en el artículo Tuning de consultas SELECT COUNT(*) y determinaré cómo es posible mejorar aún más el rendimiento de la consulta SELECT objeto del mencionado artículo. Eso sí, para poder profundizar en el estudio del rendimiento, tuve que solicitar al lector que me hizo la pregunta inicial que me enviase los datos del esquema de la base de datos Oracle para las tablas involucradas en la consulta PLSQL SELECT. Después de un par de correos pude disponer de toda la información que necesitaba, los campos de las tablas, los índices asociados, las claves primarias (primary keys), las claves extranjeras (foreign keys), y las diferentes restricciones (constraints) aplicadas sobre las mencionadas tablas.

En suma, disponía de la siguiente información incluida en las siguientes sentencias SQL:

Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este segundo artículo continuaré hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } --+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes: