Включай у свої процедури рядок - SET NOCOUNT ON:

З кожним DML виразом, SQL server дбайливо повертає нам повідомлення містить кількість оброблених записів. Дана інформаціяможе бути нам корисна під час налагодження коду, але після буде абсолютно марною. Прописуючи SET NOCOUNT ON ми відключаємо цю функцію. Для збережених процедур містять кілька виразів або цикли дана дія може дати значний приріст продуктивності, тому що кількість трафіку буде значно знижена.

Transact-SQL

Використовуй ім'я схеми з ім'ям об'єкта:

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

Transact-SQL

SELECT * FROM dbo.MyTable --Ось так робити добре -- Замість SELECT * FROM MyTable --А так робити погано --Виклик процедури EXEC dbo.MyProc --Знову ж таки добре --Замість EXEC MyProc --Погано!

Не використовуй префікс «sp_» в імені своїх процедур, що зберігаються:

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

Використовуйте IF EXISTS (SELECT 1) замість IF EXISTS (SELECT *):

Щоб перевірити наявність запису в іншій таблиці, ми використовуємо IF EXISTS вираз. Даний вираз повертає true, якщо з внутрішнього виразу повертається хоч одне значення, не важливо «1», всі колонки або таблиця. Відомі дані, в принципі не використовуються. Таким чином, для стиснення трафіку під час передачі даних логічніше використовувати «1», як показано нижче.

Мета роботи– навчитися створювати та використовувати збережені процедури на сервері БД.

1. Пророблення всіх прикладів, аналіз результатів їх виконання в утиліті SQL Server Management Studio. Перевірка наявності створених процедур у поточній БД.

2. Виконання всіх прикладів та завдань у процесі лабораторної роботи.

3. Виконання індивідуальних завдань за варіантами.

Пояснення до виконання роботи

Для освоєння програмування процедур, що зберігаються, використовуємо приклад бази даних з назвою DB_Books, яка була створена у лабораторній роботі №1. При виконанні прикладів та завдань звертайте увагу на відповідність назв БД, таблиць та інших об'єктів проекту.

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

Типи процедур, що зберігаються

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

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

Тимчасові процедури, що зберігаються, існують лише деякий час, після чого автоматично знищуються сервером. Вони поділяються на локальні та глобальні. Локальні тимчасові процедури, що зберігаються, можуть бути викликані тільки з тієї сполуки, в якій створені. Під час створення такої процедури їй необхідно дати ім'я, що починається з одного символу #. Як і всі тимчасові об'єкти, процедури цього типу, що зберігаються, автоматично видаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові процедури, що зберігаються, доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення достатньо дати їй ім'я, що починається із символів ##. Ці процедури видаляються при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.

Створення, зміна процедур, що зберігаються

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

Синтаксис оператора створення нової або зміни наявної процедури в позначеннях MS SQL Server:

( CREATE | ALTER ) PROC[ EDURE] имя_процедуры [ ;номер] [ ( @ім'я_параметра тип_даних ) [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRY [ FOR REPLICATION] AS sql_оператор [ ... n]

Розглянемо параметри цієї команди.

Використовуючи префікси sp_, #, ##, створювану процедуру можна визначити як системну або тимчасову. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона має бути розміщена. Таким чином, щоб розмістити створювану процедуру, що зберігається в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла процедури, що зберігається до об'єктів тієї ж бази даних можна використовувати укорочені імена, тобто без вказівки імені бази даних. Коли потрібно звернутися до об'єктів, розташованих у інших базах даних, вказівка ​​імені бази даних обов'язково.

Для передачі вхідних і вихідних даних у створюваній процедурі, що зберігається, імена параметрів повинні починатися з символу @. В одній процедурі, що зберігається, можна задати безліч параметрів, розділених комами. У тілі процедури не повинні застосовуватися локальні змінні, імена яких збігаються з іменами параметрів цієї процедури. Для визначення типу даних параметрів процедури, що зберігається, підходять будь-які типи даних SQL, включаючи певні користувачем. Однак тип даних CURSOR може бути використаний тільки як вихідний параметр процедури, що зберігається, тобто. із зазначенням ключового слова OUTPUT.

Наявність ключового слова OUTPUT означає, що відповідний параметр призначений для повернення даних із процедури, що зберігається. Однак це зовсім не означає, що параметр не підходить для передачі значень в процедуру, що зберігається. Вказівка ​​ключового слова OUTPUT наказує серверу при виході з процедури, що зберігається, присвоїти поточне значення параметра локальної змінної, яка була вказана при виклику процедури як значення параметра. Зазначимо, що при вказівці ключового слова OUTPUT значення відповідного параметра під час виклику процедури може бути задано лише за допомогою локальної змінної. Не дозволяється використовувати будь-які вирази або константи, допустимі для звичайних параметрів. Ключове слово VARYING застосовується разом із параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча множина.

Ключове слово DEFAULT є значенням, яке прийматиме відповідний параметр за замовчуванням. Таким чином, при виклику процедури можна явно не вказувати значення відповідного параметра.

Так як сервер кешує план виконання запиту та компільований код, при подальшому виклику процедури використовуватимуться вже готові значення. Однак у деяких випадках все ж таки потрібно виконувати перекомпіляцію коду процедури. Вказівка ​​ключового слова RECOMPILE наказує системі створювати план виконання процедури, що зберігається при кожному її викликі.

Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної процедури, що зберігається, як статті в публікацію. Ключове слово ENCRYPTION наказує серверу виконати шифрування коду процедури, що зберігається, що може забезпечити захист від використання авторських алгоритмів, що реалізують роботу збереженої процедури. Ключове слово AS розміщується на початку власне тіла процедури, що зберігається. У тілі процедури можуть застосовуватися практично всі команди SQL, оголошуватися транзакції, встановлюватися блокування та викликатися інші процедури, що зберігаються. Вихід із процедури, що зберігається, можна здійснити за допомогою команди RETURN.

Видалення процедури, що зберігається

DROP PROCEDURE ( ім'я_процедури) [ ,... n]

Виконання процедури, що зберігається

Для виконання процедури, що зберігається використовується команда: [ [ EXEC [ UTE] ім'я_процедури [ ;номер] [ [ @ ім'я_параметра= ] ( значення | @ім'я_змінної) [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]

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

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

Коли під час виклику процедури для параметра вказується ключове слово DEFAULT, буде використано значення за промовчанням. Звичайно, вказане слово DEFAULT дозволяється лише для тих параметрів, для яких визначено значення за промовчанням.

З синтаксису команди EXECUTE видно, що імена параметрів можуть бути опущені під час процедури. Однак у цьому випадку користувач повинен вказувати значення для параметрів у тому порядку, в якому вони перераховувалися при створенні процедури. Призначити параметру значення за промовчанням, просто пропустивши його під час перерахування, не можна. Якщо ж потрібно опустити параметри, для яких визначено значення за промовчанням, достатньо явної вказівки імен параметрів при викликі процедури, що зберігається. Більш того, таким способом можна перераховувати параметри та їх значення у довільному порядку.

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

Використання RETURN в процедурі, що зберігається

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Завдання 1.

EXEC Count_Books

Перевірте результат.

Приклад створення процедури з вхідним параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Завдання 2. Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команди

EXEC Count_Books_Pages 100

Перевірте результат.

Приклад створення процедури з вхідними параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Завдання 3.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команди

EXEC Count_Books_Title 100 "П%"

Перевірте результат.

Приклад створення процедури з вхідними параметрами та вихідним параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) Від Books WHERE Pages>= @Count_pages AND Title_book LIKE @Tit

Завдання 4.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть за допомогою набору команд:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q

Перевірте результат.

Приклад створення процедури з вхідними параметрами та RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкін А.С." RETURN 1 ELSE RETURN 2

Завдання 5.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команд:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

Приклад створення процедури без параметрів для збільшення значення ключового поля у таблиці Purchases у 2 рази:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Завдання 6.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команди

EXEC update_proc

Приклад процедури з вхідним параметром для отримання всієї інформації про конкретного автора:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Завдання 7.

EXEC select_author "Пушкін О.С." або select_author @k= "Пушкін А.С." або EXEC select_author @k= "Пушкін А.С."

Приклад створення процедури з вхідним параметром та значенням за замовчуванням для збільшення значення ключового поля в таблиці Purchases у задану кількість разів (за замовчуванням у 2 рази):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Процедура не повертає жодних даних.

Завдання 8.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команд:

EXEC update_proc 4 або EXEC update_proc @p = 4 або EXEC update_proc --використовується значення за промовчанням.

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

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@,

Завдання 9.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL Server Management Studio. Запустіть її за допомогою команд:

DECLARE @c2 INT EXEC count_purchases '01- jun- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2

Варіанти завдань до лабораторної роботи №4

Загальні положення.В утиліті SQL Server Management Studio створити нову сторінкудля коду (кнопка "Створити запит"). Програмно зробити активною створену базу даних DB_Books за допомогою оператора Use. Створити процедури, що зберігаються за допомогою операторів Create procedure, причому самостійно визначити імена процедур. Кожна процедура виконуватиме за одним SQL запитом, які були виконані у другій лабораторній роботі. Причому код SQL запитів потрібно змінити в такий спосіб, щоб у яких можна було передавати значення полів, якими здійснюється пошук.

Наприклад, вихідне завдання та запит у лабораторній роботі №2:

/*Вибрати з довідника постачальників (таблиця Deliveries) назви компаній, телефони та ІПН (поля Name_company, Phone та INN), у яких назва компанії (поле Name_company) „ВАТ СВІТ“.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ВАТ СВІТ"

*/ –У цій роботі буде створено процедуру:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

-Для запуску процедури використовується команда:

EXEC select_name_company "ВАТ СВІТ"

Список завдань

В утиліті SQL Server Management Studio створити нову програму. Програмно зробити активною індивідуальну БД, створену у лабораторній роботі №1, за допомогою оператора Use. Створити процедури, що зберігаються за допомогою операторів Create procedure, причому самостійно визначити імена процедур. Кожна процедура буде виконувати по одному SQL запиту, який представлений у вигляді окремих завданьза варіантами.

Варіант 1

1. Вивести список співробітників, які мають хоча б одну дитину.

2. Вивести список дітей, яким видали подарунки у вказаний період.

3. Вивести список батьків, які мають неповнолітні діти.

4. Вивести інформацію про подарунки з вартістю більше зазначеної кількості, відсортованих за датою.

Варіант 2

1. Вивести список приладів із зазначеним типом.

2. Вивести кількість відремонтованих приладів та загальну вартість ремонтів у зазначеного майстра.

3. Вивести список власників приладів та кількість їх звернень, відсортований за кількістю звернень щодо спадання.

4. Вивести інформацію про майстрів з розрядом більше зазначеної кількості або з датою прийому на роботу менше зазначеної дати.

Варіант 3

2. Вивести список кодів продажів, за якими продано кольорів на суму більшу за зазначену кількість.

3. Вивести дату продажу, суму, продавця та квітку за вказаним кодом продажу.

4. Вивести список кольорів та сорт для квітів з висотою більше зазначеного числа або квітучий.

Варіант 4

1. Вивести список ліків із зазначеним показанням до застосування.

2. Вивести список дат поставок, за якими продано більше зазначеної кількості однойменних ліків.

3. Вивести дату поставки, суму, ПІБ керівника від постачальника та назву ліків за кодом надходження більше зазначеної кількості.

Варіант 5

2. Вивести список списаного обладнання із зазначеної причини.

3. Вивести дату надходження, назву обладнання, ПІБ відповідального та дату списання для обладнання, списаного у зазначений період.

4. Вивести список обладнання із зазначеним типом або з датою надходження більше певного значення

Варіант 6

1. Вивести список страв із вагою більше за вказане число.

2. Вивести список продуктів, у назві яких трапляється вказаний фрагмент слова.

3. Вивести обсяг продукту, назву страви, назву продукту з кодом страви від зазначеного початкового значення за певним кінцевим значенням.

4. Вивести порядок приготування страви та назву страви з кількістю вуглеводів більше за певне значення або кількістю калорій більше за вказане значення.

Варіант 7

1. Вивести список співробітників із зазначеною посадою.

3. Вивести дату реєстрації, тип документа, ПІБ реєстратора та назву організації для документів, зареєстрованих у зазначений період.

4. Вивести список зареєстрованих документів з певним типом документа або з датою реєстрації більше за вказане значення.

Варіант 8

1. Вивести список співробітників із зазначеною причиною звільнення.

3. Вивести дату реєстрації, причину звільнення, ПІБ співробітника для документів, зареєстрованих у зазначений період.

Варіант 9

1. Вивести список співробітників, які брали відпустку вказаного типу.

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

3. Вивести дату реєстрації, тип відпустки, ПІБ співробітника для документів, зареєстрованих у зазначений період.

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

Варіант 10

1. Вивести список співробітників із зазначеною посадою.

2. Вивести список документів, у змісті яких трапляється зазначений фрагмент слова.

3. Вивести дату реєстрації, тип документа, ПІБ відправника та назву організації для документів, зареєстрованих у зазначений період.

4. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа меншим за певне значення.

Варіант 11

1. Вивести список працівників, призначених на вказану посаду.

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

3. Вивести дату реєстрації, посаду, ПІБ співробітника для документів, зареєстрованих у зазначений період.

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

Варіант 12

3. Вивести список осіб, які брали обладнання у прокат та кількість їх звернень, відсортований за кількістю звернень щодо спадання.

Варіант 13

1. Вивести список обладнання із зазначеним типом. 2. Вивести перелік обладнання, яке списав певний співробітник.

3. Вивести кількість списаного обладнання, що згруповано за типами обладнання.

4. Вивести інформацію про співробітників з датою прийому на роботу більше за певну дату.

Варіант 14

1. Вивести список квіток із зазначеним типом листка.

2. Вивести список кодів надходжень, за якими продано кольорів на суми більші за певне значення.

3. Вивести дату надходження, суму, назви постачальника та кольорів за визначеним кодом постачальника.

4. Вивести список кольорів та сорт для квітів з висотою більше певної кількостіабо квітучий.

Варіант 15

1. Вивести список клієнтів, які заїхали до номерів у зазначений період.

2. Вивести загальну суму оплат за номери кожного клієнта.

3. Вивести дату заїзду, тип номера, ПІБ клієнтів, зареєстрованих у зазначений період.

4. Вивести список зареєстрованих клієнтів у номерах певного типу.

Варіант 16

1. Вивести список обладнання із зазначеним типом.

2. Вивести список обладнання, яке брав у прокат певний клієнт.

3. Вивести список осіб, які брали обладнання у прокат та кількість їх звернень, відсортованих за кількістю звернень щодо спадання.

4. Вивести інформацію про клієнтів, відсортованих за адресами.

Варіант 17

1. Вивести список цінностей із закупівельною вартістю більше за певне значення або терміном гарантії більше за зазначену кількість.

2. Вивести список місць знаходження матеріальних цінностей, у назві яких трапляється зазначене слово.

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

4. Вивести список матеріально відповідальних осіб із датою прийому працювати у зазначеному діапазоні.

Варіант 18

1. Вивести перелік ремонтних робіт, виконаних певним майстром.

2. Вивести список етапів робіт, що входять до робіт, у назві яких зустрічається зазначене слово.

3. Вивести суму вартості етапів ремонтних робіт для робіт із кодом у зазначеному діапазоні.

4. Вивести список майстрів із датою прийому працювати у зазначеному діапазоні.

Варіант 19

1. Вивести список ліків із певним показанням.

2. Вивести список номерів чеків, за якими продано більше певної кількості ліків.

3. Вивести дату продажу, суму, ПІБ касира та ліки за чеком із зазначеним номером.

4. Вивести список ліків та одиниці виміру для ліків з кількістю в упаковці більше зазначеного числа або кодом ліків менше за певне значення.

Варіант 20

1. Вивести список співробітників із зазначеною посадою.

2. Вивести список документів, у змісті яких трапляється зазначений фрагмент слова.

3. Вивести дату реєстрації, тип документа, ПІБ виконавця та факт виконання для документів, зареєстрованих у зазначений період.

4. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа у певному діапазоні.

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

Предметом цього розділу є один з найбільш потужних інструментів, що пропонуються розробникам додатків баз даних InterBaseдля реалізації бізнес-логіки Збережені процедури (англ, stoied proceduies) дозволяють реалізувати значну частину логіки програми на рівні бази даних і таким чином підвищити продуктивність всього додатка, централізувати обробку даних і зменшити кількість коду, необхідного для виконання поставлених завдань. складний додатокбаз даних не обходиться без використання процедур, що зберігаються.
Крім цих широко відомих переваг використання процедур, що зберігаються, загальних для більшості реляційних СУБД, процедури InterBase, що зберігаються, можуть відігравати роль практично повноцінних наборів даних, що дозволяє використовувати повертаються ними результати в звичайних SQL-запитах.
Розробники, що часто починають, уявляють собі збережені процедури просто як набір специфічних SQL-запитів, які щось роблять всередині бази даних, причому існує думка, що працювати з процедурами, що зберігаються, набагато складніше, ніж реалізувати ту ж функціональність в клієнтському додатку, мовою високого рівня
Так що ж таке процедури, що зберігаються в InterBase?
Процедура, що зберігається (ХП) - це частина метаданих бази даних, що представляє собою відкомпільовану у внутрішнє уявлення InterBase підпрограму, написану спеціальною мовою, компілятор якого вбудований в ядро ​​сервера InteiBase
Збережену процедуру можна викликати з клієнтських додатків, з тригерів та інших процедур, що зберігаються. Зберігається процедура виконується всередині серверного процесу і може маніпулювати даними в базі даних, а також повертати клієнту, що викликав її (те тригеру, ХП, додатку) результати свого виконання
Основою потужних можливостей, закладених у ХП, є процедурна мова програмування, що має у своєму складі як модифіковані пропозиції звичайного SQL, такі, як INSERT, UPDATE і SELECT, так і засоби організації розгалужень та циклів (IF, WHILE), а також засоби обробки помилок та виняткових ситуацій Мова процедур, що зберігаються, дозволяє реалізувати складні алгоритми роботи з даними, а завдяки орієнтованості на роботу з реляційними даними ХП виходять значно компактніші за аналогічні процедури традиційними мовами.
Слід зазначити, що й тригерів використовується той самий мову програмування, крім низки особливостей та обмежень. Відмінності підмножини мови, що у тригерах, від мови ХП докладно розглянуті у розділі " Тригери " (год 1).

Приклад простої процедури, що зберігається

Настав час створити першу процедуру, що зберігається, і на її прикладі вивчити процес створення процедур, що зберігаються. Але для початку слід сказати кілька слів про те, як працювати з процедурами, що зберігаються Справа в тому, що своєю славою малозрозумілого і незручного інструменту ХП зобов'язані надзвичайно бідним стандартним засобам розробки і налагодження процедур, що зберігаються. У документації по InterBase рекомендується створювати процедури за допомогою файлів SQL-скриптів, що містять текст ХП, які подаються на вхід інтерпретатору isql, і таким чином проводити створення та модифікацію ХП Якщо в цьому SQL-скрипті на етапі компіляції тексту процедури в BLR (про BLR Розділ "Структура бази даних InterBase" (ч. 4)) виникне помилка, то isql виведе повідомлення про те, на якому рядку файлу SQL-скрипту виникла ця помилка. Виправляйте помилку та повторюйте все спочатку. Про налагодження в сучасному розумінні цього слова, тобто про трасування виконання, з можливістю подивитися проміжні значення змінних, взагалі не йдеться. Очевидно, що такий підхід не сприяє зростанню привабливості процедур, що зберігаються в очах розробника.
Однак, крім стандартного мінімалістського підходу до розробки ХП<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксис процедур, що зберігаються, описується наступним чином:

CREATE PROCEDURE name
[(param datatype [, param datatype...])]
)]
AS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> = (statement;)

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

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END

Як бачите, все просто: після команди CREATE PROCEDURE вказується ім'я новостворюваної процедури (яке має бути унікальним у межах бази даних) - в даному випадку SP_Add, потім у дужках через кому перераховуються вхідні параметри ХП - first_arg і second_arg - із зазначенням їх типів.
Список вхідних параметрів є необов'язковою частиноюоператора CREATE PROCEDURE – бувають випадки, коли всі дані для своєї роботи процедура отримує за допомогою запитів до таблиць усередині тіла процедури.

У процедурах, що зберігаються, використовуються будь-які скалярні типиданих InteiBase He передбачено застосування масивів та типів, що визначаються користувачем, - доменів

Далі йде ключове слово RETURNS, після якого в дужках перераховуються параметри, що повертаються із зазначенням їх типів - в даному випадку тільки один - Result.
Якщо процедура не повинна повертати параметри, слово RETURNS і список параметрів, що повертаються, відсутні.
Після RETURNSQ вказано ключове слово AS. До ключового слова AS йде Заголовок,а після нього - течопроцедури.
Тіло процедури, що зберігається, являє собою перелік описів її внутрішніх (локальних) змінних (якщо вони є, докладніше розглянемо нижче), що розділяється точкою з комою (;), і блок операторів, укладений в операторні дужки BEGIN END. В даному випадку тіло ХП дуже просте - ми просимо складаємо два вхідні аргументи і присвоюємо їх результат вихідному, а потім викликаємо команду SUSPEND. Трохи пізніше ми роз'яснимо суть дії цієї команди, а поки лише зазначимо, що вона потрібна для передачі параметрів, що повертаються туди, звідки була викликана процедура, що зберігається.

Розділювачі в процедурах, що зберігаються

Зверніть увагу, що оператор всередині процедури закінчується крапкою з комою (;). Як відомо, точка з комою є стандартним роздільником команд SQL - вона є сигналом інтерпретатору SQL, що текст команди введений повністю і треба починати його обробляти. Чи не вийде так, що, виявивши крапку з комою в середині ХП, інтерпретатор SQL визнає, що команда введена повністю і спробує виконати частину процедури, що зберігається? Це припущення не має сенсу. Дійсно, якщо створити файл, в який записати вищенаведений приклад, додати команду з'єднання з бази даних і спробувати виконати цей SQL-скрипт за допомогою інтерпретатора isql, то буде повернуто помилку, пов'язану з несподіваним, на думку інтерпретатора, закінченням команди створення процедури, що зберігається. Якщо створювати збережені процедури за допомогою файлів SQL-скриптів, без використання спеціалізованих інструментів розробника InterBase, то необхідно перед кожною командою створення ХП (те ж відносися 1 до тригерів) змінювати роздільник команд скрипта на інший символ, відмінний від точки з комою, а після тексту ХП відновлювати його. Команда isql, що змінює роздільник пропозицій SQL, виглядає так:

SET TERM

Для типового випадкустворення процедури, що зберігається, це виглядає так:

SET TERM ^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM ;^

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

Але повернемося до нашої процедури, що зберігається. Тепер, коли вона створена, її треба якось викликати, передати їй параметри і отримати результати, що повертаються. Це зробити дуже просто – достатньо написати SQL-запит наступного вигляду:

SELECT *
FROM Sp_add (181.35, 23.09)

Цей запит поверне нам один рядок, що містить лише одне поле Result, в якому буде перебувати сума чисел 181.35 і 23.09, тобто 204.44.
Таким чином, нашу процедуру можна використовувати у звичайних SQL-запитах, що виконуються як у клієнтських програмах, так і в інших ХП чи тригерах. Таке використання нашої процедури стало можливим через застосування команди SUSPEND в кінці процедури, що зберігається.
Справа в тому, що в InterBase (і у всіх його клонах) існують два типи процедур, що зберігаються: процедури-вибірки (selectable procedures) і виконувані процедури (executable procedures). Відмінність у роботі цих двох видів ХП полягає в тому, що процедури-вибірки зазвичай повертають безліч наборів вихідних параметрів, згрупованих рядково, які мають вигляд набору даних, а виконувані процедури можуть або взагалі не повертати параметри, або повертати тільки один набір вихідних параметрів , перерахованих у Returns, де один рядок параметрів. Процедури-вибірки викликаються у запитах SELECT, а процедури, що виконуються, - за допомогою команди EXECUTE PROCEDURE.
Обидва види процедур, що зберігаються, мають однаковий синтаксис створення і формально нічим не відрізняються, тому будь-яка здійснена процедура може бути викликана в SELECT-запиті і будь-яка процедура-вибірка - за допомогою EXECUTE PROCEDURE. Питання в тому, як поведуться ХП при різних типахвиклику. Іншими словами, різниця полягає у проектуванні процедури для певного типу виклику. Тобто процедура-вибірка спеціально створюється для виклику із запиту SELECT, а процедура, що виконується, - для виклику з використанням EXECUTE PROCEDURE. Давайте розглянемо, у чому полягають відмінності при проектуванні цих двох видів ХП.
Щоб зрозуміти, як працює процедура-вибірка, доведеться трохи заглибитися в теорію. Давайте уявімо звичайний SQL-запит виду SELECT ID, NAME FROM Table_example. В результаті його виконання ми отримуємо на виході таблицю, що складається з двох стовпців (ID і NAME) та деякої кількості рядків (що дорівнює кількості рядків у таблиці Table_example). Повертається в результаті цього запиту таблиця називається також набором даних SQL. . Далі сервер зчитує кожен запис, що задовольняє результатам запиту, вибирає з нього потрібні поля (у разі це ID і NAME) і відсилає їх клієнту. Потім процес повторюється знову - і так для кожного відібраного запису.
Все це відступ потрібне для того, щоб шановний читач зрозумів, що всі набори даних SQL формуються рядково, у тому числі і в процедурах, що зберігаються! І основна відмінність процедур-вибірок від процедур, що виконуються в тому, що перші спроектовані для повернення безлічі рядків, а другі - тільки для одного. Тому вони застосовуються по-різному: процедура-вибірка викликається за допомогою команди SELECT, яка "вимагає" від процедури віддати всі записи, які вона може повернути. Процедура, що виконується, викликається за допомогою EXECUTE PROCEDURE, яка "виймає" з ХП тільки один рядок, а решта (навіть якщо вони є!) ігнорує.
Давайте розглянемо приклад процедури-вибірки, щоб було зрозуміліше. Для прощення створимо збережену процедуру, яка працює точно так само, як запит SELECT ID, NAME FROM Table_Example, тобто вона робить вибірку полів ID і NAME з усієї таблиці. Ось цей приклад:

CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END

Розберемо дії цієї процедури, названої Simple_Select_SP. Як бачите, вона не має вхідних параметрів і має два вихідні параметри - ID та NAME. Найцікавіше, звичайно, укладено у тілі процедури. Тут використано конструкцію FOR SELECT:

FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN

/*щось робимо зі змінними procID та procName*/

END

Цей шматочок коду означає наступне: для кожного рядка, вибраного з таблиці Table_example, помістити вибрані значення змінні procID і procName, а потім зробити якісь дії з цими змінними.
Ви можете зробити здивоване обличчя і запитати: "Змінні? Які ще змінні 9" Це щось подібне до сюрпризу цього розділу - те, що в процедурах, що зберігаються, ми можемо використовувати змінні. У мові ХП можна оголошувати як власні локальні змінні всередині процедури, і використовувати вхідні і вихідні параметри як змінних.
Для того щоб оголосити локальну змінну в процедурі, що зберігається, необхідно помістити її опис після ключового слова AS і до першого слова BEGIN Опис локальної змінної виглядає так:

DECLARE VARIABLE ;

Наприклад, щоб оголосити цілісну локальну змінну Mylnt, потрібно вставити між AS і BEGIN наступний опис

DECLARE VARIABLE Mylnt INTEGER;

Змінні в нашому прикладі починаються з двокрапки. Це зроблено тому, що звернення до них йде всередині SQL-команди FOR SELECT, тому для розрізнення полів у таблицях, які використовуються в SELECT, та змінних необхідно передувати останнім двокрапкам. Адже змінні можуть мати таку саму назву, як і поля в таблицях!
Але двокрапка перед ім'ям змінної необхідно використовувати лише всередині SQL-запитів. Поза текстами звернення до змінної робиться без двокрапки, наприклад:

procName="Some name";

Але повернемось до тіла нашої процедури. Пропозиція FOR SELECT повертає дані над вигляді таблиці - набору даних, а, по одному рядку. Кожне поле, що повертається, має бути поміщене у свою змінну: ID => procID, NAME => procName. У частині DO ці змінні посилаються клієнту, що викликав) процед>р>, за допомогою команди SUSPEND
Таким чином, команда FOR SELECT... DO організує цикл із записів, що вибираються в частині SELECT цієї команди. У тілі циклу, який утворюється частиною DO, виконується передача чергового сформованого запису клієнту за допомогою команди SUSPEND.
Отже, процедура-вибірка призначена для повернення одного або більше рядків, для чого всередині тіла ХП організується цикл, що заповнює результуючі-змінні параметри. І наприкінці тіла цього циклу обов'язково стоїть команда SUSPEND, яка поверне черговий рядок даних клієнту.

Цикли та оператори розгалуження

Крім команди FOR SELECT... DO, що організує цикл із записів будь-якої вибірки, існує інший вид циклу - WHILE...DO, який дозволяє організувати цикл на основі перевірки будь-яких умов. Ось приклад ХП, що використовує цикл WHILE. DO. Ця процедура повертає квадрати цілих чисел від 0 до 99:

CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 1;
WHILE (i<100) DO
BEGIN
QUADRAT = I * I;
I=I+1;
SUSPEND;
END
END

В результаті виконання запиту SELECT FROM QUAD ми отримаємо таблицю, що містить один стовпець QUADRAT, у якому будуть квадрати цілих чисел від 1 до 99
Крім перебору результатів SQL-вибірки і класичного циклу, в мові процедур, що зберігаються, використовується оператор IF...THEN..ELSE, що дозволяє організувати розгалуження в залежності від виконання будь-яких \слів Його синтаксис схожий на більшість операторів розгалуження в мовах програмування високого рівня, на кшталт Паскаля та Сі.
Давайте розглянемо складніший приклад процедури, що зберігається, яка робить наступне.

1. Обчислює середню ціну в таблиці Table_example (див. розділ "Таблиці Первинні ключі та генератори")
2. Далі для кожного запису в таблиці робить наступну перевірку, якщо існуюча ціна (PRICE) більша за середню ціну, то встановлює ціну, рівну величині середньої ціни, плюс задається фіксований відсоток
3. Якщо існуюча ціна менша або дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою і середньою ціною.
4. Повертає всі змінені рядки у таблиці.

Для початку визначимо ім'я ХП, а також вхідні та вихідні параметри Все це прописується в заголовку процедури, що зберігається

CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION) AS

Процедура буде називатися IncreasePrices, у неї один вхідний параметр Peiceni21nciease, що має тип DOUBLE PRECISION, і 3 вихідні параметри - ID, NAME і new_pnce. Зверніть увагу, що перші два вихідні параметри мають такі ж імена, як і поля в таблиці Table_example, з якою ми збираємося працювати. Це допускається правилами мови процедур, що зберігаються.
Тепер ми повинні оголосити локальну змінну, яка буде використовуватися для зберігання середнього значення.

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Тепер перейдемо до тіла процедури, що зберігається Відкриємо тіло ХП ключове слово BEGIN.
Спочатку нам необхідно виконати перший крок нашого алгоритму – обчислити середню ціну. Для цього ми скористаємося запитом такого виду:

SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price,-

Цей запит використовує агрегатну функцію AVG, яка повертає середнє значення поля PRICE_1 серед відібраних рядків запиту - у нашому випадку середнє значення PRICE_1 по всій таблиці Table_example. Значення, що повертається запитом, поміщається в змінну avg_price. Зверніть увагу, що змінна avg_pnce передує двокрапкою для того, щоб відрізнити її від полів, що використовуються в запиті.
Особливістю даного запитує те, що він завжди повертає строго один-єдиний запис. Такі запити називаються singleton-запитами І тільки такі вибірки можна використовувати в процедурах, що зберігаються. Якщо запит повертає більше одного рядка, то його необхідно оформити у вигляді конструкції FOR SELECT...DO, яка організує цикл для обробки кожного рядка, що повертається.
Отже, ми набули середнього значення ціни. Тепер необхідно пройтися по всій таблиці, порівняти значення ціни в кожному запису із середньою ціною та вжити відповідних дій
З початку організуємо перебір кожного запису з таблиці Table_example

FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_тут оОрсшатиьаем кожен запис*/
END

При виконанні цієї конструкції з таблиці Table_example терміново будуть вийматися дані та значення полів у кожному рядку будуть надані змінним ID, NAME та new_pnce. Ви, звичайно, пам'ятаєте, що ці змінні оголошені як вихідні параметри, але турбуватися, що обрані дані будуть повернуті як результати, не варто: той факт, що вихідним параметрам що-небудь присвоєно, не означає, що клієнт ХП, що викликає, негайно отримає ці значення ! Передача параметрів здійснюється тільки при виконанні команди SUSPEND, а до цього ми можемо використовувати вихідні параметри як звичайні змінні - у прикладі ми саме так і робимо з параметром new_price.
Отже, всередині тіла циклу BEGIN.. .END ми можемо обробити значення кожного рядка. Як ви пам'ятаєте, нам необхідно з'ясувати, як існуюча ціна співвідноситься із середньою, і вжити відповідних дій. Цю процедуру порівняння ми реалізуємо за допомогою оператора IF:

IF (new_price > avg_price) THEN /*якщо існуюча ціна більша за середню ціну*/
BEGIN
/*то встановимо нову ціну, що дорівнює величині середньої ціни, плюс фіксований відсоток */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* Якщо існуюча ціна менша або дорівнює середній ціні, то встановимо ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END

Як бачите, вийшла досить велика конструкція IF, в якій важко було б розібратися, якби не коментарі, які містяться в символи /**/.
Для того, щоб змінити ціну відповідно до обчисленої різниці, ми скористаємося оператором UPDATE, який дозволяє модифікувати існуючі записи - один або кілька. Для того щоб однозначно вказати, в якому записі потрібно змінювати ціну, ми використовуємо в умові WHERE поле первинного ключа, порівнюючи його зі змінною, в якій зберігається значення ID для поточного запису: ID=:ID. Зверніть увагу, що змінна ID передує двокрапкою.
Після виконання конструкції IF...THEN...ELSE у змінних ID, NAME та new_price знаходяться дані, які ми повинні повернути клієнт\, що викликав процедуру. Для цього після IF необхідно вставити команду SUSPEND, яка перешле дані туди, звідки викликали ХП. SELECT...DO не перебере всіх записів свого запиту.
Слід зазначити, що окрім команди SUSPEND, яка лише зупиняє дію процедури, що зберігається, існує команда EXIT, яка припиняє збережену процедуру після передачі рядка. Однак командою EXIT користуються досить рідко, оскільки вона потрібна в основному для того, щоб перервати цикл при досягненні будь-якої умови
При цьому у випадку, коли процедура викликалася оператором SELECT і завершена за EXIT, останній витягнутий рядок не буде повернено. Тобто, якщо вам потрібно перервати процедуру і все-таки отримати цей рядок, треба скористатися послідовністю

SUSPEND;
EXIT;

Основне призначення EXIT – отримання singleton-наборів даних, що повертаються параметрів шляхом виклику через EXECUTE PROCEDURE. У цьому випадку встановлюються значення вихідних параметрів, але їх не формується набір даних SQL, і виконання процедури завершується.
Давайте запишемо текст нашої процедури, що зберігається повністю, щоб мати можливість охопити її логіку одним поглядом:

CREATE PROCEDURE IncreasePrices (
Percent2Increase DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM Table_Example
INTO: avg_price;
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*тут обробляємо кожен запис*/
IF (new_pnce > avg_price) THEN /*якщо існуюча ціна більша за середню ціну*/
BEGIN
/*встановимо нову ціну, що дорівнює величині середньої ціни, плюс фіксований відсоток */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* Якщо існуюча ціна менша або дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
SUSPEND;
END
END

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

Рекурсивні процедури, що зберігаються

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

Товари
- Побутова техніка
- Холодильники
- Трикамерні
- Двокамерні
- Однокамерні
- Пральні машини
- вертикальні
- фронтальні
- Класичні
- Вузькі
- Комп'ютерна техніка
....

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

Пральні машини - Вертикальні
Пральні машини - Фронтальні Класичні
Пральні машини - Фронтальні Вузькі

Визначимо структуру таблиць для зберігання інформації довідника товарів. Використовуємо спрощену схему для організації дерева в одній таблиці:

CREATE TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key(ID_GOOD));

Створюємо одну таблицю GoodsTree, в якій всього 3 поля: ID_GOOD - розумний ідентифікатор категорії, ID_PARENT_GOOD - ідентифікатор кшс горії-батька для даної категорії та GOOD_NAME - найменування катсш-рії. Щоб забезпечити цілісність даних у цій таблиці, накладемо на цю таблицю обмеження зовнішнього ключа:

ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)

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

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GOOD_NAME GOODS Побутова техніка Комп'ютери та комплектуючі Холодильники Пральні машини Трикамерні Двокамерні Однокамерні Вертикальні Фронтальні Вузькі Класичні

Тепер, коли у нас є місце для зберігання даних, ми можемо приступити до створення процедури, що зберігається, що виконує виведення всіх "остаточних" категорій товарів у "розгорнутому" вигляді - наприклад, для категорії "Трикамерні" повне ім'якатегорії буде виглядати як "Побутова техніка Холодильники Трикамерні".
У процедурах, що зберігаються, обробляють деревоподібні структури, склалася своя термінологія. Кожен елемент дерева називають вузлом; а відносини між вузлами, що посилаються один на одного, називається відносинами батько-нащадок. Вузли, що знаходяться на самому кінці дерева і не мають нащадків, називаються листям.
У каші процедури, що зберігається, вхідним параметром буде ідентифікатор категорії, починаючи з якого ми повинні будемо почати розгортку. Збережена процедура матиме такий вигляд:

CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0рганізуємо зовнішній цикл FOR SELECT за безпосередніми нащадками товару з ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Перевірка за допомогою функції EXISTS, яка повертає TRUE, якщо запит у дужках поверне хоча б один рядок. Якщо у знайденого вузла ID_PARENT_GOOD = ID_CHILD_GOOD немає нащадків, то він є "листом" дерева і потрапляє в результати */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Передаємо "аркуш" дерева в результати */
SUSPEND;
END
ELSE
/* Для вузлів, які мають нащадки*/
BEGIN
/*зберігаємо ім'я вузла-батька у тимчасовій змінній */
CURR_CHILD_NAME=full_goods_name;
/* рекурсивно запускаємо цю процедуру */
FOR
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
DO BEGIN
/*додаємо лмя вузла-батька до знайденого., імені нащадка за допомогою операції конкатенації рядків || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* повертаємо повне ім'я товару*/
END
END
END
END

Якщо ми виконаємо цю процедуру з вхідним параметром ID_GOOD2SHOW=1, то отримаємо таке:

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

Висновок

На цьому закінчимо розгляд основних можливостей мови процедур, що зберігаються. Очевидно, що повністю освоїти розробку процедур, що зберігаються при читанні одного розділу неможливо, проте тут ми постаралися уявити і пояснити основні концепції, пов'язані зі збереженими процедурами. Описані конструкції та прийоми проектування ХП можуть бути застосовані у більшості додатків баз даних
Частина важливих питань, пов'язаних з розробкою процедур, що зберігаються, буде розкрита в наступному розділі - "Розширені можливості мови збережених процедур InterBase", яка присвячена обробці винятків, вирішенню помилкових ситуацій у процедурах, що зберігаються, і роботі з масивами.

У Microsoft SQL Server для реалізації та автоматизації своїх власних алгоритмів ( розрахунків) можна використовувати процедури, що зберігаються, тому сьогодні ми з Вами поговоримо про те, як вони створюються, змінюються і видаляються.

Але спочатку трохи теорії, щоб Ви розуміли, що таке процедури, що зберігаються і для чого вони потрібні в T-SQL.

Примітка! Початківцям програмістам рекомендую наступні корисні матеріалина тему T-SQL:

• Для більш детального вивчення мови T-SQLтакож рекомендую почитати книгу – Шлях програміста T-SQL. Самовчитель з мови Transact-SQL.

Що таке процедури, що зберігаються в T-SQL?

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

Для того, щоб запустити процедуру, що зберігається в SQL Server, необхідно перед її назвою написати команду EXECUTE, також можливе скорочене написання цієї команди EXEC. Викликати збережену процедуру в інструкції SELECT , наприклад, як функцію не вийде, тобто. процедури запускаються окремо.

У процедурах, що зберігаються, на відміну від функцій, вже можна виконувати операції модифікації даних такі як: UNSERT, UPDATE, DELETE. Також у процедурах можна використовувати SQL інструкціїпрактично будь-якого типу, наприклад, CREATE TABLE до створення таблиць чи EXECUTE, тобто. виклик інших процедур. Виняток складає кілька типів інструкцій таких як: створення або зміна функцій, уявлень, тригерів, створення схем і ще кілька інших подібних інструкцій, наприклад, також не можна в процедурі, що зберігається, перемикати контекст підключення до бази даних (USE).

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

Збережені процедури дуже корисні, вони допомагають нам автоматизувати або спростити багато операцій, наприклад Вам постійно потрібно формувати різні складні аналітичні звіти з використанням зведених таблиць, тобто. оператор PIVOT. Щоб спростити формування запитів із цим оператором ( як Ви знаєте, у PIVOT синтаксис досить складний), Ви можете написати процедуру, яка буде динамічно формувати зведені звіти, наприклад, у матеріалі «Динамічний PIVOT в T-SQL» представлений приклад реалізації даної можливості у вигляді процедури, що зберігається.

Приклади роботи з процедурами, що зберігаються в Microsoft SQL Server

Вихідні дані для прикладів

Всі приклади нижче будуть виконані в Microsoft SQL Server 2016 Express. Для того щоб продемонструвати, як працюють процедури, що зберігаються з реальними даними, нам потрібні ці дані, давайте їх створимо. Наприклад, давайте створимо тестову таблицю і додамо до неї кілька записів, припустимо, що це буде таблиця, що містить список товарів за їх ціною.

Інструкція створення таблиці CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Інструкція додавання даних INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Миша", 100), (1, "Клавіатура", 200), (2, "Телефон", 400) GO --Запит на вибірку SELECT * FROM TestTable

Дані є, тепер давайте переходити до створення збережених процедур.

Створення процедури, що зберігається на T-SQL – інструкція CREATE PROCEDURE

Збережені процедури створюються за допомогою інструкції CREATE PROCEDUREПісля цієї інструкції Ви повинні написати назву Вашої процедури, потім у разі потреби в дужках визначити вхідні та вихідні параметри. Після цього Ви пишіть ключове слово AS та відкриваєте блок інструкцій ключовим словом BEGIN, закриваєте даний блоксловом END. Усередині цього блоку Ви пишіть усі інструкції, які реалізують Ваш алгоритм або якийсь послідовний розрахунок, інакше кажучи, програмуєте на T-SQL.

Для прикладу давайте напишемо процедуру, яка буде додавати новий запис, тобто. новий товару нашу тестову таблицю. І тому визначимо три вхідних параметра: @CategoryId – ідентифікатор категорії товару, @ProductName - найменування товару і @Price – ціна товару, даний параметр буде в нас необов'язковий, тобто. його можна буде не передавати в процедуру ( наприклад, ми не знаємо ще ціну), для цього в його визначенні ми задамо значення за замовчуванням. Ці параметри у тілі процедури, тобто. в блоці BEGIN ... END можна використовувати, так само як і звичайні змінні ( як Ви знаєте, змінні позначаються знаком @). Якщо Вам потрібно вказати вихідні параметри, то після назви параметра вказуйте ключове слово OUTPUT ( або скорочено OUT).

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

Ось код цієї процедури ( його я також прокоментував).

Створюємо процедуру CREATE PROCEDURE TestProcedure (--Вхідні параметри @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Інструкції, що реалізують Ваш алгоритм --Обробка вхідних параметрів --Видалення зайвих прогалин на початку і в кінці текстового рядка SET @ProductName = LTRIM(RTRIM(@ProductName)); --Додаємо новий запис INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Повертаємо дані SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Запуск процедури, що зберігається на T-SQL - команда EXECUTE

Запустити цю процедуру, як я вже зазначав, можна за допомогою команди EXECUTE або EXEC. Вхідні параметри передаються до процедур шляхом простого їх перерахування та вказівки відповідних значень після назви процедури ( для вихідних параметрів також потрібно вказувати команду OUTPUT). Однак назву параметрів можна і не вказувати, але в цьому випадку необхідно дотримуватись послідовності вказівки значень, тобто. вказувати значення в тому порядку, в якому визначено вхідні параметри ( це стосується і вихідних параметрів).

Параметри, які мають значення за промовчанням, можна і не вказувати, це так звані необов'язкові параметри.

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

1. Викликаємо процедуру без вказівки ціни EXECUTE TestProcedure @CategoryId = 1, @ProductName = " Тестовий товар 1" --2. Викликаємо процедуру із зазначенням ціни EXEC TestProcedure @CategoryId = 1, @ProductName = "Тестовий товар 2", @Price = 300 --3. Викликаємо процедуру, не вказуючи назву параметрів EXEC TestProcedure 1, "Тестовий товар 3 ", 400

Зміна процедури, що зберігається на T-SQL – інструкція ALTER PROCEDURE

Внести зміни до алгоритму роботи процедури можна за допомогою інструкції ALTER PROCEDURE. Іншими словами, щоб змінити вже існуючу процедуру, Вам достатньо замість CREATE PROCEDURE написати ALTER PROCEDURE, а все інше змінювати по необхідності.

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

Змінюємо процедуру ALTER PROCEDURE TestProcedure (--Вхідні параметри @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Інструкції, що реалізують Ваш алгоритм --Обробка вхідних параметрів --Видалення зайвих прогалин на початку та в кінці текстової рядки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Додаємо новий запис INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Видалення процедури, що зберігається на T-SQL – інструкція DROP PROCEDURE

У разі необхідності можна видалити процедуру, що зберігається, це робиться за допомогою інструкції DROP PROCEDURE.

Наприклад, давайте видалимо створену нами тестову процедуру.

DROP PROCEDURE TestProcedure

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

У мене все, сподіваюся, матеріал був Вам цікавий і корисний, поки що!

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

Типи процедур, що зберігаються

У SQL Server є кілька типів збережених процедур.

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

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

Тимчасові процедури, що зберігаються, існують лише деякий час, після чого автоматично знищуються сервером. Вони поділяються на локальні та глобальні. Локальні тимчасові процедури, що зберігаються, можуть бути викликані тільки з тієї сполуки, в якій створені. При створенні такої процедури їй необхідно дати ім'я, що починається з символу #. Як і всі тимчасові об'єкти, процедури цього типу, що зберігаються, автоматично видаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові процедури, що зберігаються, доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення достатньо дати їй ім'я, що починається із символів ##. Ці процедури видаляються при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.

Тригери

Тригериє одним з різновидів процедур, що зберігаються. Їхнє виконання відбувається при виконанні для таблиці будь-якого оператора мови маніпулювання даними (DML). Тригери використовуються для перевірки цілісності даних, а також відкату транзакцій.

Тригер- це відкомпільована SQL-процедура, виконання якої обумовлено настанням певних подій усередині реляційної базиданих. Застосування тригерів здебільшого зручне для користувачів бази даних. І все-таки їх використання часто пов'язані з додатковими витратами ресурсів на операції вводу/вывода. У тому випадку, коли тих же результатів (з набагато меншими непродуктивними витратами ресурсів) можна досягти за допомогою процедур або прикладних програм, що зберігаються, застосування тригерів недоцільно.

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

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

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

Тригерє дуже корисним і в той же час небезпечним засобом. При неправильній логіці його роботи можна легко знищити цілу базу даних, тому тригери необхідно дуже ретельно налагоджувати.

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

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

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

накопичення аудиторської інформації за допомогою фіксації відомостей про внесених змінта тих осіб, які їх виконали;

підтримка реплікації.

Основний формат команди CREATE TRIGGER показаний нижче:

<Определение_триггера>::=

CREATE TRIGGER имя_тригера

BEFORE | AFTER<триггерное_событие>

ON<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Тригерні події складаються із вставки, видалення та оновлення рядків у таблиці. У разі для тригерного події можна вказати конкретні імена стовпців таблиці. Час запуску тригера визначається за допомогою ключових слів BEFORE ( Тригерзапускається до виконання пов'язаних з ним подій) або AFTER (після їх виконання).

Дії, що виконуються тригером, задаються для кожного рядка (FOR EACH ROW), охопленого цією подією, або тільки один раз для кожної події (FOR EACH STATEMENT).

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

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