Збережені процедури icon

Збережені процедури




НазваЗбережені процедури
Дата конвертації31.03.2013
Розмір232 Kb.
ТипДокументи
1. /Конспект лекций/Использование SQL Server Management Studio.doc
2. /Конспект лекций/Лекция 0. Знайомство з SQL Server 2005.doc
3. /Конспект лекций/Лекция 1. Створення й керування базою даних SQL Server (short).doc
4. /Конспект лекций/Лекция 2. Синтаксичн_ елементи мови.doc
5. /Конспект лекций/Лекция 3. Виб_рка даних з бази даних.doc
6. /Конспект лекций/Лекция 4. Модиф_кац_я даних у базах даних SQL Server.doc
7. /Конспект лекций/Лекция 5. Збережен_ процедури.doc
8. /Конспект лекций/Лекция 5. Створення тригер_в _ керування ними ними.doc
9. /Конспект лекций/Мова SQL.Doc
10. /Конспект лекций/Создание триггеров и управление ими.doc
11. /Конспект лекций/Ц_л_сн_сть даних (short).doc
12. /Лабораторна робота малий формат.doc
Что такое sql server
Лекція Основи роботи з Microsoft sql| Server| 2005 Питання лекції Основні
Лекція Створення І керування базою даних sql server
Синтаксичні елементи мови Transact-sql
Вибірка даних з бази даних sql server
Модифікація даних у базах даних sql server
Збережені процедури
Створення|створіння| тригерів|трігерів| і керування ними (Тут додати що таке тригер і як він використовується)
Курс лекцій ( частина 1 ) для студентів спеціальності
Создание триггеров и управление ими
Цілісність даних
Методичні рекомендації для виконання лабораторних робіт для студентів спеціальності

Збережені процедури





  1. Основні відомості про збережені процедури

  2. Операції зі збереженими процедурами

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


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

1. Основні відомості про збережені процедури


Підвищується продуктивність - за рахунок локального (стосовно бази даних) зберігання, перекомпіляції вихідного тексту і хешування. Можливості програмування розширюються завдяки застосуванню таких розповсюджених засобів програмування, як використання вхідних і вихідних параметрів, а також завдяки багаторазовому використанню процедур. Функції безпеки мають на увазі шифрування тексту процедури і обмеження привілеїв. У результаті користувачі одержують обмежений доступ до внутрішньої структури бази даних, однак їм дозволено запускати збережені процедури, що виконують різні дії над базою даних.

Продуктивність


При пересиланні кожної команди (або пакета команд) Transact-SQL на сервер для обробки останній повинен визначити, чи є у відправника права на виконання цих команд і чи припустимі самі команди. Перевіривши права доступу і синтаксис команд, SQL Server будує план виконання запиту.

Збережені процедури в цьому випадку більше ефективні. При створенні вони зберігаються в SQL Server, тому при виклику збереженої процедури її вміст відразу ж обробляється сервером. Єдиний оператор дозволяє викликати складний сценарій Transact-SQL, що втримується в збереженій процедурі, який дозволяє уникнути пересилання через мережу сотень команд.


Перед створенням збереженої процедури її команди проходять синтаксичну перевірку. Якщо при цьому не виявлено жодної помилки, ім'я процедури зберігається в таблиці SysObjects, а її текст - у таблиці SysComments. При першому запуску збереженої процедури створюється план виконання і збережена процедура компілюється. Надалі її обробка здійснюється швидше, оскільки SQL Server не доводиться перевіряти синтаксис команд, створювати план виконання і компілювати текст процедури. До створення нового плану в хеші перевіряється наявність існуючого плану виконання.

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

Можливості програмування


Створену збережену процедуру можна викликати в будь-який момент, при виникненні найменшої необхідності. Це забезпечує модульність і стимулює повторне використання коду. Останнє полегшує супровід бази даних, тому що вона ізольована від бізнесі-правил, які скасовуються. Модифікувати збережену процедуру відповідно до нових правил можна в будь-який момент. Після цього всі додатки, що використають її, автоматично прийдуть у відповідність із новими бізнес-правилами без безпосередньої модифікації.

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

Якщо розроблювачам зручно писати складні програми на таких мовах, як C++, то потім ці програми можна викликати з SQL Server засобами збережених процедур особливого типу, які називаються розширеними збереженими процедурами.

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

Безпека


Інше важливе призначення збережених процедур - підвищення безпеки за допомогою ізоляції й шифрування. Користувачам можна надати право на виконання збереженої процедури без безпосереднього доступу до об'єктів бази даних, з якими працює збережена процедура. Крім того, якщо збережену процедуру зашифрувати при створенні або модифікації, користувачам не вдасться прочитати команди Transact-SQL, складові процедуру. Ці функції безпеки дозволяють ізолювати від користувача структуру бази даних, що забезпечує цілісність даних і надійність бази.

Категорії збережених процедур


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


1.Системні збережені процедури

Системні збережені процедури перебувають у базі даних Master. Як правило, їхні імена починаються із префікса sp_. Вони призначені для підтримки функцій SQL Server (зокрема, процедур для роботи з каталогом). До них відносяться вибірка даних із системних таблиць зовнішніми додатками, адміністрування бази даних і керування безпекою.


2. Локальні збережені процедури

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


3. Тимчасові збережені процедури

Тимчасова збережена процедура схожа на локальну, однак вона існує лише до закриття з'єднання, у якому створена, або до завершення роботи SQL Server. Залежно від типу така процедура видаляється після завершення роботи сервера або розриву з'єднання. Мінливість обумовлена тим, що тимчасові збережені процедури перебувають у базі даних TempDB. При кожному запуску сервера ця база створюється заново, тому після закриття сервера всі об'єкти цієї бази даних зникають. Тимчасові збережені процедури корисні при роботі з більше ранніми версіями SQL Server, які не підтримують повторне використання планів виконання, а також у тих випадках, коли нема рації зберігати процедуру, оскільки значення її параметрів постійно змінюються. Існує три типи тимчасових збережених процедур: локальні (або закриті), глобальні й створювані безпосередньо в TempDB. Локальна процедура завжди починається із символу #, а глобальна - з ##. При виконанні тимчасової збереженої процедури її область дії обмежена з'єднанням, у якому вона створена. Однак така процедура видима всім користувачам, що встановили з'єднання з базою даних. Обмеженість області її дії виключає виникнення конфліктів імен з іншими з'єднаннями, у яких створені тимчасові збережені процедури. Щоб гарантувати унікальність імені тимчасової збереженої процедури, SQL Server додає до нього набір символів підкреслення й унікальний номер з'єднання. Тимчасова збережена процедура видаляється з TempDB при закритті з'єднання, у якому вона створена. Глобальні тимчасові процедури дозволяється виконувати в будь-якім з'єднанні. Подібно тимчасовим процедурам інших типів, вони створюються в базі даних TempDB, тому в них повинні бути унікальні імена. Право на виконання глобальної тимчасової процедури автоматично надається ролі public і не може бути змінено.

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

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

  • для них дозволяється настроїти права доступу;

  • вони зберігаються навіть після завершення з'єднання, у якому створені;

  • вони не видаляються до завершення роботи SQL Server.

Оскільки процедури цього типу створюються безпосередньо в TempDB, важливо повністю визначати ім'я об'єкта бази даних у коді Transact-SQL. Наприклад, на таблицю Authors з бази даних Pubs, що належить dbo, варто посилатися по імені pubs.dbo.authors.


4. Розширені збережені процедури

Розширені збережені процедури звертаються до зовнішніх програм, скомпільованими у вигляді 32-розрядних DLL. Деякі системні збережені процедури також розглядаються як розширені. Наприклад, збережена процедура xp_sendmail, що посилає по заданій адресі поштове повідомлення із вкладеним результуючим набором, є системною й розширеною одночасно. Угода про іменування припускає використання в іменах розширених збережених процедур префікса хр_. Однак імена деяких розширених процедур починаються із префікса sp_, а в іменах деяких інших, не розширених процедур використовується префікс хр_. Тому не можна розрізнити системні й розширені збережені процедури, покладаючись лише на відмінності в іменах.

Визначити, чи є збережена процедура розширеною, дозволяє функція ОВJECTPROPERTY. Вона повертає для властивості IsExtendedProc значення 1, якщо процедура є розширеною, або 0, якщо процедура такої не є.


5. Вилучені збережені процедури

Виходячи з назви, вилучена збережена процедура працює на вилученій копії SQL Server. Вилучені збережені процедури залишені для сумісності з попередніми версіями, в SQL Server 2000 їх замінили розподілені запити.

2.Операції зі збереженими процедурами



Збережені процедури створюють, модифікують і видаляють за допомогою редактору запитів або середовища SQL Server Management Studio. Процедуру дозволяється створювати раніше об'єктів, на які вона посилається. Ця можливість називається відкладеним дозволом імен. Для створення процедур використовують ключові слова CREATE PROCEDURE.

Перед виконанням процедури варто задати значення всіх необхідних параметрів. Збережені процедури можна виконувати вручну або автоматично (при запуску SQL Server). Для виконання процедури служить ключове слово EXECUTE. Його можна опустити, якщо виконується процедура, що, складається з єдиного рядка або ім'я процедури є першим словом пакета.

Часто доводиться модифікувати створені збережені процедури, наприклад, щоб додати параметр або змінити ту або іншу команду. Модифікація процедури замість видалення й повторного створення «з нуля» дозволяє заощадити час, оскільки при модифікації зберігаються багато властивостей збережених процедур (наприклад, права доступу). Для модифікації процедур використовуються ключові слова ALTER PROCEDURE.

Для видалення процедур використовують ключове слово DROP. Процедуру можна видалити в SQL Server Management Studio, вибравши її і натиснувши клавішу DELETE. He варто видаляти процедуру доти, поки всі залежні від процедури об'єкти не будуть вилучені або модифіковані (щоб видалити залежність).

Збереження процедури


Під час створення процедури SQL Server перевіряє синтаксис складових її операторів Transact-SQL. При виявленні синтаксичної помилки SQL Server генерує повідомлення «syntax incorrect», і процедура не створюється. Якщо текст процедури проходить синтаксичну перевірку, то процедура зберігається, при цьому її ім'я і інша інформації (наприклад, автоматично згенерований ідентифікаційний номер) записується в таблицю SysObjects, а текст процедури - у таблицю SysComments поточної бази даних.

Оператор SELECT запитує ідентифікаційний номер збереженої процедури ByRoyalty з таблиці SysObjects бази даних Pubs:

SELECT [name], [id] FROM [pubs].[dbo].[SysObjects]

WHERE [name] = 'byroyalty'


Цей запит повертає результат, показаний у таблиці:

Name Id

Byroyalty 581577110


Наступний оператор SELECT за допомогою інформації з таблиці SysObjects (ідентифікаційного номера збереженої процедури ByRoyalty) виконує запит до таблиці SysComments:

SELECT [text] FROM [pubs].[dbo].[SysComments]

WHERE [id] = 581577110


Цей запит повертає вихідний текст збереженої процедури ByRoyalty, ідентифікаційний номер якої дорівнює 581577110.

Два показаних оператори SELECT можна об'єднати засобами ключового слова JOIN. Для простоти і ясності тут вони наведені окремо.

Для виводу вихідного тексту об'єкта (наприклад, незашифрованої збереженої процедури) краще використати системну збережену процедуру sp_helptext, оскільки при цьому повертається текст, розбитий на рядки. Ми вже використали збережену процедуру sp_helptext раніше.


Методи створення збережених процедур

SQL Server надає простий механізм для створення збережених процедур. Для цього необхідно скористатися або вікном Object Explorer або вікном Template Explorer. В обох випадках система запропонує користувачеві зразок коду створення збереженої процедури, який необхідно доробити у відповідності до вимог створення процедури.


Оператор CREATE PROCEDURE


Оператор CREATE PROCEDURE (або його скорочена версія CREATE PROC) призначений для створення збережених процедур за допомогою редактору запитів або утиліт командного рядка, наприклад osql. CREATE PROC дозволяє виконувати наступні завдання:

  • визначати згруповані збережені процедури;

  • визначати вхідні й вихідні параметри, їхні типи даних і значення за замовчуванням. Визначення параметра завжди починається зі знака «at» (@), після якого треба вказати ім'я параметра й опис його типу даних. В описі вихідних параметрів повинне бути присутнім ключове слово OUTPUT, що дозволяє відрізнити їх від вхідних параметрів;

  • виводити відомості про успішне або невдале завершення завдання;

  • управляти хешуванням плану виконання процедури;

  • шифрувати вміст збереженої процедури для забезпечення її безпеки;

  • контролювати режим виконання процедури для передплатника реплікації;

  • задавати дії, які виконує процедура під час виконання.



Передача контексту збереженій процедурі


Всі збережені процедури, крім тимчасових, завжди створюються в поточній базі даних. Тому перед створенням процедури варто вибирати поточну базу даних за допомогою оператора USE ім'я_бази_даних, після якого зазначається пакетна команда GO. Для вибору поточної бази даних також використовують список, що розкривається на панелі інструментів.

Перший пакет показаного далі сценарію робить базу даних Pubs поточної й створює процедуру ListAuthorNames, власником якої є dbo:

USE Pubs

GO

CREATE PROCEDURE [dbo].[ListAuthorNames]

AS

SELECT [aiLfnanie], [au_lnarne] FROM [pubs], [dbo]. [authors]


Зверніть увагу, що тут використовується повне ім'я процедури, Повне ім'я збереженої процедури складається з імені власника процедури (у цьому випадку dbo) і імені самої процедури - ListAuthorNames. Якщо процедуру варто виконувати незалежно від власника таблиці або бази даних, власником процедури повинен бути dbo. При використанні оператора CREATE PROCEDURE ім'я бази даних не входить у повне ім'я збереженої процедури.

Виконання збереженої процедури


Як уже говорилося раніше, збережену процедуру можна виконати в редакторі запитів, просто набравши її ім'я з необхідними параметрами. Наприклад, для перегляду вмісту збереженої процедури ви набирали sp_helptext і ім'я потрібної процедури, що у цьому випадку є параметром.

Якщо збережена процедура не є першим оператором пакета, то для запуску збереженої процедури необхідно випередити її ім'я ключовим словом EXECUTE (або його скороченою версією - EXEC).


Виклик збереженої процедури

Для виклику збереженої процедури досить указати її повне ім'я, наприклад [ім'я__6ази_даних].[власник].[ім'я_процедури]. Якщо ви зробите базу даних, у якій утримується збережена процедура, поточною (USE ім'я_бази_даних), то для виклику процедури досить указати частину імені: [власник].[ім'я_процедури], а якщо ім'я процедури унікально в активній базі даних, то можна використати просто [ім'я_процедури].

При виконанні системних збережених процедур із префіксом sp_, а також локальних і глобальних процедур не обов'язково використовувати повні імена. SQL Server буде шукати в базі даних Master будь-яку збережену процедуру із префіксом sp_, власником якої є dbo. Якщо ви все-таки вирішили привласнити локальній і системній процедурам однакові імена, обов'язково вкажіть у першому випадку ім'я власника, відмінне від dbo. SQL Server не виконує пошук розширених збережених процедур у базі даних Master автоматично. Тому треба або вказувати повне ім'я розширеної збереженої процедури, або зробити активної базу даних, у якій вона перебуває.

Визначення параметрів і їхніх значень


Якщо збереженій процедурі потрібні параметри, при виконанні процедури необхідно задати їхні значення. Визначення вхідних і вихідних параметрів починаються зі знака @, після якого треба вказати ім'я параметра і опис його типу даних. При виклику функції необхідно задати значення параметра і (не обов'язково) його ім'я. У наступних двох прикладах збережена процедура au_info з бази даних Pubs запускається із двома параметрами - @lastname і @firstname:


Викликати збережену процедуру з параметрами

USE Pubs

GO

EXECUTE au_info Green, Marjorie


Викликати збережену процедуру із вказівкою імен і значень параметрів

USE Pubs

GO

EXECUTE au_info @lastname = 'Green', @firstname = 'Marjorie'


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

  • Збережена процедура повертає значення у цілочисленій змінній, певній для зберігання кодів повернення. У збереженій процедурі повинне бути присутнім ключове слово RETURN із цілочисленим значенням (значеннями).

  • У збереженій процедурі визначені змінні для зберігання параметрів. Перед командою EXECUTE визначаються змінні за допомогою ключового слова DECLARE.



Модифікація збереженої процедури


Оператор ALTER PROCEDURE (або його скорочена версія ALTER PROC) дозволяє модифікувати вміст користувальницької збереженої процедури за допомогою редактору запитів. Синтаксис оператора ALTER PROCEDURE майже повністю ідентичний синтаксису CREATE PROCEDURE. Цінність застосування оператора ALTER PROCEDURE замість видалення й створення процедури «з нуля» полягає в тім, що ALTER PROCEDURE зберігає більшість властивостей процедури (наприклад, її ідентифікатор об'єкта, набір прав доступу і прапори).

Збережена процедура sp_rename дозволяє перейменовувати користувальницькі процедури. Наступний оператор перейменовує збережену процедуру ByRoyalty в RoyaltyByAuthor1:

USE PUBS

GO

EXECUTE sp_rename

@objname = 'byroyalty', @newname = 'RoyaltyByAuthorl',

@objtype = 'object'


В SQL Server Management Studio можна перейменувати користувальницьку збережену процедуру, клацнувши її правою кнопкою й вибравши команду Rename.

При перейменуванні збережених процедур і інших об'єктів (наприклад, таблиць) слід дотримуватися обережності. Збережені процедури можуть бути вкладеними, і при виклику об'єкта вихідній збереженій процедурі не вдасться знайти його, якщо він перейменований.

Видалення збереженої процедури


Для видалення однієї користувальницької збереженої процедури, декількох процедур одночасно або набору згрупованих процедур використовують оператор DROP PROCEDURE або його скорочену версію DROP PROC. Видалимо з бази даних Pubs дві процедури з іменами Procedure01 і Procedure02:

USE pubs

GO

DROP PROCEDURE procedure01, procedure02


Зверніть увагу, що база даних Pubs - поточна. При видаленні процедури не можна вказувати ім'я бази даних, повне ім'я процедури виглядає так: [власник].[ім'я_процедури].

Перш ніж видаляти збережену процедуру, варто перевірити, чи не залежать від неї будь-які інші об'єкти бази даних. Для перевірки наявності залежних об'єктів служить системна збережена процедура sp_depends. Джерелом проблем можуть стати тільки ті об'єкти, які залежать від процедури, що видаляється.


Приклади роботи зі збереженими процедурами

У цій вправі ми створимо збережену процедуру в базі даних Northwind і переконаємося в тім, що вона дійсно створена. Далі ми виконаємо, змінимо і видалимо цю процедуру.


Створення збереженої процедури в базі даних Northwind

USE northwind

GO

CREATE PROCEDURE dbo.CustOrderHistRep

@Customer_ID char(5)

AS

SELECT ContactName, ContactTitle

FROM Customers WHERE CustomerID = @CustomerID


SELECT ProductName, Total=SUM(Quantity)

FROM Products P, [Order Details] OD, Orders O, Customers C WHERE

C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID

AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

GO


При виконанні першого пакета база Northwind стає поточною. Далі створюється процедура CustOrderHistRep і визначається єдиний вхідний параметр - CustomerID.

Вхідний параметр може розташовуватися в одному рядку з оператором CREATE PROCEDURE, але тут для ясності він перенесений в окремий рядок. Аналогічний підхід використаний далі при розбивці на рядки коду операторів SELECT. Зверніть увагу, що для параметра @CustomerID заданий тип даних char(5). Якщо виконати запит до таблиці [northwind].[dbo].[customers], то видно, що довжина всіх ідентифікаторів покупців дорівнює п'яти символам. Рядок, у якій перебуває єдине ключове слово AS, є розділовою лінією між створенням процедури в таблиці SysObjects і текстом процедури, що зберігають у таблиці SysComments.

Переглянете оператори SELECT, які розташовані нижче ключового слова AS, але не витрачайте на це занадто багато часу. При виконанні запиту у відповідь на уведення ідентифікатора покупця перший оператор SELECT виводить ім'я контактної особи й заголовок контактної інформації. Другий оператор SELECT виводить назви і загальну кількість (SUM) кожного товару, що придбав покупець. Результуючий набір повертає дані, згруповані за назвою товару. Можна помітити, що кілька з'єднань реалізовано в конструкції WHERE, а не FROM. Під час модифікації процедури ми перемістимо вираз JOIN у конструкцію FROM.

Виконаємо створений код у вікні редактору запитів.

Далі виконаємо наступну команду:

sp_depends custorderhistrep


Збережена системна процедура sp_depends перевіряє процедуру CustOrderHistRep, щоб визначити її залежності. Зверніть увагу на наявність у поле Name повторюваних елементів. Однак всі записи з повторюваними елементами відрізняються значеннями інших полів.


Виконання збереженої процедури

Уведемо і виконаємо наступну команду:

EXEC [northwind].[dbo],[custorderhistrep]

@CustomerID = 'thecr'


Для запуску збереженої процедури CustOrderHistRep використана скорочена версія ключового слова EXECUTE EXEC. Зверніть увагу на використання повного імені. Це не обов'язково, але в цьому випадку такий прийом дозволяє запустити процедуру, не роблячи активної базу даних Northwind.

Повертаються два результуючі набори. Перший з них (ім'я контактної особи й заголовок контактної інформації) виводиться у верхній частині панелі Results. Другий результуючий набір (назва й кількість товару) відображається в нижній частині панелі Results.


Модифікація збереженої процедури

USE Northwind

GO

ALTER PROCEDURE dbo.CustOrderHistRep

@CustoirerID char(5)

AS

SELECT ContactName, ContactTitle

FROM Customers WHERE CustornerID = @CustomerID

SELECT ProductName. Total=SUM(Quantity)

FROM Products P. [Order Details] OD, Orders O. Customers C

WHERE

C. CustomerID = @CustomerID AND C.CustomerID = O. Customer_ID

AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

ORDER BY Total DESC

GO


У наведеному коді по відношенню до попереднього можна бачити декілька відмінностей:

  • Замінене ключове слово CREATE на ALTER. При зміні тексту процедури ключове слово ALTER дозволяє змінювати збережену процедуру без втрати будь-яких її властивостей.

  • З метою відсортування кількість замовлень по убуванню додано наступний оператор: ORDER BY Total DESC

Виконаємо запит.

Щоб переконатися, що потрібні зміни внесені виконаємо наступний оператор о продивимося результати:

sp_helptext custorderhistrep


Видалення збереженої процедури

DROP PROCEDURE dbo.custorderhistrep

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


Збережені процедури істотно розширюють можливості програмування мовою 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


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

Title Number of Sales

Onions, Leeks, and Garlic: Cooking Secrets 375

of the Mediterranean


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

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


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

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

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


Title Number of Sales

NULL NULL


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

ALTER PROCEDURE dbo.SalesForTitle

@Title varchar(80),

@YtdSales int OUTPUT.

@TitleText varchar(80) OUTPUT

AS

-- Перевірити, є чи в базі даних задана книга. Якщо ні,

-- вийти із процедури і установити код повернення рівним 1

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%;


-- Додати змінну @r_Code дня зберігання результуючого коду.

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

--Запустити процедуру і установити @r_Code

-- рівним результату процедури.

EXECUTE @r_Code = SalesForTitle

@YtdSales = @y_YtdSales OUTPUT,

@TitleText = @t_TitleText OUTPUT

@Title = "Garlic%"

-- Визначити значення @r_Code і виконати програму.

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

Secrets of the Mediterranean


Якщо не ввести значення вхідного параметра @Title, у результаті виконання запиту буде показане наступне повідомлення:

Server; Msg 201, Level 16, State 3, Procedure SalesForTitle, Line 0 procedure 'SalesFcKTitle' expects parameter '@Title', which was not supplied.

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


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

Title Number of Sales Return Code

The Psychology of Computer Cooking NULL 0


Виводиться єдиний рядок, оскільки результуючий набір оператора 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%”.


Закріплення матеріалу


  1. Чому складні збережені процедури при повторному запуску працюють, як правило, швидше, ніж при першому запуску?

  2. Які засоби захисту забезпечують збережені процедури?

  3. Яка функція дозволяє перевірити властивості збереженої процедури й інших об'єктів SQL Server?

  4. Чому модифікація збереженої процедури за допомогою ключових слів ALTER PROCEDURE більше ефективна, ніж видалення процедури з її повторним створенням?

  5. База даних Northwind установлена як поточна, і в ній створена збережена процедура #Procedure01. Однак при перевірці бази даних Northwind ця процедура не була знайдена. Проте процедуру вдалося запустити, якщо поточною базою даних є Northwind. Чому збережена процедура запускається, але не виявляється в базі даних Northwind?

  6. Які три функції виконує оператор RETURN у збережених процедурах?



Схожі:

Збережені процедури iconДо уваги учнів 11Г та 11Д класів
Розроблені іншими користувачами процедури можуть бути корисними для колективного використання. Такі процедури об’єднуються у бібліотеки...
Збережені процедури iconПрес-реліз виставки «Збережені скарби…»
України. Від моменту відкриття, музей – науково дослідний та культурно-освітній заклад, створений для вивчення, збереження, використання...
Збережені процедури iconЛьвів Несебр Стамбул
Несебру. Несебр місто-музей, так як більш старого І насиченого архітектурними пам'ятками міста в Європі більше немає. Бруковані вулички,...
Збережені процедури iconМіндоходів врегульовано процедури погашення податкового боргу
Міністерством доходів і зборів України затверджено 12 відомчих актів, якими врегульовано процедури погашення податкового боргу
Збережені процедури iconПротокол про розкриття цінових пропозицій при здійсненні процедури запиту цінових пропозицій щодо закупівлі продукції рибної
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному виданні з питань...
Збережені процедури iconПовідомлення учасникам про результати процедури закупівлі Замовник: > Найменування
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному друкованому виданні...
Збережені процедури iconПротокол про розкриття цінових пропозицій при здійсненні процедури запиту цінових пропозицій щодо закупівлі продукції рибної
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному виданні з питань...
Збережені процедури iconПротокол про розкриття цінових пропозицій при здійсненні процедури запиту цінових пропозицій щодо закупівлі масла вершкового
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному виданні з питань...
Збережені процедури iconПротокол про розкриття цінових пропозицій при здійсненні процедури запиту цінових пропозицій щодо закупівлі масла вершкового
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному виданні з питань...
Збережені процедури iconПовідомлення учасникам про результати процедури закупівлі Замовник: > Найменування. Ідентифікаційний код за єдрпоу. Місцезнаходження. Предмет закупівлі: > Найменування предмета закупівлі
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному друкованому виданні...
Збережені процедури iconПротокол про розкриття цінових пропозицій при здійсненні процедури запиту цінових пропозицій щодо закупівлі м'яса та харчових субпродуктів свійської птиці
Дата оприлюднення та номер оголошення про проведення процедури закупівлі, опублікованого в державному офіційному виданні з питань...
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©te.zavantag.com 2000-2017
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи