до їх використання. Обробники оголошуються після оголошення курсорів.
Курсори використовуються виключно для читання рядків.
З курсорами використовуються 4 команди:
1). Опис курсору:
DECLARE cur_1 CURSOR FOR SELECT dname FROM dept;
2). Відкриття курсору
OPEN cur_1;
3). Зчитування наступного запису у змінні і збільшення покажчика курсору на 1. Цю команду логічно використовувати в циклі (FETCH – вибірка).
FETCH cur_1 INTO x; --
4). CLOSE cur_1; – закриття курсору
Приклад курсору (без обробника помилок)
DELIMITER // CREATE PROCEDURE Sal_Size ( ) BEGIN DECLARE name VARCHAR (255); DECLARE s INT; DECLARE cur_1 CURSOR FOR SELECT ename, sal FROM emp; OPEN cur_1; Size: LOOP FETCH cur_1 INTO name, s; IF s > 3000 THEN SELECT name, ‘big’; ELSE SELECT name, ‘small’; END IF; END LOOP size; CLOSE cur_1; END// DELIMITER ; ERROR 1329 (0200): No data – zero rows fetched, selected, or… SELECT ename, if(sal>500, ‘big’, ‘small’) FROM emp;
Курсори. Приклад
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 =1; END REPEAT; CLOSE cur1; CLOSE cur2; END // Procedure
Обробник виключень (помилок) в процедурах і функціях
Обробник виключень (HANDLER – обробник)
Опис обробника:
DECLARE <Дія_після_обробки> HANDLER FOR <Подія> [,<Подія>… ] <Оператор>; <Подія> (клас подій) – виключення (помилка), яка активує обробник.
Якщо виключення збігається зі значенням Подія, MySQL виконає <Оператор>, а потім продовжить або завершить поточний блок коду, в залежності від значення <Дія_після_обробки>.
Поки в MySQL є лише 2 варіанти для <Дія_після_обробки>= {CONTINUE | EXIT }
А). Код ситуації помилки MySQL (число);
B). Значення SQLSTATE (5 символів) (або SQLWARNING, SQLEXCEPTION, NOTFOUND);
C). Назву умови, яка зв’язана з кодом помилки MySQL, SQLSTATE.
Класи SQLSTATE (стандарти ANSI і ODBC)
Перші 2 символи значення SQLSTATE вказують класс помилки:
Без обробника SQLEXCEPTION завжди призводить до EXIT.
Якщо процедура була викликана іншою програмою, то ця програма обробляє умову, яка описана у власних обробниках (ієхархія виконання!).
Ще не всі MySQL-виключення відповідають SQLSTATE-кодам.
Приклади обробки виключень MySQL
1). DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err= 1;
Помилка активує обробник, значення змінної err=1, а виконання програми продовжується.
2). DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'Виконано rollback (відкат), процедуру перервано'; END;
Виконується відкат транзакції, вивід повідомлення, і вихід з блоку.
3). DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;
При виконанні курсору або оператора SELECT INTO, змінна no_row_found отримає значення 1, програма виконується далі.
4). DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Помилка – дублювання ключа';
При дублюванні ключа, видається помилка MySQL 1062.
Отримання інформації про виключення При виникненні помилок (подій) можна отримати:
Події можна ігнорувати таким чином:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
Іменування виключень
Замість коду помилки при описі обробника для кодів виключень можна ввести імена, що полегшує читання програм:
DECLARE імя_помилки CONDITION FOR код_помилки;
Код_помилки = {код помилки MySQL | значення SQLSTATE}.
Замість
DECLARE EXIT HANDLER FOR 1051 SELECT 'Спочатку створіть таблицю Т1'; SELECT * FROM Т1;
вводимо і'мя коду помилки:
DECLARE table_not_found CONDITION FOR 1051; DECLARE EXIT HANDLER FOR table_not_found SELECT 'Спочатку створіть таблицю Т1'; SELECT * FROM Т1;
Типовий приклад використання обробника виключень з курсором.
DELIMITER // CREATE PROCEDURE Sal_Size ( ) BEGIN DECLARE name VARCHAR (255); DECLARE s INT; DECLARE err_no_record CONDITION FOR 1329; DECLARE EXIT HANDLER FOR err_no_record BEGIN END; DECLARE c CURSOR FOR SELECT ename, sal FROM emp; OPEN c; Size: LOOP FETCH c INTO name, s; IF s > 3000 THEN SELECT name AS Name, ‘big’ AS Size; ELSE SELECT name AS Name, ‘small’ AS Size; END IF; END LOOP size; CLOSE cur_1; END// DELIMITER ;
Приклад процедури з курсором (створена в dbForge)
DROP PROCEDURE IF EXISTS `comm`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `comm` () BEGIN DECLARE mgr_, nom, sal_, done int; DECLARE C CURSOR FOR SELECT empno FROM emp; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN c; SET done=0; WHILE done = 0 DO FETCH c INTO nom; SELECT mgr INTO mgr_ FROM emp WHERE empno = nom ; SELECT sal INTO sal_ FROM emp WHERE empno = mgr_ ; UPDATE emp SET comm = comm + sal_*0.05 WHERE empno =nom; END WHILE; CLOSE c; END$$
Приклад 2. Процедура з курсором
CREATE PROCEDURE `emp2` () BEGIN DECLARE s, nom, pred, r, done int; DECLARE C CURSOR FOR SELECT empno, sal FROM e2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; SET pred = 0; SET done=0; OPEN c; WHILE done = 0 DO FETCH c INTO nom, sal_; IF sal_ > pred THEN SET r =‘більше’; ELSE SET r =‘менше’; END IF; UPDATE e2 SET zrost = r WHERE empno =nom; SET pred = sal_; END WHILE; CLOSE c; END;