28. 23. Процедуры t-sql: параметры, создание, вызов, возврат значения, системные процедуры.

Хранимая процедура (stored procedure) — это именованный набор команд Transact-

SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базы данных.

Типы хранимых процедур:

• Системные хранимые процедуры (system stored procedures). Это хранимые процедуры, поставляемые в составе SQL Server 2000 и предназначенные для выполнения различных административных действий — создание учетных записей (sp_addlogin и другие), получение информации об объектах базы данных, управление свойствами сервера и баз данных и т.д.

• Пользовательские хранимые процедуры (user-defined stored procedures)

• Временные (temporary stored procedures) и локальные временные хранимые процедуры (local temporary stored procedures). Такие процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером

Синтаксис:

CREATE PROCEDURE addproduct @Description NVARCHAR(100),

@InStock INT = 0, -- параметр по умолчанию будет равен 0

@IdProd INT out -- выходной параметр

AS

INSERT product

([description],

instock)

VALUES (@Description,

@InStock)

SET @IdProd = @@IDENTITY

RETURN

--ПРИМЕР ВЫЗОВА:

DECLARE @IdProd INT

EXEC Saddproduct

@Description = N'Новый товар',

@IdProd = @IdProd output

SELECT @IdProd AS N'@IdProd'

Чтобы передать некоторое возвращаемое значение из хранимой процедуры обратно в вызывающий код, достаточно применить оператор RETURN [<Целое число>].

Отличие процедур от функций:

• Процедуры могут иметь INPUT/OUTPUT параметры, функции только INPUT;

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

• Функции не могут менять данные и выполнять DDL-операторы, процедуры - могут.

• Функции могут быть вызваны из процедуры, а вот процедура не может быть вызвана из функции.

• В функциях нельзя управлять транзакциями, в процедурах можно.

• В функциях нельзя использовать Try-Catch, в процедурах можно.

• Функции могут использоваться в выражении SELECT, процедуры не могут:

• Scalar-valued function - возвращают скалярное значение

• Inline function - могут возвращать один запрос SELECT

• Table-valued function - возвращают переменную типа TABLE

29. 24. Функции: типы, параметры, создание, вызов, возврат значения, принципы применения.

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

Пользовательские функции подразделяются на 2 основных типа:

• Возвращающие скалярное сзначение

• Возвращающие таблицу.

Создание:

Возвращающие скалярное сзначение:

CREATE FUNCTION dbo.Dayonly (@Date DATETIME)

returns VARCHAR(12)

AS

BEGIN

RETURN CONVERT (VARCHA(12), @Date, 101)

END

-----ВЫЗОВ ФУНКЦИИ----

SELECT *

FROM orders

WHERE dbo.Dayonly(orderdate) = dbo.Dayonly(Getdate())

Возвращающие таблицу:

CREATE FUNCTION dbo.Fnauthorlist()

returns TABLE

AS

RETURN

(SELECT au_id,

au_lname + ', ' + au_fname AS au_name,

address AS address1,

city + ', ' + state + ' ' + zip AS address2

FROM authors)

go

-----ВЫЗОВ ФУНКЦИИ----

SELECT * FROM dbo.Fnauthorlist()

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

31. 25. Триггеры: типы триггеров, создание, назначение применение, вложенные и рекурсивные триггеры, таблицы INSERTED, DELETED и их применение, последовательность выполнения триггеров.

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

В таблицах INSERTED и DELETED находятся вставленные / удалённые строки.

Типы триггеров:

• DML запускаются с помощью инструкций INSERT, UPDATE или DELETE.

• DDL с помощью инструкций CREATE, ALTER, DROP.

Виды триггеров: after и instead of

Рекурсивный триггер:

CREATE TRIGGER my_trig

ON table_name -- tableName -- закрепление триггера к таблице или представлению

after INSERT

AS

BEGIN

IF @@TRANCOUNT < 2

INSERT table_name (id, name) VALUES(1, 'Test');

END

Синтаксис

CREATE TRIGGER primer5

ON sotrydnik

after INSERT, DELETE

AS

BEGIN

BEGIN

IF (SELECT oklad

FROM inserted) < '20000'

ROLLBACK

PRINT

'Нельзя вставлять запись о сотруднике, с окладом менее 20 000 рублей'

END

BEGIN

IF (SELECT oklad

FROM deleted) >= '50000'

PRINT

'Нельзя удалять запись о сотрудике, оклад которого более 50000'

ROLLBACK

END

END

go

Порядок запуска триггеров при никак не определен и не гарантирован. В SQL Server 2000 появилась документированная процедура sp_settriggerorder. Она позволяет указать какие триггеры AFTER срабатывают первыми или последними.

EXEC Sp_settriggerorder 'dbo.TestTrigger02', 'first', 'insert'

EXEC Sp_settriggerorder 'dbo.TestTrigger03', 'last', 'insert'