Introducción a Rutinas Almacenadas en SQL

¿Qué es una rutina almacenada?

Una rutina almacenada es un conjunto de instrucciones SQL a las que damos un nombre de tal forma que cuando se llame a ese nombre se ejecutaran todas ellas.

Dentro de las rutinas almacenadas debemos hacer 2 diferenciaciones:

Procedimiento: Es una rutina almacenada en la que no se indica el tipo de salida que debe tener
Función: Es igual que el Procedimiento, pero en este caso se debe de especificar que tipo de salida debe de tener, esto se indicaría con RETURNS

En este caso nos centraremos principalmente en Procedimientos Almacenados

Estructura de un procedimiento almacenado

Cuando queramos crear un procedimiento almacenado SQL debemos utilizar siempre la siguiente estructura:

Delimiter //
   CREATE PROCEDURE NombrePorcedimiento(IN par1 TIPO, OUT par2 TIPO)
       BEGIN
           --Instrucciones SQL
       END
   //
Delimiter;

Para crear el procedimiento se utiliza CREATE PROCEDURE, seguido del nombre del procedimiento almacenado, despues del cual irán los elementos de entrada y salida dentro de un paréntesis, siendo declarados los de entrada después de IN, definiendo el nombre de la variable de entrada y el tipo, después se declararán los elementos de salida después de OUT, de la misma forma que se hace con IN.

Las instrucciones SQL que queramos que se ejecuten en nuestro procedimiento irán entre BEGIN y END.

Si os fijais en la estructura de arriba, veréis que hay un elemento que aún no he explicado cuyo nombre es DELIMITER, se utiliza para cambiar el delimitador de la función, el delimitador por defecto es ; pero al ser este el mismo que utilizan las instrucciones SQL para indicar su final lo cambiamos para evitar problemas, en este caso el delimiter que utilizaremos es //.

Ejemplos

Para ver como sería un procedimiento almacenado os dejo tres ejemplos realizados con la base de datos SAKILA:

Diagrama de la base de datos sakila, haz click en la foto para ampliar

Queremos crear un procedimiento almacenado que nos devuelva el título de las 10 primeras películas

Delimiter //
   CREATE PROCEDURE Titulo10()
       BEGIN
           --Instrucciones SQL
          SELECT title
          FROM film
          ORDER BY film_id
          LIMIT 10;
       END
   //
Delimiter;

Queremos un procedimiento almacenado que dados 2 números nos devuelva el titulo de la película cuyo códico corresponda con el mayor de esos 2 números.

DELIMITER //
   CREATE PROCEDURE NumerosPelicula(IN num1 INT, IN num2 INT)
       BEGIN
            IF num1 > num2 THEN
               SELECT title AS 'Película'
                FROM film
               WHERE film_id = num1;
           ELSE
             SELECT title AS 'Película'
             FROM film
               WHERE film_id = num2;
           END IF;
       END
   //
DELIMITER ;

Crea un procedimiento almacenado que nos devuelva el nombre del actor correspondiente al código de actor que le pasemos.

Delimiter//

   Create procedure CodActor(IN codigo INT, OUT nombre varchar(45))
       Begin
           select first_name --Nombre del campo que queremos
           into nombre --hacemos que en nombre se muestre first_name
           from actor --Indicamos la tabla de la que queremos los datos
           where actor_id=codigo; --Condicion de salida, que el campo sea igual a la entrada
       END
   //
Delimiter;
CALL CodActor(4,@nombre) --llamamos el procedimiento para que nos muestre el nombre del actor cuya ID sea 4
Select @nombre AS 'Nombre del actor' --comando para que se muestre en la pantalla el nombre del actor, siendo @nombre la variable en la que guardamos antes el nombre