Типы хранимых процедур

В SQL Server имеется несколько типов хранимых процедур .

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
• Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

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

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

<определение_процедуры>::= {CREATE | ALTER } имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

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

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

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

Ключевое слово VARYING применяется совместно с

Включай в свои процедуры строку - 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», как показано ниже.

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

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

1. Вычисляет среднюю цену в таблице Table_example (см. глава "Таблицы Первичные ключи и генераторы")
2. Далее для каждой записи в таблице делает след>ющ)ю проверку, если существующая цена (PRICE) больше средней цены, то устанавливает цену, равную величине средней цены, плюс задаваемый фиксированный процент
3. Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой.
4. Возвращает все измененные строки в таблице.

Для начала определим имя ХП, а также входные и выходные параметры Все это прописывается в заголовке хранимой процедуры

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)

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

Теперь, когда у нас есть место для хранения данных, мы можем приступить к созданию хранимой процедуры, выполняющей вывод всех "окончательных" категорий товаров в "развернутом" виде - например, для категории "Трехкамерные" полное имя категории будет выглядеть как "Бытовая техника Холодильники Трехкамерные".
В хранимых процедурах, обрабатывающих древообразные структуры, сложилась своя терминология. Каждый элемент дерева называются узлом; а отношения между ссылающимися друг на друга узлами называется отношениями родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков, называются "листьями".
У кашей хранимой процедуры входным параметром будет идентификатор категории, начиная с которого мы должны будем начать развертку. Хранимая процедура будет иметь следующий вид:

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", которая посвящена обработке исключений, разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.

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

Введение

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

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

За

• Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
• Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
• Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
• Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

• Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
• Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
• Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
• Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

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

Шаг 1: Ставим ограничитель

Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.

Шаг 2: Как работать с хранимыми процедурами

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

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//

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

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

4 характеристики хранимой процедуры:

• Language: в целях обеспечения переносимости, по умолчанию указан SQL.
• Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
• SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
• Comment: в целях документирования, значение по умолчанию - ""

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

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);

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

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Шаг 3: Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

• CREATE PROCEDURE proc1 (): пустой список параметров
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

Естественно, вы можете задавать несколько параметров разных типов.

Пример параметра IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//

Пример параметра OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //

Пример параметра INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.

Синтаксис объявления переменной выглядит так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Давайте объявим несколько переменных:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //

Шаг 5: Структуры управления потоками

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //

Конструкция CASE

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

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

Конструкция WHILE

Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Шаг 6: Курсоры

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

MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.

DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/

В этом примере мы проведем кое-какие простые операции с использованием курсора:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //

У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:

• Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
• Доступен только для чтения: курсоры нельзя изменять.
• Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.

Заключение

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

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

1. Изучить операторы описания хранимых процедур и принципы передачи их входных и выходных параметров.
2. Изучить порядок создания и отладки хранимых процедур на сервере MS SQL Server 2000.
3. Разработать пять базовых хранимых процедур для учебной базы данных «Библиотека».
4. Подготовить отчет о проделанной работе в электронном виде.

1. Общие сведения о хранимых процедурах

Хранимая процедура (Stored Procedure) — это набор команд, хранимый на сервере и выполняемый как единое целое. Хранимые процедуры являются механизмом, с помощью которого можно создавать подпрограммы, работающие на сервере и управляемые его процессами. Подобные подпрограммы могут быть активизированы вызывающим их приложением. Кроме того, они могут быть вызваны правилами, поддерживающими целостность данных, или триггерами.

Хранимые процедуры могут возвращать значения. В процедуре можно выполнять сравнение вводимых пользователем значений с заранее установленной в системе информацией. Хранимые процедуры применяют в работе мощные аппаратные решения SQL Server. Они ориентированы на базы данных и тесно взаимодействуют с оптимизатором SQL Server. Это позволяет получить высокую производительность при обработке данных.

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

Хранимые процедуры бывают двух типов: обычные и расширенные . Обычные хранимые процедуры представляют собой набор команд на Transact-SQL, в то время как расширенные хранимые процедуры представлены в виде динамических библиотек (DLL). Такие процедуры, в отличие от обычных, имеют префикс xp_ . Сервер имеет стандартный набор расширенных процедур, но пользователи могут писать и свои процедуры на любом языке программирования. Главное при этом — использовать интерфейс программирования SQL Server Open Data Services API . Расширенные хранимые процедуры могут находиться только в базе данных Master .

Обычные хранимые процедуры также можно разделить на два типа: системные и пользовательские . Системные процедуры — это стандартные процедуры, служащие для работы сервера; пользовательские — любые процедуры, созданные пользователем.

1.1. Преимущества хранимых процедур

В самом общем случае хранимые процедуры обладают следующими преимуществами:

• Высокая производительность. Является результатом расположения хранимых процедур на сервере. Сервер, как правило, — более мощная машина, поэтому время выполнения процедуры на сервере значительно меньше, чем на рабочей станции. Кроме того, информация из базы данных и хранимая процедура находятся в одной и той же системе, поэтому на передачу записей по сети время практически не затрачивается. Хранимые процедуры имеют непосредственный доступ к базам данных, что делает работу с информацией очень быстрой.
• Преимущество разработки системы в архитектуре «клиент-сервер». Заключается в возможности раздельного создания программного обеспечения клиента и сервера. Это преимущество является ключевым при разработке, и благодаря ему можно значительно уменьшить время, необходимое для окончания проекта. Код, работающий на сервере, может разрабатываться отдельно от кода клиентской части. При этом компоненты серверной части могут совместно использоваться компонентами стороны клиента.
• Уровень безопасности. Хранимые процедуры могут выступать в качестве инструмента улучшения безопасности. Можно создать хранимые процедуры, осуществляющие операции добавления, изменения, удаления и отображения списков, и, таким образом, получить контроль над каждым из аспектов доступа к информации.
• Усиление правил сервера, работающих с данными. Это одна из самых важных причин применения интеллектуального ядра баз данных. Хранимые процедуры позволяют применять правила и другую логику, помогающую контролировать вводимую в систему информацию.

Хотя язык SQL определен как непроцедурный, в SQL Server применяются ключевые слова, связанные с управлением ходом выполнения процедур. Такие ключевые слова используются при создании процедур, которые можно сохранять для последующего выполнения. Хранимые процедуры могут быть применены вместо программ, созданных с помощью стандартных языков программирования (например, С или Visual Basic) и выполняющих операции в базе данных SQL Server.

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

Откомпилированные хранимые процедуры могут значительно улучшить производительность системы. Стоит, однако, отметить, что статистика данных с момента создания процедуры до момента ее выполнения может устареть, а индексы могут стать неэффективными. И хотя можно обновить статистику и добавить новые, более эффективные индексы, план выполнения процедуры уже составлен, то есть процедура скомпилирована, и в результате способ доступа к данным может перестать быть эффективным. Поэтому имеется возможность проводить перекомпиляцию процедур при каждом вызове.

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

Хранимые процедуры могут быть выполнены либо на локальной машине, либо на удаленной системе SQL Server. Это дает возможность активизировать процессы на других машинах и работать не только с локальными базами данных, но и с информацией на нескольких серверах.

Прикладные программы, написанные на одном из языков высокого уровня, таком как С или Visual Basic .NET, также могут вызывать хранимые процедуры, что обеспечивает оптимальное решение по распределению нагрузки между программным обеспечением клиентской части и SQL-сервера.

1.2. Создание хранимых процедур

Для создания хранимой процедуры применяется инструкция Create Procedure . Имя хранимой процедуры может быть длиной до 128 символов, включая символы # и ## . Синтаксис определения процедуры:

CREATE PROC имя_процедуры [; число]
[{@параметр тип_данных} [= значение_по_умолчанию] ] [,...n]

AS
<Инструкции_SQL>

Рассмотрим параметры этой команды:

• Имя_процедуры — имя процедуры; должно удовлетворять правилам для идентификаторов: его длина не может превышать 128 символов; для локальных временных процедур перед именем используется знак #, а для глобальных временных процедур — знаки ##;
• Число — необязательное целое число, используемое для группировки нескольких процедур под одним именем;
• @параметр тип_данных — список имен параметров процедуры с указанием соответствующего типа данных для каждого; таких параметров может быть до 2100. В качестве значения параметра разрешается передавать NULL . Могут использоваться все типы данных за исключением типов text , ntext и image . В качестве выходного параметра (ключевое слово OUTPUT или VARYING ) можно использовать тип данных Cursor . Параметры с типом данных Cursor могут быть только выходными параметрами;
• VARYING — ключевое слово, определяющее, что в качестве выходного параметра используется результирующий набор (используется только для типа Cursor );
• OUTPUT — говорит о том, что указанный параметр может быть использован как выходной;
• значение_по_умолчанию — используется в случае, когда при вызове процедуры параметр пропущен; должно быть константой и может включать символы маски (% , _ , [ , ] , ^ ) и значение NULL ;
• WITH RECOMPILE — ключевые слова, показывающие, что SQL Server не будет записывать план процедуры в кэш, а будет создавать его каждый раз при выполнении;
• WITH ENCRYPTION — ключевые слова, показывающие, что SQL Server будет зашифровывать процедуру перед записью в системную таблицу Syscomments . Для того чтобы текст зашифрованных процедур было невозможно восстановить, необходимо после шифрования удалить соответствующие им кортежи из таблицы syscomments;
• FOR REPLICATION — ключевые слова, показывающие, что эта процедура создается только для репликации. Эта опция несовместима с ключевыми словами WITH RECOMPILE ;
• AS — начало определения текста процедуры;
• <Инструкции_SQL> — набор допустимых инструкций SQL, ограниченный только максимальным размером хранимой процедуры — 128 Кб. Недопустимыми являются следующие операторы: ALTER DATABASE , ALTER PROCEDURE , ALTER TABLE , CREATE DEFAULT , CREATE PROCEDURE , ALTER TRIGGER , ALTER VIEW , CREATE DATABASE , CREATE RULE , CREATE SCHEMA , CREATE TRIGGER , CREATE VIEW , DISK INIT , DISK RESIZE , DROP DATABASE , DROP DEFAULT , DROP PROCEDURE , DROP RULE , DROP TRIGGER , DROP VIEW , RESOTRE DATABASE , RESTORE LOG , RECONFIGURE , UPDATE STATISTICS .

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

CREATE Procedure Count_Ex1
-- процедура подсчета количества экземпляров книг,
-- находящихся в настоящий момент в библиотеке,
-- а не на руках у читателей
As
-- зададим временную локальную переменную
Declare @N int
Select @N = count(*) from Exemplar Where Yes_No = "1"
Select @N
GO

Поскольку хранимая процедура является полноценным компонентом базы данных, то, как вы уже поняли, создать новую процедуру можно только для текущей базы данных. При работе в SQL Server Query Analyzer установление текущей базы данных выполняется с помощью оператора Use , за которым следует имя базы данных, где должна быть создана хранимая процедура. Выбрать текущую базу данных можно также с помощью раскрывающегося списка.

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

В SQL Server 2000 используется отложенное распознавание имен (delayed name resolution), поэтому если хранимая процедура содержит обращение к другой, еще не реализованной процедуре, то выводится предупреждение, но вызов несуществующей процедуры сохраняется.

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

Создать хранимую процедуру можно также с помощью SQL Server Enterprise Manager:

Для того чтобы проверить работоспособность созданной хранимой процедуры, необходимо перейти в Query Analyzer и запустить процедуру на исполнение оператором EXEC <имя процедуры> . Результаты запуска созданной нами процедуры представлены на рис. 4.

Рис. 4. Запуск хранимой процедуры в Query Analyzer

Рис. 5. Результат выполнения процедуры без оператора вывода на экран

1.3. Параметры хранимых процедур

Хранимые процедуры — это очень мощный инструмент, но максимальной эффективности можно добиться, только сделав их динамическими. Разработчик должен иметь возможность передавать хранимой процедуре значения, с которыми она будет работать, то есть параметры. Ниже приведены основные принципы применения параметров в хранимых процедурах.

• Для процедуры можно определить один или несколько параметров.
• Параметры используются в качестве именованных мест хранения данных, точно так же, как переменные в языках программирования, таких как С, Visual Basic .NET.
• Имя параметра обязательно предваряется символом @ .
• Имена параметров являются локальными в той процедуре, где они определены.
• Параметры служат для передачи информации процедуре при ее выполнении. Они помешаются в командной строке после имени процедуры.
• В случае если процедура имеет несколько параметров, они разделяются запятыми.
• Для определения типа информации, передаваемой в качестве параметра, применяют системные или пользовательские типы данных.

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

Create Procedure Count_Ex(@ISBN varchar(14))
As
Declare @N int
Select @N
GO

При запуске этой процедуры на исполнение мы должны передать ей значение входного параметра (рис. 6).

Рис. 6. Запуск процедуры с передачей параметра

Для создания нескольких версий одной и той же процедуры, имеющих одинаковое имя, следует после основного имени поставить точку с запятой и целое число. Как это сделать, показано в следующем примере, где описано создание двух процедур с одним и тем же именем, но с разными номерами версий (1 и 2). Номер служит для контроля выполняемой версии этой процедуры. Если номер версии не указан, выполняется первая версия процедуры. Эта опция не показана в предыдущем примере, но, тем не менее, она доступна для вашего приложения.

Для вывода сообщения, идентифицирующего версию, обе процедуры применяют инструкцию print . Первая версия считает количество свободных экземпляров, а вторая — количество экземпляров на руках для данной книги.

Текст обеих версий процедур приведен ниже:

CREATE Procedure Count_Ex_all; 1
(@ISBN varchar(14))
-- процедура подсчета свободных экземпляров заданной книги
As
Declare @N int
Select @N = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
Select @N
--
GO
--
CREATE Procedure Count_Ex_all; 2
(@ISBN varchar(14))
-- процедура подсчета свободных экземпляров заданной книги
As
Declare @N1 int
Select @N1 = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "0"
Select @N1
GO

Результаты выполнения процедуры с разными версиями приведены на рис. 7.

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

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

Написанные нами процедуры не возвращают ни одного параметра, они просто выводят на экран полученное число. Однако чаще всего нам требуется получить параметр для дальнейшей обработки. Существует несколько способов возврата параметров из хранимой процедуры. Самый простой — это воспользоваться оператором возврата значений RETURN . Этот оператор позволят вернуть одно числовое значение. Но мы должны указать имя переменной или выражение, которое присваивается возвращаемому параметру. Ниже перечислены значения, возвращаемые оператором RETURN , зарезервированные системой:

Таким образом, чтобы не противоречить системе, мы можем возвращать через этот параметр только целые положительные числа.

Например, мы можем изменить текст ранее написанной хранимой процедуры Count_ex следующим образом:

Create Procedure Count_Ex2(@ISBN varchar(14))
As
Declare @N int
Select @N = count(*) from Exemplar
Where ISBN = @ISBN and YES_NO = "1"
-- возвращаем значение переменной @N,
-- если значение переменной не определено, возвращаем 0
Return Coalesce(@N, 0)
GO

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

Exec <переменная> = <имя_процедуры> <значение_входных_параметров>

Пример вызова нашей процедуры приведен на рис. 8.

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

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

Значение по умолчанию задается через знак равенства после описания входного параметра и его типа. Рассмотрим хранимую процедуру, которая считает количество экземпляров книг заданного года выпуска. Год выпуска по умолчанию — 2006.

CREATE PROCEDURE ex_books_now(@year int = 2006)
-- подсчет количества экземпляров книг заданного года выпуска
AS
Declare @N_books int
select @N_books = count(*) from books, exemplar
where Books.ISBN = exemplar.ISBN and YEARIZD = @year
return coalesce(@N_books, 0)
GO

На рис. 9 приведен пример вызова данной процедуры с указанием входного параметра и без него.

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

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

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

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

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

CREATE Procedure Count_books_all
(@ISBN varchar(14), @all int output, @free int output)
-- процедура подсчета общего количества земпляров заданной книги
-- и количества свободных экземпляров
As
-- подсчет общего количесва экземпляров
Select @all = count(*) from Exemplar Where ISBN = @ISBN
Select @free = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
GO

Пример выполнения данной процедуры приведен на рис. 10.

Рис. 10. Тестирование хранимой процедуры с выходными параметрами

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

Параметрами процедуры могут быть даже переменные типа Cursor . Для этого переменная должна быть описана как специальный тип данных VARYING , без привязки к стандартным системным типам данных. Кроме того, обязательно должно быть указано, что это переменная типа Cursor .

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

Текст процедуры выглядит следующим образом:

CREATE PROCEDURE GET3TITLES
(@MYCURSOR CURSOR VARYING OUTPUT)
-- процедура печати названий книг с курсором
AS
-- определяем локальную переменную типа Cursor в процедуре
SET @MYCURSOR = CURSOR
FOR SELECT DISTINCT TITLE
FROM BOOKS
-- открываем курсор
OPEN @MYCURSOR
-- описываем внутренние локальные переменные
DECLARE @TITLE VARCHAR(80), @CNT INT
--- устанавливаем начальное состояние счетчика книг
SET @CNT = 0
-- переходим на первую строку курсора
-- пока есть строки курсора,
-- то есть пока переход на новую строку корректен
WHILE (@@FETCH_STATUS = 0) AND (@CNT <= 2) BEGIN
PRINT @TITLE
FETCH NEXT FROM @MYCURSOR INTO @TITLE
-- изменяем состояние счетчика книг
SET @CNT = @CNT + 1
END
IF @CNT = 0 PRINT "НЕТ ПОДХОДЯЩИХ КНИГ"
GO

Пример вызова данной хранимой процедуры приведен на рис. 11.

В вызывающей процедуре курсор должен быть описан как локальная переменная. Потом мы вызвали нашу процедуру и передали ей имя локальной переменной типа Cursor . Процедура начала работать и вывела нам на экран первые три названия, а потом передала управление вызывающей процедуре, и та продолжила обработку курсора. Для этого она организовала цикл типа While по глобальной переменной @@FETCH_STATUS , которая отслеживает состояние курсора, и далее в цикле вывела все остальные строки курсора.

В окне вывода мы видим увеличенный интервал между первыми тремя строками и последующими названиями. Этот интервал как раз и показывает, что управление передано внешней программе.

Заметьте, что переменная @TITLE , являясь локальной для процедуры, будет уничтожена после завершения ее работы, поэтому в вызывающем процедуру блоке она объявляется еще раз. Создание и открытие курсора в данном примере происходит в процедуре, а закрытие, уничтожение и дополнительная обработка выполняются в блоке команд, в котором вызывается процедура.

Проще всего посмотреть текст процедуры, изменить или удалить ее с помощью графического интерфейса Enterprise Manager. Но можно это сделать и при помощи специальных системных хранимых процедур Transact-SQL. В Transact-SQL просмотр определения процедуры выполняется с помощью системной процедуры sp_helptext , а системная процедура sp_help позволяет вывести контрольную информацию о процедуре. Системные процедуры sp_helptext и sp_help используются и для просмотра таких объектов баз данных, как таблицы, правила и установки по умолчанию.

Информация обо всех версиях одной процедуры, независимо от номера, выводится сразу. Удаление разных версий одной хранимой процедуры также происходит одновременно. В следующем примере показано, как выводятся определения версий 1 и 2 процедуры Count_Ex_all , когда ее имя указано в качестве параметра системной процедуры sp_helptext (рис. 12).

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

Системная процедура SP_HELP выводит характеристики и параметры созданной процедуры в следующем виде:

Попробуйте самостоятельно расшифровать эти параметры. О чем они говорят?

1.4. Компиляция хранимой процедуры

Преимущество применения хранимых процедур для выполнения набора инструкций Transact-SQL состоит в том, что они компилируются при первом выполнении. В процессе компиляции инструкции Transact-SQL конвертируются из их первоначального символьного представления в исполняемую форму. Любые объекты, к которым происходит обращение в процедуре, также конвертируются в альтернативное представление. Например, имена таблиц конвертируются в идентификаторы объектов, а имена столбцов — в идентификаторы столбцов.

План выполнения создается точно так же, как и для выполнения одной инструкции Transact-SQL. Этот план содержит, например, индексы, применяемые для считывания строк из таблиц, к которым обращается процедура. План выполнения процедуры сохраняется в кэше и используется при каждом ее вызове.

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

1.5. Автоматическая повторная компиляция

Обычно план выполнения находится в кэше процедур. Это позволяет увеличить производительность при его выполнении. Однако при некоторых обстоятельствах процедура автоматически перекомпилируется.

• Процедура всегда перекомпилируется при начале работы SQL Server. Обычно это происходит после перезагрузки операционной системы и при первом выполнении процедуры после создания.
• План выполнения процедуры всегда автоматически перекомпилируется, если удаляется индекс таблицы, к которому обращается процедура. Поскольку текущий план обращается для считывания строк таблицы к индексу, которого уже не существует, следует создать новый план выполнения. Запросы процедуры будут выполняться только в том случае, если он будет обновлен.
• Компиляция плана выполнения происходит также в том случае, если с этим планом, находящимся в кэше, в данный момент работает другой пользователь. Для второго пользователя создается индивидуальная копия плана выполнения. Если бы первая копия плана не была занята, не понадобилось бы создания второй копии. Когда пользователь завершает выполнение процедуры, план выполнения доступен в кэше другому пользователю, имеющему соответствующее разрешение доступа.
• Процедура автоматически перекомпилируется, если она удаляется и заново создается. Так как новая процедура может отличаться от старой версии, все копии плана выполнения в кэше удаляются, и план компилируется заново.

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

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

Если повторная компиляция процедур не производится автоматически, ее можно выполнить принудительно. Например, если обновлены статистики, применяющиеся для определения возможности использования индекса в данном запросе, или если создан новый индекс, должна быть произведена принудительная перекомпиляция. Для выполнения принудительной повторной компиляции в инструкции EXECUTE применяется предложение WITH RECOMPILE :

EXECUTE имя_процедуры;
AS
<инструкции Transact-SQL>
WITH RECOMPILE

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

Замечание: Иногда бывает сложно определить, надо ли использовать опцию WITH RECOMPILE при создании процедуры или нет. Если есть сомнения, лучше не применять эту опцию, так как повторная компиляция процедуры при каждом выполнении приведет к потере очень ценного времени центрального процессора. Если в будущем вам понадобится повторная компиляция при выполнении хранимой процедуры, ее можно будет произвести, добавив предложение WITH RECOMPILE к инструкции EXECUTE .

Нельзя применять опцию WITH RECOMPILE в инструкции CREATE PROCEDURE , содержащей опцию FOR REPLICATION . Эту опцию применяют для создания процедуры, которая выполняется в процессе репликации.

1.6. Вложенность хранимых процедур

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

2. Функции, определяемые пользователем (UDF)

В MS SQL SERVER 2000 существует множество заранее определенных функций, позволяющих выполнять разнообразные действия. Однако всегда может возникнуть необходимость использовать какие-то специфичные функции. Для этого, начиная с версии 8.0 (2000), появилась возможность описывать пользовательские функции (User Defined Functions, UDF) и хранить их в виде полноценного объекта базы данных, наравне с хранимыми процедурами, представлениями и т. д.

Удобство применения функций, определяемых пользователем, очевидно. В отличие от хранимых процедур, функции можно встраивать непосредственно в оператор SELECT , причем использовать их как для получения конкретных значений (в разделе SELECT ), так и в качестве источника данных (в разделе FROM ).

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

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

2.1. Скалярные функции

Скалярные функции возвращают один скалярный результат. Этот результат может быть любого описанного выше типа, за исключением типов text , ntext , image и timestamp . Это наиболее простой вид функции. Ее синтаксис имеет следующий вид:

RETURNS скалярный_тип_данных

BEGIN
тело_функции
RETURN скалярное_выражение
END

• Параметр ENCRYPTION уже был описан в разделе, посвященном хранимым процедурам;
• SCHEMABINDING — привязывает функцию к схеме. Это означает, что нельзя будет удалить таблицы или представления, на основе которых строится функция, без удаления или изменения самой функции. Нельзя также изменить структуру этих таблиц, если изменяемая часть используется функцией. Таким образом, эта опция позволяет исключить ситуации, когда функция использует какие-либо таблицы или представления, а кто-то, не зная об этом, удалил или изменил их;
• RETURNS скалярный_тип_данных — описывает тип данных, который возвращает функция;
• скалярное_выражение — выражение, которое непосредственно возвращает результат выполнения функции. Оно должно иметь тот же тип, что и тот, что описан после RETURNS;
• тело_функции — набор инструкций на Transact-SQL.

Рассмотрим примеры использования скалярных функций.

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

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

CREATE FUNCTION min_num(@a INT, @b INT)
RETURNS INT
BEGIN
DECLARE @c INT
IF @a < @b SET @c = @a
ELSE SET @c = @b
RETURN @c
END

Выполним теперь эту функцию:

SELECT dbo.min_num(4, 7)

В результате мы получим значение 4.

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

SELECT min_lvl, max_lvl, min_num(min_lvl, max_lvl)
FROM Jobs

Создадим функцию, которая будет получать на вход параметр типа datetime и возвращать дату и время, соответствующие началу указанного дня. Например, если входной параметр — 20.09.03 13:31, то результатом будет 20.09.03 00:00.

CREATE FUNCTION dbo.daybegin(@dat DATETIME)
RETURNS smalldatetime AS
BEGIN
RETURN CONVERT(datetime, FLOOR(convert(FLOAT, @dat)))
END

Здесь функция CONVERT осуществляет преобразование типов. Сначала тип даты-времени приводится к типу FLOAT . При таком приведении целая часть — это число дней, считая с 1 января 1900 года, а дробная — время. Далее происходит округление до меньшего целого с помощью функции FLOOR и приведение к типу даты-времени.

Проверим действие функции:

SELECT dbo.daybegin(GETDATE())

Здесь GETDATE() — функция, возвращающая текущую дату и время.

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

Создадим функцию, которая будет принимать в качестве параметров две даты: начало и окончание временного интервала — и рассчитывать суммарную выручку от продаж за этот интервал. Дата продажи и количество будут браться из таблицы Sales , а цены на продаваемые издания — в таблице Titles .

CREATE FUNCTION dbo.SumSales(@datebegin DATETIME, @dateend DATETIME)
RETURNS Money
AS
BEGIN
DECLARE @Sum Money
SELECT @Sum = sum(t.price * s.qty)

RETURN @Sum
END

2.2. Inline-функции

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

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

Особенностью inline-фукций является то, что они могут содержать только один запрос в своем теле. Таким образом, функции этого типа очень напоминают представления, но дополнительно могут иметь входные параметры. Синтаксис inline-функции:

CREATE FUNCTION [владелец.]имя_функции
([{@имя_параметра скалярный_тип_данных [= значение_по_умолчанию]} [,… n]])
RETURNS TABLE

RETURN [(<запрос>)]

В определении функции указано, что она будет возвращать таблицу; <запрос> — это тот запрос, результат выполнения которого будет результатом работы функции.

Напишем функцию, аналогичную скалярной функции из последнего примера, но возвращающую не только суммирующий результат, но и строки продаж, включающие дату продажи, название книги, цену, количество штук и сумму продажи. Должны выбираться только те продажи, которые попадают в заданный период времени. Зашифруем текст функции, чтобы другие пользователи могли ей воспользоваться, но не могли читать и исправлять ее:

CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
RETURNS TABLE
WITH ENCRYPTION
AS
RETURN (
SELECT t.title, t.price, s.qty, ord_date, t.price * s.qty as stoim
FROM Titles t JOIN Sales s ON t.title_Id = s.Title_ID
WHERE ord_date BETWEEN @datebegin and @dateend
)

Теперь вызовем эту функцию. Как уже говорилось, вызвать ее можно только в разделе FROM оператора SELECT :

SELECT * FROM Sales_Period("01.09.94", "13.09.94")

2.3. Многооператорные функции, возвращающие табличный результат

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

CREATE FUNCTION [владелец.]имя_функции
([{@имя_параметра скалярный_тип_данных [= значение_по_умолчанию]} [,... n]])
RETURNS @имя_переменной_результата TABLE
<описание_таблицы>

BEGIN
<тело_функции>
RETURN
END

• TABLE <описание_таблицы> — описывает структуру возвращаемой таблицы;
• <описание_таблицы> — содержит перечисление столбцов и ограничений.

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

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

Рис. 13. Структура базы данных для описания иерархии файлов и каталогов

Теперь напишем функцию, которая будет принимать на входе идентификатор каталога и выводить все файлы, которые хранятся в нем и во всех каталогах вниз по иерархии. Например, если в каталоге Институт созданы каталоги Факультет1 , Факультет2 и т. д., в них есть каталоги кафедр, и в каждом из каталогов есть файлы, то при указании в качестве параметра нашей функции идентификатора каталога Институт должен быть выведен список всех файлов для всех этих каталогов. Для каждого файла должно выводиться имя, размер и дата создания.

Решить задачу с помощью inline-функции нельзя, поскольку SQL не предназначен для выполнения иерархических запросов, так что одним SQL-запросом здесь не обойтись. Скалярная функция тоже не может быть применена, поскольку результат должен быть таблицей. Тут нам на помощь и придет многооператорная функция, возвращающая таблицу:

CREATE FUNCTION dbo.GetFiles(@Folder_ID int)
RETURNS @files TABLE(Name VARCHAR(100), Date_Create DATETIME, FileSize INT) AS
BEGIN
DECLARE @tmp TABLE(Folder_Id int)
DECLARE @Cnt INT
INSERT INTO @tmp values(@Folder_ID)
SET @Cnt = 1
WHILE @Cnt <> 0 BEGIN
INSERT INTO @tmp SELECT Folder_Id
FROM Folders f JOIN @tmp t ON f.parent=t.Folder_ID
WHERE F.id NOT IN(SELECT Folder_ID FROM @tmp)
SET @Cnt = @@ROWCOUNT
END
INSERT INTO @Files(Name, Date_Create, FileSize)
SELECT F.Name, F.Date_Create, F.FileSize
FROM Files f JOIN Folders Fl on f.Folder_id = Fl.id
JOIN @tmp t on Fl.id = t.Folder_Id
RETURN
END

Здесь в цикле в переменную @tmp добавляются все вложенные каталоги на всех уровнях вложенности до тех пор, пока больше не останется вложенных каталогов. Затем в переменную результата @Files записываются все необходимые атрибуты файлов, находящихся в каталогах, перечисленных в переменной @tmp .

Задания для самостоятельной работы

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

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

Процедура 2. Подсчет количества свободных экземпляров заданной книги.

Процедура 3. Проверка существования читателя с заданными фамилией и датой рождения.

Процедура 4. Ввод нового читателя с проверкой его существования в базе и определением его нового номера читательского билета.

Процедура 5. Подсчет штрафа в денежном выражении для читателей-должников.

Краткое описание процедур

Процедура 1. Увеличение срока сдачи книг

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

DateAdd(day, <число добавляемых дней>, <начальная дата>)

Процедура 2. Подсчет количества свободных экземпляров заданной книги

Входным параметром процедуры является ISBN — уникальный шифр книги. Процедура возвращает 0 (ноль), если все экземпляры данной книги находятся на руках у читателей. Процедура возвращает значение N , равное числу экземпляров книги, которые в данный момент находятся на руках у читателей.

Если книги с заданным ISBN нет в библиотеке, то процедура возвращает –100 (минус сто).

Процедура 3. Проверка существования читателя с заданными фамилией и датой рождения

Процедура возвращает номер читательского билета, если читатель с такими данными существует, и 0 (ноль) в противном случае.

При сравнении даты рождения необходимо использовать функцию преобразования Convert() для преобразования даты рождения — символьной переменной типа Varchar(8) , используемой в качестве входного параметра процедуры, в данные типа datatime , которые используются в таблице Readers . В противном случае операция сравнения при поиске данного читателя не сработает.

Процедура 4. Ввод нового читателя

Процедура имеет пять входных и три выходных параметра.

Входные параметры:

• Полное имя с инициалами;
• Адрес;
• Дата рождения;
• Телефон домашний;
• Телефон рабочий.

Выходные параметры:

• Номер читательского билета;
• Признак того, был ли читатель ранее записан в библиотеке (0 — не был, 1 — был);
• Количество книг, которое числится за читателем.

Процедура 5. Подсчет штрафа в денежном выражении для читателей-должников

Процедура работает с курсором, который содержит перечень номеров читательских билетов всех должников. В процессе работы должна быть создана глобальная временная таблица ##DOLG , в которую для каждого должника будет занесен его суммарный долг в денежном выражении за все книги, которые он продержал дольше срок возврата. Денежная компенсация исчисляется в 0,5 % от цены за книгу за день задержки.

Порядок выполнения работы

• копии экранов (скриншоты), подтверждающие внесенные изменения в базы данных;
• содержимое таблиц базы данных, которые требуются для подтверждения правильности работы;
• текст хранимой процедуры с комментариями;
• процесс запуска хранимой процедуры с выводом результатов работы.

Дополнительные задания

Приведенные ниже дополнительные хранимые процедуры предназначены для индивидуальных заданий.

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

Процедура 7. Ввод новой книги с указанием количества ее экземпляров. При вводе экземпляров новой книги не забудьте ввести их корректные инвентарные номера. Подумайте, как это можно сделать. Напоминаю, что у вас есть функции Max и Min , которые позволяют найти максимальное или минимальное значение любого числового атрибута средствами запроса Select .

Процедура 8. Формирование таблицы со списком читателей-должников, то есть тех, кто должен был вернуть книги в библиотеку, но еще не вернул. В результирующей таблице каждый читатель-должник должен появляться только один раз, вне зависимости от того, сколько книг он задолжал. Кроме ФИО и номера читательского билета в результирующей таблице надо указать адрес и телефон.

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

Процедура 10. Вывод списка читателей, которые не держат ни одной книги на руках в настоящий момент. В списке указать ФИО и телефон.

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

Версия для печати