Diferencia entre revisiones de «Mysql Funciones y Procedimientos almacenados»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 892: Línea 892:
  
  
* Crea un procedimiento de nombre employees_getNombreAddSalario al que se le pase como parámetro el número de empleado y una cantidad que representa el dinero que se le va a aumentar al empleado que:
+
* Crea un procedimiento de nombre employees_getNombreAddSalario al que se le pase como parámetro el número de empleado y una cantidad que representa el dinero que se le va a aumentar al empleado.
:* Llame al procedimiento salaries_addSalario
+
: Lo que debe hacer el procedimiento es:
:* Llame al employees_getNombreCompleto
+
:* Llamar al procedimiento salaries_addSalario
 +
:* Llamar al employees_getNombreCompleto
  
 
:: Muestro los valores de los parámetros de salida de la llamada a los procedimientos anteriores en una única fila.
 
:: Muestro los valores de los parámetros de salida de la llamada a los procedimientos anteriores en una única fila.

Revisión del 07:49 15 ene 2019

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



Importando la base de datos employees

  • Vamos a trabajar con las bases de datos del sistema y con una base de datos de prueba.
  • Vamos a instalar la base de datos employees.
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, ejecutaremos 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.



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;


  • 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;
Mysql sp 1.jpg


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'.
Mysql sp 2.jpg


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.
Mysql sp 3.jpg


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 de usuario

  • 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, guardando sus permisos en variables y mostrando su valor.






Solución ejercicios propuestos variables de usuario

  • Guarda en dos variables el usuario que tenga más conexiones activas y el número de conexiones del mismo.
 1 SET @nombre='';
 2 SET @conexiones=0;
 3 
 4 SELECT USER,count(*) as conexiones
 5 INTO @nombre,@conexiones
 6 FROM information_schema.PROCESSLIST
 7 GROUP BY USER
 8 ORDER BY conexiones DESC
 9 LIMIT 1;
10 
11 select @nombre,@conexiones;
Mysql sp 5.jpg



  • 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.
 1 SET @usuario = 'user1';
 2 SET @select='';
 3 SET @insert='';
 4 SET @update='';
 5 SET @delete='';
 6 
 7 SELECT Select_priv, Insert_priv, Update_priv, Delete_priv
 8 INTO @select,@insert,@update,@delete
 9 FROM mysql.user
10 WHERE User = @usuario
11 LIMIT 1;
12 
13 select @select,@insert,@update,@delete;
Mysql sp 6.jpg



Variables globales / sesión del sistema




Procedimientos almacenados

  • Recordar que un procedimiento almacenado estará asociado a una base de datos concreta, por lo que este se creará en la base de datos activa.
  • Para crear un procedimiento podemos:
  • Crearlo desde la consola con la herramienta cliente mysql.
  • Crearlo desde la herramienta web PhpmyAdmin.
  • Crearlo desde el MysqlWorkbench, desde una ventana SQL abierta o pulsando el botón derecho sobre la sección 'Store Procedures' de una base de datos.
Mysql sp 0.jpg


  • También recordar que desde el MysqlWorkbench, una vez creado tendréis que 'refrescar' el entorno para ver los cambios:
Mysql sp 01.jpg


  • 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.



Crear / Visualizar / Borrar / Modificar procedimientos

Crear procedimientos

Un procedimiento almacenado puede estar formado por órdenes de todo tipo. Las que no permite las podéis 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.


  • Un procedimiento se crea dentro de una base de datos concreta.
Si no indicamos la base de datos, lo hará en la base de datos activa que recordar que en MysqlWorkBench es la que se visualiza en negrilla.
Cuando ejecutemos la orden CREATE PROCEDURE, podemos cambiar antes la base de datos activa con la orden SQL: USE nombre_bd;
Otra forma de indicar en qué base de datos debe crearse el procedimiento es anteponiendo al nombre del mismo, el nombre de la base de datos seguido por un punto, de la forma: CREATE PROCEDURE nombreBD.nombreProc()


  • 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.
Mysql sp 7.jpg



Listar procedimientos

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';
Mysql sp 8.jpg



Llamar a un procedimiento

  • Para llamar a un procedimiento almacenado debemos hacer uso de la orden SQL CALL.
Al llamar a un procedimiento almacenado, las órdenes de su interior será ejecutadas.
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.
Mysql sp 9.jpg



Visualizar el código de un procedimiento

1 SHOW CREATE PROCEDURE department_getList;
Mysql sp 10.jpg



Modificar un procedimiento

  • En MYSQL no se permite modificar los parámetros y el cuerpo de un procedimiento.
Para ello tendremos que borrarlo y volverlo a crear.
  • Lo que sí permite es modificar ciertas características del mismo, que iremos viendo más adelante.
  • Por ejemplo, podemos modificar la cláusula COMMNENT que sirve para tener una descripción del procedimiento.
1 ALTER PROCEDURE department_getList
2 COMMENT 'Obtiene un listado de todos los departamentos';
3 
4 SHOW PROCEDURE STATUS 
5 WHERE Db = 'employees';
Mysql sp 11.jpg


  • Desde el MysqlWorkBench podemos 'modificar' un procedimiento (realmente lo va a borrar y volverá a crearlo) de la forma:
Mysql sp 12.jpg




Borrar un procedimiento

  • Indicar que podemos incluir una cláusula 'IF EXISTS' para indicar que solamente en caso de que exista, borre el procedimiento. Esto es muy útil cuando tenemos un bloque de sentencias, ya que si no ponemos el IF, si el procedimiento no existe daría un error y se dejarían de ejecutar las sentencias.
1 DROP PROCEDURE IF EXISTS no_va_a_borrarlo;


  • Indicar que podemos hacer referencia al procedimiento anteponiendo a su nombre el nombre de la base de datos donde está creado seguido del un punto: DROP PROCEDURE IF EXISTS employees.no_va_a_borrarlo;



Ejercicios propuestos

Sobre la base de datos employees:
  • Crea un procedimiento que muestre cuantos empleados pertenecen al 'staff' (este dato lo encontrás en la tabla titles). Recuerda darle un nombre correcto.
Para obtener el número de empleados debes emplear la función count(*) en el select.
Modifica el procedimiento anterior y añádele un comentario que describa lo que devuelve el procedimiento.
Otorga permiso de ejecución al usuario 'user1' (creado previamente) sobre dicho procedimiento.
Conectándote como 'user1', ejecuta el procedimiento.
Muestra con una orden SQL, el contenido del procedimiento.


  • Crea un procedimiento que muestre el número de los empleados que estén trabajando en la empresa en el departamento 'd005' desde 1990. En la creación del procedimiento utiliza la cláusula comment. Recuerda de darle un nombre correcto.
Para obtener el número de empleados debes emplear la función count(*) en el select.
Para obtener el año de una columna de tipo date debes de aplicar la función YEAR(columna_date). Dicha función devuelve el año en forma numérica.
Fíjate que el enunciado indica 'estén trabajando' :)
Otorga permiso de ejecución al usuario 'user1' (creado previamente) sobre dicho procedimiento.
Conectándote como 'user1', ejecuta el procedimiento.
Muestra con una orden SQL el nombre de todos los procedimientos de la base de datos.


  • Crea un procedimiento de nombre Procedimiento_getUsersExecute que muestre los nombres de los usuarios que tienen permiso para ejecutar los procedimientos (a nivel de base de datos) ordenados por nombre.




Soluciones ejercicios propuestos

  • Crea un procedimiento de nombre Procedimiento_getUsersExecute que muestre los nombres de los usuarios que tienen permiso para ejecutar los procedimientos (a nivel de base de datos) ordenados por nombre.
1 CREATE DEFINER=`root`@`localhost` PROCEDURE `Procedimiento_getUsersExecute`()
2     COMMENT 'Obtiene los usuarios que tienen permiso de ejecución sobre la base de datos employees'
3 BEGIN
4     SELECT concat(User,'@',Host) as usuario
5     FROM mysql.procs_priv
6     WHERE Db = 'employees'
7     ORDER BY usuario;
8 END


Fijarse que podríamos hacer uso de variables del usuario para 'pasarle' un dato al usuario o para que el procedimiento envíe información a quien lo invoca.
Por ejemplo, la siguiente modificación devuelve un usuario que tiene permiso para ejecutar procedimientos sobre una base de datos indicada por una variable de usuario:
1 CREATE DEFINER=`root`@`localhost` PROCEDURE `Procedimiento_getUsersExecute`()
2     COMMENT 'Obtiene los usuarios que tienen permiso de ejecución sobre la base de datos employees'
3 BEGIN
4     SELECT concat(User,'@',Host) as usuario
5     FROM mysql.procs_priv
6     WHERE Db = @BD;
7 END
Ahora quien realiza la llamada va a poder 'enviarle' el nombre de la base de datos a buscar:
1 SET @BD = 'employees';
2 CALL Procedimiento_getUsersExecute();




Variables locales

  • La orden SQL utilizada para declarar variables locales dentro de un procedimiento es DECLARE.


  • Al igual que en el caso del los bloques de órdenes SQL, podemos tener variables dentro del procedimiento para guardar datos temporalmente y utilizarlos en otras instrucciones dentro del mismo.
A diferencia de las variables usada en los bloques de código SQL:
  • El nombre de las variables no empieza por @
  • Se debe declarar un tipo asociado a la variable pero esto no impide que puedas asignarle un dato que no se corresponde con el tipo, haciendo la conversión (siempre que pueda, como por ejemplo, asignar a una variable de cadena un valor numérico, pero no podría a la inversa).
  • Si no se inicializan, el valor de dichas variables por defecto es null.
  • Podemos asignar un valor a una variable con el comando SET de la forma
  • SET variable := valor;
  • SET variable = valor;
  • Veamos un ejemplo:
 1 DELIMITER \\
 2 
 3 CREATE DEFINER=`root`@`localhost` PROCEDURE `department_getCount`()
 4 	COMMENT 'Obtiene el número de departamentos'
 5 BEGIN
 6 	DECLARE numDepts int default 0;
 7    
 8 	SELECT count(*)
 9 	INTO numDepts
10 	FROM departments;
11    
12 	SELECT numDepts;
13  
14 END\\
  • Línea 2: Utilizamos la cláusula COMMENT para indicar que hace el procedimiento.
  • Línea 4: Definimos una variable local de tipo 'int' (numérica) con valor por defecto de cero.
  • Línea 7: Guardamos el resultado de la consulta en la variable.
  • Podéis consultar todos los tipos de datos que soporta Mysql en este enlace.


  • Debemos de tener cuidado con el nombre de las variables locales y que estas no coincidan con el nombre de las columnas de una tabla, ya que Mysql interpretará siempre que se trata del nombre de la variable y nunca el de la columna, como podéis ver en esta explicación.


Ejercicios propuestos variables locales

Sobre la base de datos employees:
  • Crea un método de nombre department_getLast() que guarde en variables locales los datos del último departamento ordenado alfabeticamente. Después debe mostrar en una única columna de nombre 'datos_departamento' el departamento encontrado (pista: utiliza el método concat).
Vas a necesitar utilizar las funciones de Mysql:


  • Crea un método de nombre salarie_getMax() que guarde en variables locales los datos (número y salario) del empleando con mayor sueldo. Después debe mostrar en una única columna de nombre 'datos_salario' los datos del empleado encontrado (pista: utiliza el método concat).
Vas a necesitar utilizar las funciones de Mysql:



Pista: La sentencia SQL es esta:
1     SELECT emp_no,salary
2     FROM salaries
3     WHERE salary = (SELECT max(salary)
4 		    FROM salaries)
Pero tendrás que añadir algo para asegurarte que no devuelva más de una fila.


  • Crea un método de nombre title_getYoung() que guarde en variables locales el número de empleado, el puesto y la fecha del empleado que menos tiempo lleva trabajando (sólo uno).
Después debe mostrar el contenido de dichas variables en una única columna de nombre 'empleado_mas_joven' (pista: utiliza el método concat).
Vas a necesitar utilizar las funciones de Mysql:



Pista: La sentencia SQL es esta:
1     SELECT emp_no, title, from_date
2     FROM titles
3     WHERE to_date > CURDATE() and
4 	  from_date = (SELECT MAX(from_date)
5                        FROM titles
6                        WHERE to_date > CURDATE()
7                       )
Pero tendrás que añadir algo para asegurarte que no devuelva más de una fila.


Ampliación del ejercicio:
Haz que se muestre el nombre del empleado en vez de su número (de las dos formas indicadas a continuación).
  • Puedes hacer un 'join' con la tabla de empleados para obtener el nombre.
  • Puedes utilizar la variable que guarda el número y realizar la búsqueda en la tabla empleados utilizando el valor de la variable.
En este ejercicio se ve como una variable se puede utilizar como parte de otras sentencias SQL (valdría cualquiera que necesite en 'valor').
Indicar que en una sentencia SELECT, poner un SELECT variable, col_tabla FROM TABLA hará que por cada fila que encuentre en TABLA mostrará una de las columnas con el mismo valor (variable)



Soluciones ejercicios propuestos variables locales

  • Crea un método de nombre department_getLast() que guarde en variables locales los datos del último departamento ordenado alfabeticamente. Después debe mostrar en una única fila de nombre 'datos_departamento' el departamento encontrado.
 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `department_getLast`()
 2     COMMENT 'Muestra el último departamento ordenado alfabeticamente'
 3 BEGIN
 4     
 5     DECLARE numDep char(4);	  /* Se debe corresponder con el tipo de la tabla */
 6 	DECLARE nombreDept varchar(40);  /* Se debe corresponder con el tipo de la tabla */
 7     
 8 	SELECT dept_no,dept_name
 9     INTO numDep,nombreDept
10 	FROM departments
11     ORDER BY dept_name DESC
12     LIMIT 1;
13 	
14 	SELECT CONCAT('Datos encontrados:',numDep,'----',nombreDept) as dato_departamento;  /* CONCAT realiza la conversion */
15 END
  • Crea un método de nombre salarie_getMax() que guarde en variables locales los datos (número y salario) del empleando con mayor sueldo. Después debe mostrar en una única fila de nombre 'datos_salario' los datos del empleado encontrado.
 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `salarie_getMax`()
 2     COMMENT 'Muestra los datos del empleado con mayor sueldo'
 3 BEGIN
 4 	DECLARE numEmp,salario int;
 5     
 6     SELECT emp_no,salary
 7     INTO numEmp,salario
 8     FROM salaries
 9     WHERE salary = (SELECT max(salary)
10 				    FROM salaries)
11 	LIMIT 1;
12     
13     SELECT CONCAT('EMPLEADO:', numEmp,' SALARIO:', salario) as datos_salario;  /* CONCAT realiza la conversion */
14 
15 END


  • Crea un método de nombre title_getYoung() que guarde en variables locales el número de empleado, el puesto y la fecha del empleado que menos tiempo lleva trabajando (sólo uno).
Después debe mostrar el contenido de dichas variables en una única columna de nombre 'empleado_mas_joven' (pista: utiliza el método concat).
 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `title_getYoung`()
 2     COMMENT 'Muestra los datos del empleado más joven que aún trabaja'
 3 BEGIN
 4 
 5     DECLARE empNum int;
 6     DECLARE puesto varchar(50);
 7     DECLARE fecha date;
 8     
 9     SELECT emp_no, title, from_date
10     INTO empNum,puesto,fecha
11     FROM titles
12     WHERE to_date > CURDATE() and
13 	  from_date = (SELECT MAX(from_date)
14                        FROM titles
15                        WHERE to_date > CURDATE()
16                       )
17     LIMIT 1;
18     
19     SELECT CONCAT(empNum,'-',puesto,'-',fecha) as empleado_mas_joven;  /* CONCAT realiza la conversion */
20 
21 END



Parámetros

  • Cuando llamamos a un procedimiento puede ser necesario 'enviarle' información.
Por ejemplo:
  • Podemos querer saber las conexiones activas de un determinado usuario.
  • Podemos querer saber qué usuarios tienen permiso de selección sobre determinada base de datos.
  • Podemos querer saber quien cobra más que un determinado sueldo.

....

En cada una de las consultas anteriores vamos a querer hacer uso de un dato que debe ser suministrador por el usuario en el momento de llamar al procedimiento.
  • Cada uno de los datos que enviamos a un procedimiento almacenado se denomina parámetro.
Los parámetros son variables cuyo valor se utiliza dentro del procedimiento almacenado.
Como norma general, cualquier modificación que hagamos sobre el valor del parámetro dentro del procedimiento almacenado no tiene efecto fuera de él.
Este tipo de parámetros se denominan de entrada.
  • Los parámetros de salida son parámetros que son utilizados por el procedimiento almacenado para 'informar' a quien llama y asignarle un valor a dicho parámetro dentro del procedimiento. Este valor será utilizado posteriormente por quien realizó la llamada, leyendo el valor del parámetro modificado. Este tipo de parámetro se denomina de salida.
  • Los parámetros que pueden llevar información al procedimiento, que pueden ser modificados dentro del procedimiento y que pueden ser utilizados por quien realizó la llamada al procedimiento se denominan de entrada/salida.


  • Debemos de tener cuidado con el nombre de las parámetros y que estos no coincidan con el nombre de las columnas de una tabla, ya que Mysql interpretará siempre que se trata del nombre del parámetro y nunca el de la columna, como podéis ver en esta explicación.
Podríamos solucionarlo referenciando el nombre de la columna de la forma: nombre_tabla.nombre_columna



Entrada

  • Es el tipo por defecto si no indicamos nada en la definición del procedimiento.
1 DELIMITER \\
2 CREATE PROCEDURE nombre_proc (IN nombre_parametro tipo_dato)
3 BEGIN
4 
5 END//
6 DELIMITER ;
Sería lo mismo que no poner IN (tipo por defecto)
1 DELIMITER \\
2 CREATE PROCEDURE nombre_proc (nombre_parametro tipo_dato)
3 BEGIN
4 
5 END//
6 DELIMITER ;


  • Una vez definido podremos disponer de él dentro del procedimiento como si tuviéramos una variable local.
La diferencia es que desde quien llama al procedimiento, va a poder enviar un valor de la forma:
1 CALL nombre_proc(100);
En este caso el parámetro debe estar definido como de tipo numérico (por ejemplo int)
O de la forma:
1 SET @dato = 100;
2 CALL nombre_proc(@dato);


  • Podemos definir tantos parámetros como queramos, siempre que el número de datos a enviar se corresponda con el número de parámetros del procedimiento.
1 DELIMITER \\
2 CREATE PROCEDURE nombre_proc (nombre_parametro1 tipo_dato,nombre_parametro2 tipo_dato,....)
3 BEGIN
4 
5 END//
6 DELIMITER ;



Ejercicios propuestos parámetros de Entrada

  • Crea un método de nombre User_getListDB que muestre los usuarios junto con los permisos que tengan sobre una base de datos, cuyo nombre será enviada como parámetro.
  • Crea un método de nombre User_getPermByTable que muestre los usuarios que tengan algún permiso otorgado sobre la tabla de una base de datos determinada (indicados como parámetros).
  • Crea un método de nombre Employee_getSalarieByName que dado un usuario (su nombre y apellidos) busque cual es su salario actualmente. Debe mostrar en una única fila con dos columnas su salario y nombre completo.
No hagas uso de JOIN y utiliza variables locales.
Nota: Existen varios usuarios con los mismos nombre y apellidos. Coge el primero de ellos haciendo uso de Limit.
Existen varios salarios asociados al mismo empleado. Coge el último de ellos (ordenados por fecha)
  • Crea un procedimiento de nombre Employee_getNameByTitle que dado el nombre de un puesto (tabla titles) muestre el nombre del usuario más nuevo en ese puesto. Debe mostrar en una única fila con una columna, el nombre completo del trabajador.
No hagas uso de JOIN y utiliza variables locales.
  • Crea un procedimiento de nombre Employee_add que añada un nuevo empleado. Deberás enviar los datos del empleado utilizando parámetros. No debes enviar el dato 'hire_date'.


Salida

  • En este tipo de parámetros, el procedimiento almacenado, al modificar su valor, este queda reflejado en el código desde donde se realizó la llamada.
En la definición del parámetro debemos añadir la palabra OUT.
  • Por ejemplo:
1 DELIMITER \\
2 CREATE PROCEDURE nombre_proc (OUT nombre_parametro1 tipo_dato)
3 BEGIN
4      .......
5      SET nombre_parametro1 = 1;  // Cualquier instrucción que modifique el valor, como SELECT INTO, SET,....
6 END//
7 DELIMITER ;
Al salir del procedimiento, el valor del parámetro será de 1.
Quien lo llama:
1 CALL nombre_proc(@dato);
2 
3 SELECT @dato;    // El valor de la variable @dato es uno, ya que fue modificado por el procedimiento y es de salida.


  • Podemos utilizar cualquier combinación de parámetros de salida y de entrada en el mismo procedimiento.



Ejercicios propuestos de parámetros de Salida

  • Crea un procedimiento de nombre Database_checkSelect que devuelva por medio de un parámetro de salida, el valor 'Y' si un usuario, enviado su nombre como parámetro (se va a enviar el usuario solamente sin el host) tiene permiso de selección sobre una base de datos cuyo nombre también es enviado como parámetro.
  • Crea un procedimiento de nombre Department_getNumber que dado un nombre de departamento devuelve en forma de parámetro de salida su número.
  • Crea un procedimiento de nombre Department_getName que dado un número de departamento devuelva en forma de parámetro de salida su nombre.



Solución ejercicios propuestos de parámetros de Salida
  • Crea un procedimiento de nombre Database_checkSelect que devuelva por medio de un parámetro de salida, el valor 'Y' si un usuario, enviado su nombre como parámetro (se va a enviar el usuario solamente sin el host) tiene permiso de selección sobre una base de datos cuyo nombre también es enviado como parámetro.
Procedimiento Database_checkSelect:
Recordar que los tipos de datos de los parámetros deberían corresponderse con los tipos de datos de las columnas de las tablas que van a referenciar.
 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `Database_checkSelect`(IN usuario char(32),IN basedatos char(64), OUT resultado char(1))
 2 BEGIN
 3 
 4 	SELECT Select_priv
 5     INTO resultado
 6     FROM mysql.db
 7     WHERE User = usuario and
 8           Db = basedatos;
 9 
10 END
Ejemplo de llamada:
1 call Database_checkSelect('user1','employees',@permiso);
2 
3 select @permiso;



Entrada/Salida

  • Los parámetros de Entrada/Salida son el resultado de aplicar los conceptos aprendidos para los de entrada y para los de salida.
Son parámetros a los que vamos a poder dar un valor inicial, llamar al procedimiento y que pueda hacer uso del valor enviado y dentro del procedimiento va a poder cambiar su valor y este será reflejado desde donde se llamó.


  • Por ejemplo:
 1 DELIMITER \\
 2 CREATE PROCEDURE nombre_proc (INOUT nombre_parametro1 tipo_dato)
 3 BEGIN
 4      SELECT 
 5      FROM TABLA
 6      WHERE columna = nombre_parametro1;
 7      .......
 8      SET nombre_parametro1 = 1;  // Cualquier instrucción que modifique el valor, como SELECT INTO, SET,....
 9 END//
10 DELIMITER ;
Al salir del procedimiento, el valor del parámetro será de 1.
Quien lo llama:
1 SET @dato = 100;  // El procedimiento podrá hacer uso del valor enviado en la variable @dato
2 CALL nombre_proc(@dato); 
3 
4 SELECT @dato;    // El valor de la variable @dato es uno, ya que fue modificado por el procedimiento y es de salida.





Ejercicios propuestos de parámetros de Entrada/Salida

  • Crea un procedimiento de nombre salaries_addSalario al que se le envíe como parámetros el identificador de un empleado y una cantidad que representa el dinero que se le va a aumentar al empleado.
El procedimiento debe devolver en el mismo parámetro el nuevo sueldo del empleado.


  • Crea un procedimiento de nombre employees_getNombreCompleto al que se le pase como parámetro el número de empleado y devuelve en el mismo parámetro el nombre completo del mismo.


  • Crea un procedimiento de nombre employees_getNombreAddSalario al que se le pase como parámetro el número de empleado y una cantidad que representa el dinero que se le va a aumentar al empleado.
Lo que debe hacer el procedimiento es:
  • Llamar al procedimiento salaries_addSalario
  • Llamar al employees_getNombreCompleto
Muestro los valores de los parámetros de salida de la llamada a los procedimientos anteriores en una única fila.



Soluciones ejercicios propuestos de parámetros de Entrada/Salida
  • Crea un procedimiento de nombre employees_addSalario al que se le envíe como parámetros el identificador de un empleado y una cantidad que representa el dinero que se le va a aumentar al empleado. Sólo se tiene que aumentar el último sueldo, el sueldo que tiene actualmente.
El procedimiento debe devolver en el mismo parámetro el nuevo sueldo del empleado.
Después de probar que funcione, crea un nuevo procedimiento que haga lo mismo, pero al cual se le envía el nombre y apellidos del empleado. Deberás guardar en una variable local el número del empleado para después modificar su sueldo.
Procedimiento salaries_addSalario:
Recordar que si no estamos seguros de que la consulta devuelve más de una fila habría que poner LIMIT 1 en el SELECT.
 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `salaries_addSalario`(IN numEmp int,INOUT nuevoSalario int)
 2 BEGIN
 3     
 4     UPDATE salaries
 5     SET salary = salary + nuevoSalario
 6     WHERE emp_no = numEmp AND
 7           to_date >= CURDATE();
 8     
 9     SELECT salary 
10     INTO nuevoSalario
11     FROM salaries
12     WHERE emp_no = numEmp AND
13           to_date >= CURDATE();
14 END
Ejemplo de uso:
1 SET @salario = 500;
2 call salaries_addSalario('10001',@salario);
3 
4 select @salario;


Instrucción IF-ELSE

  • Al igual que en cualquier lenguaje de programación podemos hacer uso de la instrucción condicional IF.
La sintaxis completa la podéis consultar en el siguiente enlace.


Ejercicios propuestos IF-ELSE

  • Crea un método de nombre employee_getMaxSalario(valor int).
Dicho método deberá buscar un empleado que tenga un salario igual al indicado y mostrar su nombre.
En caso de que no se encuentre ninguno, deberá mostrar 'NO EXISTE' (utiliza if-else).
  • Crea un método de nombre staff_getJefes(fecha date,OUT numJefes int).
Dicho método deberá de comprobar cuantos jefes ('Staff') están o estuvieron trabajando en la empresa desde la fecha indicada en el parámetro.
Si el número de jefes es superior a 10 debe mostrar la cadena 'CUANTOS JEFES!!!' en caso contrario debe mostrar la cadena 'PODRIA HABER MAS'.
Deberá devolver el número de jefes encontrados en el parámetro de salida.



Solución Ejercicios propuestos IF-ELSE

  • Crea un método de nombre employee_getMaxSalario(valor int).
Dicho método deberá buscar un empleado que tenga un salario igual al indicado y mostrar su nombre.
En caso de que no se encuentre ninguno, deberá mostrar 'NO EXISTE' (utiliza if-else).
 1 DELIMITER $$
 2 
 3 CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_getMaxSalario`(valor int)
 4 BEGIN
 5 	declare numEmp int default -1;
 6 	declare nombre varchar(31); -- Nombre:16 + Apellidos:15 + espacio en blanco
 7 
 8 	SELECT emp_no
 9 	INTO numEmp
10 	FROM salaries
11 	WHERE salary = valor
12 	LIMIT 1;
13 
14 	IF (numEmp<>-1) THEN
15 		SELECT CONCAT(first_name,' ', last_name) 
16 		INTO nombre
17 		FROM employees
18 		WHERE emp_no = numEmp;
19 
20 	ELSE
21 		SET nombre = 'NO EXISTE';
22 	END IF;
23 
24 	select nombre;
25 
26 END$$



  • Crea un método de nombre staff_getJefes(fecha date,OUT numJefes int).
Dicho método deberá de comprobar cuantos jefes ('Staff') están o estuvieron trabajando en la empresa desde la fecha indicada en el parámetro.
Si el número de jefes es superior a 10 debe mostrar la cadena 'CUANTOS JEFES!!!' en caso contrario debe mostrar la cadena 'PODRIA HABER MAS'.
Deberá devolver el número de jefes encontrados en el parámetro de salida.
 1 DELIMITER $$
 2 
 3 CREATE DEFINER=`root`@`localhost` PROCEDURE `staff_getJefes`(fecha date,OUT numJefes int)
 4 BEGIN
 5 	declare cadena varchar(30);
 6 
 7 	SELECT count(*)
 8 	INTO numJefes
 9 	FROM titles
10 	WHERE from_date > fecha;
11 
12 	IF (numJefes > 10) THEN
13 		SET cadena='CUANTOS JEFES!!!!';
14 	ELSE
15 		SET cadena='PODRIA HABER MAS!!!';
16 	END IF;
17 
18 	SELECT cadena;
19 
20 END$$
Ejemplo de uso:
1 CALL staff_getJefes('2002-01-01',@numJefes);
2 
3 SELECT @numJefes;



Instrucción CASE

  • La instrucción CASE viene a ser como un IF-ELSEIF-ELSEIF-ELSEIF... pero de una forma mucho más legible.



Instrucciones Repetitivas

  • Existen varias opciones para crear un bucle en el que un conjunto de instrucciones se repiten hasta o mientras se cumpla una determinada condición.
  • while: Se repite mientras se cumpla la condición.
  • repeat: Se repite hasta que se cumpla la condición.
  • loop: Se repite el bucle hasta que se ejecute la sentencia LEAVE.



Cursores

  • Un cursor es una estructura de control que nos permite recorrer fila a fila una determinada consulta y guardar los datos de cada fila en variables para hacer uso de ellas.
  • El proceso para utilizar un cursor suele ser siempre el mismo en todos los gestores de bases de datos que lo soportan:
  • Declarar el cursor:
  • DECLARE nombreCursor CURSOR FOR SENTENCIA_SQL_SELECT;
  • Abrir el cursor:
  • OPEN nombreCursor;
  • Recorrer el cursor hasta que no haya más filas.
Al recorrer el cursor vamos guardando los datos en variables (los datos indicados por las columnas de la sentencia SELECT).
Por otra banda, la forma que tiene Mysql de saber cuando ha llegado al final del cursor es 'capturando' una excepción.
DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
Estas dos líneas lo que hacen es declarar una variable (done) y dicha variable valdrá TRUE cuando el cursor llegue al final.
Para recorrer el array tendremos que utilizar la orden FETCH que guarda en variables las columnas indicadas en el SELECT del cursor.
1   read_loop: LOOP
2     FETCH nombreCursor INTO varA, varB;  --varA y varB son variables declaradas previamente y que se corresponden con las columnas indicadas en el SELECT
3     IF done THEN
4       LEAVE read_loop;
5     END IF;
6 
7     -- Hacemos lo que queramos con varA y varB que serán los datos de cada una de las filas del cursor
8   END LOOP;
  • Cerrar el cursor:
  • CLOSE nombreCursor;

Gestión de errores

  • Durante la ejecución de procedimientos almacenados pueden darse errores que hagan que el procedimiento falle.
Mysql permite 'capturar' esos errores y tratarlos mediante programación permitiendo que el procedimiento continúe su ejecución.
  • Un caso típico podría ser cuando intentamos dar de alta (INSERT) una fila con una clave primaria duplicada.
Al intentar hacer esto, el gestor 'rompe' la ejecución del procedimiento almacenado en la línea del INSERT e informa del problema.
Otro ejemplo sería intentar modificar una tabla que no existe,....


  • Lo que hace Mysql es 'declarar' primero cuales son los errores que se van a capturar y cuales serán las instrucciones que se deben ejecutar en caso de que se produzca.
Esto hará que el procedimiento almacenado continúe la ejecución con la siguiente línea a la que provocó el error.
  • La lista de todos los posibles códigos de error de Mysql lo podéis consultar en este enlace.


  • Veamos un ejemplo:
Procedimiento employee_add: Da de alta a un nuevo empleado.
Capturamos el error de clave duplicada.
 1 DELIMITER $$
 2 CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_add`(numEmp int, fechaNac date,nombre varchar(14),apellidos varchar(16),mujer boolean)
 3 BEGIN
 4     DECLARE error boolean default false;
 5     DECLARE genero char(1);
 6 
 7     DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
 8     BEGIN
 9       SET error=true; 
10     END;
11     
12     IF(mujer) THEN
13 	SET genero = 'F';
14     ELSE
15 	SET genero = 'M';
16     END IF;
17     
18     INSERT INTO employees(emp_no,birth_date,first_name,last_name,gender,hire_date)
19     VALUES (numEmp,fechaNac,nombre,apellidos,genero,'1999-01-01');
20     
21     IF (error) THEN
22 	SELECT -1,'Clave primaria duplicada';
23     ELSE
24         SELECT 0,'Fila añadida';
25     END IF;
26 
27 END$$


Ejemplo de uso:
1 CALL employee_add('10001','1972-01-01','Angel','Fernandez',false);
Mysql captura errores 1.jpg



Funciones


  • Una función lo conforman un bloque de instrucciones que se ejecutan y que 'devuelven' un dato el cual es 'recogido' por el que realiza la llamada a la función.
Podríamos ver una función como un procedimiento con un parámetro de tipo OUT.
  • Con respecto a los procedimientos, la función se diferencia (a mayores del hecho de devolver un valor) en que se pueden usar dentro de instrucciones SQL, como por ejemplo un SELECT, de la forma SELECT funcion(),col1 FROM Tabla.


  • En ejemplo de función:
1 DELIMITER //
2 
3 CREATE FUCNTION ejemploFuncion() RETURNS VARCHAR(20)
4 BEGIN
5     RETURN 'Ejemplo';
6 END
7 //
Línea 3: Como vemos, en la definición de la función indicamos el tipo de dato que va a devolver (que si consultamos la sintaxis puede ser uno de estos tipos:STRING|INTEGER|REAL|DECIMAL).
  • Línea 5: En cualquier parte de la función hacemos uso de la instrucción return pasando un dato que debe ser del mismo tipo que el indicado en la definición.


Ejemplo de llamada:
1 select ejemploFuncion();
Mostrará la cadena 'Ejemplo'.


Ejercicios propuestos funciones

  • Crea una función de nombre department_getName(numero char(4)) que en base a un número de departamento devuelva su nombre o la cadena 'DESCONOCIDO' en caso que no exista.


  • Crea una función de nombre employee_getMaxSalary(numero int) que en base a un número de empleado devuelva su salario mayor (utiliza la función de agregado MAX(columna)).



Solución Ejercicios propuestos funciones

  • Crea una función de nombre department_getName(numero char(4)) que en base a un número de departamento devuelva su nombre o la cadena 'DESCONOCIDO' en caso que no exista.

Función nombre department_getName(numero char(4)):

 1 CREATE DEFINER=`root`@`localhost` FUNCTION `department_getName`(numero char(4)) RETURNS varchar(40) CHARSET latin1
 2 BEGIN
 3 	DECLARE nombre varchar(40) default 'DESCONOCIDO';
 4     
 5     SELECT dept_name
 6     INTO nombre
 7     FROM departments
 8     WHERE dept_no=numero;
 9     
10 
11 	RETURN nombre;
12 END
Ejemplo de uso:
1 SET @nombre = department_getName('d009');
2 SELECT @nombre;


Otro ejemplo de uso: En este caso utilizaremos la función dentro del SELECT.
1 SELECT department_getName(dept_no) FROM departments;


Otro ejemplo: En este caso utilizamos la función dentro de la cláusula WHERE.
1 SELECT emp_no FROM dept_manager WHERE department_getName(dept_no)='Development';


  • Crea una función de nombre employee_getMaxSalary(numero int) que en base a un número de empleado devuelva su salario mayor (utiliza la función de agregado MAX(columna)).
 1 CREATE DEFINER=`root`@`localhost` FUNCTION `employee_getMaxSalary`(numero int) RETURNS int(11)
 2 BEGIN
 3     DECLARE sueldo int default -1;
 4     
 5     SELECT max(salary)
 6     INTO sueldo
 7     FROM salaries
 8     WHERE emp_no = numero;
 9     
10     RETURN sueldo;
11 	
12 END


Ejemplo de uso:

1 SELECT employee_getMaxSalary(10001) as SalarioMaximo

Otro ejemplo de uso:

1 SELECT employee_getMaxSalary(10001) as SalarioMaximo
2 SET @salario = employee_getMaxSalary(10001);
3 SELECT @salario;





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