Procedimientos y Funciones

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:
- Verificar si el usuario tiene permiso de ejecución.
- Verificar la validez del procedimiento o función.
- Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:
- Facilidad para gestionar la seguridad.
- Mejor rendimiento al estar compilados y almacenados en la base de datos.
- Mejor gestión de la memoria.
- Mayor productividad e integridad.

La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).

Sintaxis de un procedimiento PLSQL:

Sintaxis de una función PLSQL:

Aclaraciones sobre la sintaxis:

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

EJEMPLO:

Para poder ejecutarlo escribimos:

EXECUTE ver_precio(7);
20000

Procedimiento PL/SQL terminado con éxito.

Bloque Anonimo

Empezaremos con los bloques anónimos, caracterizados porque no tienen nombre y se suelen crear y ejecutar desde PL/SQL.

Todo bloque debe acabar en . para que sea almacenado en el buffer SQL. Una vez guardado lo podemos ejecutar con la orden “run”. También podemos guardarlo en un fichero con la siguiente orden:

save nombrefichero [replace]

El replace sólo lo pondremos si el fichero ya esta creado.

Para cargar y ejecutar este bloque anónimo guardado en fichero ejecutaremos la siguiente orden:

start nombrefichero

El start lo podemos cambiar por la @ y nos funcionará igualmente.

Pero también podemos cargarlo sin ejecutarlo con la orden “get” y luego ejecutarlo posteriormente con la orden “run”.

Además en los bloques PL/SQL se pueden utilizar lo que llamamos variables de sustitución, que nos pedirán datos por pantalla antes de ejecutar el bloque. Estas variables tienen que ir antepuestas del & para que funcionen.

Aqui dejamos un ejemplo sencillo de un Bloque Anonimo:

Gestion de Excepciones

En PL/SQL una advertencia o condición de error es llamada una excepción.

Las excepciones se controlan dentro de su propio bloque.La estructura de bloque de una excepción se muestra a continuación.

Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias.

El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN.

Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior.

Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones.

Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR.

Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE.

Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio.

Uso de Cursores

PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT.

Podemos distinguir dos tipos de cursores:

Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro.
Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos.

Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración.

El siguiente ejemplo declara un cursor explicito:

Para procesar instrucciones SELECT que devuelvan más de una fila, son necesarios cursores explicitos combinados con un estructura de bloque.

Un cursor admite el uso de parámetros. Los parámetros deben declararse junto con el cursor.

El siguiente ejemplo muestra la declaracion de un cursor con un parámetro, identificado por p_continente.

Estructuras de Control

En PL/SQL solo disponemos de la estructura condicional IF. Su sintaxis se muestra a continuación:

Un aspecto a tener en cuenta es que la instrucción condicional anidada es ELSIF y no "ELSEIF".

Sentencia GOTO

PL/SQL dispone de la sentencia GOTO. La sentencia GOTO desvia el flujo de ejecució a una determinada etiqueta.

En PL/SQL las etiquetas se indican del siguiente modo: << etiqueta >>

El siguiente ejemplo ilustra el uso de GOTO.

Bucles

En PL/SQL tenemos a nuestra disposición los siguientes iteradores o bucles:

LOOP
WHILE
FOR

El bucle LOOP, se repite tantas veces como sea necesario hasta que se fuerza su salida con la instrucción EXIT. Su sintaxis es la siguiente

El bucle WHILE, se repite mientras que se cumpla expresion.

El bucle FOR, se repite tanta veces como le indiquemos en los identificadores inicio y final.

En el caso de especificar REVERSE el bucle se recorre en sentido inverso.

Definicion de datos compatibles con SQL

En sql tenemos varios tipos de datos. Cuando creamos una tabla con la instrucción create table, tenemos que especificar el tipo de dato de cada columna.

En la siguiente tabla mostramos los diferentes tipos de datos y sus principales caracteristicas:

Tipo de de dato	Caracteristicas
VARCHAR2(tamaño)	Almacena cadenas de caracteres de una longitud variable. La longitud máxima son 4000 caracteres.
CHAR(tamaño)	Almacena caracteres con una longitud fija. Siendo 2000 caracteres el máximo
NUMBER(precision,escala)	Almacena datos numéricos, tanto enteros como decimales, con o sin signo. Precisión, indica el número máximo de digitos que va a tener el dato. Escala, indica el número de digitos que puede haber a la derecha del punto decimal.
LONG	Almacena cadenas de caracteres de longitud variable. Puede almacenar hasta 2 gigas de información
LONG RAW	Almacena datos binarios. Se emplea para el almacenamiento de gráficos, sonidos, etc. Su tamaño máximo es de 2 gigas
DATE	Almacena información de fechas y horas. De forma predeterminada almacena un dato con el siguiente formato: siglo/año/mes/dia/hora/minutos/segundos. Este formato se puede cambiar con otros parámetros.
RAW(tamaño)	Almacena datos binarios. Puede almacenar como mucho 2000 bytes.
ROWID	Se trata de un campo que representa una cadena hexadecimal que indica la dirección de una fila en su tabla
NVARCHAR2(tamaño)	Es similar al varchar2 pero el tamaño de un carácter depende de la eleccion del juego de caracteres. El tamaño máximo es 2000 bytes.
NCHAR(tamaño)	Similar al char y con las mismas caracteristicas que el nvarchar2
CLOB	Similar al LONG y se usua para objectos carácter
NCLOB	Similar al CLOB solo que el tamaño del carácter depende del juego de caracteres utilizado
BLOB	Similar al LONG RAW. Este se usa para objetos binarios.

Aqui mostramos un ejemplo:

Bloques PL/SQL

Un programa de PL/SQL está compuesto por bloques. Un programa está compuesto como mínimo de un bloque.

Los bloques de PL/SQL pueden ser de los siguientes tipos:

Bloques anónimos
Subprogramas

Los bloques PL/SQL presentan una estructura específica compuesta de tres partes bien diferenciadas:

La sección declarativa en donde se declaran todas las constantes y variables que se van a utilizar en la ejecución del bloque.
La sección de ejecución que incluye las instrucciones a ejecutar en el bloque PL/SQL.
La sección de excepciones en donde se definen los manejadores de errores que soportará el bloque PL/SQL.

Cada una de las partes anteriores se delimita por una palabra reservada, de modo que un bloque PL/SQL se puede representar como sigue:

De las anteriores partes, únicamente la sección de ejecución es obligatoria, que quedaría delimitada entre las cláusulas BEGIN y END. Veamos un ejemplo de bloque PL/SQL muy genérico. Se trata de un bloque anónimos, es decir no lo identifica ningún nombre. Los bloques anónimos identifican su parte declarativa con la palabra reservada DECLARE.