Хранимые процедуры SQL: создание и использование. Хранимые функции. За и против

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

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

Когда следует использовать хранимые процедуры и когда я должен использовать представления в SQL Server?

Разрешения позволяют создавать динамические запросы, где мы можем передавать параметры?

Какой из них самый быстрый, и на каком основании он быстрее, чем другой?

Просмотры или хранимые процедуры постоянно сохраняют память?

Что это значит, если кто-то скажет, что представления создают виртуальную таблицу, а процедуры создают таблицу материалов?

Пожалуйста, дайте мне знать о более точках, если они есть.

Solutions Collecting From Web of "В чем разница между хранимой процедурой и представлением?"

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

Хранимая процедура использует параметры для выполнения функции … будь то обновление и вставка данных или возврат отдельных значений или наборов данных.

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

Скажем, у меня есть две таблицы:

tbl_user Столбцы: .user_id, .user_name, .user_pw

tbl_profile Столбцы: .profile_id, .user_id .profile_description

Поэтому, если я нахожусь в запросе из этих таблиц ALOT … вместо того, чтобы делать соединение в КАЖДОЙ peice sql, я бы определил вид, например:

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO

Поэтому в будущем, если я хочу запросить profile_description по идентификатору пользователя … все, что мне нужно сделать, это

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

Этот код можно использовать в хранимой процедуре, например:

Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO

Поэтому позже я могу позвонить

Dbo.getDesc 25

и я получу описание для идентификатора пользователя 25, где 25 – ваш параметр.

Очевидно, что МНОГО больше, но это всего лишь основная идея.

Сначала вам нужно понять, что оба – разные вещи. Хранимые процедуры лучше всего использовать для операторов INSERT-UPDATE-DELETE. и Представления используются для операторов SELECT. и вы должны использовать оба.

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

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

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

Представления могут использоваться в Хранимой процедуре, но Хранимая процедура не может использоваться в Views …!

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

Представления являются статическими. Подумайте о них как о новых таблицах с определенным макетом, а данные в них создаются «на лету», используя запрос, с которым вы его создали. Как и в любой таблице SQL, вы можете сортировать и фильтровать ее с помощью WHERE , GROUP BY и ORDER BY .

Это зависит от того, что вы делаете.

Это зависит от базы данных. Простые представления просто запускают запрос и фильтруют результат. Но такие базы данных, как Oracle, позволяют создать «материализованное» представление, которое в основном представляет собой таблицу, которая автоматически обновляется при изменении базовых данных вида.

Материализованное представление позволяет создавать индексы в столбцах представления (особенно на вычисленных столбцах, которые не существуют нигде в базе данных).

Я не понимаю, о чем вы говорите.

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

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

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

Представление быстрее, поскольку оно отображает данные из таблиц, на которые ссылается, тогда как процедура хранилища выполняет sql-инструкции.

Проверьте эту статью: Просмотр против хранимых процедур. Именно то, что вы ищете

@Patrick правильно с тем, что он сказал, но, чтобы ответить на ваши другие вопросы, View создаст себя в памяти, и в зависимости от типа Joins, Data и если будет сделано какое-либо агрегирование, это может быть довольно голодный вид.

Хранимые процедуры выполняют всю свою обработку либо с использованием Temp Hash Table, например, # tmpTable1, либо в памяти с помощью @ tmpTable1. В зависимости от того, что вы хотите сказать.

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

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

Махеш не совсем прав, когда он предполагает, что вы не можете изменять данные в представлении. Итак, с точки зрения Патрика

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id

Я могу обновить данные … в качестве примера я могу сделать любой из этих …

Update vw_user_profile Set profile_description="Manager" where user_id=4

Update tbl_profile Set profile_description="Manager" where user_id=4

Вы не можете ВСТАВИТЬ в это представление, так как не все поля во всей таблице присутствуют, и я предполагаю, что PROFILE_ID является первичным ключом и не может быть NULL. Однако иногда вы можете вставить INSERT в представление …

Я создал представление для существующей таблицы, используя …

Create View Junk as SELECT * from

Insert into junk (Code,name) values ("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")

DELETE from Junk Where ID>4

И INSERT, и DELETE работали в этом случае

Очевидно, вы не можете обновлять какие-либо поля, которые агрегированы или рассчитаны, но любое представление, которое является просто прямым представлением, должно быть обновляемым.

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

В дополнение к приведенным выше комментариям я хотел бы добавить несколько замечаний о Views.

1. Представления могут использоваться для скрытия сложности. Представьте себе сценарий, в котором 5 человек работают над проектом, но только один из них слишком хорош с базой данных, например сложными объединениями. В таком сценарии он может создавать виды, которые могут быть легко запрошены другими членами команды, поскольку они запрашивают какую-либо одну таблицу.
2. Безопасность может быть легко реализована Views. Предположим, что мы сотрудник таблицы, который содержит чувствительные столбцы, такие как Зарплата , номер SSN . Эти столбцы не должны отображаться для пользователей, которым не разрешено их просматривать. В этом случае мы можем создать представление, которое будет выбирать столбцы в таблице, не требующие авторизации, такие как имя , возраст и т. Д., Не подвергая уязвимые столбцы (например, о зарплате и т. Д., О которых мы упоминали ранее). Теперь мы можем удалить разрешение для прямого запроса к таблице Employee и просто сохранить разрешение на чтение в представлении. Таким образом, мы можем реализовать безопасность с помощью Views.

Использование хранимых функций СУБД для реализации бизнес-логики или её части, всегда было камнем преткновения. С одной стороны баррикад DBA и программисты БД, с другой - разработчики backend.
Рискну навлечь на себя гнев из обоих лагерей, но всё же просуммирую плюсы и минусы и изложу свои соображения о том, когда стоит писать код в хранимых функциях, а когда следует выносить наружу.

Начнём с аргументов против:

Размазывание бизнес-логики

Скудность языка СУБД

Непереносимость хранимых функций

Отсутствие необходимых навыков у команды и высокая «стоимость» соответствующих специалистов

Теперь плюсы:

Скорость

Сокрытие структуры данных

Гибкое управление правами доступа

Меньшая вероятность SQL injection

Повторное использование SQL

Простая отладка SQL

Когда писать бизнес-логику в БД?

Если важна скорость обработки данных

Когда важна целостность и непротиворечивость данных

Данные имеют сложную, но устоявшуюся структуру

Когда выносить код наружу?

Работа с внешними данными

Сложные алгоритмы

Highload

Вывод такой, что чёткого алгоритма нет. Каждый раз решение остаётся за архитекторами и менеджером и от него зависит то, завязнет ли проект в проблемах с race conditions и неконсистентностью данных NoSQL, проблемах с производительностью и отладкой запросов ORM, или упрётся в проблемы масштабирования СУБД при использовании хранимых функций. Поэтому - принимайте верные решения:)

В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов ) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.

Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.

Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:

• Для более подробного изучения языка T-SQL также рекомендую почитать книгу - Путь программиста T-SQL. Самоучитель по языку Transact-SQL .

Что такое хранимые процедуры в T-SQL?

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

Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT , например, как функцию уже не получится, т.е. процедуры запускаются отдельно.

В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: UNSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).

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

Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен ), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL » представлен пример реализации данной возможности в виде хранимой процедуры.

Примеры работы с хранимыми процедурами в Microsoft SQL Server

Исходные данные для примеров

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

Инструкция создания таблицы CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Инструкция добавления данных INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1, "Мышь", 100), (1, "Клавиатура", 200), (2, "Телефон", 400) GO --Запрос на выборку SELECT * FROM TestTable

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

Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE

Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE , после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.

Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName - наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену ), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @ ). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT ).

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

Вот код данной процедуры (его я также прокомментировал ).

Создаем процедуру CREATE PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Возвращаем данные SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Запуск хранимой процедуры на T-SQL – команда EXECUTE

Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT ). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам ).

Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.

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

1. Вызываем процедуру без указания цены EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 1" --2. Вызываем процедуру с указанием цены EXEC TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 2", @Price = 300 --3. Вызываем процедуру, не указывая название параметров EXEC TestProcedure 1, "Тестовый товар 3", 400

Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE

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

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

Изменяем процедуру ALTER PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE

В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE .

Например, давайте удалим созданную нами тестовую процедуру.

DROP PROCEDURE TestProcedure

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

У меня все, надеюсь, материал был Вам интересен и полезен, пока!

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 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