Хранимые процедуры 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, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.
Объявление процедуры
CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]
END
Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры
Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.
Вызов процедуры
Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:
EXECUTE PROCEDURE [(][)]
Примечание : вызов хранимых процедур может быть сделан из приложения, другой хранимой процедуры или в интерактивном режиме.
Пример объявления процедуры
CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру
Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.
Удаление и изменение процедур
Для удаления процедуры используется оператор:
Для изменения процедуры используется оператор:
ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]
END
Привилегии на выполнение процедур
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]
Системные процедуры
Многие СУБД (включая SQL Server) имеют определенный набор встроенных системных хранимых процедур, которые можно использовать в своих целях.
Хранимые процедуры
Предметом этой главы является один из наиболее мощных
инструментов, предлагаемых разработчикам приложений баз данных InterBase для
реализации бизнес-логики Хранимые процедуры (англ, stoied proceduies) позволяют
реализовать значительную часть логики приложения на уровне базы данных и таким
образом повысить производительность всего приложения, централизовать обработку
данных и уменьшить количество кода, необходимого для выполнения поставленных
задач Практически любое достаточно сложное приложение баз данных не обходится
без использования хранимых процедур.
Помимо этих широко известных
преимуществ использования хранимых процедур, общих для большинства реляционных
СУБД, хранимые процедуры InterBase могут играть роль практически полноценных
наборов данных, что позволяет использовать возвращаемые ими результаты в обычных
SQL-запросах.
Часто начинающие разработчики представляют себе хранимые
процедуры просто как набор специфических SQL-запросов, которые что-то делают
внутри базы данных, причем бытует мнение, что работать с хранимыми процедурами
намного сложнее, чем реализовать ту же функциональность в клиентском приложении,
на языке высокого уровня
Так что же такое хранимые процедуры в InterBase?
Хранимая процедура (ХП) - это часть метаданных базы данных, представляющая
собой откомпилированную во внутреннее представление InterBase подпрограмму,
написанную на специальном языке, компилятор которого встроен в ядро сервера
InteiBase
Хранимую процедуру можно вызывать из клиентских приложений, из
триггеров и других хранимых процедур. Хранимая процедура выполняется внутри
серверного процесса и может манипулировать данными в базе данных, а также
возвращать вызвавшему ее клиенту (т е триггеру, ХП, приложению) результаты
своего выполнения
Основой мощных возможностей, заложенных в ХП, является
процедурный язык программирования, имеющий в своем составе как модифицированные
предложения обычного SQL, такие, как INSERT, UPDATE и SELECT, так и средства
организации ветвлений и циклов (IF, WHILE), а также средства обработки ошибок и
исключительных ситуаций Язык хранимых процедур позволяет реализовать сложные
алгоритмы работы с данными, а благодаря ориентированности на работу с
реляционными данными ХП получаются значительно компактнее аналогичных процедур
на традиционных языках.
Надо отметить, что и для триггеров используется этот
же язык программирования, за исключением ряда особенностей и ограничений.
Отличия подмножества языка, используемого в триггерах, от языка ХП подробно
рассмотрены в главе "Триггеры" (ч 1).
Пример простой хранимой процедуры
Настало время создать первую хранимую процедуру и на ее примере
изучить процесс создания хранимых процедур. Но для начала следует сказать
несколько слов о том, как работать с хранимыми процедурами Дело в том, что своей
славой малопонятного и неудобного инструмента ХП обязаны чрезвычайно бедным
стандартным средствам разработки и отладки хранимых процедур. В документации по
InterBase рекомендуется создавать процедуры с помощью файлов SQL-скриптов,
содержащих текст ХП, которые подаются на вход интерпретатору isql, и таким
образом производить создание и модификацию ХП Если в этом SQL-скрипте на этапе
компиляции текста процедуры в BLR (о BLR см главу "Структура базы данных
InterBase" (ч. 4)) возникнет ошибка, то isql выведет сообщение о том, на какой
строке файла SQL-скрипта возникла эта ошибка. Исправляйте ошибку и повторяйте
все сначала. Про отладку в современном понимании этого слова, т. е. о
трассировке выполнения, с возможностью посмотреть промежуточные значения
переменных, речь вообще не идет. Очевидно, что такой подход не способствует
росту привлекательности хранимых процедур в глазах разработчика
Однако
помимо стандартного минималистского подхода к разработке ХП <_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксис хранимых процедур описывается следующим образом:
CREATE PROCEDURE name
[ (param
datatype [, param datatype ...]) ]
)]
AS
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var datatype;
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> =
(
Выглядит довольно объемно и может быть
даже громоздко, но на самом деле все очень просто Для того чтобы постепенно
освоить синтаксис, давайте будем рассматривать постепенно усложняющиеся примеры.
Итак, вот пример очень простой хранимой процедуры, которая принимает на
входе два числа, складывает их и возвращает полученный результат:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END
Как
видите, все просто: после команды CREATE PROCEDURE указывается имя вновь
создаваемой процедуры (которое должно быть уникальным в пределах базы данных) -
в данном случае SP_Add, затем в скобках через запятую перечисляются входные
параметры ХП - first_arg и second_arg - с указанием их типов.
Список входных
параметров является необязательной частью оператора CREATE PROCEDURE - бывают
случаи, когда все данные для своей работы процедура получает посредством
запросов к таблицам внутри тела процедуры.
В хранимых процедурах используются любые скалярные типы данных InteiBase He предусмотрено применение массивов и типов, определяемых пользователем, - доменов
Далее идет ключевое слово RETURNS, после которого в скобках
перечисляются возвращаемые параметры с указанием их типов - в данном случае
только один - Result.
Если процедура не должна возвращать параметры, то
слово RETURNS и список возвращаемых параметров отсутствуют.
После RETURNSQ
указано ключевое слово AS. До ключевого слова AS идет заголовок,
а после
него - течо
процедуры.
Тело хранимой процедуры представляет собой
перечень описаний ее внутренних (локальных) переменных (если они есть, подробнее
рассмотрим ниже), разделяемый точкой с запятой (;), и блок операторов,
заключенный в операторные скобки BEGIN END. В данном случае тело ХП очень
простое - мы просю складываем два входных аргумента и присваиваем их результат
выходному, а затем вызываем команду SUSPEND. Чуть позже мы разъясним суть
действия этой команды, а пока лишь отметим, что она нужна для передачи
возвращаемых параметров туда, откуда была вызвана хранимая процедура.
Разделители в хранимых процедурах
Обратите внимание, что оператор внутри процедуры заканчивается точкой с запятой (;). Как известно, точка с запятой является стандартным разделителем команд в SQL - она является сигналом интерпретатору SQL, что текст команды введен полностью и надо начинать его обрабатывать. Не получится ли так, что, обнаружив точку с запятой в середине ХП, интерпретатор SQL сочтет, что команда введена полностью и попытается выполнить часть хранимой процедуры? Это предположение не лишено смысла. Действительно, если создать файл, в который записать вышеприведенный пример, добавить команду соединения с базы данных и попытаться выполнить этот SQL-скрипт с помощью интерпретатора isql, то будет возвращена ошибка, связанная с неожиданным, по мнению интерпретатора, окончанием команды создания хранимой процедуры. Если создавать хранимые процедуры с помощью файлов SQL-скриптов, без использования специализированных инструментов разработчика InterBase, то необходимо перед каждой командой создания ХП (то же относи 1ся и к триггерам) менять разделитель команд скрипта на другой символ, отличный от точки с запятой, а после текста ХП восстанавливать его обратно. Команда isql, изменяющая разделитель предложений SQL, выглядит так:
SET TERM
Для типичного случая создания хранимой процедуры это выглядит так:
SET TERM ^;
CREATE
PROCEDURE some_procedure
... . .
END
^
SET TERM ;^
Вызов хранимой процедуры
Но вернемся к нашей хранимой процедуре. Теперь, когда она создана, ее надо как-то вызвать, передать ей параметры и получить возвращаемые результаты. Это сделать очень просто - достаточно написать SQL-запрос следующего вида:
SELECT *
FROM
Sp_add(181.35, 23.09)
Этот запрос вернет нам одну строку,
содержащую всего одно поле Result, в котором будет находиться сумма чисел 181.35
и 23.09 т. е. 204.44.
Таким образом, нашу процедуру можно использовать в
обычных SQL- запросах, выполняющихся как в клиентских программах, так и в других
ХП или триггерах. Такое использование нашей процедуры стало возможным из-за
применения команды SUSPEND в конце хранимой процедуры.
Дело в том, что в
InterBase (и во всех его клонах) существуют два типа хранимых процедур:
процедуры-выборки (selectable procedures) и исполняемые процедуры (executable
procedures). Отличие в работе этих двух видов ХП заключается в том, что
процедуры-выборки обычно возвращают множество наборов выходных параметров,
сгруппированных построчно, которые имеют вид набора данных, а исполняемые
процедуры мог)т либо вообще не возвращать параметры, либо возвращать только один
набор выходных параметров, перечисленных в Returns, где одну строку параметров.
Процедуры-выборки вызываются в запросах SELECT, а исполняемые процедуры - с
помощью команды EXECUTE PROCEDURE.
Оба вида хранимых процедур имеют
одинаковый синтаксис создания и формально ничем не отличаются, поэтому любая
исполнимая процедура может быть вызвана в SELECT-запросе и любая
процедура-выборка - с помощью EXECUTE PROCEDURE. Вопрос в том, как поведут себя
ХП при разных типах вызова. Другими словами, разница заключается в
проектировании процедуры для определенного типа вызова. То есть
процедура-выборка специально создается для вызова из запроса SELECT, а
исполняемая процедура - для вызова с использованием EXECUTE PROCEDURE. Давайте
рассмотрим, в чем же заключаются отличия при проектировании этих двух видов ХП.
Для того чтобы понять, как работает процедура-выборка, придется немного
углубиться в теорию. Давайте представим себе обычный SQL-запрос вида SELECT ID,
NAME FROM Table_example. В результате его выполнения мы получаем на выходе
таблицу, состоящую из двух столбцов (ID и NAME) и некоторого количества строк
(равного количеству строк в таблице Table_example). Возвращаемая в результате
этого запроса таблица называется также набором данных SQL Задумаемся же, как
формируется набор данных во время выполнения этого запроса Сервер, получив
запрос, определяет, к каким таблицам он относится, затем выясняет, какое
подмножество записей из этих таблиц необходимо включить в результат запроса.
Далее сервер считывает каждую запись, удовлетворяющую результатам запроса,
выбирает из нее нужные поля (в нашем случае это ID и NAME) и отсылает их
клиенту. Затем процесс повторяется снова - и так для каждой отобранной записи.
Все это отступление нужно для того, чтобы уважаемый читатель понял, что все
наборы данных SQL формируются построчно, в том числе и в хранимых процедурах! И
основное отличие процедур-выборок от исполняемых процедур в том, что первые
спроектированы для возвращения множества строк, а вторые - только для одной.
Поэтому они и применяются по-разному: процедура-выборка вызывается при помощи
команды SELECT, которая "требует" от процедуры отдать все записи, которая она
может вернуть. Исполняемая процедура вызывается с помощью EXECUTE PROCEDURE,
которая "вынимает" из ХП только одну строку, а остальные (даже если они есть!)
игнорирует.
Давайте рассмотрим пример процедуры-выборки, чтобы было
понятнее. Для > прощения создадим хранимую процедуру, которая работает точно
так же, как запрос SELECT ID, NAME FROM Table_Example, т е она просто делает
выборку полей ID и NAME из всей таблицы. Вот этот пример:
CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM
table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END
Давайте разберем действия этой процедуры, названной Simple_Select_SP. Как видите, она не имеет входных параметров и имеет два выходных параметра - ID и NAME. Самое интересное, конечно, заключено в теле процедуры. Здесь использована конструкция FOR SELECT:
FOR
SELECT ID, NAME
FROM table_example
INTO:procID,
:procNAME
DO
BEGIN
/*что-то делаем с переменными procID и procName*/
END
Этот кусочек кода
означает следующее: для каждой строки, выбранной из таблицы Table_example,
поместить выбранные значения в переменные procID и procName, а затем произвести
какие-то действия с этими переменными.
Вы можете сделать удивленное лицо и
спросить: "Переменные? Какие еще переменные 9 " Это нечто вроде
сюрприза этой главы - то, что в хранимых процедурах мы можем использовать
переменные. В языке ХП можно объявлять как собственные локальные переменные
внутри процедуры, так и использовать входные и выходные параметры в качестве
переменных.
Для того чтобы объявить локальную переменную в хранимой
процедуре, необходимо поместить ее описание после ключевого слова AS и до
первого слова BEGIN Описание локальной переменной выглядит так:
DECLARE VARIABLE
Например, чтобы объявить целочисленную локальную переменную Mylnt, нужно вставить между AS и BEGIN следующее описание
DECLARE VARIABLE Mylnt INTEGER;
Переменные в нашем примере начинаются с двоеточия. Это сделано потому,
что обращение к ним идет внутри SQL-команды FOR SELECT, поэтому для различения
полей в таблицах, которые используются в SELECT, и переменных необходимо
предварять последние двоеточием. Ведь переменные могут иметь точно такое же
название, как и поля в таблицах!
Но двоеточие перед именем переменной
необходимо использовать только внутри SQL-запросов. Вне текстов обращение к
переменной делается без двоеточия, например:
procName="Some name";
Но вернемся к телу нашей
процедуры. Предложение FOR SELECT возвращает данные не в виде таблицы - набора
данных, а по одной строчке. Каждое возвращаемое поле должно быть помещено в свою
переменную: ID => procID, NAME => procName. В части DO эти переменные
посылаются клиенту, вызвавшем) процед>р>, с помощью команды SUSPEND
Таким образом, команда FOR SELECT... DO организует цикл по записям,
выбираемым в части SELECT этой команды. В теле цикла, образуемого частью DO,
выполняется передача очередной сформированной записи клиенту с помощью команды
SUSPEND.
Итак, процедура-выборка предназначена для возвращения одной или
более строк, для чего внутри тела ХП организуется цикл, заполняющий
результирующие параметры-переменные. И в конце тела этого цикла обязательно
стоит команда SUSPEND, которая вернет очередную строку данных клиенту.
Циклы и операторы ветвления
Помимо команды FOR SELECT... DO, организующей цикл по записям какой-либо выборки, существует другой вид цикла - WHILE...DO, который позволяет организовать цикл на основе проверки любых условий. Вот пример ХП, использующей цикл WHILE.. DO. Эта процедура возвращает квадраты целых чисел от 0 до 99:
CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I
INTEGER;
BEGIN
I
= 1;
WHILE (i<100) DO
BEGIN
QUADRAT= I*I;
I=I+1;
SUSPEND;
END
END
В
результате выполнения запроса SELECT FROM QUAD мы получим таблицу, содержащую
один столбец QUADRAT, в котором будут квадраты целых чисел от 1 до 99
Помимо
перебора результатов SQL-выборки и классического цикла, в языке хранимых
процедур используется оператор IF...THEN..ELSE, позволяющий организовать
ветвление в зависимости от выполнения каких-либо \словий Его синтаксис похож на
большинство операторов ветвления в языках программирования высокого уровня,
вроде Паскаля и Си.
Давайте рассмотрим более сложный пример хранимой
процедуры, которая делает следующее.
- Вычисляет среднюю цену в таблице Table_example (см. глава "Таблицы Первичные ключи и генераторы")
- Далее для каждой записи в таблице делает след>ющ)ю проверку, если существующая цена (PRICE) больше средней цены, то устанавливает цену, равную величине средней цены, плюс задаваемый фиксированный процент
- Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой.
- Возвращает все измененные строки в таблице.
Для начала определим имя ХП, а также входные и выходные параметры Все это прописывается в заголовке хранимой процедуры
CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION) AS
Процедура будет называться
IncreasePrices, у нее один входной параметр Peiceni21nciease, имеющий тип DOUBLE
PRECISION, и 3 выходных параметра - ID, NAME и new_pnce. Обратите внимание, что
первые два выходных параметра имеют такие же имена, как и поля в таблице
Table_example, с которой мы собираемся работать Это допускается правилами языка
хранимых процедур.
Теперь мы должны объявить локальную переменную, которая
будет использоваться для хранения среднего значения Эго объявление будет
выглядеть следующим образом:
DECLARE VARIABLE avg_price DOUBLE PRECISION;
Теперь перейдем к телу хранимой
процедуры Откроем тело ХП
ключевым словом BEGIN.
Сначала нам
необходимо выполнить первый шаг нашего алгоритма - вычислить среднюю цену. Для
этого мы воспользуемся запросом следующего вида:
SELECT AVG(Price_l)
FROM
Table_Example
INTO:avg_price,-
Этот запрос использует агрегатную функцию AVG, которая возвращает
среднее значение поля PRICE_1 среди отобранных строк запроса - в нашем случае
среднее значение PRICE_1 по всей таблице Table_example. Возвращаемое запросом
значение помещается в переменную avg_price. Обратите внимание, что переменная
avg_pnce предваряется двоеточием -для того, чтобы отличить ее от полей,
используемых в запросе.
Особенностью данного запроса является то, что он
всегда возвращает строго одну-единственную запись. Такие запросы называются
singleton-запросами И только такие выборки можно использовать в хранимых
процедурах. Если запрос возвращает более одной строки, то его необходимо
оформить в виде конструкции FOR SELECT...DO, которая организует цикл для
обработки каждой возвращаемой строки
Итак, мы получили среднее значение
цены. Теперь необходимо пройтись по всей таблице, сравнить значение цены в
каждой записи со средней ценой и предпринять соответствующие действия
С
начала opганизуем перебор каждой записи из таблицы Table_example
FOR
SELECT ID, NAME,
PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_здесь оОрсшатыьаем каждую запись*/
END
При выполнении этой конструкции из таблицы
Table_example построчно будут выниматься данные и значения полей в каждой строке
будут присвоены переменным ID, NAME и new_pnce. Вы, конечно, помните, что эти
переменные объявлены как выходные параметры, но беспокоиться, что выбранные
данные будут возвращены как результаты, не стоит: тот факт, что выходным
параметрам что-либо присвоено, не означает, что вызывающий ХП клиент немедленно
получит эти значения! Передача параметров осуществляется только при исполнении
команды SUSPEND, а до этого мы можем использовать выходные параметры в качестве
обычных переменных - в нашем примере мы именно так и делаем с параметром
new_price.
Итак, внутри тела цикла BEGIN.. .END мы можем обработать значения
каждой строки. Как вы помните, нам необходимо выяснить, как существующая цена
соотносится со средней, и предпринять соответствующие действия. Эту процедуру
сравнения мы реализуем с помощью оператора IF:
IF
(new_price > avg_price) THEN /*если существующая цена больше средней
цены*/
BEGIN
/*то
установим новую цену, равную величине средней цены, плюс фиксированный процент
*/
new_price = (avg_price +
avg_price*(Percent2Increase/100));
UPDATE
Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена
меньше или равна средней цене, то установим цену, равную прежней цене, плюс
половина разницы между прежней и средней ценой */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET
PRICE_1 = :new_price
WHERE ID = .ID;
END
Как видите, получилось достаточно
большая конструкция IF, в которой трудно было бы разобраться, если бы не
комментарии, заключенные в символы /**/.
Для того чтобы изменить цену в
соответствии с вычисленной разницей, мы воспользуемся оператором UPDATE, который
позволяет модифицировать существующие записи - одну или несколько. Для того
чтобы однозначно указать, в какой записи нужно изменять цену, мы используем в
условии WHERE поле первичного ключа, сравнивая его со значением переменной, в
которой хранится значение ID для текущей записи: ID=:ID. Обратите внимание, что
переменная ID предваряется двоеточием.
После выполнения конструкции
IF...THEN...ELSE в переменных ID, NAME и new_price находятся данные, которые мы
должны возвратить клиент\, вызвавшему процедуру. Для этого после IF необходимо
вставить команду SUSPEND, которая перешлет данные туда, откуда вызвали ХП На
время пересылки действие процедуры будет приостановлено, а когда от ХП
потребуется новая запись, то она будет вновь продолжена, - и так будет
продолжаться до тех пор, пока FOR SELECT...DO не переберет все записи своего
запроса.
Надо отметить, что помимо команды SUSPEND, которая только
приостанавливает действие хранимой процедуры, существует команда EXIT, которая
прекращает хранимую процедуру после передачи строки. Однако командой EXIT
пользуются достаточно редко, поскольку она нужна в основном для того, чтобы
прервать цикл при достижении какого-либо условия
При этом в случае, когда
процедура вызывалась оператором SELECT и завершена по EXIT, последняя
извлеченная строка не будет возвращена. То есть, если вам нужно прервать
процедуру и все-таки >получить эту строку, надо воспользоваться
последовательностью
SUSPEND;
EXIT;
Основное назначение EXIT - получение
singleton-наборов данных, возвращаемых параметров путем вызова через EXECUTE
PROCEDURE. В этом случае устанавливаются значения выходных параметров, но из них
не формируется набор данных SQL, и выполнение процедуры завершается.
Давайте
запишем текст нашей хранимой процедуры полностью, чтобы иметь возможность
охватить ее логику одним взглядом:
CREATE PROCEDURE
IncreasePrices (
Percent2Increase DOUBLE
PRECISION)
RETURNS (ID INTEGER, NAME
VARCHAR(80),
new_price DOUBLE PRECISION)
AS
DECLARE VARIABLE avg_price DOUBLE
PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM
Table_Example
INTO:avg_price;
FOR
SELECT ID, NAME,
PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*здесь обрабатываем каждую запись*/
IF (new_pnce > avg_price) THEN /*если существующая цена больше
средней цены*/
BEGIN
/*установим новую цену, равную величине средней цены, плюс
фиксированный процент */
new_price = (avg_price +
avg_price*(Percent2lncrease/100));
UPDATE
Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена
меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс
половина разницы между прежней и средней ценой */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET
PRICE_1 = :new_price
WHERE ID = :ID;
END
SUSPEND;
END
END
Данный пример хранимой процедуры иллюстрирует применение основных конструкций языка хранимых процедур и триггеров. Далее мы рассмотрим способы применения хранимых процедур для решения некоторых часто возникающих задач.
Рекурсивные хранимые процедуры
Хранимые процедуры InterBase могут быть рекурсивными. Это
означает, что из хранимой процедуры можно вызвать саму себя. Допускается до 1000
уровней вложенности хранимых процедур, однако надо помнить о том, что свободные
ресурсы на сервере могут закончиться раньше, чем будет достигнута максимальная
вложенность ХП.
Одно из распространенных применений хранимых процедур - это
обработка древовидных структур, хранящихся в базе данных. Деревья часто
используются в задачах состава изделия, складских, кадровых и в других
распространенных приложениях.
Давайте рассмотрим пример хранимой процедуры,
которая выбирает все товары определенного типа, начиная с определенного уровня
вложенности.
Пусть у нас есть следующая постановка задачи: имеем справочник
товаров с иерархической структурой такого вида:
Товары
- Бытовая техника
- Холодильники
-
Трехкамерные
- Двухкамерные
- Однокамерные
-
Стиральные машины
- Вертикальные
- Фронтальные
- Классические
- Узкие
- Компьютерная техника
....
Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:
Стиральные машины - Вертикальные
Стиральные машины - Фронтальные Классические
Стиральные машины - Фронтальные Узкие
Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:
CREATE TABLE GoodsTree
(ID_GOOD
INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key (ID_GOOD));
Создаем одну таблицу GoodsTree, в которой всего 3 поля: ID_GOOD - умн кальный идентификатор категории, ID_PARENT_GOOD - идентификатор кшс гории-родителя для данной категории и GOOD_NAME - наименование катсш- рии. Чтобы обеспечить целостность данных в этой таблице, наложим на эту таблиц} ограничение внешнего ключа:
ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)
Таблица ссылается
сама на себя и данный внешний ключ следит за тем. чтобы в таблице не было ссылок
на несуществующих родителей, а также препятствует попыткам удалить категории
товаров, у которых есть потомки.
Давайте занесем в нашу таблицу следующие
данные:
| ID_GOOD
1
|
ID_PARENT_GOOD
0
|
GOOD_NAME
GOODS
|
Теперь, когда у нас есть место для хранения данных, мы можем
приступить к созданию хранимой процедуры, выполняющей вывод всех "окончательных"
категорий товаров в "развернутом" виде - например, для категории "Трехкамерные"
полное имя категории будет выглядеть как "Бытовая техника Холодильники
Трехкамерные".
В хранимых процедурах, обрабатывающих древообразные
структуры, сложилась своя терминология. Каждый элемент дерева называются узлом;
а отношения между ссылающимися друг на друга узлами называется отношениями
родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков,
называются "листьями".
У кашей хранимой процедуры входным параметром будет
идентификатор категории, начиная с которого мы должны будем начать развертку.
Хранимая процедура будет иметь следующий вид:
CREATE
PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0рганизуем
внешний цикл FOR SELECT по непосредственным потомкам товара с
ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good,
gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Проверка с помощью функции EXISTS, которая возвращает TRUE, если
запрос в скобках вернет хотя бы одну строку. Если у найденного узла с
ID_PARENT_GOOD = ID_CHILD_GOOD нет потомков, то он является "листом" дерева и
попадает в результаты */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Передаем "лист" дерева в результаты */
SUSPEND;
END
ELSE
/* Для узлов, у которых есть
потомки*/
BEGIN
/*сохраняем имя узла-родителя во временной переменной */
CURR_CHILD_NAME=full_goods_name;
/* рекурсивно запускаем эту процедуру */
FOR
SELECT ID_CHILD_GOOD,
full_goods_name
FROM GETFULLNAME
(:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD,
:full_goods_name
DO BEGIN
/*добавляем лмя узла-родителя к найденном., имени потомка с
помощью операции конкатенации строк || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* возвращаем полное имя товара*/
END
END
END
END
Если мы выполним данную процедуру с входным параметром ID_GOOD2SHOW= 1, то получим следующее:
Как видите, с помощью рекурсивной хранимой процедуры мы прошлись по всему дереву категорий и вывели полное наименование категорий-"листьев", которые находятся на самых кончиках ветвей.
Заключение
На этом закончим рассмотрение основных возможностей языка хранимых
процедур. Очевидно, что полностью освоить разработку хранимых процедур при
чтении одной главы невозможно, однако здесь мы постарались представить и
объяснить основные концепции, связанные с хранимыми процедурами. Описанные
конструкции и приемы проектирования ХП могут быть применены в большинстве
приложений баз данных
Часть важных вопросов, связанных с разработкой
хранимых процедур, будет раскрыта в следующей главе - "Расширенные возможности
языка хранимых процедур InterBase", которая посвящена обработке исключений,
разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.
Хранимая процедура - это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.
Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.
При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.
Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.
Хранимые процедуры можно также использовать для следующих целей:
для создания журнала логов о действиях с таблицами баз данных.
Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.
Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.
Создание и исполнение хранимых процедур
Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE , которая имеет следующий синтаксис:
CREATE PROC proc_name [({@param1} type1 [ VARYING] [= default1] )] {, …} AS batch | EXTERNAL NAME method_name Соглашения по синтаксису
Параметр schema_name определяет имя схемы, которая назначается владельцем созданной хранимой процедуры. Параметр proc_name определяет имя хранимой процедуры. Параметр @param1 является параметром процедуры (формальным аргументом), чей тип данных определяется параметром type1. Параметры процедуры являются локальными в пределах процедуры, подобно тому, как локальные переменные являются локальными в пределах пакета. Параметры процедуры - это значения, которые передаются вызывающим объектом процедуре для использования в ней. Параметр default1 определяет значение по умолчанию для соответствующего параметра процедуры. (Значением по умолчанию также может быть NULL.)
Опция OUTPUT указывает, что параметр процедуры является возвращаемым, и с его помощью можно возвратить значение из хранимой процедуры вызывающей процедуре или системе.
Как уже упоминалось ранее, предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Если же по каким-либо причинам хранимую процедуру требуется компилировать при каждом ее вызове, при объявлении процедуры используется опция WITH RECOMPILE . Использование опции WITH RECOMPILE сводит на нет одно из наиболее важных преимуществ хранимых процедур: улучшение производительности благодаря одной компиляции. Поэтому опцию WITH RECOMPILE следует использовать только при частых изменениях используемых хранимой процедурой объектов базы данных.
Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.
По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE .
В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:
USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;
Как говорилось ранее, для разделения двух пакетов используется инструкция GO . Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.
Хранимые процедуры могут обращаться к несуществующим таблицам. Это свойство позволяет выполнять отладку кода процедуры, не создавая сначала соответствующие таблицы и даже не подключаясь к конечному серверу.
В отличие от основных хранимых процедур, которые всегда сохраняются в текущей базе данных, возможно создание временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Одним из поводов для создания временных хранимых процедур может быть желание избежать повторяющегося исполнения определенной группы инструкций при соединении с базой данных. Можно создавать локальные или глобальные временные процедуры. Для этого имя локальной процедуры задается с одинарным символом # (#proc_name), а имя глобальной процедуры - с двойным (##proc_name).
Локальную временную хранимую процедуру может выполнить только создавший ее пользователь и только в течение соединения с базой данных, в которой она была создана. Глобальную временную процедуру могут выполнять все пользователи, но только до тех пор, пока не завершится последнее соединение, в котором она выполняется (обычно это соединение создателя процедуры).
Жизненный цикл хранимой процедуры состоит из двух этапов: ее создания и ее выполнения. Каждая процедура создается один раз, а выполняется многократно. Хранимая процедура выполняется посредством инструкции EXECUTE пользователем, который является владельцем процедуры или обладает правом EXECUTE для доступа к этой процедуре. Инструкция EXECUTE имеет следующий синтаксис:
[] [@return_status =] {proc_name | @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT}.. Соглашения по синтаксису
За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.
Предложение DEFAULT предоставляет значения по умолчанию для параметра процедуры, которое было указано в определении процедуры. Когда процедура ожидает значение для параметра, для которого не было определено значение по умолчанию и отсутствует параметр, либо указано ключевое слово DEFAULT, то происходит ошибка.
Когда инструкция EXECUTE является первой инструкцией пакета, ключевое слово EXECUTE можно опустить. Тем не менее будет надежнее включать это слово в каждый пакет. Использование инструкции EXECUTE показано в примере ниже:
USE SampleDb; EXECUTE IncreaseBudget 10;
Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.
В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:
Процедура ModifyEmpId в примере иллюстрирует использование хранимых процедур, как часть процесса обеспечения ссылочной целостности (в данном случае между таблицами Employee и Works_on). Подобную хранимую процедуру можно использовать внутри определения триггера, который собственно и обеспечивает ссылочную целостность.
В примере ниже показано использование в хранимой процедуре предложения OUTPUT:
Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Удалено сотрудников: " + convert(nvarchar(30), @quantityDeleteEmployee);
Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.
Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.
Предложение WITH RESULTS SETS инструкции EXECUTE
В SQL Server 2012 для инструкции EXECUTE вводится предложение WITH RESULTS SETS , посредством которого при выполнении определенных условий можно изменять форму результирующего набора хранимой процедуры.
Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:
Процедура EmployeesInDept - это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:
USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Фамилия] CHAR(20) NOT NULL));
Результат выполнения хранимой процедуры, вызванной таким способом, будет следующим:
Как можно видеть, запуск хранимой процедуры с использованием предложения WITH RESULT SETS в инструкции EXECUTE позволяет изменить наименования и тип данных столбцов результирующего набора, выдаваемого данной процедурой. Таким образом, эта новая функциональность предоставляет большую гибкость в исполнении хранимых процедур и помещении их результатов в новую таблицу.
Изменение структуры хранимых процедур
Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.
Компонент Database Engine поддерживает тип данных CURSOR . Этот тип данных используется для объявления курсоров в хранимых процедурах. Курсор - это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.
Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE . Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.
Хранимые процедуры и среда CLR
SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.
Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure , которая запускается на выполнение инструкцией RECONFIGURE . В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:
USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:
Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.
Используя инструкцию CREATE ASSEMBLY , создать соответствующий выполняемый файл.
Выполнить процедуру, используя инструкцию EXECUTE.
На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.
Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.
В примере ниже показан исходный код хранимой процедуры на языке C#:
Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures { public static int CountEmployees() { int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; } }
В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure , который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection . Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd , которому передается нужная SQL-команда.
В следующем фрагменте кода:
Cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee";
используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.
Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).
USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE
Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM {dll_file} Соглашения по синтаксису
В параметре assembly_name указывается имя сборки. В необязательном предложении AUTHORIZATION указывается имя роли в качестве владельца этой сборки. В предложении FROM указывается путь, где находится загружаемая сборка.
Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.
Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.
Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.
В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:
USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees
Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:
assembly_name.class_name.method_name
assembly_name - указывает имя сборки;
class_name - указывает имя общего класса;
method_name - необязательная часть, указывает имя метода, который задается внутри класса.
Выполнение процедуры CountEmployees показано в примере ниже:
USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Вернет 7
Инструкция PRINT возвращает текущее количество строк в таблице Employee.
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-инъекции .
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, которое выводится из некоторой нетривиальной логики. Для этого поколения я создаю хранимую процедуру, которая затем запускается триггером всякий раз, когда обновляется строка (или другие исходные данные).
Похоже, что это несколько распространенное недоразумение, что уровень доступа к данным - это база данных, и все, что касается доступа к данным и данным, происходит именно там. Это просто неправильно, но я вижу много проектов, которые вытекают из этой идеи. Возможно, это локальный феномонон.
Я могу просто отключить идею SP, увидев так много плохо спроектированных. Например, в одном проекте, в котором я участвовал, использовался набор хранимых процедур CRUD для каждой таблицы и каждый возможный запрос, с которым они столкнулись. При этом они просто добавили еще один совершенно бессмысленный слой. Больно даже думать о таких вещах.