II. RF KMF DokuWiki

• Курсори підтримуються в процедурах, функціях і тригерах. Вони оголошуються

до їх використання. Обробники оголошуються після оголошення курсорів.

• Курсори дозволяють проглядати послідовно рядки результуючої таблиці від першого до останнього.
  

Курсори використовуються виключно для читання рядків.

• При використанні курсорів в транзакціях вони автоматично закриваються після команди 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 <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 }

• <Оператор> - це будь-який оператор або блок (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;