C, PHP, VB, .NET

Чрез процедурите MySQL много наподобява завършен език за програмиране. За това силно спомагат възможностите за логически оператори и цикли. Ще ги разгледаме поотделно:

1. IF-ELSE:

Операторите IF-ELSE имат следната структура:

IF <условие>
   THEN <заявки>;
   ELSE <заявки>;
END IF;

Нека демонстрираме с един пример - процедура, на която подаваме параметри сума и номер на акаунт. Процедурата връща резултат "1" ако в акаунта има повече пари от посочените или "0" в противен случай:

mysql> DELIMITER |

mysql> CREATE PROCEDURE check_availability(IN acc INT, IN money DECIMAL)
       BEGIN

              DECLARE acc_avail DECIMAL;

              SELECT amount
              INTO acc_avail
              FROM accounts
              WHERE id = acc;

              IF (acc_avail >= money)
                     THEN SELECT 1;
                     ELSE SELECT 0;
              END IF;

       END
       |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT id, amount FROM accounts WHERE id = 5;
+----+--------+
| id | amount |
+----+--------+
|  5 | 191.98 |
+----+--------+
1 row in set (0.00 sec)

mysql> CALL check_availability(5, 200);
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> CALL check_availability(5, 150);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Виждате, че се получи точно желания резултат - в акаунт №5 има точно 191.98. Когато попитаме процедурата дали има 200 тя връща резултат 0, а когато я попитаме дали има 150 връща резултат 1.

Искаме да обърнем внимание на заявката "DECLARE x INT". Чрез нея ние дефинираме т.нар. локална променлива за процедурата. Тя е валидна само вътре в процедурата и се изтрива след нейното приключване. Виждате, че в случая я инициализирахме като резултат от временна таблица (SELECT -> INTO).

Трябва много да внимавате при евентуално подаване на NULL стойности. Както и при обикновените заявки, всяко сравнение с NULL стойност връща резултат FALSE.

2. CASE:

CASE е аналог на оператора за многовариантен избор switch в езика за програмиране C. Синтаксисът е следния:

CASE <променлива>
   WHEN <условие>
      THEN <заявки>;
   WHEN <условие>
      THEN <заявки>;
   ...
   ELSE <заявки>
END CASE;

Частта ELSE се достига тогава, когато нито едно от условията по-горе не е изпълнено.

Нека демонстрираме с пример - процедура, която по зададен номер на акаунт връща името на типа му:

mysql> DELIMITER |
mysql>
mysql> CREATE PROCEDURE acc_type(IN acc INT)
       BEGIN

              DECLARE acc_t TINYINT;

              SELECT type
              INTO acc_t
              FROM accounts
              WHERE id = acc;

              CASE acc_t
              WHEN 1 THEN
                     SELECT "3 months deposit" AS acctype;
              WHEN 2 THEN
                     SELECT "Annual deposit" AS acctype;
              ELSE
                     SELECT "Unknown account" AS acctype;
              END CASE;

       END
       |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT id, type FROM accounts WHERE id = 6 OR id = 7;
+----+------+
| id | type |
+----+------+
|  6 |    2 |
|  7 |    1 |
+----+------+
2 rows in set (0.00 sec)

mysql> CALL acc_type(6);
+----------------+
| acctype        |
+----------------+
| Annual deposit |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> CALL acc_type(7);
+------------------+
| acctype          |
+------------------+
| 3 months deposit |
+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

3. WHILE:

Процедурите в MySQL добиват още по-голяма сила с наличието на цикли. Първият и може би най-популярен е WHILE:

WHILE <условие>
DO
   <заявки>;
END WHILE;

Например следната процедура ще изведе сумите по акаунти на всички клиенти в зададен интервал (x,y):

mysql> DELIMITER |

mysql> CREATE PROCEDURE check_clients(IN x INT, IN y INT)
       BEGIN

              DECLARE iterator INT;

              SET iterator = x;

              WHILE (iterator >= x AND iterator <= y)
              DO
                     SELECT id, amount
                     FROM accounts
                     WHERE id = iterator;

                     SET iterator = iterator + 1;
              END WHILE;

       END
       |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL check_clients(5,9);

+----+--------+
| id | amount |
+----+--------+
|  5 | 191.98 |
+----+--------+
1 row in set (0.00 sec)

+----+---------+
| id | amount  |
+----+---------+
|  6 | 1220.00 |
+----+---------+
1 row in set (0.00 sec)

+----+--------+
| id | amount |
+----+--------+
|  7 | 133.48 |
+----+--------+
1 row in set (0.00 sec)

+----+--------+
| id | amount |
+----+--------+
|  8 | 256.41 |
+----+--------+
1 row in set (0.00 sec)

+----+---------+
| id | amount  |
+----+---------+
|  9 | 1331.50 |
+----+---------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Разбира се в този си вариант процедурата никак не е удобна за работа - връща поредица от резултатни таблици, вместо една. Решението на този проблем е да използваме временна таблица по следния начин:

DELIMITER |
CREATE PROCEDURE check_clients(IN x INT, IN y INT)
BEGIN

   DECLARE iterator INT;

   SET iterator = x;

   CREATE TEMPORARY TABLE tmp_tbl(
      id INT NOT NULL,
      amount DECIMAL( 9,2 ) NOT NULL
   )ENGINE=Memory;

   WHILE (iterator >= x AND iterator <= y)
   DO
     INSERT INTO tmp_tbl(id, amount)
     SELECT id, amount FROM accounts 
     WHERE id = iterator;

     SET iterator = iterator + 1;
   END WHILE;

   SELECT * FROM tmp_tbl;
   DROP TABLE tmp_tbl;
END
|
DELIMITER ;

mysql> CALL check_clients(2,6);
+----+---------+
| id | amount  |
+----+---------+
|  2 |   36.22 |
|  3 |   42.98 |
|  4 | 1236.33 |
|  5 |  211.98 |
|  6 | 1200.00 |
+----+---------+
5 rows in set (0.01 sec)

Query OK, 0 rows affected (0.05 sec)

Имайте предвид, че временните таблици са "локални", т.е. всяка нишка ще създава нова таблица независимо, че имената може да са едни и същи. Освен това е редно да се отбележи, че заявки подобни на горната не са ефективни, защото реално цялата работа може да се извърши с един SELECT оператор (WHERE id BETWEEN x AND y). Реално вие компилирате и изпълнявате y-x на брой заявки вместо една единствена - подобно решение е ужасно откъм бързодействие. Частично подобрение може да се намери чрез употребата на т.нар. "курсори" (ще бъдат разгледани по-нататък), но дори то ще е по-лошо от решението с една заявка.

4. REPEAT-UNTIL:

Цикълът е с абсолютно същото действие както WHILE, с изключение, че ако условието е погрешно в самото начало, то въпреки това тялото на цикъла ще се изпълни поне веднъж:

REPEAT
   <заявки>;
UNTIL <условие>;
END REPEAT;

Няма да го разглеждаме подробно с пример, защото смятаме, че е тривиално.

5. LOOP

LOOP е "безкраен" цикъл. На него му се поставя етикети (label) и се използват командите ITERATE и LEAVE съответно за ново завъртане на цикъла или приключване на цикъла.

label1: LOOP
   ...
   ITERATE label1;
   ...
   LEAVE label1;
   ...
END LOOP label1;

Тези етикети за удобство могат да се поставят и на другите два вида цикли.

Задача: Преработете процедурата за прехвърляне на пари от един акаунт в друг като направите така, че ако първият акаунт няма достатъчно пари, то да се изведе съобщение за грешка.

Решение:

DELIMITER |
CREATE PROCEDURE transfer_money(IN acc_out INT, IN acc_in INT, IN money DOUBLE)
BEGIN
 START TRANSACTION;

 UPDATE accounts
 SET amount = amount - money
 WHERE id = acc_out
         AND 
       amount >= money;

 IF(ROW_COUNT()=0)
 THEN 
    ROLLBACK;
    SELECT "Not enough money in giving acct" AS result;
 ELSE
    UPDATE accounts
    SET amount = amount + money
    WHERE id = acc_in;

    IF(ROW_COUNT()=0)
    THEN 
       ROLLBACK;
       SELECT "Taking account does not exist" AS result;
    ELSE
       COMMIT;
       SELECT "Success" AS result;
    END IF;
 END IF;
END
|
DELIMITER ;