Создание процедуры в базе данных
В программе SQL Query Analyzer можно набрать текст процедуры (как указано выше) и при исполнении запроса она будет создана на сервере. Дальнейшее изменение процедуры производить не вполне удобно (например, для засылки нового текста следует дать команду DROP PROC). Из программы же Entreprise Manager в дереве базы данных можно найти ветку «Stored Procedures», в контекстном меню следует выбрать пункт «New stored procedure», после чего в окне редактирования процедуры появится шаблон текста хранимой процедуры. Кнопка «Check syntax» позволяет проверить текст процедуры до отправки на сервер. После сохранения процедуры на сервере можно редактировать ее, дважды щелкнув на ее имени левой кнопкой мыши (либо выбрав пункт «Properties» в ее контекстном меню).
Средства языка Transact-SQL
Сам язык SQL, хоть и не предназначенный для программирования алгоритмов (он ориентирован на работу с множествами), содержит достаточное количество алгоритмических конструкций, позволяющих писать достаточно сложные процедуры.
Локальные переменные:
DECLARE @имя_переменной тип_переменной [,...]
Имена переменных должны начинаться со знака @ и должны быть действительными идентификаторами SQL. Присваивания возможны только в инструкции SELECT, например:
SELECT @sUserName = ‘Ivan’
Циклы:
WHILE логическое_выражение {предложение} [BREAK] {предложение} [CONTINUE]
Логическое выражение – любое допустимое выражение, возвращающее логическое значение True (истина) или False (ложь). Предложение в теле цикла формально может быть только одно, если требуется несколько – следует воспользоваться составным оператором BEGIN..END. Предложение BREAK немедленно завершает работу цикла, предложение CONTINUE досрочно инициирует следующую итерацию.
|
|
|
Комментарий: все, что находится после двух знаков тире ‘--‘, является комментарием и игнорируется сервером.
Строки: строковые константы следует заключать в одинарные кавычки, идентификаторы, содержащие пробелы, следует заключать в квадратные скобки, например [дата выдачи].
Условный оператор:
IF логическое_выражение {предложение}[ELSE {предложение}]
Предложение опять-таки формально должно быть одно, при необходимости следует воспользоваться составным оператором. Конструкции IF могут быть вложенными.
Оператор выбора: возвращает указанное значение в зависимости от того, что было выобрано. Первая форма, выбирающая значение, равное условию выбора (простой выбор):
CASE условие_выбора
WHEN значение_выбора_1 THEN возвращаемое_значение_1
[...и т.д.]
[
ELSE возвращаемое_значение_по_умолчанию
]
END
Вторая форма, выбирающая первое истинное логическое значение (поисковый выбор):
CASE
WHEN логическое_условие_1 THEN возвращаемое_значение_1
[...и т.д.]
[
ELSE возвращаемое_значение_по_умолчанию
]
END
|
|
|
Оператор распечатки: применяется в том случае, если результат распечатки будет виден (например, с консоли SQL Query Analyzer):
PRINT 'текст' | @переменная | @@функция | строковое_выражение
Агрегатные функции: ориентированы на работу с множеством значений, но возвращают одно значение. Как правило, применяются при выборке из базы данных и принимают обычно имя поля в качестве параметра. ALL – принимают все значение, DISTINCT – только разные, если задано выражение – то только значения выражения.
AVG([ALL|DISTINCT] выражение) – среднее значение выражения в множестве
MAX([ALL|DISTINCT]выражение) – максимальное значение выражения в множестве
MIN([ALL|DISTINCT] выражение) – минимальное значение выражения в множестве
SUM([ALL|DISTINCT] выражение) – суммарное значение выражения в множестве
COUNT({[ALL|DISTINCT]выражение]|*}) – количество выражений во множестве, если задана звездочка «*» (в этом случае задается только она, ничего больше), то подсчитывается все общее количество строк выборки.
Логические операции: AND, OR, NOT – соответственно логическое «И», «ИЛИ», «НЕ». Операторы сравнения: = равно, != (либо <>) не равно, < меньше, > больше, <= меньше либо равно, >= больше либо равно, !< не меньше, !> не больше.
|
|
|
Сюда же относят оператор условного совпадения: выражение [NOT] LIKE маска
Оператор определяет, совпадает ли заданное выражение с маской и возвращает соответственно логическое значение. Если заданное выражение не строковое, оно приводится к таковому. Маска – это регулярное выражение, задаваемое строкой. Символы:
| Символ маски | Описание | Пример маски |
| % | Любое количество любых символов | «%студент%» - любая строка, содержащая слово «студент». |
| _ (подч.) | Один любой символ | «_АБВ» - строка из четырех символов, первый любой, остальные как указано. Т.е., подходят «1АБВ», «ЮАБВ» и т.д. |
| [] | Один символ из указанного множества | «[A-Z]%» - любая строка, начинающаяся с заглавной буквы. |
| [^] | Один символ не из указанного множества | «[^0-9]%» - любая строка, не начинающаяся с цифры. |
Оператор LIKE гораздо удобнее, нежели конструкция типа «равно» или «не равно», ибо имеет гораздо больше возможностей.
Пример простой хранимой процедуры (это реальная процедура из работающей базы данных). Данная процедура удаляет связанные записи из нескольких таблиц: _lists, _listItems, _listGroups. Параметр @i задает идентификатор записи в главной таблице. Организована транзакция для сохранения семантической целостности базы данных при возможных сбоях во время выполнения процедуры. Все идентификаторы взяты в квадратные скобки для наглядности.
|
|
|
CREATE PROCEDURE [_DeleteFromList]
@i INT
AS
IF NOT EXISTS (SELECT [ID] FROM [_lists] WHERE [ID] = @i) RETURN -3
BEGIN TRANSACTION
-- Delete items of list
DELETE FROM [_listItems] WHERE [ListID] = @i
-- Delete groups of list
DELETE FROM [_listGroups] WHERE [ListID] = @i
-- Delete list header
DELETE FROM [_lists] WHERE [ID] = @i
COMMIT
RETURN 0
Вызов сохраненных процедур
Сохраненная процедура может быть вызвана как непосредственно, так и из другой процедуры. Для вызова служит инструкция EXECUTE
[[EXEC[UTE]] { [@переменная_возврата =] {имя_процедуры [;номер] | @переменная_с_именем } [[@параметр =] {значение | @переменная [OUTPUT] | [DEFAULT]] [,...n][WITH RECOMPILE]
Переменная возврата – это переменная, которая получит значение, возвращаемое процедурой. Имя процедуры и номер уже описаны, имя процедуры может также быть указано в переменной. Тип переменной может быть char, varchar, nchar, nvarchar.
Затем перечисляются через запятую отсылаемые в процедуру параметры. Если требуется взять значение по умолчанию, можно указать слово DEFAULT либо опустить и имя параметра (если он не последний, просто ставится очередная запятая).
Пример. Пусть имеется процедура _myProcedure, принимающая параметры @p1, @p2, @p3. Следующие вызовы эквивалентны, второй параметр взят по умолчанию:
EXEC @j = _myProcedure 'Parameter1', , 50
EXEC @j = _myProcedure @p1 = 'Parameter1', @p2 = DEFAULT, @p3= 50
Дата добавления: 2019-11-16; просмотров: 146; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!