Sacar por pantalla los resultados de una consulta con DBMS_SQL.return_result

Desde la salida de la versión 12c de las bases de datos Oracle, es posible 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.

Asignación de permisos (ROLES) a los programas PLSQL (mejoras versión 12c)

Con anterioridad a la versión 12c de la base de datos Oracle, un procedimiento PLSQL o unidad de programa con privilegios definidos a través de la cláusula AUTHID DEFINER siempre se ejecutaba con los privilegios del propietario del programa. Por otro lado, cuando la misma unidad de programa se definía utilizando la cláusula AUTHID CURRENT_USER, dicho programa siempre se ejecutaba con los privilegios del usuario que lo ejecutaba.

Disponer de solo estas dos formas de utilización de la cláusula AUTHID limitaba bastante la funcionalidad de las bases de datos Oracle en cuestión de seguridad, ya que cuando un usuario necesitaba ejecutar un programa PL/SQL determinado, dicho usuario tenía que tener los mismos privilegios que el propietario del programa. El problema de seguridad se agravaba mucho más cuando eran todos los usuarios los que necesitan tener acceso a dicho programa.

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.

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.

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.       

Cláusula WITH FUNCTION (mejoras en la versión 12c de la base de datos Oracle)

Arquitectura de la version 12c de bases de datos Oracle

Uso de funciones PLSQL dentro de una sentencia SELECT

Ya hace bastante tiempo que los programadores de bases de datos Oracle podemos llamar a nuestras propias funciones PL/SQL desde una sentencia SQL. Por ejemplo, supongamos que definimos la función PORCENTAJE que realiza una serie de cálculos para devolver un porcentaje. La función puede ser algo tan sencillo como:

FUNCTION porcentaje (
  val1 IN NUMBER,
  val2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  RETURN (val1*100/(val1+val2));
END;

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.

Vistas materializadas (materialized views) en SQL y PL/SQL (1)

El SQL de las bases de datos Oracle permite crear vistas materializadas o materialized views. Estas vistas materializadas, a parte de almacenar la definición de la vista propiamente dicha, también almacenan los registros que resultan de la ejecución de la sentencia SELECT que define la vista. Como las vistas normales, la sentencia SELECT es la base de la vista, pero la sentencia SQL se ejecuta cuando se crea la vista y los resultados se almacenan físicamente constituyendo una tabla real que ocupa sitio en el disco duro. Esta tabla puede definirse utilizando los mismos parámetros de almacenamiento que se pueden utilizar para una tabla normal (tablespace, etcétera). Las vistas materializadas también admiten índices, esta funcionalidad resulta muy útil a la hora de mejorar el rendimiento de las sentencias PLSQL o SQL que utilicen vistas materializadas.

Recorrer colecciones PL/SQL (métodos y funciones de las colecciones)

Una operación que se debe realizar muy comúnmente con las colecciones PLSQL es tener que recorrerse todos los elementos de la misma. La razones para tener que recorrerse totalmente los datos de una colección pueden se múltiples, desde mostrar la información que almacenan hasta buscar un elemento específico o ejecutar una sentencia DML (de manipulación de datos dentro de la base de datos Oracle) utilizando los datos almacenados en los diferentes elementos de la colección.

El tipo de código PL/SQL que se debe utilizar para operar y recorrer una colección viene determinado por el tipo de colección con la que estemos trabajando y la forma en que dicha colección fue inicializada con datos. Lo normal es elegir entre utilizar un bucle WHILE o utilizar un bucle numérico FOR.

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.

El tipo de dato colección del PL/SQL (PLSQL collection datatype)

En Oracle una “colección” PL/SQL (PLSQL collection) es un tipo de datos compuesto consistente en una matriz de una sola dimensión que está compuesta por uno o más elementos accesibles a través de un índice.

Los datos de tipo colección se utilizan en algunas de las más importantes funcionalidades del PL/SQL asociadas con la optimización del rendimiento de las bases de datos Oracle tales como:

• FORALL: dentro de los cuales se incluyen sentencias INSERT, UPDATE y DELETE que utilizan colecciones para cambiar múltiples registros de datos de una manera muy rápida.
• BULL COLLECT: sentencias SELECT que devuelven múltiples registros en una simple extracción, incrementando notablemente la velocidad de la consulta.
• Funciones de tipo tabla: funciones PLSQL que devuelven datos de tipo colección y que pueden utilizarse en la cláusula FROM de una sentencia SELECT.

Las colecciones también se pueden utilizar para trabajar en nuestros programas con listas de datos que no está almacenados en tablas de las base de datos Oracle.

Las funciones PL/SQL CONCAT, UPPER, LOWER, INITCAP, SUBSTR e INSTR (manejo de cadenas de caracteres I)

Una vez que hemos visto cuales son los tipos de datos para manejar cadenas de caracteres, podemos pasar a estudiar las diversas funciones PL/SQL que permiten analizar el contenido de una cadena, combinarlo con otras cadenas, o simplemente cambiarlo de alguna manera controlada. Para ayudarnos con este tipo de requerimientos, la base de datos Oracle proporciona un conjunto bastante amplio de funciones PLSQL que permiten manejar cadenas de caracteres de una manera sencilla y cómoda.

En este capítulo hablaremos en concreto de las funciones CONCAT, UPPER, LOWER, INITCAP, SUBSTR e INSTR.

Gestión de errores en PL/SQL: generación de excepciones desde el código

En la mayoría de los casos, cuando una de nuestras aplicaciones PLSQL genera una excepción, es la base de datos Oracle la encargada de notificarlo automáticamente. Es decir, un programa PL/SQL reportará un error si durante la ejecución del mismo ocurre algún tipo de problema que la base de datos Oracle no puede manejar por si sola y nuestro código no tiene control sobre el mismo (ver artículo sobre el manejo de excepciones con PL/SQL).

Lo que muchos programadores desconocen es que PLSQL permite generar excepciones desde el propio código. ¿Por qué el código PL/SQL permite esto? Porque no todos los errores en una aplicación son debidos a fallos de procesamiento internos de la base de datos Oracle. También es posible que determinadas condiciones en los datos constituyan un error para nuestra aplicación, en cuyo caso necesitaremos parar nuestro programa y, con mucha probabilidad, notificar al usuario de que algo va mal.

Los tipos de dato CHAR, VARCHAR2 y CLOB (Cadenas de caracteres en PL/SQL)

Después de haber hablado de los tipos numérico y de fecha del PL/SQL, llega el turno de empezar con las cadenas de caracteres. Lo primero que hay que saber al respecto es que una cadena de caracteres es una secuencia de símbolos pertenecientes a un conjunto particular de caracteres. En otras palabras, una cadena de caracteres puede estar compuesta por las letras del abecedario español, pero también podría estar compuesta por un conjunto de caracteres chinos o japoneses.

En PL/SQL existe tres tipos de cadenas de caracteres:

• De longitud fija: de manera que la cadena de caracteres se rellena con espacios en blanco si su longitud es menor que la declarada.
• De longitud variable: al definir la variable se declara la longitud máxima que puede alcanzar pero no se rellena con espacios si la longitud de la cadena es menor que la declarada. La máxima longitud de una variable de este tipo es de 32,767 caracteres.
• Objetos CLOB (Character Large Objects): se trata de un tipo especial del PLSQL que permite almacenar cadenas de caracteres de longitud variable de hasta 128 terabytes.

Funciones númericas en PL/SQL y el SQL de Oracle

Las bases de datos Oracle ofrecen un extenso conjunto de funciones estándar SQL y PL/SQL para manipular números y realizar conversiones entre números y cadenas de caracteres. En este artículo hablaremos sobre las funciones numéricas más comunes y que se tienen que utilizar con mayor frecuencia a la hora de programar en PL/SQL, siendo su conocimiento fundamental para cualquier programador de bases de datos Oracle.

Las funciones numéricas estándar más comunes del PLSQL y el SQL de Oracle son:

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.

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:

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

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