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.

Cómo analizar la interdepencia entre los objetos de una base de datos Oracle

Para analizar y conocer las dependencias existentes entre funciones, procedimientos, paquetes y triggers a los que tiene acceso un usuario de la base de datos Oracle se debe utilizar la vista USER_DEPENDENCIES.

Esta vista, sin ir más lejos, se puede utilizar para realizar un análisis de nuestro código PL/SQL, permitiendo por ejemplo identificar que programas necesitan ser revisados y actualizados si realizamos algún tipo de cambio sobre una tabla determinada de la base de datos.

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.

Hints en PL/SQL para el modo de optimización

Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc) según nosotros le aconsejemos. En este primer artículo sobre los hints voy a empezar hablando de aquellos que se utilizan para seleccionar el modo de trabajar del optimizador Oracle. Estos hints, hablando desde un punto de vista práctico, no son muy utilizados, aunque no por ello pueden dejar de ser útiles en determinadas circunstancias.

Los hints se incorporan a una sentencia DML en forma de comentario y deben ir justo detrás del comando principal. Por ejemplo, si se tratara de una sentencia SELECT el formato sería el siguiente:

     SELECT /*+ COMANDO-HINT */ ...

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.

El paquete estándar DBMS_LOCK para sincronizar procesos

Hace unas semanas alguien me preguntó como se podía determinar si un procedimiento PL/SQL (procedure PLSQL) estaba siendo ejecutado para evitar tener dos instancias del mismo proceso corriendo simultáneamente. Para manejar este tipo de situaciones, así como situaciones en las que queramos ejecutar procedimientos y funciones de forma secuencial y sincronizar diferentes procesos, Oracle dispone de el paquete de funciones y procedimientos DBMS_LOCK.

Con el paquete DBMS_LOCK podemos establecer bloqueos de usuario (PL/SQL User Locks (UL)) con los que podremos parar la ejecución de un procedure si al ejecutarlo nos encontramos con que un determinado bloqueo o lock está en proceso.