Как посмотреть процедуру в sql

SQL-Ex blog

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

Как нам проверить эти изменения?

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

SELECT O.name, O.create_date, O.modify_date, m.definition 
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id 
WHERE m.definition LIKE '%SEARCH_TEXT%'; 
GO

Следуя нашему примеру, если мы хотим подтвердить, что база данных имеет процедуру ExampleProc, обновленную до версии 1.2.3, и это указано в хранимой процедуре, мы могли бы увидеть следующее, заменив SEARCH_TEXT в скрипте выше на 1.2.3:

За пределами одной базы данных

Этого достаточно нам для поиска в одной базе данных, но у нас множество баз данных, которые мы хотим просмотреть. Нужно ли нам выполнять этот скрипт на каждой отдельной базе данных поочередно? Вы можете это делать, если хотите, но я не рекомендовал бы вам тратить так много времени. Вместо этого, я бы посоветовал сочетать скрипт выше с процедурой Aaron Bertrand sp_ineachdb.

Возможно, вам знакома системная процедура sp_MSforeachdb, которая доступна в SQL Server по умолчанию. Но вы можете не знать, что хотя она имеется и может быть обнаружена во многих источниках как решение для выполнения запроса на всех базах данных, она фактически не поддерживается. Кроме того, есть обстоятельства, когда базы данных могут быть пропущены, хотя её название — foreachdb — говорит об обратном. Microsoft не собирается тратить время на её исправление, поскольку она официально не поддерживается. Это означает, что вы можете либо самостоятельно проверять выполнение её работы, либо перейти к sp_ineachdb.

Использование sp_ineachdb в нашем примере будет выглядеть примерно так:

EXEC dbo.sp_ineachdb @user_only = 1, @command = N'SELECT DB_NAME() AS ''Database'',O.name, O.create_date, O.modify_date, m.definition 
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id 
WHERE m.definition LIKE ''%1.2.3%'';';

Вы можете также использовать @select_dbname=1 в sp_ineachdb, чтобы увидеть каждую базу данных, на которой выполняется ваш скрипт:

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

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Как запросом смотреть процедуры?

Умею, например через dbeaver, находить в databases -> schemas-> папку procedures и далее вручную нахожу нужную процедуру по наименованию и выбираю генерация SQL -> DDL.
Хотел бы ускорить этот процесс через запрос, в который я бы вставлял наименование процедуры и мне выдавало ответом свойства, что внутри этой процедуры.
Можно ли через запрос в бд просмотреть из чего состоит процедура, если да, то какой запрос писать, чтобы это смотреть.
Интересует синтаксис запроса для MS SQL и postgreSQL

Пример

• Вопрос задан более года назад
• 2673 просмотра

1 комментарий

Простой 1 комментарий

Хранимые процедуры в SQL server

Использование хранимых процедур позволяет организовать бизнес-логику и логику управления данными на стороне базы данных.

В чем плюсы такого подхода?

Во-первых, SQL Server оптимизирует и компилирует хранимые процедуры, поэтому они выполняются быстро без необходимости повторять эти шаги каждый раз.

Во-вторых, они выполняются на стороне базы данных, а не в коде приложения. Т.е. минимум тратится ресурсов на дополнительные обращения к базе данных.

В-третьих, использование хранимых процедур позволяет сэкономить траффик, так как клиент посылает серверу только запрос, сервер его обрабатывает и возвращает только результат, который обычно значительно меньше, чем полный набор данных.

Создание хранимой процедуры в SQL Server ManagementStudio

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

выбираем базу данных, переходим на вкладуку «Программирование/Хранимые процедуры»

Создаем хранимую процедуру через контекстное меню:

Видим вот такой код:

Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:

CREATE PROCEDURE GetStudents AS SELECT * FROM Students GO

После этого нажимаем «Выполнить» (F5) и видим слева нашу хранимую процедуру:

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

После чего увидим результат выполнения нашей хранимой процедуры:

Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем «выполнить» или F5):

USE sampledb; EXEC GetStudents

Результатом выполнения данного кода будет выбор всех студентов из таблицы Students:

1 str@gmail.com Иван Иванов г. Рязань, ул. Ленина 54/2 50000,00 2 str@yandex.ru Петр Петров г. Рязань, ул. Ленина 54/3 50000,00 3 ilya@gmail.com Илья Ильин г. Рязань, ул. Ленина 54/4 40000,00 4 vp@gmail.com Иван Прохоров г. Рязань, ул. Ленина 57/8 40000,00 5 bak@gmail.com Борис Акунин г. Москва, ул. Лебедева 23/21 60000,00 6 el@gmail.com Екатерина Ларина г. Шахты, ул. Пражская 4/9 60000,00 7 eb@gmail.com Елизавета Бродская г. Рязань, ул. Ленина 54/2 90000,00

Внутрениие элементы хранимых процедур

Входные параметры хранимой процедуры

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

CREATE PROCEDURE proc1 @s1 nvarchar(128), @s2 int AS BEGIN select @s1 + cast(@s2 as nvarchar) END GO 

exec proc1 @s1='123', @s2 = 0

Параметры также могут быть выходными — т.е. их значение изменено в процедуре и возвращено в вызывающую сторону.

CREATE PROCEDURE proc2 @s1 nvarchar(128), @s2 int, @s3 nvarchar OUTPUT AS BEGIN set @s3 = @s1 + cast(@s2 as nvarchar) END GO 

declare @test nvarchar(max)='' exec proc1 @s1='123', @s2 = 0, @s3 = @test print @test

Использование if

Пример хранимой процедуры с условием if:

CREATE PROCEDURE checkMaxAward AS BEGIN DECLARE @maxAward money SELECT @maxAward = MAX(award) FROM Students IF (@maxAward > 100000) begin PRINT 'максимальная сумма премии больше 100000'; end ELSE begin PRINT 'максимальная сумма премии меньше 100000'; end END GO

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

DECLARE @maxAward money

DECLARE применяется д ля определения переменных , после ключевого слова «DECLARE» указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @. В данном примере определяем переменную «@maxAward» с типом данных «money».

В нашем случае результат будет такой:

максимальная сумма премии меньше 100000

Циклы в хранимых процедурах SQL Server

Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:

DECLARE @number INT, @factorial INT SET @factorial = 2; SET @number = 10; WHILE @number > 0 BEGIN SET @factorial = @factorial * @number SET @number = @number - 2 END; PRINT @factorial

Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.

Результатом выполнения данного кода будет:

7680

Также следует обратить внимание на ключевое слово «PRINT», которое выводит результат нашего кода:

Инструкция OUTPUT

OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

OUTPUT может помочь в тех случаях, когда нужно проверить или узнать какие именно строки (записи) были добавлены, удалены или изменены, без дополнительных запросов на выборку (SELECT). Данная инструкция позволяет сохранить все изменения в отдельном месте, например, в таблице, благодаря этому мы можем узнать и работать, например, со списком всех идентификаторов, которые были сгенерированы и добавлены в таблицу.

Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

Преобразование типов данных для переменных

Функция CAST преобразует выражение одного типа к другому и имеет следующую форму:

CAST(выражение AS тип_данных)

Пример. Есть такой код:

SELECT 'Средняя премия = '+ CAST(AVG(award) AS CHAR(15)) FROM Students;

Преобразуем числовое значение «award».

Результатом данного кода будет:

Средняя премия = 55714.29

Также есть функция TRY_CAST для преобразования данных. Функция TRY_CAST пытается преобразовать выражение из одного типа данных в другой тип данных. Если преобразование не удалось, функция вернет NULL. В противном случае вернет преобразованное значение.

Пример, следующий код пытается преобразовать строку «test» к типу float:

SELECT CASE WHEN TRY_CONVERT(float, 'test') IS NULL THEN 'Cast failed' ELSE 'Cast succeeded' END AS Result; GO 

Результатом данного кода будет:

Cast failed

Отличие TRY_CAST от CAST заключается в том, что если преобразование не удалось, то TRY_CAST вернет NULL, а CAST вызовет исключение.

Конкатенация строк

Функция CONCAT , которая выполняет конкатенацию, неявно преобразуя типы аргументов к строковому типу данных. Есть следующий код:

SELECT email, CONCAT(firstName, lastName) from Students

Результат данного кода будет следующий:

str@gmail.com ИванИванов str@yandex.ru ПетрПетров ilya@gmail.com ИльяИльин vp@gmail.com ИванПрохоров bak@gmail.com БорисАкунин el@gmail.com ЕкатеринаЛарина eb@gmail.com ЕлизаветаБродская

Данная функция склеивает firstName и lastName в один столбец.

Стандартные функции для работы с датой и временем

Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:

SELECT getdate()

2021-11-08 21:19:58.843

Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)

select dateadd(day, 1, getdate())

Работа с NULL через ISNULL, NULLIF

В условиях вы можете проверить равно ли какое то выражение через такую конструкцию:

select * from table1 where a1 is null

Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

ISNULL(выражение, значение)

Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись «неизвестно»:

SELECT firstName, lastName, ISNULL(email, 'неизвестно') AS Email FROM Students

Результат этого запроса ниже:

Иван Иванов str@gmail.com Петр Петров str@yandex.ru Илья Ильин ilya@gmail.com Иван Прохоров vp@gmail.com Борис Акунин bak@gmail.com Екатерина Ларина el@gmail.com Елизавета Бродская eb@gmail.com Семен Зюзин неизвестно

Последняя строка поле email было заменено на «неизвестно», т.к. имеет значение NULL.

Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:

SELECT NULLIF (4,4) AS Same, NULLIF (5,7) AS Different;

NULL 5

возвращает NULL для первого столбца (4 и 4), потому что два входных значения одинаковы. Второй столбец возвращает первое значение (5), потому что два входных значения различны.

Полезно знать о некоторых важных системных процедурах. А именно:

• sp_help SP_Name : используется для получения информации о названиях параметров процедуры, их типах и т.д. Эта процедура может быть применена к любому объекту БД (таблица, триггер и т.п.)
• sp_helptext SP_Name : используется для получения текста хранимой процедуры

Пример первая функция sp_help. Используем вот такой код:

sp_help Students

Результат выполнения данной функции ниже:

Здесь мы видим подробную информацию о таблице Students.

Пример использования второй функции:

sp_helptext GetStudents

Результат ее выполнения:

SQL-Ex blog

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

Процедурный код может содержать широкий ассортимент операторов, включая язык определения данных (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';

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

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

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, но вы можете построить процедуры, которые обладают более сложной логикой. Позже в этой серии я продемонстрирую создание более сложных составных операторов, которые вы можете встроить в свои хранимые процедуры или использовать для других типов запросов.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой