Алексей Паутов - MySQL: руководство профессионала
Локальные переменные обрабатываются подобно стандартным параметрам относительно типа данных и проверки переполнения.
Область (контекст) локальной переменной: внутри блока BEGIN … END, где это объявлено. Переменная может упоминаться в блоках, вложенных внутри блока объявления, за исключением тех блоков, которые объявляют переменную с тем же самым именем.
5.2.7.2. Команда SET
SET var_name = expr [, var_name = expr] …
Инструкция SET в сохраненных подпрограммах представляет собой расширенную версию общей инструкции SET. Вызванные переменные могут быть объявлены внутри подпрограммы или глобальными переменными системы.
Инструкция SET в сохраненных подпрограммах выполнена как часть синтаксиса SET. Это позволяет расширенный синтаксис SET a=x, b=y, …, где различные виды переменных (локально объявленные, глобальные и переменные сеанса сервера могут быть смешаны. Это также позволяет комбинации локальных переменных и некоторых параметров, которые имеют смысл только для переменных системы. В этом случае, параметры распознаны, но игнорируются.
5.2.7.3. Команда SELECT … INTO
SELECT col_name[,…] INTO var_name[,…] table_expr
Этот синтаксис SELECT сохраняет выбранные столбцы непосредственно в переменные. Следовательно, только одиночная строка может быть получена.
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
Имена переменных пользователя не чувствительны к регистру. Важно: имена переменных SQL не должны совпадать с именами столбцов. Если инструкция SQL, типа SELECT … INTO, содержит ссылку к столбцу, и есть объявленная локальная переменная с тем же самым именем, MySQL в настоящее время интерпретирует ссылку как имя переменной. Например, в следующей инструкции xname интерпретируется как ссылка к переменной, а не к столбцу с именем xname:
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
SELECT xname, id INTO newname, xid FROM table1 WHERE xname = xname;
SELECT newname;
END;
Когда эта процедура вызвана, переменная newname возвращает значение 'bob' независимо от значения столбца table1.xname.
5.2.8. Условия и драйверы
Некоторые условия могут требовать специфической обработки. Эти условия могут касаться ошибок или общего управления потоком данных внутри подпрограммы.
5.2.8.1. DECLARE условий
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
Эта инструкция определяет условия, которые нуждаются в специфической обработке. Это сопоставляет имя с определенным условием ошибки. Имя может впоследствии использоваться в инструкции DECLARE HANDLER.
Здесь condition_value может быть значением SQLSTATE или же кодом ошибки MySQL.
5.2.8.2. DECLARE драйвера
DECLARE handler_type HANDLER FOR condition_value[,…] statement
handler_type:
CONTINUE | EXIT | UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
| mysql_error_code
Инструкция DECLARE … HANDLER определяет драйверы, с каждым из которых может иметь дело одно или большее количество условий. Если одно из этих условий происходит, определенная инструкция statement будет выполнена. Инструкция может быть простой инструкцией (например, SET var_name = value), или это может быть составной инструкцией, записанной с помощью парочки BEGIN и END.
Для драйвера CONTINUE выполнение текущей подпрограммы продолжается после выполнения инструкции драйвера. Для драйвера EXIT выполнение завершается для составной инструкции BEGIN … END, в которой драйвер объявлен. Это истинно, даже если условие происходит во внутреннем блоке. Инструкция типа драйвера UNDO еще не обеспечивается (пока?).
Если происходит условие, для которого никакой драйвер не был объявлен, заданное по умолчанию действие: EXIT.
Параметр condition_value может быть любым из следующих значений:
Значение SQLSTATE или MySQL-код ошибки.
Имя условия, предварительно определенное с DECLARE … CONDITION.
SQLWARNING (краткая запись для всех кодов SQLSTATE, которые начинаются с 01).
NOT FOUND (краткая запись для всех кодов SQLSTATE, которые начинаются с 02).
SQLEXCEPTION (краткая запись для всех кодов SQLSTATE, не охваченных SQLWARNING или NOT FOUND).
Пример:
mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
– > BEGIN
– > DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
– > SET @x = 1;
– > INSERT INTO test.t VALUES (1);
– > SET @x = 2;
– > INSERT INTO test.t VALUES (1);
– > SET @x = 3;
– > END;
– > //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
Пример сопоставляет драйвер с SQLSTATE 23000, который происходит для ошибки дублирования ключа. Обратите внимание, что @x равен 3, это показывает что MySQL перейдет к концу процедуры. Если строка DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; не была представлена, MySQL примет заданный по умолчанию путь (EXIT) после второй неудачи INSERT из-за ограничения PRIMARY KEY, а SELECT @x возвратит 2.
Если Вы хотите игнорировать условие, Вы можете объявлять драйвер CONTINUE для этого и сопоставлять его с пустым блоком. Например:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
5.2.9. Курсоры
Простые курсоры обеспечиваются внутри сохраненных процедур и функций. Синтаксис как во внедренном SQL. Курсоры в настоящее время только для чтения, не поддерживают прокрутку и нечувствительны. Последнее означает, что сервер может или не может делать копию таблицы результата.
Курсоры должны быть объявлены перед объявлением драйверов и переменных, а условия должны быть объявлены перед объявлением курсоров и драйверов.
Пример:
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN INSERT INTO test.t3 VALUES (a,b);
ELSE INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
5.2.9.1. Объявление курсоров
DECLARE cursor_name CURSOR FOR select_statement
Эта инструкция объявляет курсор. Много курсоров может быть объявлено в подпрограмме, но каждый курсор в данном блоке должен иметь уникальное имя.
Инструкция SELECT не может иметь предложение INTO.
5.2.9.2. Инструкция OPEN
OPEN cursor_name
Эта инструкция открывает предварительно объявленный курсор.
5.2.9.3. Инструкция FETCH
FETCH cursor_name INTO var_name [, var_name] …
Эта инструкция выбирает следующую строку (если строка существует), используя определенный открытый курсор, и продвигает указатель курсора.
Если больше нет доступных строк, происходит условие No Data со значением SQLSTATE 02000. Чтобы обнаружить это условие, Вы можете установить драйвер для этого. Пример показывается в разделе "5.2.9. Курсоры".
5.2.9.4. Инструкция CLOSE
CLOSE cursor_name
Эта инструкция закрывает предварительно открытый курсор. Если курсор не закрыт явно, он все равно закроется в конце составной инструкции, в которой он был объявлен.
5.2.10. Конструкции управления потоком данных
Конструкции IF, CASE, LOOP, WHILE, REPLACE ITERATE и LEAVE полностью выполнены.
Многие из этих конструкций содержат другие инструкции, как обозначено спецификациями грамматики в следующих разделах. Такие конструкции могут быть вложены. Например, IF мог бы содержать цикл времени, который непосредственно содержит WHILE, который в свою очередь включает в себя оператор CASE. Циклы FOR в настоящее время не обеспечиваются.
5.2.10.1. Инструкция IF
IF search_condition THEN statement_list
[ELSEIF search_condition
THEN statement_list] …
[ELSE statement_list]
END IF
IF реализован как базисная условная конструкция. Если выражение search_condition истинно, соответствующий список инструкции SQL выполнен. Если пары search_condition не нашлось, будет выполнен операторный список в предложении ELSE. Каждый statement_list состоит из одной или большего количества инструкций.
Обратите внимание: имеется также функция IF(), которая отличается от команды IF, описанной здесь.
5.2.10.2. Инструкция CASE
CASE case_value WHEN when_value
THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE
Или: CASE WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE
Инструкция CASE для сохраненных подпрограмм осуществляет сложную условную конструкцию. Если search_condition равно true, соответствующий список инструкций SQL выполнен. Если никакие условия не подошли, отрабатывается операторный список в предложении ELSE. Каждый statement_list состоит из одной или большего количества инструкций.
