Хранимые процедуры MySQL

MySQL 5.0 поддерживает хранимые процедуры с момента своего выпуска.

Хранимая процедура — это объект базы данных, который хранит сложные программы в базе данных для вызова внешними программами.

Хранимая процедура — это набор инструкций SQL, предназначенный для выполнения определенной функции. Она компилируется, создается и сохраняется в базе данных. Пользователи могут вызвать и выполнить ее, указав имя хранимой процедуры и передав параметры (при необходимости).

Идея хранимых процедур проста: инкапсуляция и повторное использование кода на уровне языка SQL базы данных.

Преимущества

Хранимые процедуры могут инкапсулировать и скрывать сложную бизнес-логику.
Хранимые процедуры могут возвращать значения и принимать параметры.
Хранимые процедуры нельзя запускать с помощью команды SELECT, поскольку они являются подпрограммами, в отличие от представлений, таблиц или пользовательских функций.
Хранимые процедуры можно использовать для проверки данных, обеспечения соблюдения бизнес-логики и т. д.

Недостатки

Хранимые процедуры часто адаптируются под конкретные базы данных, поскольку поддерживаемые языки программирования различаются. При переходе на систему баз данных другого производителя существующие хранимые процедуры приходится переписывать.
Настройка производительности и написание хранимых процедур ограничены особенностями различных систем баз данных.

I. Создание и вызов хранимых процедур

Хранимая процедура — это именованный блок кода, используемый для выполнения определенной функции.
Созданные хранимые процедуры хранятся в словаре данных базы данных.

Создание хранимой процедуры

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]

Основной синтаксис в хранимых процедурах MySQL

Объявите разделитель операторов; его можно настроить:

DELIMITER $$
или 
DELIMITER //

Объявите хранимую процедуру:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

Символы начала и конца хранимой процедуры:

BEGIN .... END

Присваивание переменной:

SET @p_in=1

Определение переменной:

DECLARE l_int int unsigned default 4000000;

Создание хранимых процедур и хранимых функций MySQL:

create procedure 存储过程名(参数)

Тело хранимой процедуры:

create function 存储函数名(参数)

Пример

Создайте базу данных и сделайте резервную копию таблицы данных для примера операции:

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;

Ниже приведён пример хранимой процедуры, которая удаляет все матчи, в которых участвовал указанный игрок:

mysql> delimiter $$　　# Временно измените разделитель операторов с точки с запятой ; на два $$ (можно настроить) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> 　　DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows затронуто (0.01 sec) mysql> delimiter;　　# Восстановите разделитель операторов на точку с запятой

Анализ:По умолчанию хранимая процедура связана с базой данных по умолчанию. Если вы хотите создать хранимую процедуру в конкретной базе данных, укажите имя базы данных перед именем процедуры. При определении процедуры используйтеDELIMITER $$команда для изменения разделителя операторов с точки с запятой;временно на два$$, чтобы точки с запятой, используемые в теле процедуры, передавались напрямую серверу и не интерпретировались клиентом (например, mysql).

Вызов хранимой процедуры:

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 в наборе (0.00 sec) mysql> call delete_matches(57); Query OK, 1 строка затронута (0.03 сек) 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)

Анализ:В хранимой процедуре задается переменная p_playerno, требующая параметра. При вызове хранимой процедуры переменной p_playerno через параметр присваивается 57, после чего выполняются SQL-операции в хранимой процедуре.

Тело хранимой процедуры

Тело хранимой процедуры содержит операторы, которые должны выполняться при вызове процедуры, например операторы DML и DDL, операторы IF-THEN-ELSE и WHILE-DO, операторы DECLARE для объявления переменных и т. д.
Формат тела процедуры: начинается с BEGIN и заканчивается END (может быть вложенным)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Примечание:Каждый вложенный блок и каждый оператор в нем должны заканчиваться точкой с запятой. Блок BEGIN-END, обозначающий конец тела процедуры (также называемый составным оператором), не требует точки с запятой.

Маркировка блоков операторов:

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

Например:

label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　операторы; 　　　　END label3 ; 　　END label2; END label1

Метки имеют два назначения:

1. Повысить читаемость кода
2. Некоторые операторы (например, операторы LEAVE и ITERATE) требуют меток

II. Параметры хранимых процедур

Параметры хранимых процедур MySQL используются при определении хранимых процедур. Существует три типа параметров: IN, OUT и INOUT, в следующем виде:

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

Входной параметр IN: указывает, что вызывающая сторона передаёт значение в процедуру (входное значение может быть литералом или переменной)
Выходной параметр OUT: указывает, что процедура передаёт значение вызывающей стороне (может возвращать несколько значений) (выходное значение может быть только переменной)
Входной/выходной параметр INOUT: указывает, что вызывающая сторона передаёт значение в процедуру и что процедура передаёт значение вызывающей стороне (значение может быть только переменной)

1. Входной параметр 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 | +-------+

Из вышесказанного видно, что p_in изменяется в хранимой процедуре, но это не влияет на@p_inзначение последнего, потому что первый является локальной переменной, а последний — глобальной переменной.

2. Выходной параметр 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 | +-------+ 　　# Потому что OUT — выходной параметр для вызывающей стороны и не принимает входные параметры, p_out в хранимой процедуре равен NULL +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+ 　　# Вызвана хранимая процедура out_param, выходной параметр, изменяющий значение переменной p_out

3. Входной/выходной параметр 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 | +----------+ # Вызвана хранимая процедура inout_param, которая принимает входные параметры и также выводит параметры, изменяя переменную

Примечание:

1. Если у процедуры нет параметров, вы всё равно должны писать круглые скобки после имени процедуры. Например:

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

2. Убедитесь, что имена параметров не совпадают с именами столбцов; иначе в теле процедуры имя параметра будет рассматриваться как имя столбца.

Предложения:

Используйте параметры IN для входных значений.
Используйте параметры OUT для возвращаемых значений.
Используйте параметры INOUT как можно реже.

III. Переменные

1. Определение переменных

Объявления локальных переменных должны быть размещены в начале тела хранимой процедуры:

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

Здесь datatype — это тип данных MySQL, например INT, FLOAT, DATE, VARCHAR(length)

Например:

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 'Это не будет дополняться пробелами';

2. Присваивание переменных

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

3. Пользовательские переменные

Использование пользовательских переменных в клиенте 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 | +------+

Использование пользовательских переменных в хранимых процедурах

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

Передача пользовательских переменных с глобальной областью видимости между хранимыми процедурами

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Последняя процедура была ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Последняя процедура была ',@last_proc | +-----------------------------------------------+ | Последняя процедура была p1 | +-----------------------------------------------+

Примечание:

1. Имена пользовательских переменных обычно начинаются с @
2. Чрезмерное использование пользовательских переменных может затруднить понимание и управление программой

IV. Комментарии

Хранимые процедуры MySQL могут использовать два стиля комментариев

Два дефиса--: Этот стиль обычно используется для однострочных комментариев.

Стиль C: Обычно используется для многострочных комментариев.

Например:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --nameИмя хранимой процедуры ->(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 ;

Вызов хранимых процедур MySQL

Используйте CALL, а затем имя вашей процедуры и круглые скобки. Внутри круглых скобок добавьте параметры по мере необходимости. Параметры включают входные параметры, выходные параметры и входные/выходные параметры. Для конкретных способов вызова обратитесь к примерам выше.

Запрос хранимых процедур MySQL

Если мы хотим узнать, какие таблицы существуют в базе данных, мы обычно используемshowtables;для их просмотра. Тогда, если мы хотим просмотреть хранимые процедуры в базе данных, можем ли мы также использовать этот метод? Ответ: мы можем просматривать хранимые процедуры в базе данных, но другим способом.

Мы можем использовать следующий оператор для запроса:

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

或者

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

或者

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

Если мы хотим узнать подробности конкретной хранимой процедуры, что нам делать? Можем ли мы также использовать DESCRIBE table_name для её просмотра, как мы делаем с таблицами?

Ответ:Мы можем просмотреть подробности хранимой процедуры, но нам нужно использовать другой метод:

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

для просмотра подробностей текущей хранимой процедуры.

Изменение хранимых процедур MySQL

ALTER PROCEDURE

Изменение ранее заданной хранимой процедуры, созданной с помощью CREATE PROCEDURE, не влияет на связанные хранимые процедуры или хранимые функции.

Удаление хранимых процедур MySQL

Удаление хранимой процедуры относительно просто, так же как и удаление таблицы:

DROPPROCEDURE

Удаление одной или нескольких хранимых процедур из таблицы MySQL.

Управляющие операторы в хранимых процедурах MySQL

(1). Область видимости переменных

Внутренние переменные имеют более высокий приоритет в своей области видимости. Когда выполнение достигает END, внутренние переменные исчезают. В этот момент они уже находятся вне своей области видимости, и переменные больше не видны, потому что эту объявленную переменную больше нельзя найти вне хранимой процедуры. Однако можно сохранить её значение через OUT-параметр или присвоив её значение переменной сеанса.

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). Условные операторы

1. Операторы 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. оператор 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). Операторы цикла

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

Он проверяет результат после выполнения операции, тогда как while проверяет перед выполнением.

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

Цикл loop не требует начального условия, подобно циклу while, и, как цикл repeat, не требует условия завершения. Оператор leave используется для выхода из цикла.

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. МЕТКИ:

Метки можно размещать перед операторами begin, repeat, while или loop. Метки операторов можно использовать только перед допустимыми операторами. Они могут выйти из цикла, в результате чего выполняемые инструкции достигнут последнего шага составного оператора.

(4). ITERATE итерация

ITERATE перезапускает составной оператор, ссылаясь на его метку:

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 ;

Справочные статьи:

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

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