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

Сценарий может состоять из одного или нескольких пакетов.

Пакет – это набор операторов SQL, одновременно передаваемых SQL –серверу и

SQL Server компилирует операторы пакета в единую исполнимую единицу (план исполнения).

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

В современной клиент-серверной вычислительной среде приложение взаимодействует с сервером баз данных

Работа такой схеме основывается на использовании так называемого серверного кода (server-side

Использование серверного кода значительно сокращает объем сетевого трафика, и тем самым

PL/SQL является таким расширением SQL в СУБД Oracle, Transact SQL –

Процедурные расширения SQL описаны в документе SQL/PSM стандарта SQL99.
Язык SQL

Процедурные расширения
Поскольку SQL не является языком программирования), вводимые разными производителями расширения

Перечень процедурных расширений для самых популярных СУБД

Операторы SQL могут выполняться разными способами:
по одному,
как сценарии,
пакетами,

Хранимая процедура - это специальный тип пакета инструкций Transact-SQL
Основное различие

Как правило, в работе с БД используются одни и те же

Хранимые процедуры могут включать любые операторы SQL, а также некоторый набор

С точки зрения приложений, работающих с БД, хранимые процедуры – это

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

Обычно все ограничения целостности в виде правил и алгоритмов обработки данных

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

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

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

Системные хранимые процедуры хранятся в базе данных master, их имена начинаются

Запускаются хранимые процедуры всех типов с помощью команды EXECUTE, которую можно

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

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

Глобальные временные хранимые процедуры доступны для любых соединений сервера.
Для определения

Хранимая процедура после ее создания помещается в кэш процедур.
Кэш процедур

Для просмотра кода хранимой процедуры служит системная процедура sp_helptext.

Хранимая процедура компилируется один раз и после этого может выполняться многократно.

Как серверные программы хранимые процедуры имеют следующие преимущества.
Хранимые процедуры хранятся в

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

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

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

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

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут

Для создания хранимой процедуры используется оператор SQL CREATE PROCEDURE.
Синтаксис:
CREATE PROCEDURE

Ключевое слово RECOMPILE определяет режим компиляции.
Если RECOMPILE задано, то процедура

Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры

Процедуры могут быть процедурами или функциями. Эти понятия трактуются традиционно.
В

Выполнение процедуры осуществляется с помощью команды
EXEC[UTE] имя процедуры [@параметр =

Значение параметров задается путем передачи значений по позиции или с заданием

Передача значений без ссылок на имена параметров (передача значений по позиции).

Нельзя присвоить параметру значение
по умолчанию, просто пропустив его при перечислении.

Если вызов хранимой процедуры не является единственной командой в пакете, то

Возврат значений при помощи параметров вывода
Для использования параметра вывода ключевое слово

Процедурные расширения
Поскольку SQL не является языком программирования), вводимые разными производителями расширения

Элементы Transact SQL
для управления ходом
выполнения

Операторы управления выполнением программы:
В процедурах могут использоваться следующие операторы управления:
1.

IF (SELECT COUNT(*)
FROM STAFF WHERE Position = 'менеджер' ) < 3
PRINT

2. Выбор одного из нескольких значений
CASE <переменная>
WHEN <условие1> THEN

3. Циклическое выполнение операций
WHILE <логическое выражение>
BEGIN
<операторы>
END
В

Оператор RETURN выполняет безусловный выход из процедуры.

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

Для объявления переменных, которые используются в процедуре, служит директива DECLARE.
Идентификаторы

Неправильное использование SET:

1)
DECLARE @T INTEGER
SELECT @T= COUNT(*) FROM Property
SELECT @T
2)
DECLARE @T INTEGER
SET @T=

Глобальные переменные

Обработка ошибок

SQL Server позволяет обрабатывать ошибки, используя глобальную переменную @@ERROR.

Недостатки подхода с использованием @@Error
Значение переменной @@Error должно быть проверено сразу

@‍@ROWCOUNT возвращает количество строк, затронутых (возвращённых, удалённых, изменённых) последним запросом.
Например:

Инструкция RAISERROR возвращает пользовательское сообщение об ошибке, позволяет пользователям получать записи

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

Система обозначений уровней важности ошибок охватывает широкий спектр сообщений об

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

Уровень важности ошибки от 0 до 18 может указать любой пользователь.

Состояние - целое число, которое идентифицирует источник сообщения, если оно может

Сообщение об ошибке можно стандартизировать и использовать многократно. Для этого сообщение

Флаг FALSE|TRUE определяет, должно ли сообщение записываться в журнал ошибок.
REPLACE

Начиная с SQL Server 2005, появилась возможность обработать исключения, используя операторы

Внутри блока CATCH доступны функции для обработки исключений:
ERROR_NUMBER() — номер ошибки;
ERROR_SEVERITY()

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

Ошибки, обнаруженные в блоке CATCH, не передаются в вызывающее приложение. Если

CREATE PROCEDURE insert_data @a int, @b int
BEGIN TRY
BEGIN TRANSACTION

В теле процедуры не должны применяться локальные переменные, чьи имена совпадают

Пример: создается хранимая процедура для вычисления суммы 2 чисел.
USE <имя БД>

DECLARE @S INT
EXEC SUMMA 2, 3, @S OUTPUT
SELECT @S

Пример.
Создать процедуру для начисления стипендии студентам в зависимости от полученного среднего

CREATE PROCEDURE STIP
@N TINYINT --средний балл
AS
BEGIN
DECLARE @X

Вызов процедуры:
EXEC STIP 10

ALTER PROCEDURE [dbo].[STIP]
@N TINYINT, @X INT OUTPUT
AS
BEGIN
SELECT @X=

Запуск процедуры:
DECLARE @Y INT
EXEC STIP 6, @Y OUTPUT
SELECT ‘Установлена стипендия:

Пример создания процедуры с
входным параметром и значением по умолчанию
для

EXEC UPDATE_Price 4
или
EXEC UPDATE_Price - будет использовано значение по умолчанию.

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

CREATE PROCEDURE [dbo].[INCREASE_SALARY] (@Staff_no nchar(9), @percent decimal)
AS
DECLARE @S int
SELECT @S

Выражение IF EXISTS()
Выражение IF EXISTS() использует в качестве условия наличие какой-либо

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

3. Процедура для повышения стипендии студентам, прослушавшим спецкурс по заданному предмету.

CREATE PROC IND_STIP
(@NOM_ZACH int,
@PKOD smallint,
@IND real)
AS
IF

Для запуска процедуры:
EXEC IND_STIP @NOM_ZACH = 1298, @PKOD =1212, @IND=1.2

4. Создать процедуру для повышения заработной платы сотрудника только в том

CREATE PROC NEW_SALARY
(@Staff_no char(9),
@Procent decimal)
AS
IF EXISTS (SELECT property_no

CREATE PROC NEW_SALARY
(@Staff_no char(9))
AS
IF (SELECT count(*)
FROM PROPERTY
WHERE

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

ALTER PROC my_proc2 @count_number INT OUTPUT
AS
BEGIN
DECLARE @AVSAL money
EXEC @AVSAL =