Tema 3.3 - Procedimientos almacenados

Es un subprograma con nombre que realiza una o más tareas especificadas y puede devolver o no valores, a diferencia de las funciones que por obligación deben retornar un valor. La manera en la que los procedimientos devuelven valores es por medio del parámetro OUT que se explicará más adelante.

Se debe tener en cuenta que una vez que se crea un procedimiento queda guardado en la BD para cuando se requiera utilizarlo nuevamente.

Un procedimiento almacenado se puede dividir en dos partes:

1. Encabezado: Contiene el nombre del procedimiento y los parámetros (opcionales) pasados.
2. Cuerpo: Esta parte contiene todo lo que va a realizar el procedimiento.

A continuación, se muestra la sintaxis para crear el procedimiento

Donde:

1. Los corchetes son partes del código que son opcionales.
2. nom_proc es el nombre del procedimiento.
3. La cláusula REPLACE reemplaza el procedimiento si existe.
4. El bloque PL/SQL comienza con el BEGIN o con la declaración de variables locales (sin usar la palabra reservada DECLARE).
5. El procedimiento puede tener 0, 1 o más parámetros.
6. El tipo inidica los tipos de datos básicos como NUMBER, VARCHAR2, entre otros.
7. Se puede usar AS o IS (son equivalente).
8. El modo especifica el tipo de parámetro de la siguiente manera:
1. IN: Es un parámetro de sólo lectura.
2. OUT: Es un parámetro de sólo escritura que se utiliza para devolver valores a otro subprograma.
3. IN OUT: Estos parámetros son de lectura y escritura. El subprograma devolverá un valor posiblemente diferente al enviado inicialmente.

Los parámetros después de definirles su tipo (IN, OUT, IN OUT), opcionalmente se les puede especificar un valor por defecto usando la cláusula DEFAULT, este valor será el que asuma el procedimiento al ser ejecutado y no recibir parámetros.

Para eliminar un procedimiento se utiliza la palabra reservada DROP.

Ejemplo 1:

Veremos cómo se hace un procedimiento sin parámetros, y para esto vamos a utilizar la tabla EMPLEADO.

Ahora en un script escribimos el código necesario para crear el procedimiento.

Debido a que la consulta devuelve todos los campos (CODIGO, NOMBRE, DEPARTAMENTO y SUELDO) en vez de crear varias variables para guardar los datos recuperados por FETCH, creamos una variable mi_empleado y le ponemos el atributo %ROWTYPE que nos permite que esta variable tome los nombres y tipo de datos que tiene las columnas de la tabla EMPLEADO.

Cuando corremos el código nos sale en pantalla el siguiente mensaje:

También podemos verificar si se creó el procedimiento ubicándonos en la base de datos en SQL Developer y mirar la carpeta de Procedimientos.

En otro script, escribimos el código PL/SQL para invocar el procedimiento imprimir_nombre.

Y al ejecutarlo nos da el siguiente resultado:

Ejemplo 2:

Ahora se creará un procedimiento con parámetros y con el tipo de parámetro IN. Utilizaremos la los datos que tenemos de la tabla EMPLEADO.

El procedimiento que se hará a continuación, tendrá la finalidad imprimir el nombre y el salario de los empleados de un departamento en específico.

Escribimos el código para este procedimiento y lo ejecutamos.

Luego ingresamos el bloque PL/SQL para poder invocar el procedimiento, al cual le pasaremos como parámetro el número 4 y ejecutamos el código, lo que significa que se imprimirá todos los empleados que pertenezcan al departamento 4.

Si le pasamos como parámetro el número 7 al procedimiento, no nos imprimirá ningún resultado debido a que no existe empleado que pertenezca a este departamento.

Ejemplo 3:

Se va a realizar un procedimiento con parámetro utilizando los tipos IN y OUT. Seguiremos manejando la tabla EMPLEADO mostrada en el ejemplo 2.

Creamos el procedimiento consulta_emp que tendrá el parámetro cod de entrada y nom como parámetro de salida que es el que le devolverá el nombre del empleado a otro procedimiento que se encargará de mostrarlo en pantalla.

Lo que hará el procedimiento es hacer una consulta para recuperar el nombre del empleado cuando el código coincida con el valor de cod y guardarlo en la variable nom.

Ejecutamos el código para que se cree el procedimiento.

Luego de haber creado el procedimiento consulta_emp, seguiremos con la creación de otro procedimiento que se va a llamar invoca_consulta, que tendrá un parámetro de entrada el código del empleado del cual queremos saber su nombre.

El procedimiento invoca_consulta ejecutará consulta_emp que es el encargado de obtener el nombre del empleado y una vez obtenido lo guardará en la variable nombre. Luego simplemente se imprime el nombre de la persona en pantalla.

Corremos el código para que cree el procedimiento.

Para probar si nuestros procedimientos creados funcionan correctamente, realizaremos un código que ejecute invoca_consulta y nos busque al empleado con código 6.

Como nos podemos dar cuenta, si se imprimió el nombre del empleado correspondiente al código introducido, por ende, los procedimientos definidos anteriormente cumplieron su objetivo.

En caso de que se introduzca un código que no esté asociado a un empleado, el programa nos arrojará un error NO DATA FOUND.

Para ver esto, ejecutaremos el código, pero en el parámetro de invoca_consulta colocamos el valor de 9.

Este tipo de error se puede controlar usando excepciones, tema que se explicará más adelante.

Ejemplo 4:

Seguimos usando los registros de la tabla EMPLEADO.

En este ejemplo vamos a crear un procedimiento almacenado que tenga como nombre empleados_dept que tendrá un parámetro con un valor por defecto, y de acuerdo a ese valor se imprime los nombres de los empleados que hacen parte de este departamento.

Ahora en un script escribimos el siguiente código en PL/SQL para crear nuestro procedimiento almacenado y posteriormente lo ejecutamos.

Luego en otro script ejecutamos el procedimiento empleados_dept sin pasarle ningún parámetro y veamos que nos imprime.

Como no le pasamos ningún parámetro a nuestro procedimiento a la hora de ejecutarlo, tomó como valor por defecto el número 4 que fue el que le asignamos cuando lo creamos.

Si le pasamos un parámetro al procedimiento, se omite el valor por defecto que nosotros le asignamos al momento de crearlo. Para probar esto, vamos a ejecutar de nuevo empleados_dept, pero pasándole como parámetro el número 1 para que nos imprima los empleados que hacen parte de este departamento en vez de a los que pertenecen al departamento 4.