C, PHP, VB, .NET

Процедурите ни дават възможност да създаваме скриптове за извършване на типизирани заявки с различни входни данни. Нека демонстрираме една елементарна процедура, която извиква обикновена заявка 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:

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++.

3. INOUT:

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’). Разликата между OUT и INOUT параметрите е, че при INOUT параметъра трябва да бъде дефиниран глобално преди извикването на функцията – при OUT не е така (ако не съществува ще бъде създаден).

Сега вече можем да създаваме и по-смислени процедури. Нека дадем един пример – процедура, която премества пари от един акаунт в друг. В случая ще използваме 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)

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