8. Procedimientos almacenados.
Un procedimiento almacenado es un programa que es almacenado en una base de datos. Está formado por un conjunto de comandos que se almacenan bajo un nombre, de manera, que no es necesario lanzar los comandos individuales, basta con lanzar el procedimiento almacenado que contiene dichos comandos. La ventaja de un procedimiento almacenado es que al ser ejecutado, en respuesta a una petición de usuario, es ejecutado directamente en el servidor. Como tal, posee acceso directo a los datos que necesita manipular y solo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes.
Otra ventaja es la reutilización, es decir, no es necesario volver a realizar toda la consulta cuando queramos realizar una determinada acción, pues basta con invocar al procedimiento almacenado. Por lo tanto es recomendable usar procedimientos almacenados siempre que se vaya a hacer una aplicación grande, ya que nos facilitará la tarea bastante y nuestra aplicación será más rápida. Usos típicos para procedimientos almacenados incluyen la validación de datos (los procedimientos almacenados utilizados para este propósito son llamados disparadores)
Los procedimientos almacenados son de gran utilidad para realizar tareas frecuentes en una base de datos, es sorprendente la cantidad de tiempo que se puede llegar a ahorrar al hacer uso de este mecanismo. Los procedimientos almacenados los podemos dividir en: • • •
Procedimientos. Funciones. Disparadores
La sintaxis para crear un procedimiento es: CREATE PROCEDURE nombre_del_procedimiento ([parámetros[,...]]) cuerpo la sintaxis para especificar los parámetros es: [ IN | OUT | INOUT ] nombre_del_parametro tipo_de_dato – IN : indica que el parámetro es sólo de entrada. OUT : indica que el parámetro es sólo de salida. INOUT: indica que parámetro es de entrada y salida. Este sólo se utiliza en procedimientos, nunca en funciones.
• • •
Si no se especifica el modo del parámetro por defecto es de tipo IN (entrada).
La sintaxis para crear una función es: CREATE FUNCTION nombre_de_la_funcion ([parametros[,...]]) RETURNS tipo cuerpo
Características de los procedimientos almacenados: •
• • • •
•
El tipo de los parámetros sólo se les indica a los procedimientos nunca a las funciones. Las sentencias BEGIN … END se utiliza para englobar un conjunto de sentencias. Cada sentencia debe terminar con un punto y coma. Por defecto, la rutina se asocia con la base de datos actual. La cláusula RETURNS se especifica sólo con funciones, donde es obligatorio. Se usa para indicar el tipo de retorno de la función, y el cuerpo de la función debe contener un comando RETURN value . Los procedimientos almacenados no pueden usar LOAD DATA INFILE .
Ejemplo: calcular el cuadrado del 5. CREATE PROCEDURE proc_cuadrado () SELECT 5*5 as “resultado”; // resultado es el alias o nombre del campo Ejemplo: calcular el cuadrado de un número. CREATE PROCEDURE proc_cuadrado2 (IN numero TINYINT) SELECT numero*numero as “Resultado”;
Llamada a un procedimiento. CALL name([parameter],[...])
El comando CALL invoca un procedimiento definido previamente. Ejemplo: CALL proc_cuadrado; o CALL proc_cuadrado();
CALL proc_cuadrado(3);
Ejemplo de una función que toma un parámetro, realiza una operación, y retorna el resultado:
CREATE FUNCTION concatenacion (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT(“Hola “,s,”!”); Llamada a una función.
SELECT nombre([parameter],[...]) El comando SELECT invoca a una función definida previamente.
Ejemplo: para llamar a la función creada anteriormente.
SELECT concatenacion(“mundo”);
Para mostrar el uso de las variables que devuelven algún dato, crearemos otro procedimiento: CREATE PROCEDURE proc_cuadrado3(numero int, OUT resultado int) SET resultado= numero*numero; Al invocar este procedimiento debemos hacerlo con dos parámetros, el primero es el número que queremos elevar al cuadrado y el segundo es una variable en la cual almacenaremos el resultado. Las variables de usuario en MySQL deben comenzar con el caracter '@'. Estas son variables globales. CALL proc_cuadrado3(6, @r); visualizamos el contenido de la variable @r: SELECT @r;
8.1. Visualizar los procedimientos existentes
SHOW PROCEDURE STATUS [WHERE opciones]
Ejemplo: mostrar los procedimientos almacenados en la base de datos procedimientos.
SHOW PROCEDURE STATUS WHERE db=’procedimientos’ \G Ejemplo: mostrar información del procedimiento llamado ejemplo.
SHOW PROCEDURE STATUS LIKE ‘proc_cuadrado'\G;
8.2 Visualizar el código de un procedimiento o función. SHOW CREATE {PROCEDURE | FUNCTION} name
Ejemplo:
SHOW CREATE PROCEURE proc_cuadrado.
8.3 Variables. Declarar variables locales La sintaxis para declarar variables locales es: •
DECLARE variable tipo [DEFAULT valor ] Para proporcionar un valor por defecto a una variable, incluimos una cláusula DEFAULT. Si la cláusula DEFAULT no está presente, el valor inicial es NULL. Ejemplo: DECLARE x int default 1;
Asignar valores a una variable. Lo podemos hacer de dos formas diferentes: a) Con la sentencia SET. •
SET variable = expr [, variable= expr]
Ejemplo: SET @x=3
b) Con la sentencia SELECT ... INTO
SELECT columna[,...] INTO variable[,...] from tabla Almacena las columnas seleccionadas en variables. Por lo tanto, sólo puede retornarse un registro.
Ejemplo: select dnombre,loc from departamentos limit 1 into @x,@y;
A continuación veremos un ejemplo en el cual se muestra la utilidad que pueden llegar a tener los procedimientos almacenados al hacer inserciones, actualizaciones u otras tareas frecuentes que llevan varios pasos y que podemos sintetizar escribiendo un procedimiento e invocándolo tantas veces como sea necesario, especificando los parámetros correspondientes. Creamos dos tablas para trabajar en ellas: CREATE TABLE tabla1 (numero INT PRIMARY KEY AUTO_INCREMENT,numero2 INT,numero3 INT); CREATE TABLE tabla2 (numero INT PRIMARY KEY,numero2 INT,numero3 INT); Ahora un procedimiento que realiza una serie de cálculos e inserciones en la tabla. Es necesario utilizar la sentencia delimiter $$. Se utiliza para decirle a mySQL que a partir de ahora hasta que no introduzcamos $$ no se acaba la sentencia, esto lo hacemos así por que en nuestro procedimiento almacenado tendremos que introducir el carácter “;” para las sentencias, y si pulsamos ENTER MySQL pensará que ya hemos acabado la consulta y dará error.
# cambia el delimitador por defecto ";" a "$$" # esto es para poder escribir varias sentencias SQL sin que el manejador de por terminada alguna que finaliza en ";" DELIMITER $$ CREATE PROCEDURE ejemplo (num INT) BEGIN declare ultimo int; INSERT INTO tabla1 VALUES(default, num+num, num*num); # retorna el último valor generado automáticamente que se insertó en una columna AUTO_INCREMENT. select LAST_INSERT_ID() into ultimo; # usa el valor de @ultimo para hacer una inserción en la segunda tabla INSERT INTO tabla2 VALUES (ultimo, ultimo+ultimo, ultimo*ultimo); END # usa el delimitador $$ para indicar que la definición del procedimiento ha terminado $$ # restablece el ";" como delimitador DELIMITER ; Invocamos tres veces: CALL ejemplo (2); CALL ejemplo (4); CALL ejemplo (6); Si ahora vemos el contenido de nuestras tablas, observamos que efectivamente los cambios fueron realizados en ambas tablas.
Ejemplo: crear un procedimiento que cuente el número de filas de tabla1.
create procedure contarFilas(out filas int) set filas=(select count(numero) from tabla1); Llamamos al procedimiento call contarFilas(@total);
select @total;
8.4 Constructores de Flujo a) Sentencia IF IF condicion THEN statement_list [ELSEIF condition THEN statement_list] … [ELSE statement_list] END IF
IF implementa un constructor condicional básico. Si condición se evalúa a cierto, el comando SQL correspondiente listado se ejectua. Si no coincide ninguna condición se ejecuta el comando listado en la cláusula ELSE.
Ejemplo:
1. delimiter $$ 2. CREATE procedure miProc(p1 int) 3. begin 4. declare miVar int; /* se declara variable local */ 5. SET miVar = p1 +1 ; /* se establece la variable */ 6. IF miVar = 12 then 7. INSERT INTO lista VALUES(55555); 8. else 9. INSERT INTO lista VALUES(7665); 10. end IF; 11. end 12. $$
b) Sentencia CASE CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] … [ELSE statement_list] END CASE
El comando CASE para procedimientos almacenados implementa un constructor condicional complejo. Si una condición se evalúa a cierto, el comando SQL correspondiente se ejecuta. Si no coincide ninguna condición de búsqueda, el comando en la cláusula ELSE se ejecuta.
Ejemplo:
1. delimiter $$ 2. CREATE procedure miProc2 (p1 int) 3. begin 4. declare var int ; 5. SET var = p1 +2 ; 6. case var 7. when 2 then INSERT INTO lista VALUES (66666); 8. when 3 then INSERT INTO lista VALUES (4545665); 9. else INSERT INTO lista VALUES (77777777); 10. end case; 11. end; 12. $$ c) Sentencia REPEAT REPEAT statement_list UNTIL condicion END REPEAT
Las sentencias dentro de un comando REPEAT se ejecutan hasta que la condición es cierta. Ejemplo: 1. create procedure repetir(num int,out sal int) 2. begin 3. declare x int default 0; 4. repeat 5. set x=x+1; 6. until x>num 7. end repeat; 8. set sal=x; 9. end;
Llamamos al procedimiento: CALL repetir(1000) Mostramos el valor de la variable:
SELECT @x
Ejercicio: Crear un procedimiento que almacene en una tabla llamada NUMEROS los números del 1 al 100 y el cuadrado de dichos números. La tabla la crearemos fuera del procedimiento y tiene dos campos: numero y cuadrado.
d) Sentencia WHILE WHILE condicion DO statement_list END WHILE
Las sentencias dentro de un comando WHILE se repiten mientras la condición es cierta. Ejemplo:
create procedure repetir2(num int,out sal int) begin set sal=1; while num>0 do set sal = sal +1; set num =num-1; end while; end; Llamamos al procedimiento: call repetir2(3,@r) Mostramos el valor de la variable select @r Ejercicio: Crear un procedimiento que almacene en la tabla NUMEROS los números del 101 al 150 y el cuadrado de dichos números. 8.5 Borrar un procedimiento o funcion.
La sintaxis es: DROP {PROCEDURE | FUNCTION} [IF EXISTS] name
Ejemplo: drop procedure if exists prueba3;
