Студенту >> Базы данных (MySQL)


Хранимые процедуры MySQL(Часть 1)

Что же представляют из себя хранимые процедуры?

Начиная с версии MySQL 5.0 появилась возможность использования хранимых процедур. По простому хранимая процедура это процедура хранящееся в базе данных на сервере.

Чем полезные хранимые процедуры?                                                 

  • Они запускают во всех средах, и нет необходимости перестроения логики. С того момента как вы создали хранимую процедуру, не важно какое приложение вы используете для вызова процедуры. Также не важно на каком языке вы программируете, логика процедуры содержится на сервере БД.
  • Они могут сократить сетевой трафик. Сложные, повторяющиеся задачи можно обрабатывать с помощью процедур на сервере Баз данных, без необходимости отсылки промежуточных результатов приложению.

Простой пример

Эта процедура просто выведет словосочетание «Привет мир» на англ. – «Hello world»

 mysql> create procedure  hello() select "Hello world"
А теперь вызовем ее:
Пример Hello world
Параметры

Значительное преимущество хранимых процедур в том, что они могут как принимать, так и передавать значения.

Существует три типа параметров:
  • IN: По умолчанию. Этот параметр принимается процедурой и может изменяться внутри процедуру, но не может изменятся вне процедуры.
  • OUT: Никакие значения не передаются (это означает что они имеют значение NULL), но они могут быть изменены внутри процедуры, а также изменены вне процедуры.
  • INOUT: Совмещает в себе свойства параметров IN и OUT. Значение может передаваться в процедуру, изменяться внутри процедуры и быть доступным вне процедуры.

Пример IN

Данный пример хранимый процедуры демонстрирует использование IN параметра. Так как параметр IN используется по умолчанию, его можно не обязательно писать.

mysql> CREATE PROCEDURE sp_in(p VARCHAR(11)) SET @x = P;
mysql> CALL sp_in('Hello world');
mysql> SELECT @x 

Хранимая процедура с параметром IN

Пример OUT

mysql> SET @x=”Something”;
mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P=”Hello world”;
mysql> CALL sp_out(@x);
mysql> SELECT @x 

Хранимая процедура с параметром OUT
Пример INOUT

mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
mysql> set @a = 5;
mysql> CALL sp_inout(@a);
mysql> SELECT @x

Хранимая процедура с параметром INOUT

Просмотр информации о созданных хранимых процедурах

SHOW PROCEDURE STATUS – возвращает список хранимых процедур, с некоторыми данными о них.
SHOW CREATE PROCEDURE – возвращает код конкретной процедуры.

Разделители и процедуры содержащие несколько операторов

Хранимые процедуры, конечно, не очень полезные если они содержат одну инструкцию. Как же нам отличить инструкцию внутри процедуры от конца этой процедуры. Мы можем создать другой разделитель инструкции для конца оператора CREATE PROCEDURE.

Пример:
mysql>  DELIMITER |    

Обратите внимание на то, что нет точки с запятой после символа «|», которую мы будем использовать в процедуре. Вы должны выбирать разделитель, который не будет использоваться в вашей процедуре, и он может быть больше чем один символ.

Пример:
  mysql> create table sp_inst(id int primary key auto_increment,  
  	Length_str int, txt varchar(10)); 
  mysql> delimiter |
mysql> create procedure sp_ins(p varchar(10)) -> Begin -> set @x = char_length(p); -> insert into sp_ins(Length_str, txt) values (@x, p); -> end| mysql> delimiter ; mysql> call sp_ins("test332"); mysql> call sp_ins("test"); mysql> select * from sp_ins;

Когда мы вызвали процедуру, MySQL все еще использовала в качестве разделителя «|», но не точку с запятой, по этому операторы не запускались после точки с запятой.

Переменные процедуры

В хранимых процедурах также можно определить локальные переменные оператором DECLARE.

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
   -> BEGIN
   ->  DECLARE x INT;
    ->  DECLARE y INT DEFAULT 10;
    ->  SET x = P*y;
    ->  select x;
    -> END|
mysql> DELIMITER ; 

Заполнение переменных из существующих таблиц

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y VARCHAR(10);
    ->  SELECT id,txt INTO x,y FROM sp_ins LIMIT 1;
    ->  SELECT x,y;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sp_select()\G
НАВЕРХ