SQL-Ex blog

Процедурный код может содержать широкий ассортимент операторов, включая язык определения данных (DDL) и язык манипуляции данными (DML). Хранимые процедуры также поддерживают использование входных и выходных параметров, делая их исключительно гибким инструментом для инкапсуляции логики операторов.

Хранимые процедуры дают возможность повторно использовать код, когда это необходимо, помогая упростить разработку приложений и уменьшить ошибки в операторах. Разработчикам не придется писать сложные запросы по каждому требованию приложения, и команде QA не потребуется тратить много времени на проверку запросов при тестировании приложений.

Возможность повторно использовать код сокращает также сетевой трафик, поскольку хранимая процедура может вызываться с помощью единственного оператора CALL, и не важно насколько сложен запрос, заложенный в процедуру. Хранимые процедуры могут также обеспечить более высокую степень безопасности, скрывая структуру базы данных и удаляя ad hoc запросы на уровне приложений.

В этой статье я продемонстрирую как создавать и обновлять хранимые процедуры, а также как вызывать их с помощью оператора CALL. Вы узнаете как построить простые и параметризованные процедуры, которые используют входные и выходные параметры. Как и в предыдущих статьях этой серии, я использую редакцию MySQL Community на компьютере с ОС Windows для построения примеров, которые я создавал в MySQL Workbench, графическим интерфейсом пользователя (GUI), идущим вместе с MySQL Community.

Подготовка среды MySQL

Примеры в этой статье используют базу данных travel, которая уже использовалась для предыдущей статьи о представлениях MySQL. Здесь используются те же таблицы и данные для демонстрации работы с хранимыми процедурами. Если вы делали примеры из предыдущей статьи, у вас уже может быть установлена база данных travel на экземпляре MySQL. Если нет, вы можете использовать следующий скрипт для создания базы данных с таблицами:

DROP DATABASE IF EXISTS travel;

CREATE DATABASE travel;

USE travel;

CREATE TABLE manufacturers (

  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  manufacturer VARCHAR(50) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=1001;

CREATE TABLE airplanes (

  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  plane VARCHAR(50) NOT NULL,

  manufacturer_id INT UNSIGNED NOT NULL,

  engine_type VARCHAR(50) NOT NULL,

  engine_count TINYINT NOT NULL,

  max_weight MEDIUMINT UNSIGNED NOT NULL,

  wingspan DECIMAL(5,2) NOT NULL,

  plane_length DECIMAL(5,2) NOT NULL,

  parking_area INT GENERATED ALWAYS AS 

              ((wingspan * plane_length)) STORED,

  icao_code CHAR(4) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (plane_id),

  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 

    REFERENCES manufacturers (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=101;

Таблица airplanes содержит внешний ключ, который ссылается на таблицу manufacturers, поэтому следует создавать таблицы в указанном порядке. После создания таблиц вы можете добавить некоторые примерные данные, чтобы вы могли протестировать вашу хранимую процедуру. Для заполнения таблицы выполните следующий оператор INSERT:

INSERT INTO manufacturers (manufacturer)

VALUES ('Airbus'), ('Beechcraft'), ('Piper');

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    max_weight, wingspan, plane_length, icao_code)

VALUES 

  ('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),

  ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),

  ('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 117.45, 123.27, 'A320'),

  ('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 175.50, 'A30B'),

  ('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 2, 12500, 44.50, 46.00, 'PRM1'),

  ('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet', 2, 15780, 43.50, 48.42, 'BE40'),

  ('1900D', 1002, 'Turboprop', 2,17120,  57.75, 57.67, 'B190'),

  ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),

  ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29.60, 'P46T'),

  ('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');

Как и в случае с операторами CREATE TABLE, вы должны выполнять операторы INSERT в указанном порядке, чтобы не нарушалось ограничение внешнего ключа на таблице airplanes.

Создание хранимой процедуры MySQL

Для построения хранимой процедуры в MySQL вы должны использовать оператор CREATE PROCEDURE. Для начала откройте новое окно запроса в Workbench и проверьте, что активна требуемая база данных. (Чтобы активировать базу данных, выполните двойной щелчок на базе данных в навигаторе или выполните оператор USE.) Для этого примера вы будете использовать базу данных travel.

При построении оператора CREATE PROCEDURE вы должны дать имя процедуре и указать код SQL, который вы хотите хранить в базе данных. Код может включать единственный оператор SQL, такой как SELECT или UPDATE, или же это может быть составным оператором. Составной оператор - это оператор, который использует синтаксис BEGIN…END, ограничивающего блок одного или более операторов SQL. Блок может включать разнообразные элементы языка, включая операторы DDL и DML, объявления переменных, вложенные блоки или конструкции управления потоком, такие как циклы и условные операторы.

Большинство хранимых процедур используют составной оператор, даже если они включают только единственный оператор SQL. Например, код в следующем операторе CREATE PROCEDURE включает составной оператор с единственным оператором SELECT:

DELIMITER //

CREATE PROCEDURE get_plane_info()

BEGIN

  SELECT a.manufacturer_id, m.manufacturer, 

    COUNT(*) AS plane_count,

    ROUND(AVG(a.wingspan), 2) AS avg_span, 

    ROUND(AVG(a.plane_length), 2) AS avg_length

  FROM airplanes a INNER JOIN manufacturers m

    ON a.manufacturer_id = m.manufacturer_id

  GROUP BY a.manufacturer_id

  ORDER BY m.manufacturer;

END//

DELIMITER ;

Пример создает процедуру с именем get_plane_info. Обратите внимание на скобки после имени. Если бы операторы включали входные или выходные параметры, они должны определяться в скобках (это будет обсуждаться ниже). Если вы не включаете параметры, то все равно должны использовать скобки.

Составной оператор определяется синтаксисом BEGIN…END, который заключает единственный оператор SELECT. Сам оператор SELECT соединяет таблицы airplanes и manufacturers, группирует данные по столбцу manufacturer_id в таблице airplanes и вычисляет средние значения wingspan и plane_length для каждого производителя. Оператор также упорядочивает результаты по производителю и выводит для каждого общее число моделей самолетов. (Мы обсудим элементы этого оператора позже в этой серии статей).

Как можно увидеть, создание простой хранимой процедуры весьма несложный процесс. По минимуму вы должны задать имя и тело процедуры. Однако вы без сомнения заметили включение двух операторов DELIMITER, которые окружают определение процедуры.

По умолчанию MySQL использует точку с запятой (;) в качестве разделителя операторов. Это помогает гарантировать, что клиент посылает оператор на сервер целиком, не смешивая его с другими операторами. Однако составной оператор в хранимой процедуре может включать один или более разделителей в добавок к финальному разделителю определения, эти разделители могут вызвать путаницу при передаче оператора CREATE PROCEDURE с клиента на сервер.

Чтобы разрешить эту проблему, MySQL поддерживает использование оператора DELIMITER, который позволяет вам временно изменить разделитель для передачи всего определения процедуры на сервер как единого оператора. В примере выше первый оператор DELIMITER изменяет разделитель на двойной прямой слэш (//), а второй оператор DELIMITER изменяет разделитель обратно на точку с запятой. Временный разделитель затем используется в конце оператора CREATE PROCEDURE (после слова END), но сам оператор SELECT по-прежнему ограничивается разделителем в виде точки с запятой.

Я хочу также отметить, что MySQL Workbench предоставляет инструмент (в форме вкладки) для создания и редактирования хранимых процедур. Этот инструмент похож на тот, который использовался для создания и редактирования представлений. Он предлагает заглушку для построения оператора CREATE PROCEDURE, но предоставляет вам заполнить детали. На рис.1 показана вкладка Stored Procedure, когда она появляется при её первом открытии в Workbench.


Рис.1 Добавление хранимой процедуры с помощью Workbench GUI

Чтобы открыть вкладку Stored Procedure, выберите нужную базу данных в навигаторе, а затем щелкните кнопку создания хранимой процедуры на панели инструментов Workbench. (Кнопка имеет всплывающую подсказку Create a new stored procedure in the active schema in the connected server.) При появлении вкладки Stored Procedure вы можете начинать строить свой оператор. По завершению щелкните Apply. MySQL затем добавит несколько компонент оператора, которые необходимы для создания процедуры. Просмотрите окончательный скрипт, еще раз щелкните Apply, а затем - Finish. Хранимая процедура будет добавлена в соответствующую базу данных.

Вкладка Stored Procedure может оказаться полезной для создания и редактирования хранимой процедуры, поэтому я хочу, чтобы вы о ней знали. Однако я предпочитаю использовать вкладку запроса при работе с хранимой процедурой, поскольку мне кажется, что это проще и экономит время. Поэтому я для примеров в этой статье я буду использовать такой подход.

Проверка созданной новой процедуры

После выполнения оператора CREATE PROCEDURE вы можете проверить, что она была добавлена в базу данных travel, просмотром в навигаторе, как показано на рис.2. (Возможно потребуется обновить навигатор, чтобы увидеть появление новой процедуры.)


Рис.2 Наблюдение хранимой процедуры в навигаторе

Из навигатора вы можете открыть определение процедуры на вкладке Stored Procedure, щелкнув на иконке с изображением гаечного ключа возле имени процедуры. На рис.3 показано определение процедуры в том виде, в котором вы ее создали, с одним отличием. Оно включает предложение DEFINER после ключевого слова CREATE.


Рис.3 Просмотри определения процедуры на вкладке Stored Procedure

Предложение DEFINER указывает, какой аккаунт назначен в качестве создателя процедуры. Поскольку я создал хранимую процедуру под аккаунтом root на моем локальном экземпляре MySQL, это имя добавляется к определению. По умолчанию MySQL использует аккаунт пользователя, который выполнял оператор CREATE PROCEDURE, но вы можете указать отличный аккаунт, если ему предоставлены соответствующие разрешения.

Помимо предложения DEFINER, ваша хранимая процедура должна выглядеть так же, как вы ее создали, за исключением отсутствия операторов DELIMITER или пользовательским разделителем. Однако, если вы обновите определение на вкладке Stored Procedure и щелкните Apply, Workbench добавит эти элементы.

Другим способом проверить создание хранимой процедуры является запрос представления routines в базе данных INFORMATION_SCHEMA:

SELECT * FROM information_schema.routines

WHERE routine_schema = 'travel';

Оператор включает предложение WHERE, которое ограничивает результаты базой данных travel. Любые процедуры (хранимые процедуры или функции), которые были созданы в базе данных будут возвращаться этим запросом.

Вы можете еще больше ограничить результаты, указав имя процедуры в предложении WHERE, а также какие столбцы следует вернуть. Например, следующий оператор SELECT ограничивает результаты столбцом routine_definition и процедурой get_plane_info в базе данных travel:

SELECT routine_definition

FROM information_schema.routines

WHERE routine_schema = 'travel'

  AND routine_name = 'get_plane_info';

Хотя оператор возвращает единственное значение, его все же бывает трудно читать, особенно, если это сложный составной оператор. Для просмотра оператора полностью щелкните правой кнопкой на значении прямо в результатах, а затем - Open Value in Viewer (открыть значение в просмотрщике). Выберите Text, если он еще не выбран. MySQL откроет новое окно, в которое выведет значение, как показано на рис.4.


Рис.4 Проверка тела хранимой процедуры в просмотрщике

Конечно, проверка существования процедуры не скажет вам, что она работает как ожидалось. Поэтому вам следует также выполнить процедуру и посмотреть, какие результаты она вернет (в дополнение к выполнению ее в надлежащем цикле QA). Для этого используйте оператор CALL, который указывает имя процедуры, как показано в следующем примере:

CALL get_plane_info;

Когда вы вызываете процедуру, MySQL выполняет сохраненный код и возвращает результаты оператора, которые показаны на рис.5.


Рис.5 Просмотр результатов после вызова хранимой процедуры

Как ожидалось, оператор CALL возвращает агрегрованные данные самолетов, группируя их по производителю. Это те же результаты, которые вы получили бы, если выполнили непосредственно оператор SELECT в процедуре. Однако теперь оператор постоянно присутствует в базе данных, устраняя необходимость писать оператор на уровне приложения.

Добавления входного параметра в процедуру

Хранимая процедура get_plane_info из предыдущего раздела продемонстрировала большинство из основных компонентов, которые имеются в хранимых процедурах MySQL. В производственной среде составной оператор будет, скорее всего, более сложным, но этот пример все же обеспечивает большую часть базовых элементов. Тем не менее, одним из наиболее выгодных аспектов хранимой процедуры является ее возможность поддерживать входные и выходные параметры.

В этом разделе я демонстрирую, как добавить входной параметр в определение процедуры. (О выходных параметрах в следующем разделе.) Прежде, чем двинуться дальше, вы должны знать, что не можете просто изменить определение процедуры, как вы могли это сделать с определением таблицы или представления. Вы можете модифицировать характеристики процедуры, но не более. Чтобы внести более существенные обновления, вы должны удалить процедуру, а затем пересоздать её, добавляя любые новые элементы.

Чтобы удалить хранимыю процедуру, вы можете использовать оператор DROP PROCEDURE, как показано в следующем примере:

DROP PROCEDURE IF EXISTS get_plane_info;

Предложение IF EXISTS не является обязательным, но оно может помочь избежать необязательных ошибок. После выполнения этого оператора вы сможете убедиться, что процедура была удалена, если опять обратиться к представлению routines в базе данных INFORMATION_SCHEMA:

SELECT * FROM information_schema.routines

WHERE routine_schema = 'travel';

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

После удаления хранимой процедуры get_plane_info вы можете обновить оператор CREATE PROCEDURE, чтобы включить входной параметр. Для каждого параметра вам следует указать тип параметра, имя параметра и тип данных параметра. MySQL поддерживает параметры трех типов:

• IN. Входной параметр, который передает значение при вызове в код процедуры.


• OUT. Выходной параметр, который передает значение из кода обратно в вызывающее приложение.


• INOUT. Параметр, который может инициализироваться вызываюим приложением, обновляться в процедуре, а затем возвращаться в вызывающее приложение с новым значением.

Следующй оператор CREATE PROCEDURE включает один входной параметр, который называется in_name и имеет тип данных VARCHAR(50):

DELIMITER //

CREATE PROCEDURE get_plane_info(

  IN in_name VARCHAR(50))

COMMENT 'retrieves aggregated airplane information'

BEGIN

  SELECT a.manufacturer_id, m.manufacturer, 

    COUNT(*) AS plane_count,

    ROUND(AVG(a.wingspan), 2) AS avg_span, 

    ROUND(AVG(a.plane_length), 2) AS avg_length

  FROM airplanes a INNER JOIN manufacturers m

    ON a.manufacturer_id = m.manufacturer_id

  WHERE m.manufacturer = in_name;

END//

DELIMITER ;

Определение параметра заключается в круглые скобки и содержит ключевое слово IN, имя параметра тип данных. Я также обновил оператор SELECT, использовав параметр. Он больше не включает предложений GROUP BY и ORDER BY, но включает предложение WHERE, которое сравнивает параметр со столбцом manufacturer. Таким способом вызывающее приложение может указать производителя, на котором основывается запрос.

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

Пр вызове хранимой процедуры, которая принимает входной параметр, вы должны заключить значение параметра в скобки. Если это символьное значение, то нужно заключть его в одинарные кавычки. Например, следующий оператор CALL задает в качестве входного параметра процедуры значение piper:

CALL get_plane_info ('piper');

Когда MySQL выполняет код процедуры, она подставляет значение piper вместо входного параметра in_name, указанного в предложени WHERE. На рис.6 показаны результаты, которые сейчас возвращает хранимая процедура.


Рис.6 Вызов хранимой процедуры с входным параметром

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

Добавление выходных параметров в хранимой процедуре

Давайте теперь посмотрим, как добавить несколько параметров OUT в хранимую процедуру get_plane_info. Выходные параметры обеспечивают механизм возврата одного или более значений обратно в вызывающую программу, а не единственный результирующий набор. В этом примере вы добавите пять выходных параметров, которые будут соответствовать столбцам, указанным в списке SELECT процедуры.

Для добавления параметров вам необходимо опять удалить процедуру, а затем выполнить обновленный оператор CREATE PROCEDURE. Выходные параметры указываются в тех же скобках, что и входные параметры, что показано в следующем скрипте:

DROP PROCEDURE IF EXISTS get_plane_info;

DELIMITER //

CREATE PROCEDURE get_plane_info(

  IN in_name VARCHAR(50),

  OUT out_id INT UNSIGNED,

  OUT out_name VARCHAR(50),

  OUT plane_count SMALLINT UNSIGNED,

  OUT avg_wingspan DECIMAL(5,2), 

  OUT avg_length DECIMAL(5,2))

COMMENT 'retrieves aggregated airplane information'

BEGIN

  SELECT a.manufacturer_id, m.manufacturer, 

    COUNT(*),

    ROUND(AVG(a.wingspan), 2), 

    ROUND(AVG(a.plane_length), 2)

  INTO out_id, out_name, plane_count, avg_wingspan, avg_length

  FROM airplanes a INNER JOIN manufacturers m

    ON a.manufacturer_id = m.manufacturer_id

  WHERE m.manufacturer = in_name;

END//

DELIMITER ;

Для каждого выходного параметра вы должны указать ключевое слово OUT, имя параметра и тип данных параметра. Кроме того, вы должны добавить предложение INTO после списка SELECT, который возвращает результаты в выходные параметры. Я также удалил алиасы столбцов из списка SELECT, поскольку они больше не нужны.

При вызове хранимой процедуры, которая возвращает выходные параметры, вы можете захватить значения этих параметров, передавая пользовательскую переменную для каждого выходного параметра, которая будет содержать его значение, как показано в следующем операторе CALL:

CALL get_plane_info ('beechcraft', @out_id, @out_name, 

  @plane_count, @avg_wingspan, @avg_length);

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

Конкретный способ, которым вы будете обрабатывать выходные параметры в приложени, будет зависеть от используемого языка программирования. Пока же вы можете проверить, что ваши переменные содержат ожидаемые значения, выполнив оператор SELECT, например, такой:

SELECT @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length;

На рис.7 показан результат, который вернул этот оператор SELECT:


Рис.7 Просмотр значений выходных параметров процедуры для самолетов Beechcraft

На рисунке показаны результаты, когда вы вызываете хранимую процедуру со значением входного параметра beechcraft. Если задать другое значение, например, airbus, ваш оператор SELECT должен вернуть совсем другие результаты, что показано на рис.8.


Рис.8 Просмотр значений выходных параметров процедуры для самолетов airbus

Параметры IN и OUT могут сделать хранимые процедуры значительно более гибкими при поддержке приложений, управляемых данными. Вам могут встретиться ситуации, когда вы захотите использовать параметр INOUT. Например, вы можете создать хранимую процедуру, которая содержит что-то типа счетчика. Вы можете использовать параметр INOUT для установки начального значения счетчика, а затем возвращать новое значение счетчика на основе вывода программы.

Изменение хранимой процедуры в MySQL

MySQL поддерживает оператор ALTER PROCEDURE для обновления характеристик процедуры. Вы не можете использовать этот оператор, чтобы внести любые другие изменения в определение процедуры. Вы ограничены только характеристикам. Например, следующий оператор ALTER PROCEDURE добавляет две характеристики в определение хранимой процедуры, но остальная часть процедуры остается неизменной:

ALTER PROCEDURE get_plane_info

READS SQL DATA

SQL SECURITY INVOKER;

Характеристика READS SQL DATA указывает, что процедура включает операторы, которые читают данные. Этот тип характеристики носит только рекомендательный характер и никак не ограничивает код процедуры. Характеристика SQL SECURITY INVOKER указывает, что процедура должна выполняться в контексте безопасности аккаунта пользователя, который вызывает процедуру, а не под аккаунтом того, кто определял процедуру.

После выполнения оператора ALTER PROCEDURE вы можете проверить, что характеристики были добавлены, просмотром определения процедуры на вкладке Stored Procedure, как показано на рис.9.


Рис.9 Просмотр определения процедуры на вкладке Stored Procedure

Обратите внимание, что оператор CREATE PROCEDURE теперь включает три характеристики: две только что добавленных и исходную характеристику COMMENT, которую вы добавили ранее.

Работа с хранимыми процедурами в MySQL

Хранимые процедуры могут предложить большую гибкость, помогая оптимизировать разработку приложений. Однако процедура настолько эффективна, насколько эффективен ее код и содержащиеся операторы SQL. Здесь я показал на примере как создать хранимую процедуру, содержащую единственный оператор SELECT, но вы можете построить процедуры, которые обладают более сложной логикой. Позже в этой серии я продемонстрирую создание более сложных составных операторов, которые вы можете встроить в свои хранимые процедуры или использовать для других типов запросов.