Introducción a los procedimientos almacenados de MySQL. Los procedimientos almacenados de MySQL tratan eficazmente la somnolencia
Presentamos a su atención un nuevo curso del equipo. El código- “Pruebas de penetración de Aplicaciones Web desde cero”. Teoría general, preparación del entorno de trabajo, fuzzing pasivo y toma de huellas dactilares, fuzzing activo, vulnerabilidades, post-explotación, herramientas, ingeniería social y mucho más.
Procedimientos almacenados de MySQL (Parte 1)
Seguimos trabajando con la base de datos mytest creada en la anterior. Hoy agregaremos a nuestra aplicación la capacidad de responder al comentario de un usuario y también aprenderemos a crear procedimientos almacenados y funciones.
Crear un procedimiento almacenado
Abra phpmyadmin. Seleccione la base de datos mytest y haga clic en su título o en el icono Examinar. Luego vaya a la pestaña Rutinas y cree un nuevo procedimiento haciendo clic en Agregar rutina.
Aparecerá un formulario que deberás completar.
Nombre de rutina (nombre de procedimiento/función) Responder al comentario.
Tipo (tipo) - procedimiento. La diferencia entre un procedimiento y una función es que una función siempre devuelve algún valor y contiene una declaración de retorno.
Parámetros (parámetros) nuestro procedimiento tomará dos parámetros: el texto de la respuesta y el id del comentario al que estamos respondiendo. Ambos parámetros se pasarán desde nuestra aplicación cliente.
Crear parámetros de procedimiento
Creemos el primer parámetro.
Dirección indicar la dirección del parámetro (IN, OUT, INOUT). En nuestro procedimiento, ambos parámetros pasados serán entrantes (IN).
Nombre (nombre del parámetro) Contenido.
Escriba INT, VARCHAR, DATETIME, etc. El parámetro Contenido contiene el texto de la respuesta, que se almacenará en la columna comment_content. Esta columna tiene un tipo específico, para determinarlo, abra la tabla wp_comments y vaya a la pestaña Estructura, busque el nombre de la columna que necesitamos y observe su tipo en la columna Tipo. En este ejemplo, la columna es de tipo texto, se debe especificar el mismo tipo para nuestro parámetro.
Longitud/Valores (longitud o valor) para el tipo de texto, este campo no se puede configurar, pero normalmente la longitud se indica aquí, por ejemplo VARCHAR(20), INT(10) o algún valor predeterminado.
Opciones como opciones adicionales, puede especificar la codificación de la columna actual; también se puede ver en la pestaña Estructura, columna Intercalación; Establezcamos el valor en utf8.
resultado
Agreguemos un segundo parámetro haciendo clic en el botón Agregar parámetro.
Dirección - IN Nombre - Tipo ComID - BIGINT Longitud/Valores - 20 Opciones - SIN FIRMAR
Ambos parámetros han sido creados, continuamos completando el formulario.
Definición aquí describimos el cuerpo del procedimiento. El cuerpo es un bloque que comienza con la palabra clave BEGIN y termina con la palabra clave END. Dentro del cuerpo del procedimiento, puede colocar el texto de la consulta, declarar variables, usar construcciones de ramificación, bucles y mucho más, como en cualquier lenguaje de programación.
cuerpo del procedimiento
Primero, creemos un bloque para el principio y el final del cuerpo de nuestro procedimiento.
COMIENZO FINAL;
Ahora agreguemos un texto de consulta que completará los campos en la tabla wp-comments al agregar un nuevo comentario (respuesta).
COMENZAR A INSERTAR EN wp_comments (autor_comentario, correo electrónico_autor_comentario, contenido_comentario, fecha_comentario, fecha_gmt_comentario, id_post_comentario, padre_comentario, aprobado_comentario, id_usuario) VALORES; FIN;
Almacenaremos los valores sustituidos en variables. Para crear una variable, use la palabra clave DECLARE, luego especifique el nombre, el tipo y la longitud de la variable, y también puede especificar el valor predeterminado. Si una variable tiene un parámetro DEFAULT, entonces la variable se inicializa.
DECLARAR tipo de nombre (longitud) Valor predeterminado PREDETERMINADO;
También puede establecer un valor para cualquier variable utilizando el operador SET.
ESTABLECER nombre de variable = valor;
Y entonces crearemos tres variables: Autor, Correo electrónico, ID usado, que almacenarán valores para las columnas: comentario_autor, comentario_autor_correo electrónico, id_usuario.
COMENZAR DECLARAR Autor tinytext DEFAULT "admin"; DECLARAR ID de usuario bigint(20) PREDETERMINADO 1; -- Declaró la variable Correo electrónico DECLARE Correo electrónico varchar(100); -- Establecer el valor de la variable Correo electrónico SET Correo electrónico = " [correo electrónico protegido]"; FIN;
comentario_contenido Esta columna almacena el texto del comentario, que se pasa al procedimiento como parámetro de entrada Contenido. No crearemos una variable separada, simplemente sustituiremos el valor del parámetro de entrada en VALORES.
fecha_comentario Y comentario_fecha_gmt Ambas columnas tendrán los mismos valores cuando se llenen por primera vez. Creemos una variable Fecha y asignémosle como valor el resultado que devolverá la función AHORA incorporada. Esta función devuelve la fecha y hora actuales en formato DATETIME.
DECLARAR MyCurDate FECHA Y HORA POR DEFECTO AHORA();
DECLARAR MyCurDate DATETIME; ESTABLECER MiFechaCur = AHORA();
comentario_aprobado¿El comentario está aprobado? 1 (sí); de lo contrario, 0. Creemos la variable Aprobado, pero antes de establecer el valor haremos una pequeña verificación.
DECLARAR Aprobado varchar(20); SI Autor = "admin" ENTONCES ESTABLECER Aprobado = 1; ELSE SET Aprobado = 0; ENDIF;
comentario_padre aquí debe especificar la identificación del comentario al que respondemos como valor. El ID se pasa al procedimiento como segundo parámetro de entrada. Creemos una variable ParentCom y asignémosle el valor del parámetro pasado.
DECLARAR ParentCom varchar(20); SET ParentCom = ComID;
Queda el último parámetro comentario_post_id Aquí debe indicar la identificación de la publicación en la que se publicará nuestro comentario. Declaremos una variable llamada PostID.
DECLARAR PostID BIGINT(20);
En este punto, el cuerpo del procedimiento debería verse así.
BEGIN -- bloque de declaración de variables DECLARE Autor tinytext DEFAULT "admin"; DECLARAR ID de usuario bigint(20) PREDETERMINADO 1; DECLARAR Correo electrónico varchar(100); DECLARAR Fecha FECHA HORA PREDETERMINADO AHORA(); DECLARAR ParentCom varchar(20); DECLARAR Aprobado varchar(20); DECLARAR PostID BIGINT(20); -- Establecer valores de variables SI Autor = "admin" ENTONCES ESTABLECER Aprobado = 1; ELSE SET Aprobado = 0; ENDIF; ESTABLECER correo electrónico = " [correo electrónico protegido]"; SET ParentCom = ComID; - solicitud INSERTAR EN wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALORES (Autor, Correo electrónico, Contenido, Fecha, Fecha, PostID, ParentCom, Aprobado, ID de usuario);
Procedimientos almacenados MySQL
Procedimientos almacenados: ¿qué es?
Los procedimientos almacenados aparecieron a partir de la versión 5 de MySQL. Le permiten automatizar procesos complejos a nivel de MySQL, en lugar de utilizar scripts externos para ello. Esto nos da la mayor velocidad de ejecución, porque... No enviamos una gran cantidad de solicitudes, sino que llamamos a tal o cual procedimiento (o función) solo una vez.
¿Qué se necesita para esto? Instale el servidor MySQL versión 5 o superior (dev.mysql.com/downloads). Los procedimientos se pueden crear como consultas, por ejemplo a través de la línea de comandos de MySQL, pero por conveniencia le aconsejo que descargue las herramientas GUI de MySQL (dev.mysql.com/downloads/gui-tools). Este paquete incluye tres programas: MySQL Administrator, MySQL Query Browser y MySQL Migration Toolkit. Necesitaremos los dos primeros. (Aunque puede arreglárselas solo con MySQL Query Browser, todo ese dinero en los procedimientos almacenados a veces puede resultar confuso).
Primer procedimiento almacenado
Entonces, abra MySQL Administrator, conéctese al servidor MySQL y cree un nuevo esquema (base de datos): haga clic en Catálogos, seleccione Crear nuevo esquema en el área Esquemas (Ctrl+N). Nómbrelo de alguna manera (por ejemplo, db). Abra el esquema que acaba de crear, seleccione la pestaña Procedimientos almacenados y haga clic en el botón Crear proceso almacenado. Nombra tu procedimiento procedimiento1. En el cuerpo del procedimiento (entre BEGIN y END) ingrese lo siguiente:
SELECCIONE "Este es mi procedimiento almacenado";
Y haga clic en Ejecutar SQL: se crea el procedimiento. Abra MySQL Query Browser, seleccione su esquema (db) e ingrese la siguiente consulta:
LLAMADA al procedimiento1();
¡Voilá! Felicidades.
Variables en MySQL
Para obtener algún beneficio de los procedimientos almacenados en MySQL, deberá trabajar con variables. Dado que esto está más allá del alcance de este artículo, mostraré sólo algunos ejemplos.
Variables simples
DECLARAR iVar INT POR DEFECTO 0; SELECCIONE CONTAR(*) EN iVar DESDE `datos`;
Variables del sistema
ESTABLECER @iVar = 5 ; SELECCIONE @iVar;
La diferencia entre variables simples y de sistema es que se accede a las variables del sistema desde fuera del procedimiento almacenado. Es decir, para extraer algunos datos es necesario utilizar los del sistema, y las variables que sólo se necesitan dentro del procedimiento deben ser simples.
Parámetros en procedimientos almacenados.
Aquí también todo es bastante sencillo. Cambiamos la primera línea declarando el procedimiento en sí:
CREAR PROCEDIMIENTO `procedimiento1` (EN iInput1 INT, EN iInput2 INT)
Aquí, la palabra clave IN indica que el parámetro es de solo lectura. A continuación, trabajamos con este parámetro como con una variable normal dentro de un procedimiento:
Condiciones, Ciclos. SI ENTONCES OTRA COSA, MIENTRAS
Definitivamente necesitarás condiciones y bucles al escribir procedimientos almacenados complejos, pero no me extenderé en este tema. Creo que tienes al menos algunas habilidades de programación, así que solo te mostraré la sintaxis.
SI condición ENTONCES acción; OTRA acción; FINALIZAR SI;
MIENTRAS condición HACER acción; TERMINAR MIENTRAS;
Ejemplo sencillo
Un buen caso de uso para los procedimientos almacenados es cuando necesita combinar varias solicitudes en una, como agregar un tema a un foro y aumentar el número total de temas. Digamos la tabla de hilos.
CREAR TABLA `hilos` (`id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `tag` VARCHAR(255) NOT NULL, CLAVE PRIMARIA (`id`) ) MOTOR = MYISAM;
Aquí el título será el título del nuevo tema. Pues una tabla, por ejemplo, con diversas variables estadísticas del sitio, incluido el número total de temas del formulario.
CREAR TABLA `variables` (`id` INT NOT NULL AUTO_INCREMENT, `nombre` VARCHAR(255) NOT NULL, `valor` INT NOT NULL DEFAULT 0, CLAVE PRIMARIA (`id`) ) MOTOR = MYISAM;
Todo parece claro aquí, digamos que tenemos una entrada con nombre = hilos y valor = 0. Creemos un nuevo procedimiento almacenado procedimiento2.
No hay nada especial que explicar, solo se combinaron dos solicitudes en una. Ahora podemos llamar a este procedimiento así:
CALL procedimiento2("Mi nuevo hilo");
Por lo tanto, en lugar de pasar dos o más solicitudes (por ejemplo a través de php), podemos pasar una: optimización, código limpio y se puede cambiar en cualquier momento sin afectar otros scripts.
Cursores (cursores MySQL)
Los cursores le permiten recorrer todos los resultados de una consulta. Es difícil de explicar en teoría, pero te lo mostraré en la práctica. Agreguemos otra tabla a nuestra base de datos: visitas:
CREAR TABLA `etiquetas` (`id` INT NOT NULL AUTO_INCREMENT, `tag` VARCHAR(255) NOT NULL, CLAVE PRIMARIA (`id`)) MOTOR = MYISAM
Aquí registraremos todas las etiquetas de todos los temas. El procedimiento almacenado se verá así:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREAR PROCEDIMIENTO `procedimiento3` () COMENZAR DECLARAR hecho INT DEFAULT 0; |
DECLARAR ciervo VARCHAR(255);
DECLARAR iCount INT POR DEFECTO 0;
DECLARAR rCursor CURSOR PARA SELECCIONAR `etiqueta` DE `hilos` DONDE 1;
DECLARAR CONTINUAR MANEJADOR PARA SQLSTATE "02000" SET hecho = 1;
ABRIR cursor;
Recordemos las variables del sistema y consideremos otra manipulación de nuestras tablas: obtener el número total de etiquetas y temas. Vayamos directamente al procedimiento:
| 1 2 3 4 5 6 7 8 9 10 | CREAR PROCEDIMIENTO `procedimiento4` () COMENZAR DECLARAR iTags INT DEFAULT 0; |
DECLARAR iThreads INT POR DEFECTO 0;
SELECCIONE CONTAR(*) EN iTags DESDE `etiquetas`;
SELECCIONE CONTAR(*) EN iThreads DESDE `hilos`;
ESTABLECER @tags = iTags, @threads = iThreads; FIN
Declaramos dos variables: iTags, el número de etiquetas, e iThreads, el número total de temas.
A continuación hay dos consultas de selección simples, completando nuestras variables. Bueno, al final asignamos los valores de las variables simples actuales a variables del sistema. Cuando se llama, este procedimiento no devuelve nada, pero después de llamarlo podemos leer los valores requeridos de las variables del sistema:
LLAMADA al procedimiento4(); SELECCIONE @etiquetas, @hilos;
MySQL 5 tiene muchas características nuevas, una de las más importantes es la creación de procedimientos almacenados. En este tutorial, hablaré sobre qué son y cómo pueden hacerte la vida más fácil.
- Introducción
- Un procedimiento almacenado es una forma de encapsular acciones repetitivas. Los procedimientos almacenados pueden declarar variables, manipular el flujo de datos y utilizar otras técnicas de programación.
- El motivo de su creación es claro y se confirma por el uso frecuente. En cambio, si se habla con quienes trabajan con ellos de forma irregular, las opiniones se dividirán en dos flancos completamente opuestos. No olvides esto.
- Para
Compartir lógica con otras aplicaciones. Los procedimientos almacenados encapsulan la funcionalidad; esto proporciona conectividad para acceder y administrar datos a través de diferentes aplicaciones.
- Aislar a los usuarios de las tablas de la base de datos. Esto le permite dar acceso a los procedimientos almacenados, pero no a los datos de la tabla en sí.
- Proporciona un mecanismo de protección. Según el punto anterior, si solo puede acceder a los datos a través de procedimientos almacenados, nadie más puede borrar sus datos mediante el comando SQL DELETE.
- Está duplicando la lógica de su aplicación en dos lugares: el código del servidor y el código de los procedimientos almacenados, lo que complica el proceso de manipulación de datos.
- La migración de un DBMS a otro (DB2, SQL Server, etc.) puede generar problemas.
La herramienta con la que trabajo se llama MySQL Query Browser, que es bastante estándar para interactuar con bases de datos. La herramienta de línea de comandos MySQL es otra excelente opción. La razón por la que les digo esto es porque el phpMyAdmin favorito de todos no admite la ejecución de procedimientos almacenados.
Por cierto, estoy usando una estructura de tabla básica para que le resulte más fácil comprender este tema. Estoy hablando de procedimientos almacenados, y son lo suficientemente complejos como para requerir profundizar en la engorrosa estructura de la tabla.
Paso 1: Coloca un limitador
Un delimitador es un carácter o cadena de caracteres que se utiliza para indicarle al cliente MySQL que ha terminado de escribir la expresión SQL. Durante años, el punto y coma ha sido el delimitador. Sin embargo, pueden surgir problemas porque puede haber varias expresiones en un procedimiento almacenado, cada una de las cuales debe terminar con un punto y coma. En este tutorial utilizo la cadena “//” como delimitador.
Paso 2: Cómo trabajar con procedimientos almacenados
Crear un procedimiento almacenado
DELIMITER // CREAR PROCEDIMIENTO `p2` () IDIOMA SQL DETERMINISTA SQL DEFINIDOR DE SEGURIDAD COMENTARIO "Un procedimiento" COMENZAR SELECCIONAR "¡Hola mundo!"; FIN//
La primera parte del código crea un procedimiento almacenado. El siguiente contiene parámetros opcionales. Luego viene el nombre y, finalmente, el cuerpo del procedimiento en sí.
Los nombres de los procedimientos almacenados distinguen entre mayúsculas y minúsculas. Tampoco puede crear varios procedimientos con el mismo nombre. No puede haber expresiones dentro de un procedimiento almacenado que modifiquen la propia base de datos.
4 características de un procedimiento almacenado:
- Idioma: Para fines de portabilidad, el valor predeterminado es SQL.
- Determinista: si el procedimiento siempre devuelve el mismo resultado y toma los mismos parámetros de entrada. Esto es para el proceso de replicación y registro. El valor predeterminado NO ES DETERMINISTA.
- Seguridad SQL: los derechos del usuario se verifican durante la llamada. INVOKER es el usuario que llama al procedimiento almacenado. DEFINER es el “creador” del procedimiento. El valor predeterminado es DEFINIDOR.
- Comentario: Para fines de documentación, el valor predeterminado es ""
Llamar a un procedimiento almacenado
Para llamar a un procedimiento almacenado, debe escribir la palabra clave CALL, seguida del nombre del procedimiento, seguido de los parámetros (variables o valores) entre paréntesis. Se requieren paréntesis.
LLAMADA nombre_procedimiento_almacenado (param1, param2, ....) LLAMADA procedimiento1(10, "parámetro de cadena", @parameter_var);
Modificar un procedimiento almacenado
MySQL tiene una declaración ALTER PROCEDURE para cambiar procedimientos, pero solo es adecuada para cambiar ciertas características. Si necesita cambiar los parámetros o el cuerpo de un procedimiento, debe eliminarlo y volver a crearlo.
Eliminar un procedimiento almacenado
PROCEDIMIENTO DE BAJA SI EXISTE p2;
Este es un comando simple. La declaración IF EXISTS detecta un error si dicho procedimiento no existe.
Paso 3: Opciones
Veamos cómo podemos pasar parámetros a un procedimiento almacenado.
- CREAR PROCEDIMIENTO proc1(): lista de parámetros vacía
- CREAR PROCEDIMIENTO proc1 (EN varname TIPO DE DATOS): un parámetro de entrada. La palabra IN es opcional porque los parámetros predeterminados son IN (in).
- CREAR PROCEDIMIENTO proc1 (OUT varname TIPO DE DATOS): se devuelve un parámetro.
- CREAR PROCEDIMIENTO proc1 (INOUT varname TIPO DE DATOS): un parámetro, tanto de entrada como de retorno.
Naturalmente, puedes especificar varios parámetros de diferentes tipos.
Ejemplo de parámetro IN
DELIMITER // CREAR PROCEDIMIENTO `proc_IN` (IN var1 INT) COMENZAR SELECCIONAR var1 + 2 COMO resultado; FIN//
Ejemplo de parámetro de salida
DELIMITER // CREAR PROCEDIMIENTO `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Esto es una prueba"; FIN //
Ejemplo de parámetro INOUT
DELIMITER // CREAR PROCEDIMIENTO `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; FIN //
Paso 4: Variables
Ahora te enseñaré cómo crear variables y almacenarlas dentro de procedimientos. Debes declararlos explícitamente al principio del bloque BEGIN/END, junto con sus tipos de datos. Una vez que haya declarado una variable, puede usarla de la misma manera que las variables de sesión, los literales o los nombres de columnas.
La sintaxis de declaración de variables se ve así:
DECLARE varname DATA-TYPE DEFAULT valor predeterminado;
Declaremos algunas variables:
DECLARAR a, b INT PREDETERMINADO 5; DECLARAR cadena VARCHAR(50); DECLARAR hoy TIMESTAMP DEFAULT CURRENT_DATE; DECLARAR v1, v2, v3 TINYINT;
Trabajar con variables
Una vez que haya declarado una variable, puede establecer su valor usando los comandos SET o SELECT:
DELIMITER // CREAR PROCEDIMIENTO `var_proc` (IN paramstr VARCHAR(20)) COMENZAR DECLARAR a, b INT DEFAULT 5;
DECLARAR cadena VARCHAR(50);
DECLARAR hoy TIMESTAMP DEFAULT CURRENT_DATE;
DECLARAR v1, v2, v3 TINYINT;
INSERTAR EN LA tabla1 VALORES (a);
SET str = "Soy una cadena";<>SELECCIONE CONCAT(str,paramstr), hoy DESDE la tabla2 DONDE b >=5; FIN //
Paso 5: Estructuras de control de subprocesos
MySQL admite construcciones IF, CASE, ITERATE, LEAVE LOOP, WHILE y REPEAT para controlar subprocesos dentro de un procedimiento almacenado. Veremos cómo usar IF, CASE y WHILE ya que son los más utilizados.
diseño si
Usando la construcción IF, podemos realizar tareas que contengan condiciones:
DELIMITER // CREAR PROCEDIMIENTO `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT;
ESTABLECER variable1 = parámetro1 + 1;
SI variable1 = 0 ENTONCES SELECCIONE variable1;< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
ENDIF;
SI param1 = 0 ENTONCES SELECCIONE "Valor del parámetro = 0";
ELSE SELECT "Valor del parámetro
DECLARAR nombre-cursor CURSOR PARA SELECCIONAR...; /*Declarar un cursor y llenarlo */ DECLARAR CONTINUAR MANEJADOR PARA NO ENCONTRADO /*Qué hacer cuando no hay más registros*/ OPEN cursor-name; /*Abrir cursor*/ FETCH nombre-cursor INTO variable [, variable]; /*Asignar un valor a una variable igual al valor actual de la columna*/ CLOSE cursor-name; /*Cerrar cursor*/
En este ejemplo realizaremos algunas operaciones simples usando un cursor:
DELIMITER // CREAR PROCEDIMIENTO `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT;
DECLARAR cur1 CURSOR PARA SELECCIONAR col1 DE tabla1;
- DECLARAR CONTINUAR MANEJADOR PARA EL CONJUNTO NO ENCONTRADO b = 1;
- ABRIR cur1;
- ESTABLECER b = 0;
ESTABLECER c = 0;
MIENTRAS b = 0 BUSCA cur1 EN a;
SI b = 0 ENTONCES ESTABLEZCA c = c + a;
ENDIF;
TERMINAR MIENTRAS; CERRAR cur1;
Un procedimiento almacenado puede incluir declaraciones SQL y estructuras de control especiales. Útil cuando es necesario realizar la misma función desde diferentes aplicaciones o plataformas, o como medio para ocultar funciones. En una base de datos, los procedimientos almacenados se consideran análogos al enfoque de programación orientado a objetos. Los procedimientos almacenados se pueden utilizar para controlar cómo se accede a los datos.
Crear un procedimiento almacenado
Creemos un procedimiento llamado GetAllProducts() para obtener una lista de todos los productos de la tabla.
Para hacer esto, necesita descargar el cliente mysql y ejecutar los siguientes comandos:
Equipo DELIMITADOR // no incluido en los procedimientos almacenados. DELIMITER es un comando especial que cambia el separador de consultas estándar (predeterminado “;”) al especificado después. Con este comando lo cambiaremos a 2 barras (//).
Si no cambia el delimitador, MySQL malinterpretará el procedimiento y arrojará un error. Después de END, se utiliza un delimitador // y el comando DELIMITER devuelve el valor del delimitador ";".
Las palabras reservadas CREAR PROCEDIMIENTO le dicen a mysql que CREA UN PROCEDIMIENTO. Después de estas palabras es necesario indicar el nombre del procedimiento almacenado (en el ejemplo GetAllProducts). Los paréntesis vacíos "()" después del nombre del procedimiento significan que el procedimiento no acepta ninguna variable.
Los comandos BEGIN y END abren y cierran respectivamente un bloque de código SQL.
No es muy conveniente escribir procedimientos almacenados en la consola MySQL. En este caso, puede utilizar herramientas GUI, con las que puede crear procedimientos almacenados en una interfaz intuitiva.
EN Banco de trabajo MySQL Se crea un procedimiento haciendo clic derecho en Rutinas y seleccionando el elemento en el menú desplegable. Crear procedimiento…
Puede ver el código completo que se enviará a MySQL antes de escribirlo en la base de datos. Si no hay errores, haga clic Aplicar.
Después de la compilación, MySQL escribe el procedimiento en un directorio. Una vez completada la grabación, presione el botón Finalizar.
Puede ver el procedimiento almacenado creado en la lista Rutinas.
Llamar a un procedimiento almacenado
Para llamar a un procedimiento almacenado, use el comando SQL incorporado LLAMAR(LLAMAR):
En lugar de STORED_PROCEDURE_NAME Se indica el nombre del procedimiento almacenado y entre paréntesis se indica una lista de variables. Para llamar al procedimiento creado sin variables se utilizará el código:
Porque el procedimiento debe ejecutar el comando * SELECCIONAR DE productos;**, es decir SELECCIONE todo DE la tabla PRODUCTOS, entonces un ejemplo de cómo funcionaría sería el siguiente resultado:
Variables en procedimientos almacenados
Definición 2
Variable es un objeto de datos con nombre que contiene algún valor cuando se ejecuta un procedimiento almacenado.
Se utilizan para almacenar cualquier resultado de una consulta o expresión. Las variables en los procedimientos almacenados son locales, es decir. no se les puede llamar desde fuera.
Declaración de variables
Definición 3
El proceso de decirle a mysql que se debe usar una variable se llama declaración.
Para declarar variables, use el operador DECLARAR:
DECLARAR– operador reservado.
nombre_variable– el nombre de la variable, a la que se aplican las mismas reglas que al nombre de una columna de datos en MySQL.
tipo de datos (tamaño)– el tipo de variable que se utiliza y su tamaño. Las variables en MySQL pueden tomar valores de cualquier tipo de datos, como DATETIME, VARCHAR, INT, etc.
DEFAULT valor_predeterminado– le permite establecer el valor inicial de la variable. Si no se especifica, el valor predeterminado será NULL.
Para crear una variable total_sale, que almacenará una lista de compras de tipo INT, y que por defecto será igual a 0, escribe el código:
Para declarar varias variables del mismo tipo, puedes escribir el código:
Asignar valores a una variable
Para asignar un valor a una variable, use el operador COLOCAR.
Por ejemplo, declaramos la variable total_count y luego le asignamos el valor 10:
Además del operador SET, el operador SELECT INTO se puede utilizar para transferir el resultado de una consulta a una variable. Tenga en cuenta que la consulta debe devolver un valor escalar (es decir, uno).
La primera línea de código declara la variable total_products y establece su valor en 0.
La segunda y tercera líneas utilizan la instrucción SELECT INTO para escribir el resultado de la consulta SELECT COUNT(*) FROM productos en la variable creada.
Alcance variable
Cada variable tiene su propio alcance, que determina su vida útil.
Si crea una variable dentro de un procedimiento almacenado, existirá mientras esté en uso.
Si declara una variable dentro de un bloque BEGIN END, entonces existirá solo dentro de este bloque.
Las variables con @ al principio de sus nombres son global, es decir. están disponibles durante toda la sesión.
¡Atención! Este trabajo está basado en la traducción del apartado “17.1. Rutinas almacenadas y tablas de concesión" descripción del software MySQL 5.0.19, "Manual de referencia. Documenta MySQL 5.0 hasta 5.0.19. Documento generado el: 2006-01-23 (revisión:995)"
"Lea todo primero y luego pruebe los ejemplos".
Los procedimientos almacenados son un conjunto de comandos SQL que se pueden compilar y almacenar en un servidor. De esta manera, en lugar de almacenar una consulta utilizada con frecuencia, los clientes pueden hacer referencia al procedimiento almacenado correspondiente. Esto proporciona un mejor rendimiento porque una solicitud determinada solo necesita analizarse una vez y reduce el tráfico entre el servidor y el cliente. El nivel conceptual también se puede aumentar creando una biblioteca de funciones en el servidor.
Un disparador es un procedimiento almacenado que se ejecuta cuando ocurre un evento específico. Por ejemplo, puede definir un procedimiento almacenado que se ejecute cada vez que se elimina un registro de una tabla transaccional, garantizando así que el cliente correspondiente se elimine automáticamente de la tabla de clientes cuando se eliminen todas sus transacciones.
Los programas almacenados (procedimientos y funciones) son compatibles con MySQL 5.0. Los procedimientos almacenados son un conjunto de expresiones SQL que se pueden almacenar en el servidor. Una vez hecho esto, el cliente ya no necesita retransmitir la solicitud, sino que simplemente llama al programa almacenado.
Esto puede resultar útil cuando:
- Numerosas aplicaciones cliente están escritas en diferentes idiomas o se ejecutan en diferentes plataformas, pero deben utilizar la misma base de datos de transacciones.
- la seguridad es lo primero
Los procedimientos y funciones almacenados (rutinas) pueden proporcionar un mejor rendimiento porque se requiere enviar menos información entre el cliente y el servidor. La elección aumenta la carga en el servidor de la base de datos, pero reduce los costos del lado del cliente. Utilícelo si muchas máquinas cliente (como servidores web) cuentan con una o más bases de datos.
Las rutinas almacenadas también le permiten utilizar bibliotecas de funciones almacenadas en la base de datos del servidor. Esta característica se proporciona para muchos lenguajes de programación modernos, lo que permite llamarlos directamente (por ejemplo, mediante clases).
MySQL sigue la sintaxis de SQL:2003 para procedimientos almacenados, que ya se utiliza en DB2 de IBM.
De las palabras a los hechos...
Al crear, modificar y eliminar rutinas almacenadas, el servidor manipula la tabla mysql.proc
A partir de MySQL 5.0.3 se requieren los siguientes privilegios:
CREAR RUTINA para crear procedimientos almacenados
ALTERAR LA RUTINA necesario cambiar o eliminar procedimientos. Este privilegio se asigna automáticamente al creador del procedimiento (función)
EJECUTAR Se requerirá privilegio para ejecutar la subrutina. Sin embargo, se asigna automáticamente al creador del procedimiento (función). Además, por defecto, el parámetro SQL SECURITY para la rutina DEFINER, que permite a los usuarios con acceso a la base de datos llamar a rutinas asociadas a esta base de datos.
Sintaxis de procedimientos y funciones almacenados.
Una rutina almacenada es un procedimiento o función. Las rutinas almacenadas se crean utilizando declaraciones CREATE PROCEDURE o CREATE FUNCTION. Una rutina almacenada se llama mediante una instrucción CALL, y solo se utilizan variables que devuelven valores como variables de salida. La función se puede llamar como cualquier otra función y puede devolver un valor escalar. Las rutinas almacenadas pueden llamar a otras rutinas almacenadas.
Desde MySQL 5.0.1, el procedimiento o función cargado está asociado a una base de datos específica. Esto tiene varios significados:
- Cuando se llama a una subrutina, la intención es llamar a USE db_name (y descartar la base de datos cuando la subrutina se haya completado y la base de datos ya no sea necesaria)
- Puede calificar nombres regulares con un nombre de base de datos. Esto se puede utilizar para hacer referencia a una rutina que no está en la base de datos actual. Por ejemplo, para ejecutar un procedimiento almacenado p o una función f que está asociada con la base de datos de prueba, puede decirle al intérprete de comandos así: CALL test.p() o test.f() .
- Cuando se elimina una base de datos, también se eliminan todas las rutinas cargadas asociadas con ella. En MySQL 5.0.0, las rutinas cargadas son globales y no están asociadas con la base de datos. Heredan la base de datos predeterminada de la declaración de llamada. Si USE db_name se realiza dentro de una subrutina, la base de datos actual original se restaurará después de salir de la subrutina (por ejemplo, la base de datos actual es db_11, llamamos a una subrutina que usa db_22, después de salir de la subrutina permanece el db_11 actual)
MySQL es totalmente compatible con extensiones que le permiten utilizar expresiones SELECT regulares (sin utilizar cursores ni variables locales) dentro de procedimientos almacenados. El conjunto de resultados devuelto por la consulta simplemente se envía directamente al cliente. Una consulta SELECT múltiple genera múltiples conjuntos de resultados, por lo que el cliente debe usar una biblioteca que admita múltiples conjuntos de resultados.
CREAR PROCEDIMIENTO- crear un procedimiento almacenado.
CREAR FUNCIÓN- crear una función almacenada.
Sintaxis:
CREAR PROCEDIMIENTO nombre_procedimiento ([parámetro_procedimiento[,...]])[características...] subrutina_cuerpo
CREAR FUNCIÓN nombre_función ([parámetro_función[,...]])
DEVOLUCIONES tipo
[característica...] subrutina_cuerpo
parámetro_procedimiento:
[ EN | AFUERA | ENTRADA FUERA] tipo de nombre_parámetro
parámetro_función:
tipo de nombre_parámetro
tipo:
Cualquier tipo de datos MySQL
característica:
IDIOMA SQL
|
DETERMINISTA
| ( CONTIENE SQL | NO SQL | LEE DATOS SQL | MODIFICA DATOS SQL )
| SEGURIDAD SQL (DEFINIDOR | INVOCADOR)
| COMENTARIO "cadena"
cuerpo_subrutina:
Corrija la expresión SQL.
Miremos todo en la práctica.
Primero, creemos un procedimiento almacenado con la siguiente consulta:
CREAR PROCEDIMIENTO `my_proc`(OUT t INTEGER(11))
NO DETERMINISTA
INVOCADOR DE SEGURIDAD SQL
COMENTARIO ""
COMENZAR
seleccione val1+val2 en "t" desde `my` LIMIT 0,1;
FIN;
El uso de la expresión LIMIT en esta consulta se realiza en reconocimiento del hecho de que no todos los clientes pueden aceptar un conjunto de resultados de varias filas.
Después de eso, llamémoslo:
LLAMAR a my_proc(@a);
SELECCIONE @a; Para separar una solicitud interna de una externa siempre se utiliza un separador diferente al habitual (para la tarea utilice el comando <строка/символ>)
DELIMITADOR
Aquí hay otro ejemplo teniendo en cuenta todos los requisitos.
Mysql> delimitador //
mysql> CREAR PROCEDIMIENTO simpleproc (OUT param1 INT)
-> COMENZAR
-> SELECCIONE CONTAR(*) EN param1 DE t;
-> //
->FIN;
mysql>delimitador;
mysql> CALL simpleproc(@a);
Consulta correcta, 0 filas afectadas (0,00 s)
+------+
mysql> SELECCIONAR @a;
+------+
| 3 |
+------+
| @a |
1 fila en conjunto (0,00 seg)
Todo el proceso se puede observar en la siguiente figura:
Desencadenantes
La compatibilidad con disparadores ha estado disponible en MySQL desde la versión 5.0.2. Desencadenar
- un objeto de base de datos con nombre que está asociado con una tabla y se activa cuando ocurre un evento determinado, un evento asociado con esta tabla.
Por ejemplo, el siguiente código crea una tabla y un disparador INSERT. El disparador suma los valores insertados en una de las columnas de la tabla.
Mysql> CREATE TABLE cuenta (num_cuenta INT, cantidad DECIMAL(10,2));
Consulta correcta, 0 filas afectadas (0,03 segundos)
mysql> CREAR DISPARADOR ins_sum ANTES DE INSERTAR EN la cuenta
-> PARA CADA FILA SET @sum = @sum + NEW.amount;
Consulta correcta, 0 filas afectadas (0,06 segundos) Declaremos una suma variable y le asignaremos el valor 1. Después de eso, cada vez que insertemos en la tabla cuenta
el valor de esta variable aumentará según la parte insertada. Comentario
. Si el valor de la variable no se inicializa, ¡el disparador no funcionará!
Sintaxis para crear un disparadorCREAR DESENCADENAR
nombre_disparador hora_disparador evento_disparador
Si todo está claro de inmediato con el nombre del activador y el nombre de usuario, hablaremos de "tiempo de activación" y "evento" por separado.
tiempo_disparador
Define el momento en el que se produce la acción desencadenante. ANTES significa que el disparador se ejecutará antes de que se complete el evento desencadenante y DESPUÉS significa después. Por ejemplo, al insertar registros (ver ejemplo arriba), nuestro disparador se activó antes de la inserción real del registro y calculó la suma. Esta opción es apropiada para el cálculo preliminar de algunos campos adicionales en la tabla o para la inserción paralela en otra tabla.
evento_disparador
Aquí todo es más sencillo. Indica claramente en qué evento se ejecuta el disparador.
- INSERTAR: es decir durante operaciones de inserción o expresiones similares (INSERT, LOAD DATA y REPLACE)
- ACTUALIZACIÓN: cuando se modifica una entidad (fila)
- ELIMINAR: Cuando se elimina un registro (consultas que contienen declaraciones ELIMINAR y/o REEMPLAZAR)
