Съхранените процедури представляват съвкупност от SQL заявки, съхранени в специален каталог в базата данни и много наподобяват методите(функциите) в езиците за програмиране. Често се налага да се пазят заявки, които ще се ползват често, но с различни параметри (динамични), като например отчети, справки, проверки и тн. Друг типичен случай е, когато се налага да правим зареждане на данни от едно място на друго, или когато трябва да изпълним последователност от операции за приключване на отчетен период, например. Тогава удобно решение е да имаме процедури в базата, съдържащи необходимото множество от заявки, които да бъдат извиквани от някаква програма или от самия сървър в определено време. Писането на съхранени процедури може да увеличи производителността на едно приложение. Веднъж написани, процедурите в MySQL се компилират всеки път по време на извикването им и компилираната им версия се пази в кеша на сървъра за текущата отделна връзка (сесия). Съществено предимство на работата с процедури е, че те намаляват значително трафика на данни между приложението и сървъра на СУБД. Вместо да се изпращат множество заявки към сървъра, той да връща отговори на всяка една от тях, програмата да проверява или валидира тези отговори и пак да върне заявка към сървъра, то всичко това би могло да се случи само с извикването на името на процедурата и предаването на определени параметри, с които тя да си свърши работата.

Синтаксис:

DELIMITER <symbol> -> Сменя знака за край на заявка.

CREATE PROCEDURE <name of procedure>( ) -> декларира процедура с име<име>

BEGIN 

<QUERIES> -> заявки, които ще се изпълняват при викане на процедурата

…

END 

<Symbol> -> край на процедурата

DELIMITER ; -> Сменя символа за край на завка да бъде “;“.

Когато променяме знака за край на заявка чрез DELIMITER, можем да подберем всякаква комбинация от символи, която да служи за край на заявка. Целта на смяната на знака за край е, че ако искаме MySQL да интерпретира нашата заявка за създаване на процедура като едно цяло, вътре в което да има повече от една заявка (нормално завършваща с ;), трябва да сменяме знака за край докато пишем процедурата. Когато сме готови с писането на процедурата, сменяме обратно знака на „;“.

Пример: Нека да създадем процедура, която да показва всички групи, заедно със спортовете, по които са те:

delimiter |
create procedure getAllSportGroupsWithSports()
begin
SELECT sg.location as locationOfGroup,
sg.dayOfWeek as trainingDay,
sg.hourOfTraining as trainingHour,
sp.name as sportName
FROM sportgroups as sg JOIN sports as sp
ON sg.sport_id = sp.id;
end
|
delimiter ;

За да извикаме процедурата, използваме CALL.

CALL getAllSportGroupsWithSports();

Това ще даде следния резултат:

 

Това е добре, но така можем да пишем само статични заявки. Добре е да може да предаваме параметри към тези процедури, с които да променяме динамично условията. MySQL предоставя възможност за предаване на параметри на процедура. Нека обаче първо да разгледаме променливите, след което ще обърнем внимание и на параметрите.

Променливи:

Променливите служат за временно съхранение на стойности. Всеки потребител може да си дефинира променливи(user-defined variables). Това става като променливата се декларира с @ отпред. Тогава тя има смисъл на сесийна променлива. Това означава, че тя е валидна само в рамките на текущата сесия на текущия потребител и след приключване на сесията се изтрива. Тези променливи могат да бъдат от ограничен брой типове- integer, decimal, floating-point, binary или nonbinary string, NULL.  Ако променливата е резултат от Select, то тя се връща като стринг.

Синтаксис:

DECLARE @<име на променлива>  <тип на променлива>  – представлява деклариране на променлива с име и определен тип.

За инициализация се използва SET , но може и директно да създадете променливата по този начин(без преди това да сте я декларирали):

SET @<име на променлива> = <стойност>;

set @customer_number = 'A454647';
select @customer_number;

SET @coach_name = 'Иван Тодоров Петров';
SELECT *
FROM coaches
WHERE name = @coach_name;

Параметри на процедури в MySQL:

В зависимост от това за какво ни трябва даден параметър, той може да бъде предаден на процедура по три различни начина:

1. IN- параметри . Предаване на параметри ПО СТОЙНОСТ. Предаваме параметър, подобно на предаването на аргументи по стойност на методите в езиците за програмиране. Ще създадем глобална променлива и ще и присвоим стойността на параметъра, предаден на процедурата. След това ще извикаме процедурата с подаден параметър. Ще използваме глобалната променлива като параметър на динамична заявка.

delimiter |
create procedure inParamProc(IN nameParam VARCHAR(255))
begin
SET @coachName = nameParam;
end;
|
delimiter ;
call inParamProc('Иван Тодоров Петров');
SELECT * from coaches
WHERE name = @coachName;

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

delimiter |
create procedure inParamProcWithChangedParam(IN nameParam VARCHAR(255))
begin
SET nameParam = 'Ivan Petkov';
end;
|
delimiter ;

SET @testCoachName = 'Иван Тодоров Петров';
call inParamProcWithChangedParam(@testCoachName);
SELECT  @testCoachName;

2. OUT– параметри – не се предава реална стойност, т.е. каквато и стойност да има записана в предавания параметър, тя се приема за NULL на входа на процедурата. Използват се, за да им се задават стойности вътре в процедурата и те да се ползват евентуално извън нея. Тоест, след приключване на процедурата, в OUT параметрите ще има записана стойност. Използват се често, за да може процедурата да върне резултат по този начин.

 

delimiter |
create procedure outParamProc(OUT nameParam VARCHAR(255))
begin
SELECT nameParam; #only for test
SET nameParam = 'Иван Тодоров Петров';
end;
|
delimiter ;
SET @testOutParam = 'Some name';
call outParamProc(@testOutParam);

Тук умишлено правим select на подадения параметър, за да сме сигурни, че той се приема за NULL на входа на процедурата. След извикването резултатът е:

Ако след това изпълним заявката:

SELECT @testOutParam;

Щя видим и присвоената и стойност след като процедурата е извикана:

На практика няма значение каква ще бъде подадената стойност на OUT- параметъра, тъй като, както виждате, тя се игнорира от процедурата. Целта на използването на такива параметри е единствено да им се присвои стойност по време на изпълнение на процедурата и те да „излязат“ от нея вече инициализирани. OUT- параметрите в базите данни много приличат на предаването на аргумент като псевдоним (по референция) в езиците за програмиране.

3. INOUT- параметри. Комбинират горните два типа параметри. Ако такъв параметър бъде променен вътре в процедурата, то той ще остане променен и глобално, както е при OUT. Ако се подаде параметър с определена стойност, то той се приема заедно със стойността си, както е при IN:

delimiter |
create procedure inoutParamProc(INOUT nameParam VARCHAR(255))
begin
SELECT nameParam; #only for test
SET nameParam = 'Иван Тодоров Петров';
end;
|
delimiter ;

SET @testinOutParam = 'Some name';
call inoutParamProc(@testinOutParam);

Това беше резултатът от select, който е вътре, преди реинициализирането на параметъра. След като го изведем:

SELECT @testinOutParam;

Резултатът е:

И при OUT, и при INOUT, подаването на несъществуващи параметри не е разрешено, тъй като смисъла им не е такъв. Трябва да подавате истински променливи. Тоест следната заявка ще даде грешка:

call inoutParamProc('Ivan Petrov');

Всички процедури, които сме създали вече, може да видите в менюто Strored procedures в MySQL Workbench:

Локални променливи.

В рамките на една съхранена процедура може да дефинирате локални променливи. Те са аналог на локалните променливи в методите при езиците за програмиране. Обхватът им е само в рамките на процедурата. След приключване на изпълнението на процедурата, те се трият. Достъпни са само в нея. Локална променлива се декларира по следния начин:

DECLARE  name  varchar(100);

За да инициализирате локална променлива, може да ползвате команда SET:

SET name = ‘Ivan Petrov’;

Друг вариант е да инициализирате променливата с резултат от SELECT заявка. Синтаксисът е SELECT …INTO..:

SELECT coaches.name INTO name 
FROM coaches WHERE id = 1;

Внимавайте с подаването на NULL стойности. Както при всички заявки, така и тук, всяко сравнение на нещо с NULL дава резултат лъжа.
Ако искате да принтирате съобщение по време на изпълнение на процедурата, може да напишете SELECT. Например:

SELECT ‘SUCCESS’; или SELECT 1;

Локалните променливи се декларират в рамките на процедура, не го забравяйте. В следващата статия ще покажем и повече примери на употреба.

Даниел Джолев