C, PHP, VB, .NET » Процедури и входни параметри
* Процедури и входни параметри
Публикувано на 18 април 2009 в раздел Бази от Данни.
Процедурите ни дават възможност да създаваме скриптове за извършване на типизирани заявки с различни входни данни. Нека демонстрираме една елементарна процедура, която извиква обикновена заявка SELECT:
mysql> CREATE PROCEDURE show_customers()
->  SELECT * FROM customers;
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 |
Обърнете внимание на командата „DELIMITER“. Чрез първото ѝ изпълнение указваме, че края на заявка вече няма да става с „;“, а с „|“. Това е нужно, защото при създаването на процедурата трябва да използваме символа „;“ при края на заявката SELECT. DELIMITER може да бъде всяка комбинация от символи. Можем да създаваме процедури за изпълнение на повече от една заявка наведнъж. Освен това една процедура може да изпълнява различни заявки, в зависимост от подадени към нея входни данни. За целта на помощ идват т.нар. параметри (parameters). Нека демонстрираме как се дефинира променлива (параметър) в MySQL с елементарен пример от базата от данни banks:
mysql> USE banks;
mysql> SET @cust_name = 'Ivan Ivanov';
mysql> SELECT * FROM CUSTOMERS
WHERE name = @cust_name;
| id | name        | address | bank_mgr |
|  7 | Ivan Ivanov | NULL    |        4 |
Казахме, че има аналогия с променливи в потребителски сесии – това е така, защото тази променлива е валидна само за текущата връзка. При приключване на връзката тя ще бъде унищожена. Процедурите могат да достъпват параметри по три различни начина:
1. IN: Предава параметрите по стойност
mysql> CREATE PROCEDURE proc_in(IN var VARCHAR(255))
SET @cust_name = var;
mysql> CALL proc_in('Atanas Petrov');
mysql> SELECT * FROM customers WHERE name = @cust_name;
+----+---------------+---------+----------+
| id | name          | address | bank_mgr |
|  6 | Atanas Petrov | NULL    |        4 |
Виждаме как към процедурата подадохме параметър и чрез него променихме стойността на променливата, която беше дефинирана глобално. Трябва да знаете, че ако към IN параметър на процедура подадете параметър вместо конкретна стойност и го промените вътре в процедурата, то този параметър ще се върне в първоначалното си състояние след приключване на процедурата. Можете да си направите аналогия с предаването на параметър към функция по стойност от програмирането на C++.
2. OUT: Не предава параметри – каквато и стойност да се предаде, тя ще се приеме за NULL. След приключване на процедурата обаче в този входен параметър има записана стойност в глобална сесийна променлива.
mysql> CREATE PROCEDURE proc_out(OUT var VARCHAR(255))
->    SET var = 'Todor Ivanov';
mysql> CALL proc_out(@cust_name);
+----+--------------+---------+----------+
| id | name         | address | bank_mgr |
|  1 | Todor Ivanov | NULL    |        1 |
Тук подадохме променлива като входен параметър на процедурата и ѝ зададохме стойност вътре във функцията. Виждате, че след приключването на процедурата глобалния параметър остана променен – това нямаше да стане, ако го бяхме направили с IN. Можете да си направите аналогия с подаването на параметър към функция като псевдоним в програмирането на C++. Отново подчертаваме, че каквато и стойност да е имал глобалния параметър преди извикването на функцията, той е пропуснат от процедурата и тя го е приела за NULL.
3. INOUT: Предава параметри „по адрес“, т.е. ако ги промените вътре във функцията, ще останат променени и глобално. Ако подадете несъществуващ параметър, той ще се приеме за стойност NULL (т.е. ще действа точно както при OUT).
mysql> SET @newvar = 'Petko Stoianov';
mysql> CREATE PROCEDURE proc_inout(INOUT var VARCHAR(255))
->    SET var = @cust_name;
mysql> CALL proc_inout(@newvar);
mysql> SELECT * FROM customers WHERE name = @newvar;
Виждаме, че променливата @newvar беше със стойност ‘Petko Stoianov’, но след изпълнението на процедурата я променихме със стойността на @cust_name (‘Todor Ivanov’).
Сега вече можем да създаваме и по-смислени процедури. Нека дадем един пример – процедура, която премества пари от един акаунт в друг. В случая ще използваме IN входен параметър (в процедурата не променяме стойностите на параметри):
mysql> CREATE PROCEDURE transfer_money(acc_in INT, acc_out INT, money DOUBLE)
WHERE id = acc_out;
Ето как можем да преместим 20 лева от акаунт номер 5 в акаунт номер 6:
mysql> SELECT id, amount
WHERE id = 5 OR id = 6;
mysql> CALL transfer_money(6, 5, 20);
|  5 |  191.98 |
В следващата статия ще покажем как можем да дефинираме и използваме променливи вътре в процедура.
14 август 2011 в 12:10
При процедурата за трансфер на пари няма ли да се спести писане, ако използваме транзакция вместо процедура.
14 август 2011 в 18:06
Да, но в случая ние правим процедурите с друга цел. По същия начин в програмирането можем директно да напишем кода на една функция в тялото на програмата, но въпреки това я отделяме като самостоятелна отделна функция.
Деан Христов каза:
08 юни 2013 в 22:22
Благодаря ти за този урок.
08 март 2014 в 20:50
При 1. IN направих втори параметър
@cust_name2 = ‘Atanas Petrov’;
CALL proc_in(@cust_name2);
Защо при когато махна изпълнението на процедурата(въобще цялата процедура) и отново извикам същата заявка резултата е отново същия – за Atanas Petrov. Нали при IN параметъра се връща в първоначално състояние т.е трябва да ми излезе резултата на Ivan Ivanov?
Или става така, защото заявките се пазят кеширани? Ако е така как мога да видя само стойността на @cust_name просто за проверка?
09 март 2014 в 19:36
В тази статия съм описал нещата малко лошо.
IN входните параметри предават по стойност
@ параметрите са „глобални“ за текущата сесия – наричат се сесийни променливи.
Ако промениш @cust_name, независимо къде, тя е променена за текущата сесия във всички последващи извиквания на заявки или процедури. Ако затвориш връзката към базата от данни, сесията се затваря и съответно променливата при следващото извикване първоначално ще е празна.
Конкретно за твоя въпрос отговора е – защото използваш една и съща връзка към базата и не си си „reset“-нал сесията.
12 март 2014 в 17:43
10 юни 2014 в 16:35
Не намирам темата за „съхранени процедури“. Ще ни трябва ли за второто контролно?