Хранимая процедура (англ. stored procedure) – это именованный программный объект БД. В SQL Server есть хранимые процедуры нескольких типов.
Системные хранимые процедуры (англ. system stored procedure) поставляются разработчиками СУБД и используются для выполнения действий с системным каталогом или получения системной информации. Их названия обычно начинаются с префикса "sp_". Запускаются хранимые процедуры всех типов с помощью команды EXECUTE, которую можно сократить до ЕХЕС. Например, хранимая процедура sp_helplogins, запущенная без параметров, формирует два отчета об именах учетных записей (англ. logins) и соответствующих им в каждой БД пользователях (англ. users).
EXEC sp_helplogins;
Чтобы дать представление о действиях, выполняемых с помощью системных хранимых процедур, в табл. 10.6 приведены некоторые примеры. Всего же системных хранимых процедур в SQL Server более тысячи.
Таблица 10.6
Примеры системных хранимых процедур SQL Server
Пользователю доступно создание хранимых процедур в пользовательских БД и в БД для временных объектов. В последнем случае хранимая процедура будет являться временной. Так же как в случае с временными таблицами, название временной хранимой процедуры должно начинаться с префикса "#", если это локальная временная хранимая процедура, или с "##" – если глобальная. Локальная временная процедура может использоваться только в рамках соединения, в котором ее создали, глобальная – и в рамках других соединений.
Программируемые объекты SQL Server могут создаваться как с использованием средств Transact-SQL, так и с помощью сборок (англ. assembly) в среде CRL (Common Language Runtime) платформы Microsoft.Net Framework . В данном учебнике будет рассматриваться только первый способ.
Для создания хранимых процедур используется оператор CREATE PROCEDURE (можно сократить до PROC), формат которого приведен ниже:
CREATE {PROC I PROCEDURE) proc_name [ ; number ]
[{gparameter data_type }
[“default] |
[ WITH
[ FOR REPLICATION ]
AS {[ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Если хранимая процедура (или триггер, функция, представление) создается с опцией ENCRYPTION, ее код преобразуется таким образом, что текст становится нечитаемым. В то же время, как отмечается в , используемый алгоритм перенесен из ранних версий SQL Server и не может рассматриваться в качестве надежного алгоритма защиты – существуют утилиты, позволяющие быстро выполнить обратное преобразование.
Опция RECOMPILE указывает на то, что при каждом вызове процедуры система будет перекомпилировать текст. В обычном случае скомпилированная при первом запуске процедура сохраняется в кэше, что позволяет увеличить быстродействие.
EXECUTE AS определяет контекст безопасности, в котором должна быть выполнена процедура. Далее указывается одно из значений f CALLER | SELF | OWNER | " user_name"). CALLER является значением по умолчанию и означает, что код будет выполняться в контексте безопасности пользователя, вызывающего этот модуль. Соответственно, пользователь должен иметь разрешения не только на сам программируемый объект, но и на другие затрагиваемые им объекты БД. EXECUTE AS SELF означает использование контекста пользователя, создающего или изменяющего программируемый объект. OWNER указывает, что код будет выполняться в контексте текущего владельца процедуры. Если для нее не определен владелец, то подразумевается владелец схемы, к которой она относится. EXECUTE AS "user_name" позволяет явно указать имя пользователя (в одинарных кавычках).
Для процедуры могут указываться параметры. Это локальные переменные, используемые для передачи значений в процедуру. Если параметр объявлен с ключевым словом OUTPUT (или сокращенно OUT), он является выходным: заданное ему в процедуре значение после ее окончания может быть использовано вызвавшей процедуру программой. Ключевое слово READONLY означает, что значение параметра не может быть изменено внутри хранимой процедуры.
Параметрам могут быть назначены значения но умолчанию, которые будут использованы, если при вызове процедуры значение параметра не будет указано в явном виде. Рассмотрим пример:
CREATE PROC surma (@а int, @b int=0,
©result int OUTPUT) AS
SET @result=0a+0b
Мы создали процедуру с тремя параметрами, причем у параметра @b значение по умолчанию =0, а параметр @result – выходной: через него возвращается значение в вызвавшую программу. Выполняемые действия достаточно просты – выходной параметр получает значение суммы двух входных.
При работе в SQL Server Management Studio созданную хранимую процедуру можно найти в разделе программируемых объектов БД (англ. Programmability) в подразделе для хранимых процедур (рис. 10.2).
При вызове процедуры в качестве входных параметров можно использовать как переменные, так и константы. Рассмотрим два примера. В первом входные параметры процедуры явно заданы константами, для выходного параметра в вызове указано ключевое слово OUTPUT. Во втором варианте в качестве первого входного параметра используется значение переменной, а для второго параметра с помощью ключевого слова DEFAULT указано, что должно быть использовано значение по умолчанию:
Рис. 10.2.
DECLARE @с int;
EXEC summa 10,5,@c OUTPUT;
PRINT 0c; – будет выведено 15
DECLARE Gi int = 5;
– при вызове используем значение по умолчанию
EXEC summa Gi,DEFAULT , 0с OUTPUT;
PRINT 0c; – будет выведено 5
Рассмотрим теперь пример с анализом кода возврата, с которым заканчивается процедура. Пусть надо подсчитать, сколько в таблице Bookl книг, изданных в заданном диапазоне лет. При этом если начальный год оказался больше конечного, процедура возвращает "1" и подсчет не проводит, иначе – считаем количество книг и возвращаем 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
IF 0FirsYear>0LastYear RETURN 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN 0FirsYear AND 0LastYear) ;
Рассмотрим вариант вызова данной процедуры, в котором код возврата сохраняется в целочисленной переменной 0ret, после чего анализируется его значение (в данном случае это будет 1). Используемая в операторе PRINT функция CAST служит для преобразования значения целочисленной переменной Gres к строковому типу:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Начальный год больше конечного"
PRINT "Число книг "+ CAST(Gres as varchar(20))
Хранимые процедуры могут не только считывать данные из таблицы, но и изменять данные и даже создавать таблицы и ряд других объектов БД.
Однако создавать схемы, функции, триггеры, процедуры и представления из хранимой процедуры нельзя.
Следующий пример иллюстрирует как эти возможности, так и вопросы, связанные с областью видимости временных объектов. Приведенная ниже хранимая процедура проверяет наличие временной таблицы #ТаЬ2; если этой таблицы нет, то создает ее. После этого в таблицу #ТаЬ2 заносятся значения двух столбцов, и содержимое таблицы выводится оператором SELECT:
CREATE PROC My_Procl (@id int, @name varchar(30))
IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL
INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)
SELECT * FROM dbo. #Tab2 –№1
Перед первым вызовом хранимой процедуры создадим используемую в ней временную таблицу #ТаЬ2. Обратите внимание на оператор ЕХЕС. В предыдущих примерах параметры передавались в процедуру "по позиции", а в данном случае используется другой формат передачи параметров – "по имени", явно указывается имя параметра и его значение:
CREATE TABLE dbo.#Tab2 (id int, name varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
В приведенном примере оператор SELECT отработает дважды: первый раз – внутри процедуры, второй раз – из вызывающего фрагмента кода (отмечен комментарием "№ 2").
Перед вторым вызовом процедуры удалим временную таблицу #ТаЬ2. Тогда одноименная временная таблица будет создана из хранимой процедуры:
DROP TABLE dbo.#Tab2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
В этом случае данные выведет только оператор SELECT, находящийся внутри процедуры (с комментарием "Ха 1"). Выполнение SELECT "№ 2" приведет к ошибке, так как созданная в хранимой процедуре временная таблица на момент возврата из процедуры будет уже удалена из базы tempdb.
Удалить хранимую процедуру можно с помощью оператора DROP PROCEDURE. Его формат представлен ниже. Одним оператором можно удалить несколько хранимых процедур, перечислив их через запятую:
DROP (PROC I PROCEDURE) { procedure } [
Например, удалим ранее созданную процедуру summa:
DROP PROC summa;
Внести изменения в существующую процедуру (а фактически – переопределить ее) можно с помощью оператора ALTER PROCEDURE (допу
стимо сокращение PROC). За исключением ключевого слова ALTER, формат оператора практически совпадает с форматом CREATE PROCEDURE. Например, изменим процедуру dbo. rownum, установив ей опцию выполнения в контексте безопасности владельца:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner – устанавливаемая опция
IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN SFirsYear AND SLastYear);
В некоторых случаях может возникнуть необходимость в динамическом формировании команды и выполнении ее на сервере БД. Эта задача также может решаться с помощью оператора ЕХЕС. В приведенном ниже примере выполняется выборка записей из таблицы Bookl по условию равенства атрибута Year значению, задаваемому с помощью переменной:
DECLARE 0у int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;
Выполнение динамически сформированных инструкций создает предпосылки для реализации компьютерных атак типа "SQL-инъекция" (англ. SQL injection). Суть атаки заключается в том, что нарушитель внедряет в динамически формируемый запрос собственный код на SQL. Обычно это происходит, когда подставляемые параметры берут из результатов ввода данных пользователем.
Несколько изменим предыдущий пример:
DECLARE 0у varchar(100);
SET 0у="2ООО"; – это мы получили от пользователя
Если предположить, что присваиваемое в операторе SET строковое значение мы получили от пользователя (неважно каким образом, например, через веб-приложение), то пример иллюстрирует "штатное" поведение нашего кода.
DECLARE 0у varchar(100);
SET 0у="2000; DELETE FROM dbo.Book2"; – инъекция
EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);
В рекомендуется по возможности использовать в подобных случаях системную хранимую процедуру sp_executcsql, которая позволяет контролировать тип параметров, что является одним из барьеров на пути SQL- инъекций. Не рассматривая в подробностях ее формат, разберем пример, аналогичный представленному ранее:
EXECUTE sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
Здесь явно указывается тип используемого в запросе параметра, и SQL Server при выполнении будет его контролировать. Буква "N" перед кавычками указывает, что это литерная константа в формате Unicode, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.
1. Включай в свои процедуры строку - SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
--Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO
2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
SELECT * FROM dbo.MyTable --Вот так делать хорошо
-- Вместо
SELECT * FROM MyTable --А так делать плохо
--Вызов процедуры
EXEC dbo.MyProc --Опять же хорошо
--Вместо
EXEC MyProc --Плохо!
3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")
5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server"ом появился более правильный и удобный способ решения этой проблемы:
BEGIN TRY
--код
END TRY
BEGIN CATCH
--код отлова ошибки
END CATCH
Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.
P.S.
Мой первый пост, не судите строго.
22 ответов
В моем опыте написания в основном приложений WinForms Client/Server это простые выводы, к которым я пришел:
Использовать хранимые процедуры:
- Для любой сложной работы с данными. Если вы собираетесь делать что-то действительно требующее таблиц курсора или temp, это, как правило, самый быстрый способ сделать это в SQL Server.
- Если вам нужно заблокировать доступ к данным. Если вы не предоставляете доступ к таблице пользователям (или роли или чему-либо еще), вы можете быть уверены, что единственный способ взаимодействия с данными - через создаваемый вами СП.
Использовать специальные запросы:
- Для CRUD, когда вам не нужно ограничивать доступ к данным (или делаете это по-другому).
- Для простых поисков. Создание SP для множества критериев поиска - это боль и сложность в обслуживании. Если вы можете создать достаточно быстрый поисковый запрос, используйте это.
В большинстве моих приложений я использовал как SP, так и ad-hoc sql, хотя я считаю, что я использую SP все меньше и меньше, поскольку они в конечном итоге являются кодом, как С#, только сложнее контролировать, тестировать и поддерживать. Я бы рекомендовал использовать ad-hoc sql, если вы не можете найти конкретную причину.
Хранимые процедуры представляют собой контракт на программное обеспечение, который инкапсулирует действия, предпринятые против базы данных. Код в процедурах и даже сама схема базы данных может быть изменен без влияния на скомпилированный, развернутый код, так что входы и выходы процедуры остаются неизменными.
Встраивая запросы в ваше приложение, вы тесно связываете себя с вашей моделью данных.
По той же причине также не является хорошей практикой просто создавать хранимые процедуры, которые являются запросами CRUD для каждой таблицы в вашей базе данных, так как это все еще тесно связано. Вместо этого процедуры должны быть громоздкими, крупнозернистыми.
Я думаю, что это основной конфликт между людьми, которые должны поддерживать базу данных и людей, которые разрабатывают пользовательские интерфейсы.
Как человек с данными, я бы не стал рассматривать работу с базой данных, к которой обращаются через adhoc-запросы, потому что их трудно эффективно настраивать или управлять. Как я могу узнать, что повлияет на изменение схемы? Кроме того, я не думаю, что пользователям следует предоставлять прямой доступ к таблицам базы данных по соображениям безопасности (и я имею в виду не только атаки SQL-инъекций, но также и потому, что это базовый внутренний элемент управления, который не допускает прямых прав и требует от всех пользователей используйте только procs, предназначенные для приложения, чтобы предотвратить возможное мошенничество. Любая финансовая система, которая позволяет напрямую вставлять, обновлять или удалять права на таблицы, имеет огромный риск для мошенничества. Это плохо.).
Базы данных не являются объектно-ориентированными, а код, который кажется хорошим из объектно-ориентированной точки зрения, может быть крайне плохим с точки зрения базы данных.
Наши разработчики сообщают нам, что они рады, что весь наш доступ к базам данных осуществляется через procs, потому что он значительно ускоряет исправление ошибки, зависящей от данных, а затем просто запускает proc в рабочей среде, а не создает новую ветвь кода и перекомпилировать и перезагрузить в производство. Мы требуем, чтобы все наши процессы были в подрывной деятельности, поэтому контроль источника не является проблемой вообще. Если он не находится в Subversion, он будет периодически удаляться dbas, поэтому нет никакого сопротивления использованию Source Control.
Хранимые процедуры, безусловно, подходят... они скомпилированы, имеют план выполнения перед началом работы, и вы можете заниматься управлением правами на них.
Я не понимаю эту проблему с исходным кодом на хранимой процедуре. Вы определенно можете их контролировать, если только вы немного дисциплинированы.
Всегда начинайте с файла.sql, который является источником хранимой процедуры. Поместите его в управление версиями после того, как вы написали свой код. В следующий раз, когда вы захотите отредактировать свою хранимую процедуру, вы получите ее из своего исходного элемента управления, чем ваша база данных. Если вы последуете этому, у вас будет такой же хороший источник управления, как и ваш код.
Я хотел бы процитировать Tom Kyte от Oracle здесь... Вот его правило о том, где писать код... хотя и немного несвязанный, но хорошо знаю, я думаю.
В нашем приложении есть слой кода, который предоставляет содержимое запроса (а иногда и вызов хранимой процедуры). Это позволяет нам:
- легко получить все запросы при управлении версиями
- чтобы сделать все изменения для каждого запроса для разных серверов баз данных
- исключает повторение одного и того же кода запроса через наш код
Контроль доступа реализуется в среднем слое, а не в базе данных, поэтому нам не нужны хранимые процедуры. Это в некотором роде средняя дорога между специальными запросами и хранимыми процедурами.
Существуют убедительные аргументы для обеих хранимых процедур, которые находятся в центральном репозитории, но (потенциально) трудно переносится, а специальные запросы легче отлаживать, поскольку они с вашим кодом, но они также могут быть сложнее найти в коде.
Аргумент, что хранимые процедуры более эффективны, больше не содержит воды. текст ссылки
Выполнение google для хранимой процедуры vs Dynamic Query покажет достойные аргументы в любом случае и, вероятно, лучше для вас принять ваше собственное решение...
Некоторые вещи, о которых нужно подумать: Кому нужны хранимые процедуры, Anyways?
Ясно, что это вопрос ваших собственных потребностей и предпочтений, но очень важно подумать о том, что при использовании специальных запросов в среде, ориентированной на общественность, есть безопасность. Всегда производите их параметризацию и следите за типичными уязвимостями, такими как SQL-инъекции .
Сохраненные процедуры великолепны, потому что они могут быть изменены без перекомпиляции. Я постараюсь использовать их как можно чаще.
Я использую ad-hoc для запросов, которые динамически генерируются на основе пользовательского ввода.
Procs по причинам, упомянутым другими, а также проще настроить proc с помощью профилировщика или частей proc. Таким образом, вам не нужно рассказывать кому-либо о запуске своего приложения, чтобы узнать, что отправляется на сервер SQL
Если вы используете ad-hoc-запросы, убедитесь, что они параметризированы
Параметрированный SQL или SPROC... не имеет значения с точки зрения производительности... вы можете запросить оптимизацию одного из них.
Для меня последнее оставшееся преимущество SPROC заключается в том, что я могу исключить много прав на управление правами SQL, только предоставляя свои права на вход для выполнения sprocs... если вы используете Parametized SQL, логин, связанный с вашей строкой подключения, имеет намного больше прав (запись любого вида оператора выбора на одну из таблиц, к которым у них есть доступ, например).
Я по-прежнему предпочитаю параметризованный SQL, хотя...
Аргумент производительности sproc является спорным - 3 верхних RDBM используют кэширование плана запросов и некоторое время. Его документально подтвердили... Или еще 1995 год?
Однако встраивание SQL в ваше приложение также является ужасным дизайном - обслуживание кода, по-видимому, является недостающей концепцией для многих.
Если приложение может начинаться с нуля с помощью ORM (приложения с зеленым полем далеки от нескольких!), это отличный выбор, поскольку модель вашего класса управляет вашей моделью БД и экономит время.
Если структура ORM недоступна, мы использовали гибридный подход создания XML файла SQL-ресурсов для поиска строк SQL по мере необходимости (они затем кэшируются инфраструктурой ресурсов). Если SQL нуждается в каких-либо незначительных манипуляциях, выполненных в коде, - если требуется большая манипуляция строкой SQL, мы переосмыслим этот подход.
Этот гибридный подход облегчает управление разработчиками (возможно, мы являемся меньшинством, поскольку моя команда достаточно ярка, чтобы читать план запроса), а развертывание - это простая проверка из SVN. Кроме того, он упрощает коммутацию RDBM - просто замените файл ресурсов SQL (не так просто, как инструмент ORM, конечно, но это работает с устаревшими системами или не поддерживаемой базой данных)
Мой опыт состоит в том, что 90% запросов и/или хранимых процедур вообще не должны записываться (по крайней мере, вручную).
Доступ к данным должен генерироваться как-то автоматически. Вы можете решить, хотите ли вы статически генерировать процедуры во время компиляции или динамически во время выполнения, но если вы хотите добавить столбец в таблицу (свойство объекта), вы должны изменить только один файл.
Я предпочитаю хранить все данные доступ в коде программы, в котором уровень доступа к данным выполняет прямые SQL-запросы. С другой стороны, логика управления , которую я поместил в базу данных в виде триггеров, хранимых процедур, пользовательских функций и еще чего-то. Примером того, что я считаю достойным базы данных, является генерация данных - предположим, что у нашего клиента есть имя FirstName и LastName. Теперь для пользовательского интерфейса требуется DisplayName, которое выводится из некоторой нетривиальной логики. Для этого поколения я создаю хранимую процедуру, которая затем запускается триггером всякий раз, когда обновляется строка (или другие исходные данные).
Похоже, что это несколько распространенное недоразумение, что уровень доступа к данным - это база данных, и все, что касается доступа к данным и данным, происходит именно там. Это просто неправильно, но я вижу много проектов, которые вытекают из этой идеи. Возможно, это локальный феномонон.
Включай в свои процедуры строку - SET NOCOUNT ON:
С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
Transact-SQL
Используй имя схемы с именем объекта:
Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
Transact-SQL
SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!
Не используй префикс «sp_» в имени своих хранимых процедур:
Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.
Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в в виде различных объектов. Другими словами, это объект, в котором содержатся SQL-инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого-либо другого раздела.
Введение
Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).
В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».
При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые удаление, исполнение, изменение. За все это отвечает 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. Хранимые процедуры в таком случае считаются локальными.
Таким образом, чтобы создать процедуру, нам нужно имя процедуры и, по меньшей мере, один параметр в качестве тела процедуры. Обратите внимание, что отличным вариантом в таком случае является создание и выполнение процедуры с именем схемы в классификаторе.
Тело процедуры может иметь любой вид из например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:
Тело не должно создавать какой-либо другой хранимой процедуры;
Тело не должно создать ложное представление об объекте;
Тело не должно создавать никаких триггеров.
2. Установка переменной в тело процедуры
Вы можете сделать переменные локальными для тела процедуры, и тогда они будут находиться исключительно внутри тела процедуры. Хорошей практикой является создание переменных в начале тела хранимой процедуры. Но также вы можете устанавливать переменные в любом месте в теле данного объекта.
Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.
Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.
В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, 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, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.