Diferencia entre revisiones de «Mysql Funciones y Procedimientos almacenados»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 479: Línea 479:
 
     ORDER BY usuario;
 
     ORDER BY usuario;
 
END
 
END
 +
</syntaxhighlight>
 +
 +
 +
: 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 <u>un usuario</u> que tiene permiso para ejecutar procedimientos sobre una base de datos indicada por una variable de usuario:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="5,7" >
 +
CREATE DEFINER=`root`@`localhost` PROCEDURE `Procedimiento_getUsersExecute`()
 +
    COMMENT 'Obtiene los usuarios que tienen permiso de ejecución sobre la base de datos employees'
 +
BEGIN
 +
    SELECT concat(User,'@',Host) as usuario
 +
    FROM mysql.procs_priv
 +
    WHERE Db = @BD;
 +
END
 +
</syntaxhighlight>
 +
 +
: Ahora quien realiza la llamada va a poder 'enviarle' el nombre de la base de datos a buscar y el procedimiento no mostrará ningún resultado ya que este estará guardado en la variable @usuario:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
SET @BD = 'employees';
 +
CALL Procedimiento_getUsersExecute();
 
</syntaxhighlight>  
 
</syntaxhighlight>  
  

Revisión del 18:00 15 feb 2018

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;


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



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




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 los nombres 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 y el procedimiento no mostrará ningún resultado ya que este estará guardado en la variable @usuario:
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 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.
  • 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.


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


Entrada/Salida


Ejercicios propuestos de parámetros de Salida


Instrucción If-then-else

Instrucción CASE

Instrucciones Repetitivas

Cursores

Gestión de errores

Funciones











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