Introducción
Los procedimientos almacenados de MySQL agrupan varias tareas en uno y guarde la tarea en el servidor para usarla en el futuro.
Los procedimientos almacenados simplifican la gestión de bases de datos y reducen el tráfico de red. Por ejemplo, emitir una consulta al servidor MySQL procesa la consulta y devuelve los resultados. El uso de procedimientos almacenados guarda las consultas en el servidor para que puedan ejecutarse más tarde.
En este tutorial, aprenderá a crear, enumerar, modificar y descartar procedimientos almacenados.
Requisitos previos
- Servidor MySQL y MySQL Workbench instalados
- Una cuenta de usuario de MySQL con privilegios de root
¿Qué son los procedimientos almacenados en MySQL?
Los procedimientos almacenados de MySQL están precompilados Declaraciones SQL almacenada en una base de datos. Son subrutinas que contienen un nombre, una lista de parámetros y sentencias SQL.
Todos los sistemas de bases de datos relacionales admiten procedimientos almacenados y no requieren ningún paquete de entorno de tiempo de ejecución adicional.
¿Cómo utilizar los procedimientos almacenados?
Para invocar procedimientos almacenados, puede utilizar CALL instrucción u otros procedimientos almacenados. La primera vez que se invoca un procedimiento almacenado, MySQL lo busca en el catálogo de la base de datos, compila el código y lo coloca en la memoria caché. y lo ejecuta.
Las ejecuciones posteriores en la misma sesión ejecutan procedimientos almacenados desde la memoria caché, lo que los hace extremadamente útiles para tareas repetitivas.
Los procedimientos almacenados hacen uso de parámetros para pasar valores y personalizar los resultados. Los parámetros se utilizan para especificar las columnas de una tabla en las que opera la consulta y devuelve resultados.
Los procedimientos almacenados también pueden incluir el IF , CASE y LOOP declaraciones de flujo de control que implementan procedimentalmente el código.
Crear procedimiento almacenado
Cree un procedimiento almacenado de dos formas:
1. Usar MySQL Shell
Utilice la siguiente sintaxis para crear un procedimiento almacenado en MySQL:
DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN
SQL statements
END //
DELIMITER ;
De forma predeterminada, la sintaxis está asociada con la base de datos en uso, pero también puede usar la sintaxis para otra base de datos especificando el nombre de la base de datos de la siguiente manera:database_name.procedure_name .
Aquí, el primer DELIMITER argumento establece el delimitador predeterminado en // , mientras que el último DELIMITER el argumento lo vuelve a colocar en el punto y coma ; . Para usar varias declaraciones, especifique diferentes delimitadores como $$ .
El nombre del procedimiento viene después de CREATE PROCEDURE argumento. Después del nombre del procedimiento, use paréntesis para especificar los parámetros que se usarán en el procedimiento, el nombre del parámetro, el tipo de datos y la longitud de los datos. Separe cada parámetro con una coma.
Los modos de parámetros son:
IN– Se usa para pasar un parámetro como entrada. Cuando se define, la consulta pasa un argumento al procedimiento almacenado. El valor del parámetro siempre está protegido.OUT– Se usa para pasar un parámetro como salida. Puede cambiar el valor dentro del procedimiento almacenado y el nuevo valor se devuelve al programa de llamada.INOUT– Una combinación deINyOUTparámetros El programa que llama pasa el argumento y el procedimiento puede modificar elINOUTparámetro, devolviendo el nuevo valor al programa.
Por ejemplo:
Ejecute el procedimiento almacenado llamándolo:
CALL procedure_name;
La consulta devuelve resultados para el procedimiento almacenado.
2. Usar MySQL Workbench
Otra forma de crear un procedimiento almacenado es usar el Asistente de MySQL Workbench. El asistente es intuitivo y simplifica el proceso ya que no tienes que colocar delimitadores ni preocuparte por el formato.
Sigue estos pasos:
Paso 3: Revisa el código y haz clic en Aplicar .
Paso 6: CALL el procedimiento en la pestaña SQL y haga clic en Ejecutar .
Si no regresa ningún error, MySQL ejecuta el procedimiento almacenado y muestra los resultados.
Lista de procedimientos almacenados
Hay tres formas de ver una lista de todos los procedimientos almacenados:
1. Usar MySQL Shell
Para obtener una lista de todos los procedimientos almacenados a los que tiene acceso, incluidas sus características, utilice la siguiente sintaxis:
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
El SHOW PROCEDURE STATUS declaración devuelve una salida larga. La instrucción muestra los nombres y las características de los procedimientos almacenados a los que tiene acceso en el servidor.
Desplácese por la salida para encontrar los procedimientos actualmente en el servidor.
El LIKE argumento encuentra procedimientos almacenados que contienen una palabra específica en su nombre. Usar % para reemplazar cualquier número de caracteres, incluido cero.
Por ejemplo:
El WHERE El argumento le permite enumerar los procedimientos almacenados solo en una base de datos en particular.
Por ejemplo:
En este ejemplo, la declaración devuelve solo los procedimientos almacenados para la 'lista_clientes ' base de datos.
2. Usar diccionario de datos
El esquema_de_información La base de datos contiene una tabla llamada rutinas , que tiene información sobre procedimientos almacenados y funciones relacionadas con todas las bases de datos en el servidor MySQL actual.
Utilice la siguiente sintaxis para ver todos los procedimientos almacenados de una base de datos:
SELECT
routine_name
FROM
information_schema.routines
WHERE
routine_type = 'PROCEDURE'
AND routine_schema = 'database_name';
3. Usar MySQL Workbench
Para un enfoque de GUI para ver los procedimientos almacenados, use MySQL Workbench. Siga estos pasos para ver los procedimientos almacenados:
Paso 2 :Expanda los Procedimientos almacenados elemento desplegable.
Este elemento muestra todos los procedimientos almacenados para la base de datos actual.
Alterar procedimiento almacenado
Alterar un procedimiento almacenado significa cambiar las características de un procedimiento. No hay ninguna declaración en MySQL para modificar los parámetros o el cuerpo de un procedimiento almacenado. Para cambiar los parámetros o el cuerpo, descarte el procedimiento almacenado y cree uno nuevo.
Modifique un procedimiento almacenado de dos maneras:
1. Usar MySQL Shell
Cambie la característica de un procedimiento utilizando ALTER PROCEDURE declaración. Por ejemplo, podemos agregar un comentario a un procedimiento que creamos anteriormente. La sintaxis es:
ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';
2. Usar MySQL Workbench
MySQL Workbench GUI permite a los usuarios alterar un procedimiento almacenado donde los usuarios pueden agregar parámetros o cambiar el código. MySQL Workbench gotas el procedimiento almacenado existente y crea una nueva después de que se hayan realizado los cambios.
Sigue estos pasos:
Paso 3: Una ventana de revisión de SQL Script aparece mostrando el proceso:eliminando el procedimiento almacenado existente y creando uno nuevo que contiene los cambios.
Haz clic en Aplicar y luego Finalizar en la siguiente ventana para ejecutar el script.
Soltar procedimiento almacenado
Para descartar (eliminar) un procedimiento:
1. Usar MySQL Shell
Elimine un procedimiento almacenado del servidor utilizando el DROP PROCEDURE declaración.
La sintaxis básica es:
DROP PROCEDURE [IF EXISTS] stored_procedure_name;
El IF EXISTS El parámetro elimina el procedimiento almacenado solo si existe en el servidor. Introduzca el nombre del procedimiento almacenado en lugar de stored_procedure_name sintaxis.
Por ejemplo:
Dado que no existe un procedimiento llamado 'test ' en el servidor, el resultado indica que 0 filas se vieron afectadas y que el procedimiento especificado no existe.
Descartar un procedimiento inexistente sin IF EXISTS el parámetro devuelve un error.
2. Usar MySQL Workbench
Para eliminar un procedimiento almacenado con MySQL Workbench, siga estos pasos:
Esta acción borra permanentemente el procedimiento.
Ventajas y desventajas de los procedimientos almacenados de MySQL
Los procedimientos almacenados tienen varias ventajas y desventajas, ya que se adaptan a necesidades específicas. A continuación se presentan algunas de las ventajas y desventajas.
Ventajas de usar procedimientos almacenados
Las ventajas de los procedimientos almacenados son:
Reducción del tráfico de red
Los procedimientos almacenados ayudan a reducir el tráfico de red entre las aplicaciones y el servidor MySQL al mantener toda la lógica de programación en el servidor. En lugar de enviar varios resultados de consultas a través de la red, las aplicaciones solo envían el nombre del procedimiento y la entrada del parámetro.
Seguridad mejorada
El administrador de la base de datos otorga privilegios a las aplicaciones para llamar y acceder solo a procedimientos almacenados específicos sin darles acceso directo a las tablas. Los procedimientos almacenados ayudan a prevenir los ataques de inyección de secuencias de comandos, ya que los parámetros de entrada se tratan como valores y no como código ejecutable.
Lógica empresarial centralizada
Los procedimientos almacenados encapsulan la lógica empresarial reutilizable por varias aplicaciones. Eso ayuda a reducir la duplicación de esa misma lógica en muchas aplicaciones diferentes y hace que la base de datos sea más consistente.
Desventajas de usar procedimientos almacenados
Las desventajas de los procedimientos almacenados son:
Uso de recursos
El uso de muchos procedimientos almacenados y operaciones lógicas hace que el uso de la memoria y la CPU aumente significativamente con cada conexión.
Sin Portabilidad
No es fácil portar procedimientos almacenados escritos en un idioma específico de una instalación a otra. Confiar en un procedimiento almacenado también vincula al usuario a una base de datos en particular.
Resolución de problemas y pruebas
MySQL no proporciona utilidades para probar y depurar procedimientos almacenados, por lo que puede ser difícil depurarlos. Desarrollar y mantener procedimientos almacenados requiere un amplio conocimiento. Este es un desafío para los nuevos desarrolladores y genera costos de mantenimiento adicionales.