Создание хранимых процедур в mysql. Sql создание процедуры
Создание хранимых процедур в mysql
Определение 1
Хранимая процедура – это самостоятельная часть программного код, которую создают и хранят в БД MySQL.
В состав хранимой процедуры могут входить SQL-операторы и специальные управляющие структуры. Удобна в использовании, если одну и ту же функцию нужно выполнить из разных приложений или с разных платформ, или в качестве средства сокрытия функциональных возможностей. В базе данных хранимые процедуры считаются аналогом объектно-ориентированного подхода в программировании. С помощью хранимых процедур можно управлять способом доступа к данным.
Создание хранимой процедуры
Создадим процедуру с именем GetAllProducts() для получения списка всех продуктов из таблицы.
Для этого нужно загрузить mysql-клиент и выполнить следующие команды:
Команда DELIMITER // не входит в хранимые процедуры. DELIMITER – специальная команда, изменяющая стандартный разделитель запросов (по умолчанию «;») на указанный после нее. Этой командой изменим его на 2 слеша (//).
Если не изменить разделитель, то mysql ошибочно интерпретирует процедуру и выдаст ошибку. После END используется разделитель // и с помощью команды DELIMITER возвращается значение разделителя «;».
Зарезервированные слова CREATE PROCEDURE указывают mysql, что нужно СОЗДАТЬ ПРОЦЕДУРУ. После этих слов нужно указать название хранимой процедуры (в примере GetAllProducts). Пустые скобки «()» после названия процедуры означает, что процедура не принимает никаких переменных.
Команды BEGIN и END соответственно открывают и закрывают блок кода SQL.
В консоли mysql хранимые процедуры писать не очень удобно. В таком случае можно воспользоваться GUI tools, с помощью которых можно создавать хранимые процедуры в интуитивно понятном интерфейсе.
В MySQL Workbench процедура создается нажатием правой кнопкой мыши на Routines и выбором в выпадающем меню пункта Create Procedure…
Далее вводим название хранимой процедуры и нажимаем кнопку Apply.
Можно просмотреть полный код, который отправится в MySQL, перед тем, как он запишется в базу данных. Если ошибок нет, нажимаем Apply.
После компиляции MySQL записывает процедуру в каталог. После завершения записи нажимаем кнопку Finish.
Можно увидеть созданную хранимую процедуру в списке Routines.
Вызов хранимой процедуры
Для вызова хранимой процедуры используется встроенная SQL команда CALL (ВЫЗВАТЬ):
Вместо STORED_PROCEDURE_NAME указывается имя хранимой процедуры, а в скобках указывается список переменных. Для вызова созданной процедуры без переменных будет использоваться код:
Т.к. процедура должна выполнять команду *SELECT FROM products;**, т.е. ВЫБРАТЬ все ИЗ таблицы ПРОДУКТЫ, то примером ее работы может быть следующий результат:
Переменные в хранимых процедурах
Определение 2
Переменная является именованным объектом данных, содержащим некоторое значение при выполнении хранимой процедуры.
Их используют для сохранения какого-либо результата запроса или выражения. Переменные в хранимых процедурах являются локальными, т.е. их нельзя вызвать извне.
Декларирование переменных
Определение 3
Процесс сообщения mysql о том, что нужно использовать переменную, называют декларированием.
Для декларирования переменных используется оператор DECLARE:
DECLARE – зарезервированный оператор.
variable_name – название переменной, к которому применяют такие же правила, что и к названию столбца данных в MySQL.
datatype(size) – тип переменной, которая используется, и ее размер. Переменные в MySQL могут принимать значения любого типа данных, например DATETIME, VARCHAR, INT и др.
DEFAULT default_value – позволяет задавать начальное значение переменной. Если оно не задано, то будет установлено значение по умолчанию NULL.
Для создания переменной total_sale, в которой будет храниться список покупок типа INT, и которая по умолчанию будет равна 0, запишем код:
Для декларирования нескольких переменных одного типа можно записать код:
или:
Присвоение значений переменной
Для присвоения значения переменной используют оператор SET.
К примеру, декларируем переменную total_count, а затем присвоим ей значение 10:
Помимо оператора SET может использоваться оператор SELECT INTO для передачи результата запроса в переменную. Обратим внимание, что запрос должен вернуть скалярное значение (т.е. одно).
В первой строке кода объявляется переменная total_products и устанавливается ее значение в 0.
Во второй и третьей строке используется оператор SELECT INTO для записи результата выполнения запроса SELECT COUNT(*) FROM products в созданную переменную.
Область видимости переменных
Каждая переменная имеет свою область видимости, определяющую время ее жизни.
Если создать переменную внутри хранимой процедуры, то она будет существовать, пока используется.
Если декларировать переменную внутри блока BEGIN END, то она будет существовать лишь в пределах этого блока.
Переменные со знаком @ вначале названия являются глобальными, т.е. они являются доступными на протяжении всей сессии.
spravochnick.ru
Создание выполняемых хранимых процедур
Хранимые процедуры создаются при помощи оператора create procedure, который имеет следующий синтаксис:
create proc[edure] [schema_name.]proc_name
[{{Qparaml} typel [ varying] [= default!] [output])] (, ...} [with { recompile | encryption | execute as 'user_name' }} [for replication]
as batch i external name method_name;
Здесь schema_name- имя схемы, которой назначается создаваемая хранимая процедура; ргос_ - имя новой хранимой процедуры; Qparami - это имя параметра, a typel задает его тип данных. Параметр в хранимой процедуре имеет тот же самый логический смысл, что и локальная переменная в пакете. Параметры являются значениями, передаваемыми от вызвавшего объекта хранимой процедуре и используемыми в хранимой процедуре. Параметр defauiti задает необязательное значение по умолчанию соответствующего параметра. (Значением по умолчанию может быть также и null.)
Опция output указывает, что параметр является выходным параметром и может быть возвращен вызывающей процедуре или системе (см. пример 8.8).
Как вы уже знаете, предварительно скомпилированная форма хранимой процедуры хранится в базе данных и используется при вызове хранимой процедуры. Если же вы хотите генерировать компилированную форму каждый раз, когда вызывается хранимая процедура, используйте опцию with recompile.
Предложение execute as задает контекст безопасности, под которым выполняется хранимая процедура после доступа к ней. Задавая этот контекст, под которым хранимая процедура выполняется, вы можете управлять тем, какую учетную запись пользователя можно применять для проверки полномочий объектов, ссылающихся на данную хранимую процедуру.
По умолчанию оператор create procedure могут использовать только участники с фиксированной серверной ролью sysadmin и участники с фиксированными ролями базы данных dbowner и dbddladmin. Однако пользователи с этими ролями могут назначать эти привилегии другим пользователям с помощью оператора grant create procedure. (Обсуждение полномочий пользователя, фиксированных серверных ролей и фиксированных ролей базы данных.)
В примере 8.5 показано создание простой хранимой процедуры для таблицы
project.
Хранимая процедура increase_budget увеличивает бюджеты всех проектов на заданный процент, который указывается с использованием параметра @percent. В процедуре также определяется значение по умолчанию (5), которое будет использовано, если во время выполнения процедуры ей не передается никакой аргумент.
В отличие от «базовых» хранимых процедур, которые размещаются в текущей базе данных, возможно создание и временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Вы можете создавать временную хранимую процедуру для исключения повторного выполнения группы операторов при соединении с базой данных. Вы можете создавать локальные или глобальные временные процедуры, задав перед именем процедуры одиночный символ # (#proc_name) для локальной временной процедуры и два символа # (например, ##proc_name) для глобальной временной процедуры. Локальная временная хранимая процедура может быть выполнена только пользователем, ее создавшим, и только во время того же соединения. Глобальная временная процедура может выполняться всеми пользователями, но только пока не завершилось последнее соединение, в котором она выполняется (обычно создателем процедуры).
Жизненный цикл хранимой процедуры имеет две фазы: ее создание и ее выполнение. Каждая процедура создается однажды, а выполняется многократно. Оператор execute выполняет существующую процедуру. Выполнение хранимой процедуры допустимо для любого пользователя, который либо является ее владельцем, либо имеет привилегию execute к этой процедуре. Оператор execute имеет следующий синтаксис:
[[exec[ute]] [@return_status = ] [proc__name i @proc_name_var\ {[[Qparameterl = ] value
i [Qparameterl = ] ^variable [output]] | default}... [with recompile];
Все опции оператора execute, за исключением return_status, имеют то же логическое значение, что и опции с теми же именами в операторе create procedure. Опция return_status является необязательной целочисленной переменной, которая хранит возвращаемый статус процедуры. Значение параметру может быть присвоено с использованием либо значения (value), либо локальной переменной (^variable). Порядок задания параметров безразличен, если используются именованные параметры, однако, если параметры не имеют имен, значения параметров должны быть представлены точно в том порядке, в каком они были указаны в операторе create procedure.
Предложение default указывает на значение по умолчанию для параметра, которое было определено в процедуре. Если процедура ожидает получение параметра, для которого не определено значение по умолчанию, а параметр отсутствует или указано предложение default, то возникает ошибка.
В примере 8.6 показано использование оператора execute.
Оператор execute в примере 8.6 выполняет хранимую процедуру increase_ budget (см. пример 8.5) и увеличивает бюджеты всех проектов на 10%.
В примере 8.7 показано создание процедуры, которая ссылается на таблицы
employee и works_on.
Процедура modifyempno в примере 8.7 демонстрирует использование хранимых процедур в виде части средств поддержки ссылочной целостности данных (в данном случае между таблицами employee и workson). Подобная хранимая процедура может быть использована внутри определения триггера, который фактически обеспечивает ссылочную целостность (см. пример 14.3).
В примере 8.8 показано использование предложения output.
Эта хранимая процедура может быть выполнена с использованием следующих операторов:
DECLARE @quantity INT
EXECUTE delete_emp @employee_no = 28559, @counter = @quantity OUTPUT
Предыдущий пример содержит операторы создания процедуры deleteemp и ее выполнения. Эта процедура вычисляет количество проектов, над которыми работает служащий (с номером служащего @employee_no). Вычисленное значение затем присваивается параметру @counter. После удаления всех строк с указанным номером служащего из таблиц employee и workson полученное значение будет присвоено переменной @quantity.
serversql.ru
sql - Создание хранимой процедуры SQL
Я создал три таблицы в моей базе данных. Пассажир и стол рейса уже имеют информацию.
create table IT260_AV.dbo.Passenger ( "Passport ID" nvarchar(20) not null, "Lastname" nvarchar(50), "Firstname" nvarchar(50), "Nationality" int , "Telephone" nvarchar(20), "Email" nvarchar(50), "Birthdate" datetime, "NumberOfChildren" int, "PassportExpDate" datetime) alter table IT260_AV.dbo.Passenger add constraint Passenger_PK primary key clustered ("Passport ID") create table IT260_AV.dbo.Flight ("FlightNo" int not null, "Air Company" nvarchar (50), "Departure Date" datetime, "Departure Time" time, "MaximumCapacity" int, "BookingCapacity" int) alter table IT260_AV.dbo.Flight add constraint FlightNo_PK primary key clustered ("FlightNo") create table IT260_AV.dbo.Booking ("Booking Number" nvarchar(20) not null, "Flight Number" int not null, "Passenger ID" nvarchar(20), "SeatNo" int not null IDENTITY(1, 1), constraint FK_FlightNumber foreign key ("Flight Number") references Flight("FlightNo"), constraint FK_PassengerID foreign key ("Passenger ID") references Passenger("Passport ID") ) alter table IT260_AV.dbo.Booking add constraint BookingNumber_PK primary key clustered ( "Booking Number")Я пытаюсь создать процедуру, которая будет принимать в качестве входного паспорта пассажиров и номер рейса, и он создаст новую запись в таблице бронирования.
Процедура должна проверить, достаточно ли максимальной мощности полета до завершения бронирования. Если этого недостаточно, оно должно отображать сообщение. Кроме того, он должен проверить, действителен ли данный паспорт на основании даты истечения срока действия.
Кто-нибудь знает, что я должен делать?
Я пробовал это, но я все еще получаю сообщение об ошибке:
create procedure sp_Booking_AV @PassengerPassport nvarchar(20), @Flightn0 int as begin declare @Capacity int , @SeatAv int , @ExpDate date set @Capacity = (select MaximumCapacity from Flight where FlightNo=@Flightn0) set @SeatAv = (select max(SeatNo) from Booking) set @ExpDate = (select PassportExpDate from Passenger where "Passport ID" = @PassengerPassport) if (@SeatAv is null) set @SeatAv = 0 if(@SeatAv <=@Capacity) if (@ExpDate>=Convert(date,getdate())) insert into IT260_AV.dbo.Booking values (@PassengerPassport,@Flightn0) else print 'The passport has been expired'; else print 'The fly is fool' end goqaru.site
НОУ ИНТУИТ | Лекция | Создание хранимых процедур
Аннотация: Понятие хранимой процедуры. Примеры хранимых процедур. Процедуры с параметрами. Создание процедур в SQL Query Analyzer, SQL Server Enterprise Manager, Visual Studio .NET
Внимание! Для работы с лекциями 5, 6 необходимы учебные файлы, которые Вы можете загрузить здесь.Создание хранимых процедур
Создание хранимых процедур в SQL Query Analyzer
Хранимая процедура - это одна или несколько SQL-конструкций, которые записаны в базе данных. Задача администрирования базы данных включает в себя в первую очередь распределение уровней доступа к ней. Разрешение выполнения обычных SQL-запросов большому числу пользователей может стать причиной неисправностей из-за неверного запроса или их группы. Чтобы их избежать, разработчики базы данных могут создать ряд хранимых процедур для работы с данными и полностью запретить доступ для обычных запросов. Такой подход при прочих равных условиях обеспечивает большую стабильность и надежность работы. Это одна из главных причин создания собственных хранимых процедур. Другие причины - быстрое выполнение, разбиение больших задач на малые модули, уменьшение нагрузки на сеть - значительно облегчают процесс разработки и обслуживания архитектуры "клиент-сервер".
Сами базы данных используют огромное количество встроенных хранимых процедур для функционирования. Запустим программу SQL Query Analyzer1, входящую в пакет Microsoft SQL Server 2000. Создадим новый бланк (Ctrl +N) и введем в нем следующее:
В результате выполнения выводится список всех баз, созданных на данном локальном сервере (рис. 5.1):
Мы запустили одну из системных хранимых процедур, которая находится в базе master. Ее можно найти в списке "Stored Procedures" базы - все системные хранимые процедуры имеют приставку "sp". Обратите внимание, что системные процедуры выделяются бордовым цветом и для многих из них не нужно указывать в выпадающем списке конкретную базу. Запустим еще одну процедуру:
В результате ее выполнения выводится статистика текущего SQL-сервера (рис. 5.2).
Рис. 5.2. Статистика Microsoft SQL-Server Для вывода списка хранимых процедур в учебной базе Northwind используем следующую процедуру:
USE Northwind exec sp_stored_proceduresМожно было, конечно, указать название и в выпадающем списке. База Northwind содержит 38 хранимых процедур (рис. 5.3), большая часть из которых - системные. Для просмотра списка в других базах следует вызвать для них название этой же процедуры.
Рис. 5.3. Вывод списка хранимых процедур базы данных NorthwindПерейдем к созданию своих собственных процедур. Скопируйте базу BDTur_firm.mdb из "Элементы работы с базами данных" , назовите ее "BDTur_firm2.mdb". Открываем ее в Microsoft Access и в названиях таблиц и полей удаляем все пробелы. Например, таблица "Информация о туристах" будет теперь называться так: "Информацияотуристах", а поле "Код туриста" станет полем "Кодтуриста". Затем конвертируем базу в формат Microsoft SQL и присоединяем ее к локальному серверу2. Запускаем SQL Query Analyzer, открываем чистый бланк и вводим запрос3:
create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from ТуристыЗдесь create procedure - оператор, указывающий на создание хранимой процедуры, proc1 - ее название, далее после оператора as следует обычный SQL-запрос. Запускаем его - появляется сообщение:
The COMMAND(s) completed successfully.Это означает, что мы все сделали правильно и команда создала процедуру proc1. Для просмотра результата вызываем ее:
Появляется уже знакомое нам извлечение всех записей таблицы "Туристы" со всеми записями (рис. 5.4):
Рис. 5.4. Результат запуска процедуры proc1Как видите, создание содержимого хранимой процедуры не отличается ничем от создания обычного SQL-запроса. В таблице 5.1 приведены примеры хранимых процедур:
| 1 | create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы | exec proc1 | Вывод всех записей таблицы Туристы |
| Результат запуска | |||
| 2 | create procedure proc2 as select top 3 Фамилия from туристы | exec proc2 | Вывод первых трех значений поля Фамилия таблицы Туристы |
| Результат запуска | |||
| 3 | create procedure proc3 as select * from туристы where Фамилия = 'Андреева' | exec proc3 | Вывод всех полей таблицы Туристы, содержащих в поле Фамилия значение " Андреева " |
| Результат запуска | |||
| 4 | create procedure proc4 as select count (*) from Туристы | exec proc4 | Подсчет числа записей таблицы Туристы |
| Результат запуска | |||
| 5 | create procedure proc5 as select sum(Сумма) from Оплата | exec proc5 | Подсчет значений поля Сумма таблицы Оплата |
| Результат запуска | |||
| 6 | create procedure proc6 as select max(Цена) from Туры | exec proc6 | Вывод максимального значения поля Цена таблицы Туры |
| Результат запуска | |||
| 7 | create procedure proc7 as select min(Цена) from Туры | exec proc7 | Вывод минимального значения поля Цена таблицы Туры |
| Результат запуска | |||
| 8 | create procedure proc8 as select * from Туристы where Фамилия like '%и%' | exec proc8 | Вывод всех записей таблицы Туристы, содержащих в значении поля Фамилия букву "и" (в любой части слова) |
| Результат запуска | |||
| 9 | create procedure proc9 as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc9 | Операция inner join объединяет записи из двух таблиц, если поле (поля), по которому связаны эти таблицы, содержат одинаковые значения. Общий синтаксис выглядит следующим образом:from таблица1 inner join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2 |
| Результат запуска | |||
| 10 | create procedure proc10 as select * from Туристы left join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc10 | Прежде чем создать эту процедуру и затем ее извлечь, запускаем программу SQL Server Enterprise Manager, выделяем таблицу "Туристы" базы данных " BDTur_firm2". Щелкаем на ней правой кнопкой и в появившемся меню выбираем Open Table - Return all rows. Теперь добавляем запись - "Корнеев Глеб Алексеевич". В результате в таблице "Туристы" у нас получилось 6 записей, а в связанной с ней таблице "Информацияотуристах" - 5. В SQL Query Analyzer создаем хранимую процедуру и запускаем ее. Операция left join используется для создания так называемого левого внешнего соединения. С помощью объединения выбираются все записи первой (левой) таблицы, даже если они не соответствуют записям во второй (правой) таблице. Общий синтаксис имеет вид:from таблица1 left join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2. Здесь в таблице "Информацияотуристах" нет связанной записи для туриста "Корнеев Глеб Алексеевич", поэтому соответствующие поля заполняются значениями null |
| Результат запуска | |||
| 11 | create procedure proc11 as select * from Туристы right join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc11 | Перед созданием этого запроса нам снова придется изменить таблицы. В SQL Server Enterprise Manager удаляем шестую запись в таблице "Туристы", добавляем шестую запись в таблицу " Информацияотуристах"(значения полей - см. на рисунке). Операция right join используется для создания правого внешнего соединения. С его помощью выбираются все записи второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице. Общий синтаксис имеет вид:from таблица1 right join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2. |
| Результат запуска | |||
www.intuit.ru
Хранимые процедуры SQL: создание и использование
Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в базе данных в виде различных объектов. Другими словами, это объект, в котором содержатся SQL инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого либо другого раздела.
Введение
Многие считают, что они похожи на процедуры различных языков программирования высокого уровня (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название – UDF).
При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые действия с информацией: удаление, исполнение, изменение. За все это отвечает DDL оператор, который в одиночку совершает сложнейшие действия по обработке объектов. Причем все это происходит очень быстро, а сервер фактически не нагружается. Такая скорость и производительность позволяют очень быстро передавать большие объемы информации от пользователя на сервер и наоборот.
Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от системы управления базами данных Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.
Продуктивность
Эти объекты баз данных могут быть запрограммированы различными путями. Это позволяет пользователям выбирать тип используемого способа, который будет наиболее подходящим, что экономит силы и время. Кроме того, процедура сама обрабатывается, что позволяет избежать огромных временных затрат на обмен между сервером и пользователем. Также модуль можно перепрограммировать и изменить в нужное направление в абсолютно любой момент. Особенно стоит отметить скорость, с которой происходит запуск хранимой процедуры SQL: это процесс происходит быстрее иных, схожих с ним, что делает его удобным и универсальным.
Безопасность
Такой тип обработки информации отличается от схожих процессов тем, что он гарантирует повышенную безопасность. Это обеспечивается за счет того, что доступ других пользователей к процедурам может быть исключен целиком и полностью. Это позволит администратору проводить операции с ними самостоятельно, не опасаясь за перехват информации или несанкционированный доступ к базе данных.
Передача данных
Связь между хранимой процедурой SQL и клиентским приложением заключается в использовании параметров и возвращаемых значениях. Последним не обязательно передавать данные в хранимую процедуру, однако эта информация (в основном по запросу пользователя) и перерабатывается для SQL. После того как хранимая процедура завершила свою работу, она отсылает пакеты данных обратно (но, опять же, по желанию) к вызвавшему его приложению, используя различные методы, с помощью которых может быть осуществлен как вызов хранимой процедуры SQL, так и возврат, например:
передача данных с помощью параметра типа Output;
передача данных с помощью оператора возврата;
передача данных с помощью оператора выбора.
А теперь разберемся, как же выглядит этот процесс изнутри.
1. Создание EXEC хранимой процедуры в SQL
Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC процесс, который содержит имя самого объекта.
При создании процедуры ее название появляется первым, после чего производится один или несколько параметров, присвоенных ему. Параметры могут быть необязательными. После того как параметр(ы), то есть тело процедуры, будут написаны, нужно провести некоторые необходимые операции.
Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.
Таким образом, чтобы создать процедуру, нам нужно имя процедуры и, по меньшей мере, один параметр в качестве тела процедуры. Обратите внимание, что отличным вариантом в таком случае является создание и выполнение процедуры с именем схемы в классификаторе.
Тело процедуры может иметь любой вид из операторов SQL, например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:
тело не должно создавать какой либо другой хранимой процедуры;
тело не должно создать ложное представление об объекте;
тело не должно создавать никаких триггеров.
2. Установка переменной в тело процедуры
Вы можете сделать переменные локальными для тела процедуры, и тогда они будут находиться исключительно внутри тела процедуры. Хорошей практикой является создание переменных в начале тела хранимой процедуры. Но также вы можете устанавливать переменные в любом месте в теле данного объекта.
Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.
Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.
3. Создание хранимой процедуры SQL
В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.
В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.
Как в SQL выполнить хранимую процедуру
Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.
Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.
4. Хранимые процедуры SQL Server: способы возврата
Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:
возврат значения хранимой процедуры;
выход параметра хранимых процедур;
выбор одной из хранимых процедур.
4.1 Возврат значений хранимых процедур SQL
В этой методике процедура присваивает значение локальной переменной и возвращает его. Процедура может также непосредственно возвращать постоянное значение. В следующем примере, мы создали процедуру, которая возвращает общее число авторов. Если сравнить эту процедуру с предыдущими, вы можете увидеть, что значение для печати заменяется обратным.
Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select оператор, например, Select @RetValue, а также OutputValue.
4.2 Выход параметра хранимых процедур SQL
Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.
В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.
Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.
4.3 Выбор одной из хранимых процедур SQL
Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.
В заключение
Хранимая процедура является довольно серьезным программным модулем, возвращающим или передающим, а также устанавливающим необходимые переменные благодаря клиентскому приложению. Поскольку хранимая процедура выполняется на сервере сама, обмена данными в огромных объемах между сервером и клиентским приложением (для некоторых вычислений) можно избежать. Это позволяет снижать нагрузки на сервера SQL, что, конечно же, идет на руку их держателям. Одним из подвидов являются хранимые процедуры T SQL, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.
autogear.ru
