Приклади програмування збережених процедур

Збережені процедури істотно розширюють можливості програмування мовою Transact-SQL. У наступних прикладах детальніше розглянемо використанню вхідних і вихідних параметрів.

Параметри і змінні - основа динамічності збережених процедур. Вхідні параметри дозволяють користувачеві, що виконує процедуру, передавати процедурі різні значення і одержувати в кожному випадку конкретні унікальні результати. Вихідні параметри дозволяють одержувати не тільки результуючий набір, але й додаткові відомості. Під час виконання процедури значення вихідних параметрів зберігаються в пам'яті. Щоб одержати значення вихідного параметра, необхідно створити змінну для його зберігання. Отримане значення можна відобразити за допомогою команд SELECT і PRINT або використати його для виконання інших команд процедури.

Вхідні параметри ми вже застосовували раніше - при створенні збереженої процедури CustOrderHistRep використали вхідний параметр @CustomerID, якому перед виконанням процедури привласнювали значення thecr.

Вхідний параметр визначають у збереженій процедурі, а його значення задають при її виконанні. Вихідний параметр збереженої процедури визначається за допомогою ключового слова OUTPUT. При виконанні процедури значення вихідного параметра зберігається в пам'яті. Щоб використати його, необхідно оголосити змінну для зберігання цього значення. Як правило, вихідні значення відображаються після завершення виконання процедури. Використання вхідних і вихідних параметрів ілюструє наступна процедура:

USE Pubs

GO

CREATE PROCEDURE dbo.SalesForTitle

@Title varchar(80),

@YtdSales int OUTPUT,

@TitleText varchar(80) OUTPUT

AS

SELECT @YtdSales = ytd_sales, @TitleText=title

FROM titles WHERE title LIKE @Title

GO

@Title - це вхідний параметр, а вихідні — @YtdSales і @TitleText. Зверніть увагу, що для всіх трьох параметрів визначений тип даних. У визначенні вихідних параметрів присутнє обов'язкове ключове слово OUTPUT. Всі параметри після визначення застосовуються в операторі SELECT. Спочатку значення вихідних параметрів встановлюються рівними іменам стовпців у запиті. Під час виконання запиту у вихідних параметрах будуть зазначені значення із цих двох стовпців. У конструкції WHERE оператора SELECT є присутнім вхідний параметр @Title. При виконанні цієї процедури необхідно задати значення вхідного параметра, інакше запит не виконається. Наступний оператор виконує процедуру SalesForTitle:

-- Оголосити змінні, що одержують вихідні значення процедури.

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

@YtdSales = @y_YtdSales OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "%Garlic%"

Select "Title" = @t_TitleText. "Number of Sales" = @y_YtdSales

GO

Оголошуються дві змінні: @y_YtdSales і @t_TitleText. Вони одержують значення, які зберігаються у вихідних параметрах. Зверніть увагу, що типи даних, оголошені для цих змінних, збігаються з типами даних відповідних вихідних параметрів. Ці змінні можуть мати ті ж імена, що й вихідні параметри, оскільки в збережених процедурах змінні локальні для пакета, у якому вони оголошені. Для ясності тут використані імена змінних, відмінні від імен вихідних параметрів. При оголошенні змінної її значення не збігається зі значенням вихідного параметра. Значення змінних стають рівними значенням вихідних параметрів лише після виконання оператора EXECUTE. Зверніть увагу, що після присвоєння змінним значень параметрів задане ключове слово OUTPUT. Без нього оператор SELECT, розташований наприкінці пакета, не зможе вивести значення змінних. Зверніть також увагу на значення вхідного параметра @Title, що дорівнює %Garlic%. Це значення передається в конструкцію WHERE оператора SELECT збереженої процедури. Оскільки в конструкції WHERE зазначене ключове слово LIKE, можна використати знаки підстановки, наприклад %. Отриманий у результаті запит настроєний на пошук заголовків, у яких утримується слово «Garlic».

Нижче показаний більше простий спосіб виконання процедури. Зверніть увагу, що зовсім не обов'язково явно привласнювати змінним збереженої процедури значення вхідного параметра або оголошених тут вихідних змінних:

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

"%Garlic%"

@y_YtdSales OUTPUT,

@t_TitleText OUTPUT

Select "Title" = @t_TitleText, "Number of Sales" = @y_YtdSales

GO

Особливість цієї процедури в тім, що вона повертає єдиний рядок. Навіть якщо оператор SELECT із процедури повертає кілька рядків, у кожної змінній зберігається тільки одне значення (дані з останнього повернутого рядка). Способи рішення цієї проблеми, описані далі.

Оператор RETURN і обробка помилок

Часто основні зусилля при програмуванні якісних збережених процедур (та й будь-яких програм) витрачаються на реалізацію обробки помилок. SQL Server надає функції і оператори для обробки помилок, що виникають під час виконання процедури. Помилки можна розділити на дві основні категорії: пов'язані з комп'ютерами, наприклад, коли недоступний сервер баз даних, і користувальницькі. Для обробки помилок, що виникають під час виконання процедур, використовуються коди повернення й функція @@ERROR.

Коди повернення придатні не тільки для обробки помилок. Оператор RETURN використовується для генерації кодів повернення і виходу з пакету, він може повернути програмі, яка викликала процедуру будь-яке цілочислене значення. У цьому розділі показані приклади програм, у яких оператор RETURN повертає значення для обробки помилок і інших цілей. Оператор RETURN використовується головним чином для обробки помилок, оскільки при виконанні цього оператора виконується безумовний вихід із процедури.

Для користувача більш корисне повідомлення про відсутність записів, що відповідають запиту. У наступному прикладі показано, як модифікувати збережену процедуру SalesEorTitle, щоб задіяти оператор RETURN (і створити більше інформативне повідомлення для користувача):

ALTER PROCEDURE dbo.SalesForTitle

@Title varchar(80),

@YtdSales int OUTPUT.

@TitleText varchar(80) OUTPUT

AS

IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

RETURN(1)

ELSE

SELECT @YtdSales = ytd_sales, @TitleText=title

FROM titles WHERE title LIKE @Title

GO

Оператор IF після ключового слова AS визначає, чи заданий вхідний параметр при виконанні процедури і чи відповідають йому які-небудь записи в базі даних. Якщо функція COUNT повертає 0, то код повернення встановлюється рівним 1: RETURN(1). Якщо функція COUNT повертає значення, відмінне від 0, то оператор SELECT запитує з таблиці Titles річний обсяг продажу і відомості про видання. У цьому випадку код повернення дорівнює 0.

Щоб задіяти коди повернення, прийде перепрограмувати оператори для виконання процедури. У наступному прикладі вхідному параметру @Title привласнюється значення Garlic%;

DECLARE @y_YtdSales int, @t_Titl-@Text varchar(80), @r_Code int

EXECUTE @r_Code = SalesForTitle

@YtdSales = @y_YtdSales OUTPUT,

@TitleText = @t_TitleText OUTPUT

@Title = "Garlic%"

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"Number of Sales" = @y_YtdSales,

"Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),ir_Code)

GO

До оператора DECLARE була додана нова змінна - @r_Code. Далі ця змінна зберігає значення, що повертає оператором RETURN. Вона визначена як цілочислена, оскільки код повернення передається як ціле число. У рядку з оператором EXECUTE змінній @r_Code привласнюється значення коду повернення. Зверніть увагу, що змінна @r_Code містить значення, що повертає збережена процедура. Тепер замість %Garlic% для вхідного параметра @Title задане значення Garlic%. Інакше кажучи, оператор SELECT буде шукати в таблиці Titles видання, назви яких починаються зі слова “ Garlic ”. Нижче параметрів і коментарю розміщена умова. Спочатку виконується перевірка галузі оператором IF. Якщо процедура знаходить запис, код повернення дорівнює 0 і виконується оператор SELECT. Якщо процедура не знаходить ні одного відповідного запису, змінна @r_Code дорівнює 1 і виконується оператор PRINT. Оскільки в базі даних немає книги, заголовок якої починається зі слова “Garlic”, результат процедури визначає оператор PRINT:

No matching titles in the database. Return code-1

Якщо змінити значення вхідного параметра на %Garlic% і виконати процедуру ще раз, то вона поверне результуючий набір, показаний у наступній таблиці.

Title Number of Sales Return Code

Onions, Leeks, and Garlic: Cooking 375 0

Методи добування наборів даних

У процедурі, яка використовується як приклад, є обмеження: вона може повернути не більше одного рядка даних. Наприклад, якщо вхідний параметр дорівнює «The%», процедура поверне єдиний рядок - останній запис про книгу, заголовок якої починається з «The%».

Виводиться єдиний рядок, оскільки результуючий набір оператора SELECT передається змінній, здатній зберігати тільки одне значення. Ця проблема має кілька рішень. Найпростіше полягає у відмові від використання вихідних параметрів в операторі SELECT і поверненні при виконанні процедури набору записів, як показано нижче:

ALTER PROCEDURE dbo.SalesForTitle

@Title varchar(80)

AS

SELECT Title = title, [Number of Sales]=ytd_sales

FROM titles WHERE title LIKE @Title

GO

Зверніть увагу, зі збереженої процедури вилучені всі вихідні параметри. Цю просту збережену процедуру можна виконати за допомогою наступного коду:

EXFCUTE SalesForTitle

@Title = "The%"

При виконанні ця процедура повертає результуючий набір, показаний у наступній таблиці.

Title Number of Sales

The Busy Executive's Database Guide 4095

The Gourmet Microwave 22.246

The Psychology of Computer Cooking NULL

Для простоти зі збереженої процедури вилучені фрагменти з кодами повернення, тому стовпець Return Code відсутній. У результуючому наборі перераховані всі книги, заголовок яких починається з “The%”.

Тема 7. Створення|створіння| тригерів|трігерів| і керування ними

Тригери — це особливий клас збережених процедур, які автоматично виконубться під час модифікації даних або після неї. Вони спрацьовують при виконанні трьох команд: UPDATE, INSERT и DELETE. Тригери використовуються для забезпечення цілісності даних і реалізації складнох бізнес-логіки.

Настав час познайомится з|із| методами створення|створіння| тригерів|трігерів| і керування ними. Як правило, для створення|створіння| тригерів|трігерів| і керування ними використовується вікно редактору запитів|. Для створення|створіння| тригера|трігера| служить оператор CREATE| TRIGGER |. В процесі створення|створіння| тригер|трігер| прив'язується до таблиці або відображення|вистави|. Створений тригер|трігер| можна модифікувати засобами|коштами| оператора ALTER| TRIGGER |. Перейменування і огляд тригерів|трігерів| здійснюється| за допомогою системних процедур, що зберігаються, або SQL Server Management Studio |.

Для видалення|віддалення| тригера|трігера| використовується оператор DROP| TRIGGER |, а для включення|приєднання| і виключення тригерів|трігерів| — оператор ALTER| TABLE |.

1. Створення|створіння| тригерів|трігерів| за допомогою мови|язика| TRANSACT-SQL|

При використанні оператора CREATE| TRIGGER| необхідно задати ім'я тригера|трігера|, таблицю або відображення|виставу|, до якого буде прив'язаний тригер|трігер|, клас (INSTEAD| OF| або AFTER |), подія або події, що викликають|спричиняють| спрацьовування тригера|трігера|, і завдання|задачу|, яке він повинен виконувати. Також можна (але|та| не обов'язково) задати необхідність реплікації або шифрування тригера|трігера|.

Основні конструкції оператора CREATE| TRIGGER |:

CREATE TRIGGER ім’я__тригера

ON ім’я_таблиці або ім’я_відображення

FOR клас_триггера і тип(и) _тригера

AS оператори TRANSACT-SQL

Конструкція CREATE| TRIGGER|

Тригер створюється конструкцією CREATE TRIGGER, за якою слідує ім'я тригера.Тригери не допускають вказівки імені бази даних у вигляді префікса імені об'єкту. Тому перед створенням тригера необхідно вибрати потрібну базу даних за допомогою конструкції USE ім’я_базы_данных і ключового слова GO. Ключове слово GO потрібне, посколькуоператор CREATE TRIGGER має бути першим в пакеті. Право на створення тригерів за умовчанням належить власникові таблиці. Краще створювати таблиці, тригери і інші об'єкти бази даних так, щоб їх власником був dbo. Наприклад, наступний код TRANSACT-SQL дозволяє створити тригер Alerter в базі даних BookShopDB:

USE BookShopDB

GO

CREATE TRIGGER dbo.alerter

Імена тригерів|трігерів| повинні підкорятися правилам, визначеним для ідентифікаторів. Наприклад, щоб|аби| створити тригер|трігер| Alerter| for| the| Employees| Table |, слід укласти його ім'я в квадратні дужки:

CREATE TRIGGER dbo[alerter for employees table]

При адмініструванні тригерів|трігерів|, наприклад при їх видаленні|віддаленні|, необхідно слідувати|прямувати| правилам для ідентифікаторів.

Конструкція ON|

Тригер|трігер| необхідно прив'язати до таблиці або відображення|вистави|. Конструкція ON| дозволяє вказати таку таблицю або відображення|виставу|, які після|потім| прив'язки називаються таблицею тригера|трігера| або представленням тригера|трігера| відповідно. Для узгодженості|погодженості| після|потім| конструкції ON | слід вказувати|вказувати| власника таблиці або відображення|вистави|. Наприклад, наступний|такий| код TRANSACT-SQL| прив'язує тригер|трігер| Alerter| до таблиці Employees | (обидва об'єкти, тригер|трігер| і таблиця, належать dbo |):

CREATE TRIGGER dbo.alerter

ON dbo.employees

Будь-який тригер|трігер| можна прив'язати тільки|лише| до однієї таблиці або відображення|вистави|. Щоб|аби| прив'язати до іншої таблиці тригер|трігер|, що виконує те ж саме завдання|задачу|, слід створити новий тригер|трігер| з|із| іншим ім'ям, але|та| з|із| тією ж самою бізнес-логікою і прив'язати його до іншої таблиці. AFTER -триггеры| (цей клас заданий за умовчанням) дозволено прив'язувати тільки|лише| до таблиць, а тригери|трігери| нового класу INSTEAD| OF| — як до таблиць, так і до відображень|вистав|.

Конструкції FOR|, AFTER| і INSTEAD| OF|

При створенні|створінні| тригера|трігера| слід задати тип|тип| події, що викликає|спричиняє| його спрацьовування. Типів події три: INSERT|, UPDATE| і DELETE |. Один і той же тригер|трігер| може спрацювати на одне, два або всі три події. Якщо необхідно, щоб|аби| він спрацьовував на всі події, то після|потім| конструкцій FOR|, AFTER| або INSTEAD| OF| слід помістити всі три ключові|джерельні| слова: INSERT|, UPDATE| і DELETE| у будь-якому порядку. Наприклад, наступний|слідуючий| код TRANSACT-SQL| примушує|заставляє| тригер|трігер| Alerter | спрацьовувати на всі події:

CREATE TRIGGER dbo.alerter

ON dbo.employees

FOR INSERT, UPDATE, DELETE

Конструкція FOR | - синонім AFTER |. Тому приведений вище код створює ALTER | тригер|трігер|. Щоб|аби| створити тригер|трігер| Alerter | як INSTEAD| OF |, використовуйте наступний|слідуючий| код:

CREATE TRIGGER dbo.alerter

ON dbo.employees

INSTEAD OF INSERT, UPDATE, DELETE

Зверніть увагу, що замість конструкції FOR | застосовується конструкція INSTEAD| OF |.

Конструкція AS|

Конструкція AS | і наступні|слідуючі| за нею команди мови|язика| TRANSACT-SQL| визначають завдання|задачу|, яке виконуватиме тригер|трігер|. Нижче наводиться приклад створення|створіння| тригера|трігера| Alerter |, який посилає по електронній пошті повідомлення|сполучення| користувача Hello, якщо в таблиці employees | відбуваються|походять| події INSERT |, UPDATE | або DELETE |:

USE BookShopDB

GO

CREATE TRIGGER dbo.alerter

ON dbo.employees

AETER INSERT. UPDATE, DELETE

AS

EXEC master.xp_sendmail 'Hello'.

'A record was just inserted, updated or deleted in the Employees table'

GO

|

Приклад|зразок| ми спростили, щоб|аби| ясніше продемонструвати створення|створіння| завдання|задачі| у тригері|трігері|. Є декілька способів зробити результати завдання|задачі| кориснішими. Наприклад, запрограмувати завдання|задачу| так, щоб в поштовому повідомленні|сполученні| відображувалися|відображали| докладні|детальні| відомості про внесені зміни.

Управління тригерами|трігерами|

Тригери|трігери| — це потужні|могутні| об'єкти бази даних, які автоматично виконуються при модифікації таблиці або відображення|вистави|. Для управління тригерами|трігерами| призначена низка|низка| команд і інструментів баз даних. Тригери можна:

• модифікувати за допомогою оператора ALTER| TRIGGER |;
• перейменувати засобами|коштами| системної збереженої процедури sp_rename |;
• проглянути шляхом запиту системних таблиць або з використанням системних збережених процедур sp_helptrigger| або sp_helptext |;
• видаляти|віддаляти| за допомогою оператора DROP| TRIGGER |;
• включити або вимкнути за допомогою конструкцій DISABLE| TRIGGER| і ENABLE| TRIGGER| оператора ALTER| TABLE. |

Зміна і перейменування тригерів

Для модифікації тексту тригер|трігер| можна видалити|віддаляти| і відтворити з|із| новим текстом. Інший спосіб, без видалення|віддалення|, полягає у використанні оператора ALTER| TRIGGER |. Синтаксис цього оператора аналогічний синтаксису оператора CREATE| TRIGGER |, але|та| ALTER| TRIGGER| не видаляє|віддаляє| тригер|трігер| з|із| системних таблиць SysComments| і SysObjects |. У наступному|слідуючому| прикладі|зразку| показано, як модифікувати тригер|трігер| Alerter| так, щоб він повідомив тільки|лише| про оновлення записів в таблиці Employees |:

ALTER TRIGGER dbo.alerter

ON dbo.employees

AFTER UPDATE

AS

EXEC master..xp_sendmail 'Barry"

'A record was just updated in the Employees table.'

GO

Зверніть увагу, що після|потім| конструкції AFTER| вказана тільки|лише| одне подія UPDATE |, а також зверніть увагу на зміни в тексті повідомлення|сполучення|.

Інколи|іноді| нові угоди про іменування вимагають перейменувати тригер|трігер|, це також буває необхідно при реалізації в таблиці декількох тригерів|трігерів|. Для перейменування тригера|трігера| застосовують системну збережену процедуру sp_rename |. У наступному|слідуючому| прикладі|зразку| показано, як перейменувати тригер|трігер| Alerter| в EmpAlerter |:

sp_rename @objname = alerter @newname = empalerter

Зверніть увагу, що в синтаксисі перейменування не вказаний dbo |. Системна збережена процедура sp_rename | не дозволяє передати тригер|трігер| іншому власникові. Щоб|аби| змінити|зраджувати| власника тригера|трігера|, слід використовувати оператора CREATE| TRIGGER |. Перейменувавши вкладений тригер|трігер|, обов'язково модифікуйте і тригер, який його викликає -|трігер| у ньому має бути вказане вірне ім'я вкладеного тригера|трігера|.

Перегляд|проглядати|, видалення|віддалення| і відключення тригерів|трігерів|

При створенні|створінні| тригера|трігера| його ім'я і інші ідентифікаційні дані записуються|занотовують| в системну таблицю SysObjects| поточної бази даних. Текст тригера|трігера| записується|занотовує| в системну таблицю SysComments |. Наступний|слідуючий| оператор SELECT| покаже всі тригери|трігери|, створені до таблиць бази даних BookShopDB |:

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alerter]') AND type in (N'TR')

Тригери|трігери| завжди помічені|позначити| в стовпці type| значенням ‘ TR ’|. Для виведення властивостей тригера|трігера| використовується системна процедура sp_helptrigger ||, що зберігається. Наприклад, щоб|аби| вивести властивості всіх тригерів|трігерів|, визначених для таблиці authors |, наберіть наступну|слідуючу| команду:

sp_helptrigger @tabname = employees

Щоб|аби| побачити вміст тригера|трігера|, необхідно зробити запит до стовпця Text | системної таблиці SysComments |. Системна збережена процедура sp_helptext | виводить інформацію в більш організованому вигляді|виді|. Наприклад, для виведення тексту тригера|трігера| alerter | наберіть наступну|слідуючу| команду:

sp_helptext @objnarne = alerter

При видаленні|віддаленні| тригера|трігера| інформація про нього віддаляється з|із| системних таблиць SysComments | і SysObjects |. Для видалення|віддалення| з|із| бази даних одного або декількох тригерів|трігерів| застосовують оператора DROP| TRIGGER |. Якщо віддаляється таблиця тригера|трігера| або його відображення|вистава|, то всі прив'язані до них тригери|трігери| також віддаляються. Щоб|аби| видалити|віддаляти| тригер|трігер| Alerter | з|із| бази даних|, наберіть наступну|слідуючу| команду:

USE Pubs

DROP TRIGGER [dbo].[alerter]

Щоб|аби| відключити|відключати| тригер|трігер|, скористайтеся оператором ALTER| TABLE |. Наступна|слідуюча| команда відключає тригер|трігер| Alerter | в таблиці authors |:

ALTER TABLE authors DISABLE TRIGGER alerter

Щоб|аби| відключити|відключати| всі прив'язані до таблиці тригери|трігери|, слід вказати ключове|джерельне| слово ALL | після|потім| конструкції DISABLE| TRIGGER |. Щоб|аби| включити один або декілька тригерів|трігери|, замініть ключове|джерельне| слово DISABLE | в операторові ALTER| TABLE | на ENABLE |.

2. Програмування тригерів|трігерів|

Псевдотаблиці Inserted| і Deleted|

При спрацьовуванні тригера|трігера| на події INSERT|, UPDATE| або DELETE | створюється одна або декілька псевдотаблиць (також відомих як логічні таблиці). Можна розглядати|розглядати| логічні таблиці як журнали|часописи| транзакцій для події. Існує два типи логічних таблиць: Inserted| і Deleted |. Inserted | створюється в результаті|внаслідок| події додавання|добавляти| або оновлення даних. У ній знаходиться|перебуває| набір доданих|добавляти| або змінених записів. UPDATE -триггер| створює також логічну таблицю Deleted |. У ній знаходиться|перебуває| початковий|вихідний| набір записів в тому стані|достатку|, в якому він був до операції оновлення. Наступний|слідуючий| приклад|зразок| створює тригер|трігер|, який виводить вміст Inserted| і Deleted| після|потім| події UPDATE | в таблиці Authors |:

CREATE TRIGGER dbo.updatetables

ON dbo.authors

AFTER UPDATE

AS

SELECT ‘Description’ = ‘The Inserted table:’

SELECT * FROM inserted

SELECT ‘Description’ = ‘The Deleted table:’

SELECT * FROM deleted

Після|потім| виконання простого оператора UPDATE |, що змінює|зраджує| ім'я автора з|із| Dean | на Denby |, спрацьовує тригер|трігер|, який виводить наступні|слідуючі| результати:

The Inserted table:

Straight Denby Oakland CA 94009

The Deleted table:

Straight Dean Oakland CA 94B09

Дата добавления: 2015-06-29