Iniciación a Oracle - Programación PL/SQL

PL: El lenguaje de programación para SQL

Ya dijimos en los primeros capítulos que SQL es un lenguaje de comandos, no un lenguaje de programación con todas las estructuras de control típicas. Así, SQL sólo contempla instrucciones, más o menos simples, pero no tiene ningún tipo de instrucciones de control de flujo o de otro tipo más propias de los lenguajes de programación 3GL. Para subsanar esta carencia, Oracle definió un lenguaje de programación de tercera generación, que admitía sentencias SQL embebidas. Este lenguaje se llama PL/SQL (Programming Language/SQL)

La idea básica sobre la que se sustenta el PL/SQL es aplicar las estructuras típicas de un lenguaje de programación (bifurcaciones, bucles, funciones…) a las sentencias SQL típicas. Así podemos tener el siguiente pseudocódigo:

Sentencia SELECT que recupera el total de sueldos

Si el total de sueldos > 1.000.000 Sentencia UPDATE que incrementa un 10% los sueldos Si no Sentencias UPDATE que incrementa un 5% los sueldos Fin-si

Estructura básica en PL/SQL: El bloque de código

Cuando se escribe código en PL/SQL, este debe estar agrupado en unidades denominadas “bloques de código”. Un bloque de código puede contener otros sub-bloques de código y así sucesivamente.

Un bloque de código queda delimitado por las palabras reservadas BEGIN y END. Por ejemplo:

BEGIN Sentencias . . . Sentencias . . . Sentencias . . .

BEGIN

Sentencias . . .

Sentencias . . .

Sentencias . . .

END;

Sentencias . . .

Sentencias . . .

Sentencias . . . END;

En este ejemplo podemos ver que hay un bloque de código externo que contiene un bloque de código interno.

Un bloque de código opcionalmente puede contar con las siguientes secciones:

DECLARE

Declaración de variables BEGIN

Sentencias SQL y PL/SQL EXCEPTION

Manejadores de excepciones END;

La única sección obligatoria es la contenida dentro de BEGIN y END;

Comentarios

Los comentarios pueden ser multilínea encerrados entre /* y */ o monolínea, que comienzan por –

Declaración de variables

Las variables deben declararse dentro de la sección DECLARE y deben seguir la siguiente sintaxis:

Nombre_de_variable {CONSTANT} TIPO {:= inicialización};

Los tipos posibles son todos aquellos válidos para SQL añadiendo algunos propios de PL/SQL. Para más información sobre los tipos propios de PL/SQL consultar el PL/SQL User’s Guide and Reference

Ejemplos:

Interes NUMBER(5,3); Descripcion VARCHAR2(50) := ‘inicial’; Fecha_max DATE; Contabilizado BOOLEAN := TRUE; PI CONSTANT REAL := 3.14159

Estructuras básicas de control

Como PL/SQL es un lenguaje 3GL, cuenta con las estructuras típicas de control de flujo: bifurcaciones condicionales y bucles:

Bifurcaciones condicionales

La sintaxis básica es:

IF condición_1 THEN Se ejecuta si se cumple condicion_1 ELSIF condicion_2 THEN –- ojo a ‘ELSIF’ y no ELSEIF Se ejecuta si no se cumple condicion_1 y se cumple condicion_2 ELSE Se ejecuta si no se cumple condicion_1 ni condicion_2 END IF;

Como en cualquier lenguaje de programación, las estructuras IF se pueden anidar unas dentro de otras.

Bucles Existen varias variantes de la estructura bucle. La más sencilla es la siguiente:

LOOP sentencias END LOOP;

Las sentencias de dentro del bucle se ejecutarán durante un número indefinido de vueltas, hasta que aparezca la instrucción EXIT; que finalizará el bucle. Este tipo de bucle se denomina bucle incondicional.

Otra opción es incluir la estructura EXIT WHEN condición, se terminará el bucle cuando la condición se cumpla:

LOOP Sentencias EXIT WHEN condicion; Sentencias

END LOOP;

El bucle anterior es equivalente al siguiente:

LOOP Sentencias IF condicion THEN

EXIT; END IF; Sentencias

END LOOP;

Un tipo de bucle más común son los bucles condicionales:

WHILE condicion LOOP Sentencias END LOOP;

Y por último el bucle FOR:

FOR contador IN {REVERSE} limite_inferior..limite_superior LOOP sentencias END LOOP;

Contador deberá ser una variable de tipo numérico que sea capaz de contener los valores comprendidos entre limite_inferior y limite_superior. Limite_inferior y limite_superior deberán ser expresiones numéricas, ya sean constantes (1,10…) o funciones (ROUND(max,0), ASCII(‘A’)…)

Si la variable contador no está definida, PL/SQL definirá una variable de tipo INTEGER al iniciar el bucle, y la liberará al finalizar el bucle.

Registros y tablas

Existen dos tipos de datos que no hemos mencionado anteriormente: los registros (o estructuras) y las tablas (o arrays o vectores).

Los dos tipos deben ser definidos en un como un nuevo tipo antes de declarar variables de ese nuevo tipo.

El modo de definir nuevos tipos de variables en PL/SQL es a través de la palabra reservada TYPE:

TYPE nuevo_tipo IS tipo_original.

Una vez definido en nuevo tipo, ya se pueden definir variables de ese nuevo tipo:

Una_variable nuevo_tipo;

Registros Los registros no son más que agrupaciones de tipos de variables que se acceden con el mismo nombre. La sintaxis de definición de registros es:

TYPE nombre_registro IS RECORD( Campo1 tipo, Campo2 tipo, Campo3 tipo );

Por ejemplo:

TYPE alumno IS RECORD( n_alumno VARCHAR2(5), nombre VARCHAR2(25), apellido_1 VARCHAR2(25), apellido_2 VARCHAR2(25), tlf VARCHAR2(15) );

Tablas Una tabla no es más que una colección de elementos identificados cada uno de ellos por un índice. En muchos lenguajes se les denomina arrays. La sintaxis de definición de tablas es:

TYPE nombre_tabla IS TABLE OF tipo_de_elementos;

El tamaño de la tabla se define durante la declaración de la variable

Nombre_variable nombre_tabla := nombre_variable(lista elementos);

Por ejemplo:

DECLARE TYPE array_enteros IS TABLE OF INTEGER;

Un_array array_enteros := array_enteros( 0, 0, 0, 0 );

BEGIN . . . END;

El ejemplo anterior define un tipo de array de enteros y después declara una variable de ese tipo, inicializándola a 4 elementos (todos con 0).

Excepciones

Anteriormente dijimos que un bloque de código puede contener una sección denominada EXCEPTION. Esta sección es la encargada de recoger todas las anomalías que se puedan producir dentro del bloque de código.

Una excepción es una situación especial dentro de la ejecución de un programa, que puede ser capturada para asignar un nuevo comportamiento. Una excepción puede ser un error de ejecución (una división entre 0) o cualquier otro tipo de suceso.

Las excepciones deben ser declaradas dentro de la sección DECLARE, como si de una variable se tratasen:

DECLARE e_sin_alumnos EXCEPTION;

Una vez que la excepción está definida, ésta debe ser lanzada, ya sea automáticamente por Oracle, o lanzada manualmente a través de la instrucción RAISE.

SELECT COUNT(*) INTO num_alumnos;

IF num_alumnos = 0 THEN RAISE e_sin_alumnos; END IF;

Una vez que la excepción ha sido lanzada, la ejecución continua en la sección EXCEPTION, concretamente en el manejador apropiado (o el manejador WHEN OTHERS cuando no exista el específico).

Un manejador de excepciones es una sub-sección dentro de la sección EXCEPTION que se encarga de capturar una excepción concreta.

La sintaxis para escribir manejadores es:

EXCEPTION WHEN THEN . . . WHEN THEN . . . WHEN OTHERS THEN . . . END;

Las líneas de código debajo del manejador específico se ejecutarán cuando esa excepción se produzca.

Un ejemplo completo:

DECLARE e_sin_alumnos EXCEPTION; num_alumnos NUMBER(5);

BEGIN SELECT COUNT(*) INTO num_alumnos;

IF num_alumnos = 0 THEN RAISE e_sin_alumnos; END IF;

EXCEPTION

WHEN e_sin_alumno INSERT INTO ERROR( FECHA, DESCRIPCION ) VALUES( SYSDATE, ‘No se han encontrado alumnos en la tabla ALUMNO.’);

WHEN OTHERS

Raise_application_error( -20000, ‘Error en bloque de codigo PL/SQL’ ); -- este error se transmite a la aplicación que llame a este bloque -- de código (PL/SQL, Java, C++, etc.) END;

Anteriormente habíamos dicho que las excepciones puede lanzarse automáticamente o manualmente a través de la instrucción RAISE.

Algunas excepciones se lanzarán automáticamente cuando se produzcan ciertos tipos de errores en la ejecución del bloque de código. Cada excepción automática tiene asociado un código de error ORA-XXXX el cual si se produce, hará que se lance la excepción correspondiente.

A continuación se muestra una lista de las excepciones automáticas predefinidas por Oracle:

Cursores

Cuando dentro de un intérprete SQL escribimos una consulta SELECT, el intérprete nos muestra las distintas filas del resultados para que podamos verlas. Sin embargo, dentro de un lenguaje de programación tenemos un problema, ya que lo más común no es mostrar el resultado, sino almacenarlo en variables para su posterior tratamiento.

Ahora tenemos que dividir el problema en dos partes, dependiendo del número de filas que nos retorna la consulta SELECT:

· Si retorna cero o una fila: El valor se podrá almacenar en tantas variables como columnas consultadas. Es decir, si escribimos un SELECT de tres columnas, y sólo retorna una fila (matriz 1x3), podremos almacenar el valor dentro de tres variables definidas para este uso. El modo de hacer esto en PL/SQL es:

SELECT col1, col2, col3 INTO var1, var2, var3 FROM TABLA;

De este modo se almacenará en las variables var1, var2 y var3 los valores recuperados por la consulta SELECT o NULL si esta consulta no retorna ninguna fila.

· Si retorna más de una fila: En este caso no es posible almacenar directamente los valores en variables. Para ello existen los cursores, que no son más que consultas SELECT que se recuperar fila a fila y no todo su conjunto de resultados a la vez.

Para utilizar realizar una consulta SELECT … INTO dentro de un bloque de código no hay más que escribir la consulta en el lugar adecuado y ésta se ejecutará y retornará el valor a las variables correspondientes.

Sin embargo para realizar una consulta a través de un cursor hay que realizar los siguientes pasos:

1.- Declarar el cursor (dentro de la sección DECLARE) 2.- Abrir el cursor en el servidor 3.- Recuperar cada una de sus filas (bucle) 4.- Cerrar el cursor

1.- Declarar el cursor En este paso se define el nombre que tendrá el cursor y qué consulta SELECT ejecutará. No es más que una declaración. La sintaxis básica es:

DECLARE CURSOR nombre_cursor IS SELECT . . . FROM . . .;

Una vez que el cursor está declarado ya podrá ser utilizado dentro del bloque de código.

2.- Abrir el cursor en el servidor Un cursor internamente es una sentencia SELECT cuyo resultado se guarda en el servidor en tablas temporales y que se va retornando cada una de las filas según se va pidiendo desde el cliente. El primer paso es ejecutar la sentencia SELECT y guardar su resultado dentro de las tablas temporales. Este paso se denomina Abrir el cursor. La apertura del cursor debe realizarse sólo una vez. La sintaxis de apertura de un cursor es:

OPEN nombre_cursor;

Una vez que el cursor está abierto, se podrá empezar a pedir los resultados al servidor.

3.- Recuperar cada una de sus filas Una vez que el cursor está abierto en el servidor se podrá hacer la petición de recuperación de fila. Este paso es equivalente a hacer una consulta SELECT de una sola fila, ya que estamos seguros de que no vamos a recuperar más de una fila. La sintaxis de recuperación de fila de un cursor es:

FETCH nombre_cursor INTO variables;

Podremos recuperar filas mientras la consulta SELECT tenga filas pendientes de recuperar. Para saber cuándo no hay más filas podemos consultar los siguientes atributos de un cursor:

Así, la acción más típica es recuperar filas mientras queden alguna por recuperar en el servidor.

Esto lo podremos hacer a través del siguiente bloque de código:

LOOP FETCH nombre_cursor INTO variables; EXIT WHEN nombre_cursor%NOTFOUND;

> END LOOP;

4.- Cerrar el cursor Una vez que se han recuperado todas las filas del cursor, hay que cerrarlo para que se liberen de la memoria del servidor los objetos temporales creados. Si no cerrásemos el cursor, la tabla temporal quedaría en el servidor almacenada con el nombre dado al cursor y la siguiente vez ejecutásemos ese bloque de código, nos daría la excepción CURSOR_ALREADY_OPEN (cursor ya abierto) cuando intentásemos abrir el cursor. Para cerrar el cursor se utiliza la siguiente sintaxis:

CLOSE nombre_cursor;

Funciones, procedimientos y paquetes

Una vez que tenemos escrito un bloque de código, podemos guardarlo en un fichero .SQL para su posterior uso, o bien guardarlo en base de datos para que pueda ser ejecutado por cualquier aplicación.

A la hora de guardar un bloque de código hay que tener en cuenta ciertas normas:

1.- Palabra reservada DECLARE desaparece

2.- Podremos crear procedimientos y funciones. Los procedimientos no podrán retornar ningún valor, mientras que las funciones deben retornar un valor de un tipo de dato básico.

Para crear un procedimiento (stored procedure: procedimiento almacenado) usaremos la siguiente sintaxis:

CREATE {OR REPLACE} PROCEDURE nombre(tipo_dato p1, tipo_dato p2...) IS > BEGIN

{EXCEPTION} END;

Para crear una función usaremos la siguiente sintaxis:

CREATE {OR REPLACE} FUNCTION nombre(tipo_dato p1, tipo dato p2...