Inicialización y asignación de valores a colecciones PL/SQL

Cuando se trabaja en PLSQL con tablas anidadas y varrays (ver artículo sobre los tipos de dato colección del PL/SQL), es necesario inicializar la variable colección antes de que pueda ser utilizada. Para conseguir esto se puede utilizar la denominada función constructora, una función que la base de datos Oracle crea de forma automática cuando se declara la variable colección.

Los que hace la función constructora es construir una instancia del tipo correspondientes al de la función. La función puede ser llamada sin utilizar argumento alguno, aunque también se le pueden pasar una o más expresiones del mismo tipo que sean elementos de la colección PL/SQL, elementos que se insertarán en la colección.

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

Este artículo es continuación del anterior en el que hablé sobre los tipos de dato fecha DATE, TIMESTAMP e INTERVAL y en él hablaré sobre las funciones PL/SQL que se pueden utilizar para convertir datos de tipo fecha en datos de tipo carácter 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.

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.

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.       

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.

Utilidad del paquete estándar PL/SQL DBMS_ROWID

Hace unas semanas me llegó una consulta sobre PL/SQL en la que se me preguntaba si era posible conocer, utilizando una consulta SQL, el nombre de la partición en la que se encontraba almacenado un determinado registro de una tabla. El paquete estándar PLSQL DBMS_ROWID nos puede ayudar a obtener esta información mediante la extracción del ROWID_OBJECT que identifica de manera única el segmento donde se encuentran los datos. Después bastará que asociemos este identificador con una de las vistas XXX_OBJECTS (donde XXX puede ser DBA, ALL o USER) para obtener el nombre de la partición (ver ejemplo a continuación).

SELECT sh.order_number
     , do.subobject_name
     , do.data_object_id
  FROM oe.so_headers_all sh
     , dba_objects do
 WHERE do.data_object_id = 
       DBMS_ROWID.ROWID_OBJECT(sh.rowid)
   AND sh.order_number = '123456'

ORDER_NUMBER SUBOBJECT_NAME DATA_OBJECT_ID
------------ -------------- --------------
123456       PART1          15107         

Cómo evitar el uso de constantes fijas (hard-coded) en PL/SQL

Esta claro que la mayoría de los programas y aplicaciones PLSQL tienen su propio conjunto de constantes que determinan las características de dicha aplicación. Por lo general, estos valores constantes tienen que ser utilizados en distintos lugares del código. En muchas ocasiones estos valores permanecerán invariables durante todo el ciclo de vida de la aplicación pero, en muchos otros casos, cambiarán de forma periódica (por ejemplo, una vez al año).

Pongamos un ejemplo, supongamos que en un programa PL/SQL establecemos que el salario anual de un empleado no puede superar los 50.000 euros. Podemos codificar esta norma utilizando la siguiente subrutina:

Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento

En este artículo o voy a proponer un ejercicio práctico bastante sencillo que puede resultar de bastante útilidad práctica. Supongamos que queremos crear un procedimiento PLSQL que utilice un par de parámetros de entrada, que podrán tomar el valor NULL, y con un parámetro de salida que será el cursor PL/SQL correspondiente a la siguiente sentencia SELECT:

SELECT * FROM empleados
WHERE nombre = parametro_1
AND apellidos = parametro_2;

Cómo ejecutar sentencias DDL dentro de un trigger PL/SQL

Supongo que muchos conoceréis el hecho de que no es posible incluir sentencias DDL (es decir, sentencias de definición de objetos como CREATE, REVOKE, GRANT, ALTER, etcétera) dentro de un trigger PL/SQL. Esto es un hecho que tiene una explicación muy sencilla, ¿qué ocurriría si dentro de un trigger ejecutamos una sentencia no transaccional o DDL y necesitamos deshacer la transacción (rollback)? Sencillamente no podríamos deshacer la ejecución de dicha sentencia. En esta situación nuestra transacción dentro del trigger PLSQL no habría tenido lugar, pero la sentencia DDL se habría ejecutado dejando nuestra base de datos Oracle en una situación claramente no deseable.

La ejecución de sentencias no transaccionales en un trigger sólo puede derivar en problemas, así que, en el caso improbable de que necesitemos ejecutar una sentencia DDL dentro de un trigger, mi recomendación es utilizar el paquete PLSQL DBMS_JOB para programar la ejecución de dicha sentencia DDL (CREATE, REPLACE, DROP, etc.).

Problemas con los triggers SQL

Mucha gente piensa que los triggers PL/SQL son una de las más potentes herramientas de las bases de datos Oracle. De hecho lo son, pero existen dos razones fundamentales por las que, personalmente, trato de evitar la utilización de triggers a la hora de implementar mis proyectos en PL/SQL.

Problemas de mantenimiento

A largo plazo, la utilización de triggers suele causar grandes dolores de cabeza a la hora de pensar en el mantenimiento. Al ser piezas del código que sólo ocurren como consecuencia de que se ha realizado otra operación, es muy frecuente que la gente se olvide de que los triggers están allí, y revisar el código pensando en todas las piezas de la base de datos que pueden afectarle, se hace poco menos que imposible.

Vistas materializadas y la funcionalidad "Query Rewrite"

Ya he escrito anteriormente un par de artículos sobre vistas materializadas (materialized views): uno sobre los aspectos generales de las vistas materializadas en SQL y PLSQL y otro sobre el refresco de las vistas materializadas en SQL y PL/SQL. En este artículo voy a tratar una de las funcionalidades soportadas por las vistas materializadas, funcionalidad conocida como QUERY REWRITE.

Funcionalidad de reescritura de una consulta

Esta claro que acceder a una vista materializada puede ser significativamente más rápido que acceder a todas las tablas base utilizadas al crear dicha vista materializada. Es por esta causa por la que, si así lo hemos indicado al crear la vista materializada, el optimizador Oracle, si la consulta o query lo permite, puede reescribir el plan de ejecución de dicha consulta para acceder a la vista en lugar de a las tablas base. Obviamente, la reescritura de la consulta es transparente a las aplicaciones que la estén utilizando. Así pues, de alguna manera, el uso del QUERY REWRITE es similar al uso de un índice.

El refresco de las vistas materializadas en SQL y PL/SQL

Ya he hablado en otro artículo acerca del funcionamiento básico de las vistas materializadas (materialized views), en éste voy a exponer los distintos tipos de refresco en SQL y PLSQL que se pueden utilizar para actualizar una vista materializada con los cambios provocados por las actualizaciones en las tablas base utilizadas en la misma. El tipo de refresco que debemos elegir dependerá de la frecuencia de actualización de las tablas base y de las necesidades que tengamos de disponer de datos exactos.

Tipos de refresco

COMPLETE: Este tipo de refresco implica el borrado de los datos existentes y la reinserción de todos los datos mediante la reejecución de la consulta SELECT que define la vista materializada.

SQL y PL/SQL - La sentencia INSERT multitabla

La versión 9i de las bases de datos Oracle introdujo la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT cambió ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Ejecución de cursores PLSQL y sentencias DML utilizando SQL dinámico nativo (Native Dynamic SQL)

Una de las preguntas más frecuentes que me suele hacer la gente es acerca de la posibilidad de definir un procedimiento PL/SQL en el que se declaren múltiples cursores en base al valor de los parámetros de entrada de dicho procedimiento. Las preguntas suelen incluir condiciones muy variadas, pero lo normal es que los implicados sólo necesiten hacer variable la cláusula WHERE y que el resto del cuerpo del cursor PLSQL se mantenga fijo. Cuando esto ocurre yo siempre contesto remitiendo a los que preguntan al artículo que escribí sobre el paquete estándar PL/SQL DBMS_SQL, un paquete que permite crear sentencias SQL dinámicas.

Si he decidido escribir un artículo nuevo al respecto es por el hecho de que resulta mucho más legible un código que utilice directamente SQL dinámico nativo, es decir, un código en el que no se utiliza el paquete estándar DBMS_SQL. A continuación os dejo un sencillo ejemplo de cómo se podría construir, utilizando SQL dinámico nativo, un procedimiento PLSQL en el que se define un cursor cuya cláusula WHERE varíe en función de un parámetro.

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

La cláusula PIPELINED en las funciones PL/SQL y la excepción NO_DATA_NEEDED

En esta entrada explicaré para que sirve la cláusula PIPELINED en las funciones PL/SQL, y como se debe utilizar la excepción NO_DATA_NEEDED, que nada tiene que ver con la excepción NO_DATA_FOUND, para controlar las funciones PLSQL que incluyen dicha cláusula y que en inglés se denominan pipelined functions. Primero quiero remarcar que la funcionalidad pipelined fue introducida por primera vez en la versión 9i de las bases de datos Oracle. Básicamente, el uso de la cláusula PIPELINED resulta de gran utilidad y es prácticamente imprescindible cuando necesitamos que en lugar de una tabla sea una rutina PL/SQL la que nos sirva como fuente de datos.

La mejor forma de explicar el funcionamiento de esta cláusula es con un ejemplo.

SQL y PL/SQL - La sentencia MERGE

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

La claúsula WITH en SQL y PL/SQL

La versión 9i de las bases de datos Oracle permite el uso de la claúsula WITH en SQL y PLSQL. Este comando permite reusar una consulta SELECT cuando esta hay que utilizarla más de una vez en una sentencia o consulta SQL compleja. Los resultados de la consulta definida en la claúsula WITH son almacenados en una tabla temporal pudiendo de esta forma mejorar el rendimiento de la sentencia principal.

Aunque no siempre conseguiremos mejorar el rendimiento utilizando la claúsula WITH, lo que sin duda facilitaremos es la lectura y el mantenimiento del código PL/SQL o SQL. Dentro de la claúsula WITH daremos un nombre a las consultas SELECT a reutilizar (WITH admite la definición de múltiples consultas con sólo separarlas por comas), dicho nombre será visible para todas las consultas definidas posteriormente dentro del mismo WITH. Obviamente, también será visible para la sentencia o consulta principal.

Operaciones aritméticas con fechas y la función TRUNC del PL/SQL

Siguiendo con el tema de algunos de los artículos de este blog, hoy pretendemos dejar ya zanjado el tutorial sobre como podemos trabajar en PL/SQL con los campos tipo DATE y TIMESTAMP de las bases de datos Oracle. En esta ocasión empezaremos hablando de las operaciones aritméticas que podemos realizar con estos campos y terminaremos escribiendo sobre la función estándar del PLSQL TRUNC.

Las bases de datos Oracle permiten realizar una gran variedad de operaciones aritméticas con los tipos de datos DATE y TIMESTAMP, pudiendo realizar dichas operaciones de diversas maneras.