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), които са много подобни на променливите в програмирането. Още по-добра аналогия може да се направи със потребителските сесии при програмиране в интернет (session variables). Нека демонстрираме параметрите в 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 AND amount >= money;

              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)

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