====8. Курсори==== *Курсори підтримуються в процедурах, функціях і тригерах. Вони оголошуються до їх використання. Обробники оголошуються після оголошення курсорів.\\ *Курсори дозволяють проглядати послідовно рядки результуючої таблиці від першого до останнього.\\ Курсори використовуються виключно для читання рядків.\\ *При використанні курсорів в транзакціях вони автоматично закриваються після команди COMMIT.\\ З курсорами використовуються 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 HANDLER FOR <Подія> [,<Подія>… ] <Оператор>; <Подія> (клас подій) – виключення (помилка), яка активує обробник. Якщо виключення збігається зі значенням Подія, MySQL виконає <Оператор>, а потім продовжить або завершить поточний блок коду, в залежності від значення <Дія_після_обробки>.\\ Поки в MySQL є лише 2 варіанти для <Дія_після_обробки>= {CONTINUE | EXIT }\\ *<Оператор> - це будь-який оператор або блок (BEGIN … END) *<Подія> може приймати значення: А). Код ситуації помилки MySQL (число);\\ B). Значення SQLSTATE (5 символів) (або SQLWARNING, SQLEXCEPTION, NOTFOUND);\\ C). Назву умови, яка зв’язана з кодом помилки MySQL, SQLSTATE. **Класи SQLSTATE (стандарти ANSI і ODBC)**\\ Перші 2 символи значення SQLSTATE вказують класс помилки: * CLASS = "00" - успішне виконання команд, * CLASS = "01" (SQLWARNING ) – попередження (<Дія_після_обробки> завжди = CONTINUE, поки що), * CLASS = "02" (NOTFOUND) – використовують для курсору або команди SELECT … FROM ... INTO <змінна> (рекомендується <Дія_після_обробки> = CONTINUE). * SQLEXCEPTION - class значень SQLSTATE, які не починаються з '00', '01', '02'. Без обробника 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. **Отримання інформації про виключення** При виникненні помилок (подій) можна отримати: *код помилки MySQL: CALL mysql_errno(); * значення SQLSTATE : CALL mysql_sqlstate() ; * рядок повідомлення: CALL mysql_error() ; Події можна ігнорувати таким чином: 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;