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

Сентябрь 2, 2022

Главная
Алгебра
Хранимые процедуры и триггеры

Содержание

2. Хранимые процедуры Хранимая процедура (Stored Procedure) – это именованный набор команд языка Transact-SQL, хранящийся на сервере
3. Типы хранимых процедур Системные ХП – входят в состав SQL Server, реализуют все действия администрирования сервера,
4. Создание хранимых процедур CREATE PROC[EDURE] [{@ } [= ] [OUTPUT] ] [,…n] AS [,…n] - начинается
5. Создание хранимых процедур CREATE PROC DeleteReader @ReaderID INT AS DELETE FROM Readers WHERE reader_id = @ReaderID
6. Передача параметров Вызов хранимой процедуры: EXEC[UTE] [ @ = ] [ [ @ = ] {
7. Передача параметров Способы передачи параметров: Позиционные параметры DECLARE @R CHAR(50) EXEC GetReaderName 5, @R OUTPUT PRINT
8. Возвращение данных из хранимой процедуры Стандартный набор строк или набор данных (RecordSet, DataSet – несколько RecordSet).
9. Управление хранимыми процедурами Изменение (кроме имени). Процедура заменяется полностью: ALTER PROCEDURE далее как в CREATE PROCEDURE
10. Управляющие конструкции Transact-SQL BEGIN…END – блок BREAK – выход из цикла CONTINUE – переход к началу
11. Управляющие конструкции Transact-SQL DECLARE @MyCounter INT; SET @MyCounter = 1; -- Все строки будут заполнены одинаково:
12. Управляющие конструкции Transact-SQL WHILE (SELECT AVG(ListPrice) FROM Production.Product) BEGIN UPDATE Production.Product SET ListPrice = ListPrice *
13. Триггеры Триггер – процедура, связанная с таблицей или представлением, которая автоматически выполняется при выполнении операции вставки,
14. Триггеры Создание триггера: CREATE TRIGGER ON | {FOR | AFTER | INSTEAD OF} { [DELETE] [,]
15. Триггеры AFTER триггер выполняется в транзакции Сравнение AFTER и INSTEAD OF триггеров: AFTER триггер выполняется после
16. Триггеры CREATE TRIGGER ExemplarsUpdateTrigger ON Exemplars AFTER UPDATE AS BEGIN IF (EXISTS (select e.reader_id FROM Exemplars
17. Триггеры Результат выполнения триггера
18. Триггеры Таблицы inserted и deleted Команда INSERT: inserted содержит все вставляемые строки, deleted – пустая Команда
19. Триггеры UPDATE( ) – логическая функция для проверки изменения значения столбца (только для команд INSERT и
20. Триггеры CREATE TRIGGER updEmployeeData ON employeeData AFTER UPDATE AS /*Check whether columns 2, 3 or 4
22. Скачать презентацию

Слайд 2

Хранимые процедуры
Хранимая процедура (Stored Procedure) – это именованный набор команд языка

Transact-SQL, хранящийся на сервере в качестве самостоятельного объекта БД

SP хранится на сервере (пакет – на клиенте)
Для вызова используется имя (у пакета нет имени)
SP могут вызвать друг друга (пакеты не могут)
Изменения вносятся в одном месте, на сервере (в пакеты – на всех клиентах)
Реализуется модульность
Уменьшается объем передаваемой информации по сети

Слайд 3

Типы хранимых процедур
Системные ХП – входят в состав SQL Server, реализуют

все действия администрирования сервера, начинаются с sp_, хранятся в БД master. Контекст выполнения любой
Пользовательские ХП (User-Defined SP) – размещаются в пользовательских БД и выполняются в контексте только одной БД
Временные ХП – существуют только в рамках одного соединения с сервером (временно хранятся в tempDB)

Слайд 4

Создание хранимых процедур
CREATE PROC[EDURE] <имя процедуры>
[{@<параметр> <тип>} [= <значение по

умолчанию>]
[OUTPUT] ] [,…n]
AS [,…n]

<имя процедуры> - начинается с «sp_» для системных ХП, «#» - для локальных временных ХП, «##» - для глобальных временных ХП.
Параметры и локальные переменные начинаются с @. Параметры разделяются запятыми, количество параметров до 1024.
OUTPUT помечает выходной параметр (он же является и входным)
<значение по умолчанию> присваивается входному параметру, если при вызове процедуры ему не присваивается значение
Возвращаемое значение процедуры (как для функции) задается в теле процедуры оператором RETURN

Слайд 5

Создание хранимых процедур
CREATE PROC DeleteReader @ReaderID INT
AS
DELETE FROM Readers WHERE reader_id = @ReaderID
--

-----------------------------------------------------------------
CREATE PROC GetReaderName @ReaderID INT, @ReaderName VARCHAR(50) OUTPUT
AS
SELECT @ReaderName = first_name+’ ‘ + last_name FROM Readers WHERE reader_id = @ReaderID
-- -----------------------------------------------------------------
CREATE PROC ReaderExists @Surname VARCHAR(50)
AS
DECLARE @Count INT
SET @Count = (SELECT COUNT(*) FROM Readers WHERE last_name = @Surname)
IF @Count = 0 RETURN 0
ELSE RETURN 1

-- Без параметров
CREATE PROC GetReaders
AS
SELECT * FROM Readers

Слайд 6

Передача параметров
Вызов хранимой процедуры:
EXEC[UTE]
[ @<переменная> = ] <имя процедуры>
[

[ @<параметр> = ] {<значение> | @<переменная> [OUT[PUT]] | [DEFAULT] } ] [,…n]

Слайд 7

Передача параметров
Способы передачи параметров:
Позиционные параметры DECLARE @R CHAR(50) EXEC GetReaderName 5, @R OUTPUT PRINT

@R
Ключевые параметры DECLARE @R CHAR(50) EXEC GetReaderName @ReaderID = 5, @ReaderName = @R OUTPUT -- или в другом порядке EXEC GetReaderName @ReaderName = @R OUTPUT, @ReaderID = 5

Слайд 8

Возвращение данных из хранимой процедуры
Стандартный набор строк или набор данных (RecordSet,

DataSet – несколько RecordSet). Если в теле процедуры выполняется SELECT, то возвращается набор строк (таблица), если несколько операторов SELECT, то набор данных (набор таблиц). EXEC GetReaders
OUTPUT-параметры (пример выше)
Код завершения возвращается всегда. По умолчанию он равен 0. Изменяется оператором RETURN: DECLARE @R INT EXEC @R = ReaderExists ‘Петров’ PRINT @R

Слайд 9

Управление хранимыми процедурами
Изменение (кроме имени). Процедура заменяется полностью: ALTER PROCEDURE <имя процедуры> далее

как в CREATE PROCEDURE
Изменение имени – при помощи системной хранимой процедуры sp_rename ‘<старое имя>’, ‘<новое имя>’ [, ‘object’] (она же используется для переименования таблиц и столбцов в таблице) например, exec sp_rename ‘GetReader’, ‘GetAllReaders’, ‘object’
Удаление хранимой процедуры DROP PROCEDURE <имя процедуры>

Слайд 10

Управляющие конструкции Transact-SQL
BEGIN…END – блок
BREAK – выход из цикла
CONTINUE – переход

к началу цикла
GOTO – безусловный переход
IF…ELSE – ветвление
RETURN – возврат из хранимой процедуры
WHILE – цикл с предусловием
TRY…CATCH – обработка исключения

Слайд 11

Управляющие конструкции Transact-SQL
DECLARE @MyCounter INT;
SET @MyCounter = 1;
-- Все строки будут

заполнены одинаково:
SELECT FirstColumnHeading = 'xyz',
SecondColumnHeading = ProductID
FROM Production.Product;
DECLARE @compareprice money, @cost money
EXECUTE usp_GetList '%Bikes%', 700,
@compareprice OUT,
@cost OUTPUT
IF @cost <= @compareprice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed $'+
RTRIM(CAST(@compareprice AS varchar(20)))+'.'

Слайд 12

Управляющие конструкции Transact-SQL
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
UPDATE Production.Product

SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice) FROM Production.Product
IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';

Слайд 13

Триггеры
Триггер – процедура, связанная с таблицей или представлением, которая автоматически выполняется

при выполнении операции вставки, изменения или удаления строки этой таблицы или представления
Назначение триггеров:
Обеспечение целостности данных (например, не стандартное каскадное удаление)
Сокращение затрат на программирование приложений (общие действия – в триггер)
Автоматическое ведение журнала изменений базы данных
Автоматическое предупреждение об изменении данных в БД
Классификация по типу действия:
INSERT TRIGGER – запускаются при выполнении команды INSERT
UPDATE TRIGGER – запускаются при выполнении команды UPDATE
DELETE TRIGGER – запускаются при выполнении команды DELETE
Классификация по типу поведения:
AFTER – триггер выполняется после успешного выполнения команды
INSTEAD OF – триггер вызывается вместо выполнения команды. Для представлений можно использовать только триггер INSTEAD OF.

Слайд 14

Триггеры
Создание триггера:
CREATE TRIGGER <имя триггера>
ON <имя таблицы> | <имя представления>
{FOR |

AFTER | INSTEAD OF}
{ [DELETE] [,] [INSERT] [,] [UPDATE]}
AS
[…n]
FOR и AFTER – синонимы.

Слайд 15

Триггеры
AFTER триггер выполняется в транзакции
Сравнение AFTER и INSTEAD OF триггеров: AFTER

триггер выполняется после того, как действие команды было завершено. Поэтому, если необходимо отменить действие команды, то в AFTER триггере надо использовать конструкцию ROLLBACK TRANSACTION. В этой же ситуации в INSTEAD OF триггере не надо отменять действие, т.к. оно не выполняется (т.е. не надо использовать ROLLBACK TRANSACTION). Но для фиксации операции сам триггер должен выполнить соответствующую операцию (INSERT, DELETE, UPDATE).

Слайд 16

Триггеры
CREATE TRIGGER ExemplarsUpdateTrigger
ON Exemplars
AFTER UPDATE
AS
BEGIN
IF (EXISTS (select

e.reader_id
FROM Exemplars e WHERE e.reader_id IS NOT NULL
GROUP BY e.reader_id
HAVING count(*) > 2))
BEGIN
ROLLBACK TRANSACTION
PRINT ‘Попытка взять более 2 книг'
END
END

-- Взять все свободные экземпляры
UPDATE Exemplars SET reader_id = 1 WHERE reader_id IS NULL

Слайд 17

Триггеры
Результат выполнения триггера

Слайд 18

Триггеры
Таблицы inserted и deleted
Команда INSERT: inserted содержит все вставляемые строки, deleted

– пустая
Команда DELETE: inserted – пустая, deleted содержит удаленные строки
Команда UPDATE: inserted содержит новые значения строк, deleted – старые (заменяемые) значения

CREATE TRIGGER ExemplarsDeleteTrigger
ON Exemplars
AFTER DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM deleted WHERE reader_id IS NOT NULL)
BEGIN
ROLLBACK TRANSACTION
PRINT ‘Запрещено удалять не возвращенные книги'
END
END

DELETE FROM Exemplars WHERE inv = 2

Слайд 19

Триггеры
UPDATE(<имя столбца>) – логическая функция для проверки изменения значения столбца (только

для команд INSERT и UPDATE)
COLUMNS_UPDATED () - функция возвращает результат типа VARBINARY, каждому разряду соответствует номер столбца (0 – не изменен, 1 – изменен)

CREATE TRIGGER reminder ON Person.Address AFTER UPDATE
AS
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;

Слайд 20

Триггеры
CREATE TRIGGER updEmployeeData ON employeeData AFTER UPDATE
AS
/*Check whether columns

2, 3 or 4 have been updated. If any or all columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test whether all columns 2, 3, and 4 are updated, use = 14 instead of >0 (below).*/
IF (COLUMNS_UPDATED() & 14) > 0 /*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData (audit_log_type, audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN)
SELECT 'OLD', del.emp_id, del.emp_bankAccountNumber, del.emp_salary, del.emp_SSN FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData (audit_log_type, audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN)
SELECT 'NEW', ins.emp_id, ins.emp_bankAccountNumber, ins.emp_salary, ins.emp_SSN FROM inserted ins
END;

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

Содержание

Хранимые процедурыХранимая процедура (Stored Procedure) – это именованный набор команд языка

Типы хранимых процедурСистемные ХП – входят в состав SQL Server, реализуют

Создание хранимых процедурCREATE PROC[EDURE] <имя процедуры> [{@<параметр> <тип>} [= <значение по

Создание хранимых процедурCREATE PROC DeleteReader @ReaderID INTASDELETE FROM Readers WHERE reader_id = @ReaderID--

Передача параметровВызов хранимой процедуры:EXEC[UTE] [ @<переменная> = ] <имя процедуры> [

Передача параметровСпособы передачи параметров:Позиционные параметры DECLARE @R CHAR(50) EXEC GetReaderName 5, @R OUTPUT PRINT

Возвращение данных из хранимой процедурыСтандартный набор строк или набор данных (RecordSet,

Управление хранимыми процедурамиИзменение (кроме имени). Процедура заменяется полностью: ALTER PROCEDURE <имя процедуры> далее

Управляющие конструкции Transact-SQLBEGIN…END – блокBREAK – выход из циклаCONTINUE – переход

Управляющие конструкции Transact-SQLDECLARE @MyCounter INT;SET @MyCounter = 1;-- Все строки будут

Управляющие конструкции Transact-SQLWHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300BEGIN UPDATE Production.Product

ТриггерыТриггер – процедура, связанная с таблицей или представлением, которая автоматически выполняется

ТриггерыСоздание триггера:CREATE TRIGGER <имя триггера>ON <имя таблицы> | <имя представления>{FOR |

ТриггерыAFTER триггер выполняется в транзакцииСравнение AFTER и INSTEAD OF триггеров: AFTER

ТриггерыCREATE TRIGGER ExemplarsUpdateTrigger ON Exemplars AFTER UPDATEAS BEGINIF (EXISTS (select

ТриггерыРезультат выполнения триггера

ТриггерыТаблицы inserted и deletedКоманда INSERT: inserted содержит все вставляемые строки, deleted

ТриггерыUPDATE(<имя столбца>) – логическая функция для проверки изменения значения столбца (только

ТриггерыCREATE TRIGGER updEmployeeData ON employeeData AFTER UPDATE AS /*Check whether columns

Похожие презентации