Хранимые процедуры
Предметом этой главы является один из наиболее мощных
инструментов, предлагаемых разработчикам приложений баз данных 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", которая посвящена обработке исключений,
разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.
Храни́мая процеду́ра - объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.
Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL:
CALL процедура(…)
EXECUTE процедура(…)
Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими хранимыми процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.
Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры написанные не на SQL могут самостоятельно выполнять SQL-запросы, а могут и не выполнять.
За
Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Назначение и преимущества хранимых процедур
Хранимые процедуры позволяют повысить производительность, расширяют возможности программирования и поддерживают функции безопасности данных.
Вместо хранения часто используемого запроса, клиенты могут ссылаться на соответствующую хранимую процедуру. При вызове хранимой процедуры её содержимое сразу же обрабатывается сервером.
Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными - изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.
В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.
Возможности программирования
Созданную хранимую процедуру можно вызвать в любой момент, что обеспечивает модульность и стимулирует повторное использование кода. Последнее облегчает сопровождение базы данных, так как она становится изолированной от меняющихся бизнес-правил. Модифицировать хранимую процедуру в соответствии с новыми правилами можно в любой момент. После этого все приложения, использующие её, автоматически придут в соответствие с новыми бизнес-правилами без непосредственной модификации.
Безопасность
Использование хранимых процедур позволяет ограничить или вообще исключить непосредственный доступ пользователей к таблицам базы данных, оставив пользователям только разрешения на выполнение хранимых процедур, обеспечивающих косвенный и строго регламентированный доступ к данным. Кроме того, некоторые СУБД поддерживают шифрование текста (wrapping) хранимой процедуры.
Эти функции безопасности позволяют изолировать от пользователя структуру базы данных, что обеспечивает целостность и надежность базы.
Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.
Реализация хранимых процедур
Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle - PL/SQL, в InterBase и Firebird - PSQL, в PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 - SQL/PL (англ.), в Informix - SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.
В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название - UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке.NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java. В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix.
В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ. packages). Пакет состоит из двух частей - спецификации (англ. package specification), в которой указывается определение хранимой процедуры, и тела (англ. package body), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации.
В СУБД IBM DB2 хранимые процедуры можно объединять в модули.
Синтаксис
CREATE PROCEDURE `p2` ()
SQL SECURITY DEFINER
COMMENT "A procedure"
SELECT "Hello World !";
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
4 характеристики хранимой процедуры:
Language: в целях обеспечения переносимости, по умолчанию указан SQL.
Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Параметры
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): один параметр, одновременно входящий и возвращаемый.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.
В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.
Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL - непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.
Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.
Прежде чем выполнять примеры, обратите внимание на то, что реализация и способ хранения триггеров и хранимых процедур в используемой вами СУБД могут отличаться от приведенных в этой статье. Кроме того, для создания серверных объектов следует иметь соответствующие разрешения, предоставляемые администратором базы данных.
Отметим также, что некоторые ODBC-драйверы не поддерживают вызов хранимых процедур из клиентских приложений, даже если таковые поддерживаются самой СУБД. Однако в этом случае хранимые процедуры по-прежнему могут быть вызваны из триггеров.
Давайте начнем с представлений, затем обсудим хранимые процедуры и закончим главу обзором триггеров.
Представления
Представление - это виртуальная таблица, обычно содержащая набор колонок одной или нескольких таблиц. В действительности представление содержит не данные, а лишь SQL-запрос типа SELECT, указывающий, какие именно данные и из каких таблиц нужно взять при обращении к этому представлению. С этой точки зрения представление - это хранимый запрос.
В большинстве случаев представления используются для обеспечения безопасности данных. Например, некоторые категории пользователей могут иметь доступ к представлению, но не к таблицам, данные которых его формируют; кроме того, SQL-запрос может содержать параметр USER (имя, под которым зарегистрировался пользователь), и в этом случае данные, доступные при обращении к представлению, будут зависеть от имени пользователя.
Ниже перечислены основные характеристики представлений:
- представления ведут себя подобно таблицам;
- представления не содержат данных;
- представления могут использовать данные более чем из одной таблицы.
Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации - предложение ALTER VIEW, а для удаления его - предложение DROP VIEW.
Мы начнем с оператора CREATE VIEW, позволяющего создать представление для текущей базы данных.
Предложение CREATE VIEW
Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:
CREATE VIEW view_name AS select_statement
Аргумент view_name указывает на имя представления. Ключевое слово , используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.
Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.
Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.
Предложение DROP VIEW
Это предложение используется для удаления представления из базы данных. Обратите внимание на то, что при удалении таблицы из базы данных удаляются и все представления, ссылающиеся на нее. Используя это предложение, мы должны указать имя удаляемого представления. После того как представление удалено, вся информация о нем удаляется из системных таблиц.
Еще один случай, когда представление нужно удалить, может возникнуть при условии, что структура таблиц, на которых оно основано, изменилась после создания представления. В этом случае можно удалить представление и затем создать его заново с помощью предложения CREATE VIEW.
Создание и использование представлений
Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.
Наилучший способ создать представление - создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).
Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц - поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1 .
Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).
Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).
Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.
Верхняя часть View Designer позволяет указать, как связаны таблицы и какие поля будут отображаться в представлении. Ниже можно указать псевдонимы таблиц и полей, ограничения на их значения, способ отображения. Далее приведены исходный текст представления и результаты его выполнения.
Прежде чем мы закончим краткий обзор представлений, поговорим немного о том, как получить дополнительную информацию о них. В Microsoft SQL Server 7.0 мы можем использовать следующие системные хранимые процедуры:
- для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
- для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
- для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
- для переименования представления можно использовать системную хранимую процедуру sp_rename.
В данном разделе мы рассмотрели, как использовать представления для получения данных, удовлетворяющих тем или иным критериям. Однако вернемся к последнему примеру. В базе данных NorthWind имеется четыре региона, и для получения списка территорий всех регионов нам нужно четыре разных представления. Эту задачу можно было бы упростить, если бы мы могли передать значение RegionID в качестве параметра. Это можно сделать с помощью хранимой процедуры, о чем мы поговорим в следующем разделе.
Хранимые процедуры
Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Хранимые процедуры могут принимать и возвращать параметры. Когда пользователь создает хранимую процедуру, сервер компилирует ее и помещает в разделяемый кэш, после чего скомпилированный код может быть применен несколькими пользователями. Когда приложение использует хранимую процедуру, оно передает ей параметры, если таковые требуются, и сервер выполняет процедуру без перекомпиляции.
Хранимые процедуры позволяют повысить производительность приложений. Во-первых, по сравнению с обычными SQL-запросами, посылаемыми из клиентского приложения, они требуют меньше времени для подготовки к выполнению, поскольку они уже скомпилированы и сохранены. Во-вторых, сетевой трафик в этом случае также меньше, чем в случае передачи SQL-запроса, так как по сети передается меньшее количество данных. Рис. 3 иллюстрирует вызов хранимой процедуры клиентским приложением.
Хранимые процедуры автоматически перекомпилируются, если с объектами, на которые они влияют, произведены какие-либо изменения; иными словами, они всегда актуальны. Как уже было сказано выше, хранимые процедуры могут принимать параметры, что позволяет разным приложениям использовать одну и ту же процедуру, применяя различные наборы входных данных.
Хранимые процедуры обычно используются для поддержки ссылочной целостности данных и реализации бизнес-правил. В последнем случае достигается дополнительная гибкость, поскольку, если бизнес-правила изменяются, можно изменить только текст процедуры, не изменяя клиентских приложений.
Для создания, изменения и удаления процедур существуют специальные SQL-предложения - CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.
Предложение CREATE PROCEDURE
Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:
CREATE PROC proc_name [ {@parameter data_type} [= default] ] [...] AS sql_statements
Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.
Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.
Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию - это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, и [^]).
Ключевое слово OUTPUT показывает, что это возвращаемый параметр.
Ключевое слово AS указывает действие, которое процедура должна выполнить, в виде любого количества SQL-предложений и предложений на процедурном расширении SQL, характерном для данного сервера.
Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст - в таблице syscomments.
Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.
Предложение DROP PROCEDURE
Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент - имя удаляемой процедуры.
При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.
Создание и использование хранимых процедур
В разделе, посвященном представлениям, мы обращали внимание на то, что было бы удобно, если бы мы могли передать в представление параметр, содержащий значение RegionID для выбора одного из четырех регионов в базе данных NorthWind. Давайте еще раз рассмотрим запрос, возвращающий список территорий региона:
SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1
Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.
В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:
CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID
Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.
Результат выполнения созданной процедуры в SQL Server Query Analyzer для RegID =2 показан на рис. 3 .
Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.
В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:
- sp_stored_procedures - показывает список хранимых процедур;
- sp_helptext - показывает исходный текст хранимой процедуры;
- sp_depends - показывает сведения о зависимостях хранимых процедур;
- sp_procoption - устанавливает опции хранимых процедур или устанавливает их;
- sp_recompile - перекомпилирует процедуру в момент ее следующего вызова;
- sp_rename - меняет имя процедуры.
Системные хранимые процедуры
Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.
Обратите внимание на то, что триггеры не должны возвращать пользователю данные.
В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:
SELECT * FROM deleted
В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.
Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.
Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:
CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID
Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения - в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.
Как видим, этот триггер позволяет следить за изменениями и вставкой новых записей в таблице.
Перед тем как закончить краткий обзор триггеров, мы должны сообщить, где можно найти сведения об имеющихся триггерах. Таблица sysobjects хранит сведения о триггерах и их типах, а таблица syscomments содержит их исходный текст.
Заключение
В этой части мы рассмотрели несколько типов объектов баз данных - хранимые процедуры, представления и триггеры. Мы узнали следующее:
- Представление - это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации - предложение ALTER VIEW, а для удаления - предложение DROP VIEW.
- Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения - ALTER PROCEDURE, а для удаления - DROP PROCEDURE.
- Tриггер - это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления - предложение DROP TRIGGER.
КомпьютерПресс 12"2000
Включай в свои процедуры строку - SET NOCOUNT ON:
С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
Transact-SQL
Используй имя схемы с именем объекта:
Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
Transact-SQL
SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!
Не используй префикс «sp_» в имени своих хранимых процедур:
Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.
Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в в виде различных объектов. Другими словами, это объект, в котором содержатся SQL-инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого-либо другого раздела.
Введение
Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).
В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».
При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые удаление, исполнение, изменение. За все это отвечает DDL-оператор, который в одиночку совершает сложнейшие действия по обработке объектов. Причем все это происходит очень быстро, а сервер фактически не нагружается. Такая скорость и производительность позволяют очень быстро передавать большие объемы информации от пользователя на сервер и наоборот.
Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.
Продуктивность
Эти объекты баз данных могут быть запрограммированы различными путями. Это позволяет пользователям выбирать тип используемого способа, который будет наиболее подходящим, что экономит силы и время. Кроме того, процедура сама обрабатывается, что позволяет избежать огромных временных затрат на обмен между сервером и пользователем. Также модуль можно перепрограммировать и изменить в нужное направление в абсолютно любой момент. Особенно стоит отметить скорость, с которой происходит запуск хранимой процедуры SQL: это процесс происходит быстрее иных, схожих с ним, что делает его удобным и универсальным.
Безопасность
Такой тип обработки информации отличается от схожих процессов тем, что он гарантирует повышенную безопасность. Это обеспечивается за счет того, что доступ других пользователей к процедурам может быть исключен целиком и полностью. Это позволит администратору проводить операции с ними самостоятельно, не опасаясь за перехват информации или несанкционированный доступ к базе данных.
Передача данных
Связь между хранимой процедурой SQL и клиентским приложением заключается в использовании параметров и возвращаемых значениях. Последним не обязательно передавать данные в хранимую процедуру, однако эта информация (в основном по запросу пользователя) и перерабатывается для SQL. После того как хранимая процедура завершила свою работу, она отсылает пакеты данных обратно (но, опять же, по желанию) к вызвавшему его приложению, используя различные методы, с помощью которых может быть осуществлен как вызов хранимой процедуры SQL, так и возврат, например:
Передача данных с помощью параметра типа Output;
Передача данных с помощью оператора возврата;
Передача данных с помощью оператора выбора.
А теперь разберемся, как же выглядит этот процесс изнутри.
1. Создание EXEC-хранимой процедуры в SQL
Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.
При создании процедуры ее название появляется первым, после чего производится один или несколько параметров, присвоенных ему. Параметры могут быть необязательными. После того как параметр(ы), то есть тело процедуры, будут написаны, нужно провести некоторые необходимые операции.
Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.
Таким образом, чтобы создать процедуру, нам нужно имя процедуры и, по меньшей мере, один параметр в качестве тела процедуры. Обратите внимание, что отличным вариантом в таком случае является создание и выполнение процедуры с именем схемы в классификаторе.
Тело процедуры может иметь любой вид из например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:
Тело не должно создавать какой-либо другой хранимой процедуры;
Тело не должно создать ложное представление об объекте;
Тело не должно создавать никаких триггеров.
2. Установка переменной в тело процедуры
Вы можете сделать переменные локальными для тела процедуры, и тогда они будут находиться исключительно внутри тела процедуры. Хорошей практикой является создание переменных в начале тела хранимой процедуры. Но также вы можете устанавливать переменные в любом месте в теле данного объекта.
Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.
Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.
В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.
В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.
Как в SQL выполнить хранимую процедуру
Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.
Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.
4. Хранимые процедуры SQL Server: способы возврата
Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:
Возврат значения хранимой процедуры;
Выход параметра хранимых процедур;
Выбор одной из хранимых процедур.
4.1 Возврат значений хранимых процедур SQL
В этой методике процедура присваивает значение локальной переменной и возвращает его. Процедура может также непосредственно возвращать постоянное значение. В следующем примере, мы создали процедуру, которая возвращает общее число авторов. Если сравнить эту процедуру с предыдущими, вы можете увидеть, что значение для печати заменяется обратным.
Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.
4.2 Выход параметра хранимых процедур SQL
Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.
В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.
Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.
4.3 Выбор одной из хранимых процедур SQL
Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.
В заключение
Хранимая процедура является довольно серьезным программным модулем, возвращающим или передающим, а также устанавливающим необходимые переменные благодаря клиентскому приложению. Поскольку хранимая процедура выполняется на сервере сама, обмена данными в огромных объемах между сервером и клиентским приложением (для некоторых вычислений) можно избежать. Это позволяет снижать нагрузки на сервера SQL, что, конечно же, идет на руку их держателям. Одним из подвидов являются хранимые процедуры T SQL, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.
Loading...