Mysql Funciones y Procedimientos almacenados

Introducción

• Ya vimos en un punto anterior de la WIKI cuales son los permisos que debe poseer un usuario para poder crear, borrar, modificar y ejecutar procedimientos almacenados y funciones.

• Podemos definir un procedimiento almacenado (store procedure) como un conjunto de órdenes SQL agrupadas bajo el nombre del procedimiento.

De tal forma, que cuando un procedimiento se llama, se ejecutan todas las órdenes SQL del mismo.

• El uso de los procedimientos puede deberse a varios motivos:

• Labores administrativas: procedimientos que realizan funciones de chequeo, seguimiento, registro de información que pueda ser relevante para un administrador.

• Labores de control de acceso a la información de una base de datos.

Utilizar procedimientos almacenados para acceder a los datos de las tablas tiene varias ventajas:

• Seguridad: El usuario no tiene acceso directo a la tabla por lo que no sabe nada de la misma y no puede realizar operaciones que no estén 'controladas' por el código del procedimiento almacenado.

Impedimos que desde programas 'cliente' se pueda intentar ataques como los de inyección de CSS.

• Rendimiento: Las sentencias SQL que se encuentran en un procedimiento almacenado ya ha sido 'compiladas' para verificar que no tiene errores sintácticos. Una sentencia SQL enviada directamente por el cliente va a requerir algo más de tiempo que la misma sentencia en un procedimiento almacenado.

• Conjunto de instrucciones que vamos a necesitar ejecutar varias veces en diferentes días. Podemos agrupar dichas órdenes en un proc. almacenado y llamarlo para que ejecute esas instrucciones y no tener que 'teclearlas' cada vez.

• Mysql identifica rutinas con procedimientos almacenados y funciones.

Una función (function) es como un procedimiento almacenado, pero a diferencia del mismo, puede 'devolver' un valor escalar (un número) y se puede invocar desde dentro de un comando (por ejemplo dentro de la sentencia SELECT nombre_funcion() ).

Un procedimiento almacenado se invoca con la orden: CALL nombre_proced_almacenado();

Bloques de órdenes SQL y variables de usuario

• Desde un editor SQL podemos utilizar ejecutar un conjunto de órdenes SQL sepaarados por punto y coma.

Por ejemplo:

1 SELECT nombre 
2 FROM ALUMNOS;
3 
4 UPDATE ALUMNOS
5 SET nombre = 'JUAN'
6 WHERE apellido = 'MARTINEZ';

Estos bloques de instrucciones podemos guardarlar en un archivo de texto (se recomienda con extensión .sql) para poder 'cargarlo' posteriormente y volver a ejecutar las órdenes del archivo.

En MYSQL cuando hacemos una copia de seguridad de una base de datos, se crea un archivo .sql con todas las órdenes SQL necesarias para crear y añadir datos a las tablas.

• Dentro de estos bloques de instrucciones podemos hacer uso de variables de usuario para guardar información de una consulta o utilizarlas dentro de una orden SQL, como un UPDATE, DELETE,...

Las variables de usuario deben:

• Comenzar su nombre con el símmbolo @, por ejemplo: @nombre
• Puede incluír cualquier carácter alfanumérico.
• Puede incluír los símbolos: ".", "_", "$"
• Puede incluír otro tipo de símbolos (incluído espacio en blanco) a condición de que se escriba el nombre de la variable entre comillas: @'my var', @'my-var', @"my-var", or @'my-var'.
• No se distingue mayúsculas de minúsculas
• Longitud máxima: 64 caracteres.

• Para asignar un valor a una variable de usuario usaremos la orden SQL SET de la forma: SET @nombre='pepe';

Fijarse que cuando usamos una variable no indicamos el tipo de dato que va a guardar, por lo que podríamos poner este código:

1 SET @nombre = 'pepe';
2 SET @nombre = 1;

• Veamos un ejemplo con una base de datos de muestra de Mysql.

Vamos a instalar dicha base de datos.

Los pasos para instalarla los tenéis en este enlace.

Básicamente tenemos que:

• Descargar de github la base de datos. Desde Linux, en una consola podemos escribir la orden: wget https://github.com/datacharmer/test_db/archive/master.zip
• Descomprimimos el archivo descargado desde consola con la orden: unzip master.zip
• Dentro del directorio test-db-master/ se encuentra el archivo employees.sql que es el que crea todas las tablas y añade los datos a cada una de ellas. Para ejecutarlo, podemos abrirlo desde el MysqlWorkBench o cualquier otro pero podemos ejecutar desde consola la orden: sudo mysql -uroot -p < employees.sql (suponemos que root tiene password). Una vez hecho esto ya tenemos la base de datos de nombre employees creada.

• Ejemplos de uso de variables del usuario:

Encuentra los id´s de los empleados que tienen un sueldo superior a 10.000.

1 use employees;
2 
3 set @salarioMinimo = 10000;
4 
5 select distinct emp_no
6 from salaries
7 where salary > @salarioMinimo;

Guarda el valor de una consulta en una variable

1 use employees;
2 
3 select count(*)
4 into @numEmpleoados
5 from dept_emp
6 where dept_no = 'd005';
7 
8 select @numEmpleoados;

Debemos de tener cuidado con el nombre de las variables, ya que si nos equivocamos, tendremos un valor erróneo.

1 use employees;
2 
3 select count(*)
4 into @numEmpleoados
5 from dept_emp
6 where dept_no = 'd005';
7 
8 select @numEmpleoadoss;

• Línea 8: Nos equivocamos en el nombre de la variable, al ponerle dos eses. En este caso aparecerá el valor null, que significa 'sin valor asignado'.

Podemos guardar un o varios valores, teniendo en cuenta que cada variable se corresponde en el mismo orden con el select asociado. El número de columnas obtenidas en el select se deben de corresponder con el mismo número de variables. Si el select tiene 4 columnas necesitamos 4 variables en la cláusula INTO.

1 use employees;
2 
3 select max(salary),min(salary)
4 into @maximo,@minimo
5 from salaries;
6 
7 select @maximo as 'Salario Maximo',@minimo as 'Salario Minimo';

• Linea 3: El select va a obtener dos valores.
• Línea 4: Guardamos los dos valores en dos variables.

Podemos asignar valores a varias variables en la misma línea:

1 use employees;
2 
3 set @dept_no = 'D999',@dept_name='Departamento de prueba';
4 
5 insert into departments (dept_no,dept_name)
6 values (@dept_no,@dept_name);

• Podemos generar 'nuevas' variables en base a valores de otras variables, por medio de expresiones.

Por ejemplo, si tengo dos variables numéricas voy a poder sumarlas, multiplicarlas, dividirlas,...generando un valor nuevo que podría guardar en otra variable.

En el caso de las cadenas de caracteres, debemos hacer uso de funciones. Por ejemplo, la función que permite concatenar dos o más cadenas en Mysql es la función CONCAT.

Un ejemplo de uso:

1 use employees;
2 
3 set @dept_no = 'D999',@dept_name='Departamento de prueba';
4 
5 set @cadenaCompleta = CONCAT(@dept_no,' > ', @dept_name);
6 
7 select @cadenaCompleta;

• IMPORTANTE: Si estamos a utilizar SELECT ....INTO debemos asegurarnos de que el select sólo devuelva una fila, ya que en caso de devolver más de uno no podría guardar los datos en el INTO y produciría un error.

Una forma de asegurarnos (aunque la orden SQL debería de dar una única fila a no ser que seamos conscientes de que puede devolver más de uno) es utilizar la cláusula SQL: LIMIT.

Lo que hace es obtener el número de filas indicado por el número que va a continuación de la palabra LIMIT.

Por ejemplo:

1 SELECT nombre
2 FROM alumnos
3 LIMIT 5

• En este caso sólo devolvería 5 filas como máximo.

Si ponemos 1, devolverá siempre una fila como máximo.

Ejercicios propuestos variables locales

• Guarda en dos variables el usuario que tenga más conexiones activas y el número de conexiones del mismo.

Pista: Mira en este enlace en que tabla del sistema pudedes consultar las conexiones activas.

• Crea un bloque de instrucciones SQL en el que una variable de usuario de nombre 'usuario' tenga asignado el valor de un usuario (sin la parte host). Comprueba si dicho usuario tiene permiso de selección, insercción, borrado y actualización a nivel de todo Mysql.

Solución ejercicios propuestos variables locales

• Guarda en dos variables el usuario que tenga más conexiones activas y el número de conexiones del mismo.

1 SELECT USER,count(*) as conexiones
2 INTO @nombre,@conexiones
3 FROM information_schema.PROCESSLIST
4 GROUP BY USER
5 ORDER BY conexiones
6 LIMIT 1;
7 
8 select @nombre,@conexiones;

• Crea un bloque de instrucciones SQL en el que una variable de usuario de nombre 'usuario' tenga asignado el valor de un usuario (sin la parte host). Comprueba si dicho usuario tiene permiso de selección, insercción, borrado y actualización a nivel de todo Mysql.

set @usuario = 'user1';

SELECT Select_priv, Insert_priv, Update_priv, Delete_priv INTO @select,@insert,@update,@delete FROM mysql.user WHERE User = @usuario LIMIT 1;

select @select,@insert,@update,@delete; </syntaxhighlight>

Variables globales / sesión del sistema

• Ya vimos qué son y cómo hacer uso de ellas durante la instalación.

Procedimientos almacenados

• La orden SQL que permite crear un procedimiento almacenado es CREATE PROCEDURE.

Un procedimiento almacenado puede estar formado por órdenes de todo tipo. Las que no permite las podeis consultar en este enlace.

Veremos poco a poco las opciones que nos permite utilizar la orden SQL anterior.

Como mínimo debemos de indicar un nombre para el procedimiento y un conjunto de órdenes SQL entre las palabras clave BEGIN y END.

• El nombre del procedimiento puede ser cualquiera, ya que no existe un regla que nos obligue a poner un nombre u otro, pero en este curso intentaremos seguir la siguiente regla: [Objeto]_[Acción][Proceso]

Por ejemplo:

• Alumno_Add (añade un alumno).
• Alumno_GetByNif (devuelve un alumno que tenga el nif enviado).
• Alumno_Update (actualiza un alumno).
• Alumno_GetList (devuelve todos los alumnos)

De esta forma podemos buscar facilmente todos los métodos que trabajen con alumnos.

• Lo primero que llama la atención cuando se ve la definición de un procedimiento almacenado en Mysql es la orden SQL DELIMITER:

 1 use employees;
 2 
 3 DELIMITER $$
 4 
 5 CREATE PROCEDURE department_getList()
 6 BEGIN
 7     SELECT dept_no,dept_name
 8     FROM departments;
 9 
10 END$$
11 
12 DELIMITER ;

Mysql toma como limitador de sentencias SQL el punto y coma por defecto. Quiere decir que cada vez que encuentra un punto y coma intenta ejecutar la orden SQL.

Debemos de ver al procedimiento almacenado como un todo, como si fuera una única instrucción que queremos que Mysql ejecute entera.

Si mantenemos el punto y coma tendríamos algo así:

1 use employees;
2 
3 CREATE PROCEDURE department_getList()
4 BEGIN
5 
6 END;

¿ Pero qué pasa ahora si queremos poner una orden SQL dentro del procedimiento almacenado ?

Que intetará ejecutarla y no la interpretará como parte del procedimiento almacenado.

Para evitarlo, indicamos con la orden SQL DELIMITER cual es el símbolo que debe encontrar Mysql para ejecutar la orden SQL (en nuestro caso el CREATE PROCEDURE) y por lo tanto dentro del mismo ya podemos poner sentencias SQL acabadas en punto y coma para separar unas de otras.

• Una vez creado, aparecerá en la sección Stores Procedures en MysqlWorkBench.

La orden SQL que permite ver la información de los procedimientos almacenados es: SHOW PROCEDURE STATUS.

Por ejemplo, para ver la información de los procedimientos almacenados de la base de datos 'employees':

1 show procedure status
2 where Db = 'employees';

• Para llamar a un procedimiento almacenado debemos hacer uso de la orden SQL CALL.

Por ejemplo:

1 call employees.department_getList();

• Fijarse que delante del nombre del procedimiento almacenado lleva el nombre de la base de datos. Esto no sería necesario si estamos ejecutando el procedimiento con la base de datos seleccionada o teniendo la orden SQL: USE db_name; antes de la llamada al procedimiento.

Funciones

-- Ángel D. Fernández González -- (2017).