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

Скачать презентацию Использование хранимых процедур и триггеров в клиент-серверных Скачать презентацию Использование хранимых процедур и триггеров в клиент-серверных

Использование ХП и триггеров.ppt

  • Количество слайдов: 19

> Использование хранимых процедур и триггеров в клиент-серверных системах Использование хранимых процедур и триггеров в клиент-серверных системах

>  Хранимые процедуры CREATE { PROC | PROCEDURE }   [schema_name. ]procedure_name Хранимые процедуры CREATE { PROC | PROCEDURE } [schema_name. ]procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ , . . . n ] [ WITH [ ENCRYPTION ] [ RECOMPILE ] ] AS { [; ][. . . n ] | } [; ] : : = { [ BEGIN ] statements [ END ] } 2

>      Пример ХП USE Adventure. Works; GO CREATE PROCEDURE Пример ХП USE Adventure. Works; GO CREATE PROCEDURE Production. Get. List @Product varchar(40), @Max. Price money, @Compare. Price money OUTPUT, @List. Price money OUT AS SET NOCOUNT ON; SELECT p. [Name] AS Product, p. List. Price AS 'List Price' FROM Production. Product AS p JOIN Production. Product. Subcategory AS s ON p. Product. Subcategory. ID = s. Product. Subcategory. ID WHERE s. [Name] LIKE @Product AND p. List. Price < @Max. Price; -- Populate the output variable @List. Pprice. SET @List. Price = (SELECT MAX(p. List. Price) FROM Production. Product AS p JOIN Production. Product. Subcategory AS s ON p. Product. Subcategory. ID = s. Product. Subcategory. ID WHERE s. [Name] LIKE @Product AND p. List. Price < @Max. Price); -- Populate the output variable @compareprice. SET @Compare. Price = @Max. Price; GO 3

>      Выполнение ХП DECLARE @Compare. Price money, @Cost money Выполнение ХП DECLARE @Compare. Price money, @Cost money EXECUTE Production. usp. Get. List '%Bikes%', 700, @Compare. Price OUT, @Cost OUTPUT IF @Cost <= @Compare. Price BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@Compare. Price AS varchar(20)))+'. ' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@Compare. Price AS varchar(20)))+'. ' 4

>  Результат выполнения ХП Product     List Price ------------------------- Road-750 Результат выполнения ХП Product List Price ------------------------- Road-750 Black, 58 539. 99 Mountain-500 Silver, 40 564. 99 Mountain-500 Silver, 42 564. 99. . . Road-750 Black, 48 539. 99 Road-750 Black, 52 539. 99 (14 row(s) affected) These items can be purchased for less than $700. 5

>     Примеры ХП    Выбор данных за период Примеры ХП Выбор данных за период CREATE PROCEDURE [dbo]. [ORDER_SELECT] @DATE_BEG DATETIME, @DATE_END DATETIME AS BEGIN SET NOCOUNT ON; SELECT ID_ORDER, NUM_ORDER, DATE_ORDER, O. ID_SHOP, O. ID_AGENT, S. NAME AS NAME_SHOP, S. PHONE AS SHOP_PHONE, S. ADDRESS AS SHOP_ADDRESS, A. MNAME, A. FNAME, A. LNAME, A. GENDER FROM ORDERS O INNER JOIN SHOPS S ON O. ID_SHOP=S. ID_SHOP INNER JOIN AGENTS A ON A. ID_AGENT=O. ID_AGENT WHERE DATE_ORDER BETWEEN @DATE_BEG AND @DATE_END ORDER BY DATE_ORDER, NUM_ORDER END GO 6

>    Примеры ХП      Удаление записи CREATE Примеры ХП Удаление записи CREATE PROCEDURE [dbo]. [ORDER_ITEM_DELETE] @ID_ITEM INT AS BEGIN SET NOCOUNT ON; DELETE FROM ORDER_ITEMS WHERE [email protected]_ITEM END Модификация записи CREATE PROCEDURE [dbo]. [ORDER_ITEM_EDIT] @ID_ITEM INT, @ID_GOOD INT, @ID_UNIT INT, @QUANTITY NUMERIC(16, 4), @PRICE NUMERIC(16, 4), @SUMMA NUMERIC(16, 2) AS BEGIN SET NOCOUNT ON; UPDATE ORDER_ITEMS SET [email protected]_GOOD, [email protected]_UNIT, [email protected], [email protected], [email protected] WHERE [email protected]_ITEM END GO 7

>    Примеры ХП     Добавление новой записи CREATE Примеры ХП Добавление новой записи CREATE PROCEDURE [dbo]. [ORDER_ITEM_ADD] @ID_ORDER INT, @ID_GOOD INT, @ID_UNIT INT, @QUANTITY NUMERIC(16, 4), @PRICE NUMERIC(16, 4), @SUMMA NUMERIC(16, 2), @ID_ITEM INT OUTPUT AS BEGIN SET NOCOUNT ON; INSERT INTO ORDER_ITEMS (ID_ORDER, ID_GOOD, ID_UNIT, QUANTITY, PRICE, SUMMA) VALUES (@ID_ORDER, @ID_GOOD, @ID_UNIT, @QUANTITY, @PRICE, @SUMMA) SET @[email protected]@IDENTITY END GO 8

>      TRY…CATCH BEGIN TRY  { sql_statement | statement_block TRY…CATCH BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ] Функции доступные в блоке CATCH ERROR_NUMBER() возвращает номер ошибки; ERROR_SEVERITY() возвращает степень серьезности ошибки; ERROR_STATE() возвращает код состояния ошибки; ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, в котором произошла ошибка; ERROR_LINE() возвращает номер строки внутри подпрограммы, которая вызвала ошибку; ERROR_MESSAGE() возвращает полный текст сообщения об ошибке. Текст содержит значения подставляемых параметров, таких как длина, имена объектов или время. 9

>  Пример использования CREATE PROCEDURE usp_Get. Error. Info AS SELECT ERROR_NUMBER() AS Error. Пример использования CREATE PROCEDURE usp_Get. Error. Info AS SELECT ERROR_NUMBER() AS Error. Number, ERROR_SEVERITY() AS Error. Severity, ERROR_STATE() AS Error. State, ERROR_PROCEDURE() AS Error. Procedure, ERROR_LINE() AS Error. Line, ERROR_MESSAGE() AS Error. Message; GO BEGIN TRY -- Generate divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- Execute error retrieval routine. EXECUTE usp_Get. Error. Info; END CATCH; 10

>  Генерация исключений RAISERROR ( { msg_id | msg_str | @local_variable } { Генерация исключений RAISERROR ( { msg_id | msg_str | @local_variable } { , severity , state } [ , argument [ , . . . n ] ] ) [ WITH option [ , . . . n ] ] Пример BEGIN TRY RAISERROR ('Error raised in TRY block. ', 16, 1); END TRY BEGIN CATCH DECLARE @Error. Message NVARCHAR(4000); DECLARE @Error. Severity INT; DECLARE @Error. State INT; SELECT @Error. Message = ERROR_MESSAGE(), @Error. Severity = ERROR_SEVERITY(), @Error. State = ERROR_STATE(); RAISERROR (@Error. Message, @Error. Severity, @Error. State); END CATCH; 11

>    Создание триггеров CREATE TRIGGER [schema_name. ]trigger_name ON { table | Создание триггеров CREATE TRIGGER [schema_name. ]trigger_name ON { table | view } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ , . . . n ] } Отключение триггера ALTER TABLE имя_таблицы DISABLE TRIGGER имя_триггера Включение триггера ALTER TABLE имя_таблицы ENABLE TRIGGER имя_триггера 12

>Порядок выполнения транзакций • Проверка Identity Insert • Ограничение допустимости пустых значений • Проверка Порядок выполнения транзакций • Проверка Identity Insert • Ограничение допустимости пустых значений • Проверка типа данных • Выполнение триггера INSTEAD OF • Ограничение первичного ключа. • Ограничение проверки • Ограничение внешнего ключа • Выполнение инструкции DML и обновление журнала транзакций • Выполнение триггера AFTER • Подтверждение транзакции • Запись в файл данных 13

>    Примеры триггеров CREATE TRIGGER Trigger. One ON Person INSTEAD OF Примеры триггеров CREATE TRIGGER Trigger. One ON Person INSTEAD OF Insert AS PRINT 'Instead of Trigger‘ GO Вызов команды INSERT Person(Person. ID, Last. Name, First. Name, Gender) VALUES (51, 'Ebob', 'M') Результат Instead of Trigger (1 row(s) affected) SELECT Last. Name FROM Person WHERE Personl. D =51 14

>   Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing. Purchase. Order. Header p JOIN inserted AS i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor AS v ON v. Vendor. ID = p. Vendor. ID WHERE v. Credit. Rating = 5) BEGIN RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders. ', 16, 1); ROLLBACK TRANSACTION; RETURN END; -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO … GO 15

>Таблицы inserted и deleted Инструкция  Таблица Inserted   Таблица Deleted  DML Таблицы inserted и deleted Инструкция Таблица Inserted Таблица Deleted DML INSERT Вставленные строки Пустая UPDATE Строки базы данных после Строки базы данных до обновления DELETE Пустая Строки, подлежащие удалению 16

>    Примеры триггеров ALTER TRIGGER Trigger. One ON Person AFTER Insert, Примеры триггеров ALTER TRIGGER Trigger. One ON Person AFTER Insert, Update AS SET No. Count On; IF Update(Last. Name) SELECT 'Вы изменили значение столбца Last. Name на ' + Inserted. Last. Name FROM Inserted UPDATE Person SET Last. Name = 'Johnson' WHERE Person. ID =32 17

>  Примеры триггеров CREATE TRIGGER Person_Parents ON Person AFTER INSERT, UPDATE AS Примеры триггеров CREATE TRIGGER Person_Parents ON Person AFTER INSERT, UPDATE AS IF UPDATE(Father. ID) BEGIN -- Неверный пол отца IF EXISTS( SELECT * FROM Person JOIN Inserted ON Inserted. Father. ID = Person. ID WHERE Person. Gender =‘F’) BEGIN ROLLBACK RAISERROR('Incorrect Gender for Father’, 14, 1) RETURN END 18

>Рекурсивные и вложенные триггеры      19 Рекурсивные и вложенные триггеры 19