Funciones de grupo en SQL (SUM, AVG, COUNT, MAX y MIN)

Las llamadas group functions o funciones de grupo son funciones que operan sobre múltiples registros de una sentencia SELECT. En este artículo hablaré sobre las funciones de grupo más comunes del SQL (en un artículo posterior también hablaré sobre las cláusulas GROUP BY y HAVING muy relacionadas con este tipo de funciones).

Lo primero que hay que saber de las funciones de grupo es que hacen cálculos sobre un grupo de registros devolviendo un resultado único, por lo que permiten, por ejemplo, obtener totales.

Gestión de errores y ROLLBACKS, lo que hay que saber

Es importante conocer el hecho de que cuando se produce un error PLSQL no gestionado dentro de una sección EXCEPTION, este hecho no supone que automáticamente se realice un ROLLBACK y que todos los cambios realizados en la base de datos Oracle desde el último COMMIT se deshagan.

De hecho, a menos que nuestro código incluya de forma explícita la sentencia ROLLBACK dentro de una sección EXCEPTION, o que el error no gestionado lo propaguemos hasta la aplicación desde la que estamos ejecutando nuestro código PL/SQL, no se producirá ningún ROLLBACK.

Gestión de errores en PLSQL - Visión general

Incluso si fuéramos capaces de escribir un programa PL/SQL absolutamente perfecto, es altamente probable que algo pueda ir mal y se produzcan errores durante la ejecución. La manera en que nuestro código responde frente a estos errores, a menudo determina la diferencia entre una aplicación que funciona correctamente y otra que da continuos problemas a los usuarios y a los encargados de su mantenimiento.

Este es el primer artículo de una serie que escribiré sobre la gestión de errores en PLSQL. En ellos podréis leer sobre: los diferentes tipos de excepciones que se pueden dar; cuándo, cómo y por qué se generan excepciones; cómo definir nuestras propias excepciones; como manejar las excepciones cuando estas se producen; y cómo es posible informar a los usuarios cuando aparece un problema.

Sentencia CASE en PL/SQL de Oracle

Las versiones de base de datos Oracle 9i y posteriores incluyen la posibilidad de utilizar la sentencia CASE dentro de una sentencia SQL (SELECT, UPDATE, etcétera). La sentencia CASE permite realizar las mismas operaciones que las sentencias de control PL/SQL IF-THEN-ELSIF-ELSE pero con la particularidad de que pueden utilizarse dentro de una sentencia SQL. La sintaxis de la sentencia CASE es como sigue:

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.

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.

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.

SQL y PL/SQL - La sentencia INSERT multitabla

La versión 9i de las bases de datos Oracle ha introducido la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT ha cambiado 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.

Las funciones PL/SQL LPAD, RPAD, REPLACE, TRANSLATE, TRIM, LTRIM y RTRIM (manejo de cadenas de caracteres II)

En este artículo encontraréis toda la información necesaria para que desde vuestros programas PLSQL podáis realizar tres operaciones básicas con las cadenas de caracteres: rellenar dicha cadena con espacios u otros caracteres, reemplazar un determinado carácter o grupo de caracteres dentro de dicha cadena, o eliminar un determinado carácter o grupo de caracteres en la mencionada cadena.

Las bases de datos Oracle proporcionan diferentes funciones que pueden utilizarse en bloques SQL y PLSQL para realizar estas operaciones. Las funciones de las que estamos hablando son LPAD, RPAD,

El paquete DBMS_PARALLEL_EXECUTE, procesamiento en paralelo desde PL/SQL

En muchas ocasiones nos encontraremos con la necesidad de realizar operaciones DML (UPDATE, INSERT, etcétera) sobre millones de registros de una tabla. Escribir el código PL/SQL encargado de realizar estas operaciones no tiene por qué ser complicado, pero el manejo de los segmentos de rollback y conseguir que el rendimiento de dicho código sea bueno, es decir, que termine en un tiempo aceptable, puede ser harina de otro costal.

Todas las nuevas versiones de las bases de datos Oracle incluyen una amplia variedad de nuevas funcionalidades que amplían su capacidad. La release 2 de la versión 11g de las bases de datos Oracle no es una excepción a esta norma e incluye más de cincuenta nuevos paquetes PLSQL, siendo uno de ellos el DBMS_PARALLEL_EXECUTE.

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.

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.

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).

Oracle 11g y la cláusula PIVOT: Como 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 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.

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.

Nuevos tipos de datos PL/SQL soportados por la versión 12c al asociar datos vía SQL (SQL binding)

Anteriormente a la versión 12c de la base de datos Oracle, siempre que era necesario asociar una variable a una expresión PL/SQL mediante el uso de la sentencia EXECUTE IMMEDIATE o del paquete DBMS_SQL, el tipo de datos PLSQL de dicha expresión debía ser un tipo de dato SQL permitido. Concretamente, no era posible asociar datos de tipo BOOLEAN, ni tampoco tipos de datos definidos por el usuarios que hubiesen sido declarados en la especificación de un paquete PL/SQL, incluyendo registros y colecciones.

En la versión 12c se han eliminado prácticamente todas las restricciones de este tipo. Ahora, por ejemplo, se pueden asociar datos de tipo BOOLEAN para ejecutar un bloque de PLSQL dinámico con el comando EXECUTE INMEDIATE. Además, también se pueden asociar matrices asociativas y utilizarlas dentro de una llamada al operador TABLE. Ambas cosas no estaban soportadas por versiones anteriores a la 12c.

Sacar por pantalla los resultados de una consulta con DBMS_SQL.return_result (mejoras de Oracle 12c)

La nueva versión 12c de las bases de datos Oracle permite sacar por pantalla conjuntos de resultados de forma implícita. Esto es algo bastante útil si nos vemos en la tesitura de tener que migrar código escrito en SQL transaccional a código PL/SQL.

Para versiones anteriores de la base de datos Oracle, el PLSQL no soportaba la posibilidad de crear un procedimiento que simplemente volcase el contenido de una consulta SQL a la pantalla. Para conseguirlo, los desarrolladores de PL/SQL teníamos que escribir la consulta, recorrer con un bucle el conjunto de resultados devuelto por dicha consulta, y llamar en cada iteración al procedimiento estándar DBMS_OUTPUT.PUT_ LINE encargado de mostrar los datos en la pantalla.