MySQL comenzó a admitir procedimientos almacenados desde la versión 5.0.

Un procedimiento almacenado (Stored Procedure) es un objeto de base de datos que almacena programas complejos en la base de datos para que puedan ser llamados por programas externos.

Un procedimiento almacenado es un conjunto de sentencias SQL diseñado para completar una función específica. Se compila, se crea y se guarda en la base de datos. El usuario puede llamarlo y ejecutarlo especificando el nombre del procedimiento almacenado y proporcionando parámetros (cuando sea necesario).

Conceptualmente, un procedimiento almacenado es muy simple: es la encapsulación y reutilización de código a nivel del lenguaje SQL de la base de datos.

Ventajas

  • Los procedimientos almacenados pueden encapsular y ocultar lógica de negocio compleja.
  • Los procedimientos almacenados pueden devolver valores y aceptar parámetros.
  • Los procedimientos almacenados no pueden ejecutarse con la instrucción SELECT, porque son subprogramas, a diferencia de las vistas, tablas de datos o funciones definidas por el usuario.
  • Los procedimientos almacenados se pueden utilizar para la validación de datos, la aplicación forzosa de lógica de negocio, etc.

Desventajas

  • Los procedimientos almacenados suelen estar personalizados para una base de datos específica, porque los lenguajes de programación soportados difieren. Al cambiar a un sistema de base de datos de otro fabricante, es necesario reescribir los procedimientos almacenados originales.
  • El ajuste de rendimiento y la escritura de procedimientos almacenados están limitados por los distintos sistemas de base de datos.

1. Creación y llamada de procedimientos almacenados

  • Un procedimiento almacenado es un bloque de código con nombre que se utiliza para completar una función específica.
  • El procedimiento almacenado creado se guarda en el diccionario de datos de la base de datos.

Crear un procedimiento almacenado

CREATE [DEFINER = { user | CURRENT_USER }] 　PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: 　　Valid SQL routine statement [begin_label:] BEGIN 　　[statement_list] 　　　　…… END [end_label]

Sintaxis clave en los procedimientos almacenados de MySQL

Declarar el delimitador de sentencias, se puede personalizar:

DELIMITER $$
o 
DELIMITER //

Declarar el procedimiento almacenado:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

Símbolos de inicio y fin del procedimiento almacenado:

BEGIN .... END

Asignación de variables:

SET @p_in=1

Definición de variables:

DECLARE l_int int unsigned default 4000000;

Crear procedimientos almacenados y funciones almacenadas de MySQL:

create procedure 存储过程名(参数)

Cuerpo del procedimiento almacenado:

create function 存储函数名(参数)

Ejemplo

Crear una base de datos y hacer una copia de seguridad de las tablas de datos para las operaciones de ejemplo:

mysql> create database db1; mysql> use db1; mysql> create table PLAYERS as select * from TENNIS.PLAYERS; mysql> create table MATCHES as select * from TENNIS.MATCHES;

El siguiente es un ejemplo de procedimiento almacenado que elimina todos los partidos en los que participó un jugador determinado:

mysql> delimiter $$　　# Cambia temporalmente el delimitador de sentencias de punto y coma ; a dos $$ (puede ser personalizado) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> 　　DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter;　　# Restablece el delimitador de sentencias a punto y coma

Análisis:De forma predeterminada, el procedimiento almacenado está asociado con la base de datos predeterminada. Si desea especificar que el procedimiento almacenado se cree en una base de datos determinada, agregue el nombre de la base de datos como prefijo antes del nombre del procedimiento. Al definir el procedimiento, useDELIMITER $$el comando para cambiar el delimitador de sentencias de punto y coma;temporalmente a dos$$, de modo que los punto y coma utilizados en el cuerpo del procedimiento se envíen directamente al servidor sin ser interpretados por el cliente (como mysql).

Llamar al procedimiento almacenado:

call sp_name[(传参)];
mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows in set (0.00 sec) mysql> call delete_matches(57); Query OK, 1 row affected (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows in set (0.00 sec)

Análisis:En el procedimiento almacenado se define la variable `p_playerno` que requiere pasar un parámetro. Al llamar al procedimiento almacenado, se asigna el valor 57 a `p_playerno` mediante el paso de parámetros, y luego se ejecutan las operaciones SQL dentro del procedimiento almacenado.

Cuerpo del procedimiento almacenado

  • El cuerpo del procedimiento almacenado contiene las sentencias que deben ejecutarse al llamar al procedimiento, por ejemplo: sentencias DML, DDL, sentencias if-then-else y while-do, sentencias declare para declarar variables, etc.
  • Formato del cuerpo del procedimiento: comienza con `BEGIN` y termina con `END` (se puede anidar).
BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Nota:Cada bloque anidado y cada sentencia dentro de él debe terminar con punto y coma. El bloque `BEGIN-END` que indica el final del cuerpo del procedimiento (también llamado sentencia compuesta / compound statement) no requiere punto y coma.

Poner etiquetas a los bloques de sentencias:

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

Por ejemplo:

label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　statements; 　　　　END label3 ; 　　END label2; END label1

Las etiquetas tienen dos funciones:

  • 1. Mejorar la legibilidad del código
  • 2. En algunas sentencias (por ejemplo, las sentencias `LEAVE` e `ITERATE`), se necesitan etiquetas.

2. Parámetros de los procedimientos almacenados

Los parámetros de los procedimientos almacenados de MySQL se utilizan en la definición del procedimiento almacenado. Hay tres tipos de parámetros: `IN`, `OUT`, `INOUT`, con la forma:

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
  • Parámetro de entrada `IN`: indica que el llamador pasa un valor al procedimiento (el valor pasado puede ser un literal o una variable).
  • Parámetro de salida `OUT`: indica que el procedimiento devuelve un valor al llamador (puede devolver múltiples valores) (el valor devuelto solo puede ser una variable).
  • Parámetro de entrada/salida `INOUT`: indica tanto que el llamador pasa un valor al procedimiento como que el procedimiento devuelve un valor al llamador (el valor solo puede ser una variable).

1. Parámetro de entrada `in`

mysql> delimiter $$ mysql> create procedure in_param(in p_in int) -> begin -> 　　select p_in; -> 　　set p_in=2; -> select P_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; mysql> call in_param(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+ mysql> select @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+

De lo anterior se puede ver que `p_in` se modifica dentro del procedimiento almacenado, pero no afecta@p_inal valor de la variable externa, porque la primera es una variable local y la segunda es una variable global.

2. Parámetro de salida `out`

mysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ 　　# Como `out` es un parámetro de salida hacia el llamador y no recibe parámetros de entrada, `p_out` dentro del procedimiento almacenado es `NULL` +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+ 　　# Se llamó al procedimiento almacenado `out_param`, parámetro de salida, y se cambió el valor de la variable `p_out`

3. Parámetro de entrada/salida `inout`

mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ # Se llamó al procedimiento almacenado `inout_param`, aceptó el parámetro de entrada, también emitió el parámetro de salida y cambió la variable

Nota:

1. Si el procedimiento no tiene parámetros, también se deben escribir paréntesis después del nombre del procedimiento. Ejemplo:

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2. Asegúrese de que el nombre del parámetro no sea igual al nombre de una columna; de lo contrario, en el cuerpo del procedimiento, el nombre del parámetro se tratará como nombre de columna.

Sugerencias:

  • Para valores de entrada, use el parámetro `in`.
  • Para valores de retorno, use el parámetro `out`.
  • Utilice el parámetro `inout` lo menos posible.

3. Variables

1. Definición de variables

La declaración de variables locales debe colocarse al comienzo del cuerpo del procedimiento almacenado:

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

Donde `datatype` es el tipo de datos de MySQL, por ejemplo: `int`, `float`, `date`, `varchar(length)`.

Por ejemplo:

DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

2. Asignación de variables

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. Variables de usuario

Usar variables de usuario en el cliente MySQL:

mysql > SELECT 'Hello World' into @x; mysql > SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ mysql > SET @y='Goodbye Cruel World'; mysql > SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ mysql > SET @z=1+2+3; mysql > SELECT @z; +------+ | @z | +------+ | 6 | +------+

Usar variables de usuario dentro de los procedimientos almacenados

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+

Pasar variables de usuario de ámbito global entre procedimientos almacenados

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Last procedure was ',@last_proc | +-----------------------------------------------+ | Last procedure was p1 | +-----------------------------------------------+

Nota:

  • 1. Los nombres de las variables de usuario generalmente comienzan con `@`.
  • 2. El uso excesivo de variables de usuario puede hacer que el programa sea difícil de entender y administrar.

4. Comentarios

Los procedimientos almacenados de MySQL pueden usar dos estilos de comentarios.

Dos guiones--: Este estilo se usa generalmente para comentarios de una sola línea.

Estilo C: Generalmente se usa para comentarios de varias líneas.

Por ejemplo:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --nameNombre del procedimiento almacenado ->(IN parameter1 INTEGER) -> BEGIN -> DECLARE variable1 CHAR(10); -> IF parameter1 = 17 THEN -> SET variable1 = 'birds'; -> ELSE -> SET variable1 = 'beasts'; -> END IF; -> INSERT INTO table1 VALUES (variable1); -> END -> // mysql > DELIMITER ;

Llamada a procedimientos almacenados de MySQL

Use `CALL` junto con el nombre de su procedimiento y un paréntesis. Dentro del paréntesis, agregue los parámetros según sea necesario. Los parámetros incluyen parámetros de entrada, parámetros de salida y parámetros de entrada/salida. El método de llamada específico se puede consultar en los ejemplos anteriores.

Consulta de procedimientos almacenados de MySQL

Si queremos saber qué tablas hay en una base de datos, generalmente usamosshowtables;para verlas. Entonces, si queremos ver los procedimientos almacenados de una base de datos, ¿también podemos usar ese método? La respuesta es que podemos ver los procedimientos almacenados de una base de datos, pero de otra manera.

Podemos usar las siguientes sentencias para consultarlos:

selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

Si queremos conocer los detalles de un procedimiento almacenado, ¿qué debemos hacer? ¿También podemos usar `DESCRIBE nombre_de_tabla` para verlo, como hacemos con las tablas?

La respuesta es:Podemos ver los detalles del procedimiento almacenado, pero necesitamos otro método:

SHOW CREATE PROCEDURE 数据库.存储过程名;

así se pueden ver los detalles del procedimiento almacenado actual.

Modificación de procedimientos almacenados de MySQL

ALTER PROCEDURE

Cambiar un procedimiento almacenado previamente especificado creado con `CREATE PROCEDURE` no afecta a los procedimientos almacenados ni a las funciones almacenadas relacionadas.

Eliminación de procedimientos almacenados de MySQL

Eliminar un procedimiento almacenado es bastante simple, igual que eliminar una tabla:

DROPPROCEDURE

Eliminar uno o más procedimientos almacenados de MySQL.

Sentencias de control en procedimientos almacenados de MySQL

(1). Ámbito de las variables

Las variables internas tienen mayor prioridad dentro de su ámbito. Cuando la ejecución llega a `END`, la variable interna desaparece; en ese momento ya está fuera de su ámbito y la variable ya no es visible, porque fuera del procedimiento almacenado ya no se puede encontrar esta variable declarada. Pero puede conservar su valor mediante el parámetro `out` o asignando su valor a una variable de sesión.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;

(2). Sentencias condicionales

1. Sentencia if-then-else

mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ;

2. Sentencia case:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case

(3). Sentencias de bucle

1. while ···· end while

mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ;
while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

Comprueba el resultado después de realizar la operación, mientras que while lo comprueba antes de realizarla.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> // mysql > DELIMITER ;
repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

El bucle loop no necesita condición inicial, similar al bucle while, y al igual que el bucle repeat no necesita condición final; la sentencia leave sirve para salir del bucle.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

4. LABLES (etiquetas):

Las etiquetas pueden usarse antes de las sentencias begin, repeat, while o loop. Las etiquetas de sentencia solo pueden usarse delante de sentencias válidas. Pueden salir de un bucle, haciendo que la ejecución alcance el último paso de la sentencia compuesta.

(4). ITERATE (iteración)

ITERATE reinicia la sentencia compuesta haciendo referencia a su etiqueta:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

Artículos de referencia:

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html