C, PHP, VB, .NET

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


* Процедури и входни параметри

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

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

mysql> DELIMITER |

mysql> CREATE PROCEDURE show_customers()
    -> BEGIN
    ->  SELECT * FROM customers;
    -> END
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL show_customers();
+----+-------------------+---------+----------+
| id | name              | address | bank_mgr |
+----+-------------------+---------+----------+
|  1 | Todor Ivanov      | NULL    |        1 |
|  2 | Petko Stoianov    | NULL    |        1 |
|  3 | Neno Nenov        | NULL    |     NULL |
|  4 | Mariana Zaharieva | NULL    |        3 |
|  5 | Elica Zaharieva   | NULL    |        3 |
|  6 | Atanas Petrov     | NULL    |        4 |
|  7 | Ivan Ivanov       | NULL    |        4 |
|  8 | Zlatomir Petrov   | NULL    |        4 |
|  9 | Mihail Ivchev     | NULL    |        5 |
| 11 | Ivailo Ivanov     | NULL    |        7 |
| 12 | George Lucas      | NULL    |     NULL |
| 13 | George Harison    | NULL    |     NULL |
| 14 | Michael Jackson   | NULL    |     NULL |
| 15 | Tony Martin       | NULL    |     NULL |
| 16 | Tony McCarter     | NULL    |     NULL |
| 17 | Alexander Smith   | NULL    |       11 |
| 18 | Maria Smith       | NULL    |       11 |
| 19 | Alain Delrick     | NULL    |       12 |
| 20 | Devry Henry       | NULL    |       12 |
| 21 | Lenard Renne      | NULL    |       12 |
| 22 | Fontaine Rupert   | NULL    |       13 |
+----+-------------------+---------+----------+
21 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Обърнете внимание на командата "DELIMITER". Чрез първото ѝ изпълнение указваме, че края на заявка вече няма да става с ";", а с "|". Това е нужно, защото при създаването на процедурата трябва да използваме символа ";" при края на заявката SELECT. DELIMITER може да бъде всяка комбинация от символи. Можем да създаваме процедури за изпълнение на повече от една заявка наведнъж. Освен това една процедура може да изпълнява различни заявки, в зависимост от подадени към нея входни данни. За целта на помощ идват т.нар. параметри (parameters). Нека демонстрираме как се дефинира променлива (параметър) в MySQL с елементарен пример от базата от данни banks:

mysql> USE banks;
Database changed

mysql> SET @cust_name = 'Ivan Ivanov';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM CUSTOMERS
       WHERE name = @cust_name;
+----+-------------+---------+----------+
| id | name        | address | bank_mgr |
+----+-------------+---------+----------+
|  7 | Ivan Ivanov | NULL    |        4 |
+----+-------------+---------+----------+
1 row in set (0.05 sec)

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

1. IN: Предава параметрите по стойност

mysql> DELIMITER |

mysql> CREATE PROCEDURE proc_in(IN var VARCHAR(255))
BEGIN
   SET @cust_name = var;
END
|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL proc_in('Atanas Petrov');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM customers WHERE name = @cust_name;
+----+---------------+---------+----------+
| id | name          | address | bank_mgr |
+----+---------------+---------+----------+
|  6 | Atanas Petrov | NULL    |        4 |
+----+---------------+---------+----------+
1 row in set (0.02 sec)

Виждаме как към процедурата подадохме параметър и чрез него променихме стойността на променливата, която беше дефинирана глобално. Трябва да знаете, че ако към IN параметър на процедура подадете параметър вместо конкретна стойност и го промените вътре в процедурата, то този параметър ще се върне в първоначалното си състояние след приключване на процедурата. Можете да си направите аналогия с предаването на параметър към функция по стойност от програмирането на C++.

2. OUT: Не предава параметри - каквато и стойност да се предаде, тя ще се приеме за NULL. След приключване на процедурата обаче в този входен параметър има записана стойност в глобална сесийна променлива.

mysql> DELIMITER |

mysql> CREATE PROCEDURE proc_out(OUT var VARCHAR(255))
    -> BEGIN
    ->    SET var = 'Todor Ivanov';
    -> END
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL proc_out(@cust_name);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM customers WHERE name = @cust_name;
+----+--------------+---------+----------+
| id | name         | address | bank_mgr |
+----+--------------+---------+----------+
|  1 | Todor Ivanov | NULL    |        1 |
+----+--------------+---------+----------+
1 row in set (0.00 sec)

Тук подадохме променлива като входен параметър на процедурата и ѝ зададохме стойност вътре във функцията. Виждате, че след приключването на процедурата глобалния параметър остана променен - това нямаше да стане, ако го бяхме направили с IN. Можете да си направите аналогия с подаването на параметър към функция като псевдоним в програмирането на C++. Отново подчертаваме, че каквато и стойност да е имал глобалния параметър преди извикването на функцията, той е пропуснат от процедурата и тя го е приела за NULL.

3. INOUT: Предава параметри "по адрес", т.е. ако ги промените вътре във функцията, ще останат променени и глобално. Ако подадете несъществуващ параметър, той ще се приеме за стойност NULL (т.е. ще действа точно както при OUT).

mysql> SET @newvar = 'Petko Stoianov';
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER |
mysql> CREATE PROCEDURE proc_inout(INOUT var VARCHAR(255))
    -> BEGIN
    ->    SET var = @cust_name;
    -> END
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL proc_inout(@newvar);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM customers WHERE name = @newvar;
+----+--------------+---------+----------+
| id | name         | address | bank_mgr |
+----+--------------+---------+----------+
|  1 | Todor Ivanov | NULL    |        1 |
+----+--------------+---------+----------+
1 row in set (0.00 sec)

Виждаме, че променливата @newvar беше със стойност ‘Petko Stoianov’, но след изпълнението на процедурата я променихме със стойността на @cust_name (‘Todor Ivanov’).

Сега вече можем да създаваме и по-смислени процедури. Нека дадем един пример - процедура, която премества пари от един акаунт в друг. В случая ще използваме IN входен параметър (в процедурата не променяме стойностите на параметри):

mysql> USE banks;
Database changed

mysql> DELIMITER |

mysql> CREATE PROCEDURE transfer_money(acc_in INT, acc_out INT, money DOUBLE)
       BEGIN
              START TRANSACTION;

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

              UPDATE accounts
              SET amount = amount + money
              WHERE id = acc_in;

              COMMIT;

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

mysql> DELIMITER ;

Ето как можем да преместим 20 лева от акаунт номер 5 в акаунт номер 6:

mysql> SELECT id, amount
       FROM accounts
       WHERE id = 5 OR id = 6;
+----+---------+
| id | amount  |
+----+---------+
|  5 |  211.98 |
|  6 | 1200.00 |
+----+---------+
2 rows in set (0.00 sec)

mysql> CALL transfer_money(6, 5, 20);
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT id, amount
       FROM accounts
       WHERE id = 5 OR id = 6;
+----+---------+
| id | amount  |
+----+---------+
|  5 |  191.98 |
|  6 | 1220.00 |
+----+---------+
2 rows in set (0.00 sec)

В следващата статия ще покажем как можем да дефинираме и използваме променливи вътре в процедура.

 



7 коментара


  1. При процедурата за трансфер на пари няма ли да се спести писане, ако използваме транзакция вместо процедура.

  2. Да, но в случая ние правим процедурите с друга цел. По същия начин в програмирането можем директно да напишем кода на една функция в тялото на програмата, но въпреки това я отделяме като самостоятелна отделна функция.

  3. При 1. IN направих втори параметър
    @cust_name2 = 'Atanas Petrov';
    ...
    CALL proc_in(@cust_name2);
    Защо при когато махна изпълнението на процедурата(въобще цялата процедура) и отново извикам същата заявка резултата е отново същия - за Atanas Petrov. Нали при IN параметъра се връща в първоначално състояние т.е трябва да ми излезе резултата на Ivan Ivanov?
    Или става така, защото заявките се пазят кеширани? Ако е така как мога да видя само стойността на @cust_name просто за проверка?

  4. В тази статия съм описал нещата малко лошо.

    IN входните параметри предават по стойност

    @ параметрите са "глобални" за текущата сесия - наричат се сесийни променливи.

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

    Конкретно за твоя въпрос отговора е - защото използваш една и съща връзка към базата и не си си "reset"-нал сесията.

  5. Не намирам темата за "съхранени процедури". Ще ни трябва ли за второто контролно?

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

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


*