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();

• Desde un editor SQL podemos utilizar variables 'de usuario' para guardar información de una consulta o utilizarlas dentro de una orden de manipulación de datos.

Las variables locales 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.

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.

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;

Procedimientos almacenados

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

Funciones

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