Хранимые процедуры
Хранимые процедуры
С точки зрения приложений, работающих с БД, хранимые процедуры – это подпрограммы, которые выполняются на сервере. По отношению к БД – это объекты, которые хранятся в БД. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно снизить трудозатраты на создание прикладного программного обеспечения. Также как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их вовсе.
Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами. Хранимые процедуры пишутся на специальном встроенном языке программирования, могут включать любые операторы SQL, а также некоторый набор операторов, которые управляют ходом выполнения программы, которые во многом схожи с подобными операторами процедурно ориентированных языков программирования. В СУБД Oracle для создания хранимых процедур используется язык PL/SQL (в последних версиях – Java), а в MS SQL Server - язык Transact SQL.
1. Элементы Transact SQL
Операторы управления выполнением программы:
В процедурах могут использоваться следующие операторы управления:
1. Оператор условия
IF <выражение>
BEGIN
Рекомендуемые материалы
<операторы>
END
[ELSE]
[IF <выражение>]
BEGIN
<операторы>
END
Если используется один оператор, то BEGIN … END не нужен.
2. Циклическое выполнение операций
WHILE <логическое выражение>
BEGIN
<операторы>
END
В этом операторе можно также использовать операторы BREAK и CONTINUE, которые позволяет прервать выполнение этого цикла.
Выбор одного из нескольких значений
CASE <переменная>
WHEN <условие1> THEN <оператор1>
WHEN <условие2> THEN <оператор2>
WHEN <условие3> THEN <оператор3>
…
ELSE <оператор>
END
Оператор RETURN выполняет безусловный выход из запроса или процедуры.
В программу можно включать комментарии. Однострочные комментарии отделяются от оператора двумя дефисами (--). Многострочные комментарии заключаются в символы: (/*…*/).
Для объявления переменных, которые используются в процедуре, служит директива DECLARE. Если необходимо присвоить переменной какое-либо значение, используется ключевое слово SET или SELECT. Если значение присваивается одной переменной, эти команды эквивалентны. Команда SELECT может присваивать значения одновременно нескольким переменным. Идентификаторы переменных начинаются с символа @.
Функции
Для большинства функций используется следующий синтаксис:
имя функции(аргумент) или @@имя функции.
Например: @@TRANCOUNT возвращает число незавершенных транзакций
IF (@@TRANCOUNT>0)
BEGIN
RAISEERROR (‘задание не может быть выполнено в транзакции’, 10,1)
RETURN
END
Оператор RAISEERROR возвращает пользовательское сообщение об ошибке, позволяет пользователям получать записи из системной таблицы sysmessages или создавать сообщение динамически, на основе заданного пользователем уровня и кода ошибки.
Синтаксис:
RAISEERROR({код ошибки|символьная строка} уровень ошибки, состояние). Сообщение об ошибке можно стандартизировать и использовать многократно. Для этого сообщение следует определить в БД. Синтаксис включения сообщения в БД:
Sp_addmessage код сообщения, код важности, текст сообщения[,язык[FALSE|TRUE],REPLACE]]]
Флаг FALSE|TRUE определяет, должно ли сообщение записываться в журнал ошибок. REPLACE заменяет существующее сообщение с указанным кодом.
Функция @@ROWCOUNT возвращает количество строк, измененных последним запросом.
Например:
UPDATE STAFF SET LastName = ‘Иванов’
WHERE LastName = ‘Иванков’
IF(@@ROWCOUNT=0)
BEGIN
PRINT ‘Внимание : Ни одна запись не была изменена’
RETURN
END
Операторы TRANSACT-SQL могут выполняться разными способами: по одному, пакетами, в составе хранимых процедур или триггеров, как сценарии.
Пакет – это набор операторов TRANSACT-SQL, одновременно передаваемых SQL –серверу и выполняемых как единая группа. Пакеты выполняются интерактивно или в составе сценария. Пакеты являются основой для программ SQL Server. Пакет содержит одну или несколько команд SQL. Команды пакета анализируются, компилируются и выполняются как единая группа. Если вызов хранимой процедуры не является первой командой пакета, перед именем процедуры должно стоять ключевое слово EXEC.
2. Создание хранимых процедур
Обработка хранимых процедур включает ее создание и перемещение в кэш процедур – область памяти, используемую сервером для хранения скомпилированных планов выполнения сохраненной процедуры. Хранимую процедуру можно создавать в следующем порядке: сначала написать операторы Transact SQL, которые должны быть включены в хранимую процедуру, и проверить и работу, а затем, если нужный результат получен, можно создать хранимую процедуру.
Для создания хранимой процедуры используется оператор SQL CREATE PROCEDURE.
Синтаксис:
CREATE PROCEDURE <имя процедуры> (@<имя перем1> <тип данных>, @<имя перем2> <тип данных>…)
[@параметр тип данных[=значение по умолчанию] [OUTPUT]
[WITH
{ RECOMPILE
|ENCRYPTION
|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS <тело процедуры>
Ключевое слово RECOMPILE определяет режим компиляции. Если RECOMPILE задано, то процедура будет перекомпилироваться всякий раз, когда она будет передаваться на выполнение (план выполнения не кэшируется, при каждом выполнении создается новый план). Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Кроме имени все остальные параметры являются необязательными.
Каждая хранимая процедура компилируется при первом выполнении. Описание процедуры совместно с планом ее работы хранится в системных таблицах БД.
3. Выполнение хранимой процедуры.
Выполнение процедуры осуществляется с помощью команды
EXEC[UTE] имя процедуры [@параметр = значение]
Значение параметра задается путем передачи значения или ссылки на позицию.
Передача параметра со ссылками на имена осуществляется следующим образом: @параметр = значение. При этом значения параметров можно указывать в любом порядке, а параметры, имеющие значение по умолчанию, опускать.
Передача значений без ссылок на имена параметров называется передачей значений по позиции. Если указываются только значения, их следует перечислять в порядке определения в операторе CREATE PROC. При этом можно опускать параметры, имеющие значения по умолчанию, но последовательность параметров нарушать нельзя.
Возврат значений при помощи параметров вывода.
Для использования параметра вывода ключевое слово OUTPUT должно указываться как в операторе CREATE PROC, так и в операторе EXEC. Вызывающий оператор должен указать для параметра вывода имя переменной.
Пример: создается хранимая процедура для вычисления суммы 2 чисел.
USE <имя БД>
GO
CREATE PROCEDURE SUMMA
@A smallint,
@B smallint,
@S smallint OUTPUT
AS
SET @S= @A+@B
Вызов:
DECLARE @result smallint
EXEC SUMMA 5,7,@result OUTPUT
SELECT ‘результат=’, @result
После выполнения оператора EXEC переменная @result будет хранить значение параметра вывода. Вывод значения осуществляется оператором SELECT.
Процедуры могут быть процедурами или функциями. Эти понятия трактуются традиционно. Процедура в явном виде не возвращает значение, но в ней может быть использовано ключевое слово, которое определяет, что данный параметр является выходным.
Примеры:
1.Создадим процедуру NEW_COURSE для увеличения значения атрибута COURSE при переводе студентов на следующий курс.
CREATE PROCEDURE NEW_COURSE
AS UPDATE STUDENT
SET COURS=COURSE + 1
Вызов процедуры:
EXEC NEW_COURSE
2. Создадим процедуру для начисления стипендии студентам в зависимости от полученного среднего балла. В процедуру передается значение среднего балла, в зависимости от которого система устанавливает ставку стипендии.
CREATE PROCEDURE STIP
@N TINYINT --средний балл
AS
BEGIN
DECLARE @X REAL
SELECT @X=
CASE @N
WHEN 3 THEN 20
WHEN 4 THEN 30
WHEN 5 THEN 40
ELSE 0
END
PRINT ‘Установлена стипендия:’
PRINT @X
END
Вызов:
EXEC STIP 3
3.Функция, реализующая эту же задачу:
CREATE PROCEDURE STIP
@N TINYINT
AS
BEGIN
DECLARE @X REAL
SELECT @X=
CASE @N
WHEN 3 THEN 20
WHEN 4 THEN 30
WHEN 5 THEN 40
ELSE 0
END
RETURN @X
END
DECLARE @ST REAL
EXEC @St=Stip 3
SELECT "стипендия =", @ST
3. Процедура для повышения стипендии студентам, прослушавшим спецкурс по заданному предмету.
В процедуру будут передаваться следующие параметры: @NOM_ZACH – номер зачетки, @PKOD – код предмета и @IND – процент индексации стипендии.
CREATE PROC IND_STIP
(@NOM_ZACH int,
@PKOD smallint,
@IND real)
AS
IF EXISTS (SELECT PKOD, NOM_ZACH
FROM USP
WHERE NOM_ZACH = @ NOM_ZACH
AND PKOD= @PKOD)
UPDATE STUDENTS SET STIP=STIP*@IND
Для запуска процедуры:
EXEC IND_STIP @NOM_ZACH = 1298, @PKOD =1212, @IND=1.2
4. КОНТРОЛЬНЫЕ ВОПРОСЫ
1. С какой целью создаются хранимые процедуры?
2. Где хранятся хранимые процедуры?
3. Как осуществляется вызов хранимых процедур?
4. Какие операторы управления включает Transact SQL?
В лекции "10 Мониторы и видеоадаптеры" также много полезной информации.
5. Каким образом осуществляется присваивание значений в Transact SQL?
6. Как осуществляется вызов хранимой процедуры?
7. Какими ключевыми словами задается режим компиляции и режим сохранения исходного текста при создании хранимой процедуры?
8. Какими способами могут быть переданы параметры в хранимую процедуру?
9. Какая функция Transact SQL возвращает количество незавершенных транзакций?
10. Каким образом задаются параметры вывода при создании и вызове хранимых процедур?
