C, PHP, VB, .NET

Дневникът на Филип Петров


* Логически оператори и цикли

Публикувано на 19 април 2009 в раздел Бази от Данни.

Чрез процедурите 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 ;

 



9 коментара


  1. Тодор каза:

    При написаната по-горе процедура check_clients:

    mysql> CALL check_clients(5,6);

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

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

    Процедурата връща 2 „сет“-а с по 1 резултат;
    Въпроси:
    1) Възможно ли е в MySql да се върнат 2-та резултата като 1 сет? Например:
    +–-+–––+
    | id | amount |
    +–-+–––+
    | 5 | 191.98 |
    +–-+–––+
    | 6 | 1220.0 |
    +–-+–––+

    2) Възможно ли е по някакъв начин да се извика процедурата и в същото време да се направи нещо по колекцията, която е върнала?
    Например:

    select *
    from check_clients(5,6)
    where (some condition)

  2. 1) Да, разбира се. Ето например как може да стане с използването на временна таблица (temporary table):

    DELIMITER |
    
    CREATE PROCEDURE check_clients(IN x INT, IN y INT)
    BEGIN
    	DECLARE iterator INT;
    	SET iterator = x;
    
    	CREATE TEMPORARY TABLE result_table (
    		id INT(11),
    		amount decimal(9,2)
    	);
    
    	WHILE (iterator >= x AND iterator <= y)
            DO
    		INSERT INTO result_table(id, amount)
    		SELECT id, amount FROM accounts WHERE id = iterator;
    
                    SET iterator = iterator + 1;
    	END WHILE;
    
    	SELECT * FROM result_table;
    	DROP TABLE result_table;
    END
    |
    
    DELIMITER ;

    Ще добавя примера в статията.

    2) Не, не можеш да изпълняваш заявка върху resultset от съхранена процедура. Вместо това можеш да накараш процедурата да съхранява информацията във временна таблица, вместо да връща resultset. Така след изпълнението на процедурата можеш да изпълниш заявката върху тази временна таблица и накрая да я изтриеш.

  3. Тодор каза:

    Ясно. Мерси :)

  4. Kiril каза:

    Има ли възможност в една процедура да се извиква друга ?

  5. Димитър каза:

    Доколкото разбрах в последния пример функцията ROW_COUNT() връща броя на обновените, попълнени или изтрити редове, нали така?
    Ако е така и върнатия резултат е 0 защо ни е ROLLBACK? Или просто като предпазна мярка?

  6. Така се приключва трансакцията. Ако например сме взели пари от първия акаунт, но втория не съществува и върне 0? Тогава трябва да обърнем нещата и да върнем парите обратно в първия акаунт.

  7. Заявка, която завърши успешно, та било то и с „0 засегнати реда“, не е грешка. Автоматичен rollback се прави само при error.

  8. Rosen каза:

    Ако не използваме ROLLBACK няма ли все пак при изпълнение на 1-та заявка и при неизпълнение на 2-та нищо да не се промени.Нали за това е трансакция, ако не се изпълни едната, то нищо не се изпълнява?

Добави коментар

Адресът на електронната поща няма да се публикува


*