Коли слід використовувати процедури, що зберігаються, і коли я повинен використовувати уявлення в SQL Server?
Дозволи дозволяють створювати динамічні запити, де ми можемо надсилати параметри?
Який з них найшвидший, і на якій підставі він швидше, ніж інший?
Перегляди або процедури, що зберігаються, постійно зберігають пам'ять?
Що це означає, якщо хтось скаже, що уявлення створюють віртуальну таблицю, а процедури створюють таблицю матеріалів?
Будь ласка, дайте мені знати про точках, якщо вони є.
Solutions Collecting From Web of "У чому різниця між процедурою, що зберігається, і поданням?"
Вид є віртуальнутаблицю. Ви можете приєднатися до кількох таблиць у поданні та використовувати уявлення для представлення даних, якби дані надходили з однієї таблиці.
Збережена процедура використовує параметри для виконання функції … чи це оновлення та вставка даних або повернення окремих значень або наборів даних.
Створення уявлень і процедур, що зберігаються – містить деяку інформацію від Microsoft про те, коли і чому використовувати їх.
Скажімо, у мене є дві таблиці:
tbl_user Стовпці: .user_id, .user_name, .user_pw
tbl_profile Стовпці: .profile_id, .user_id .profile_description
Тому, якщо я перебуваю в запиті з цих таблиць ALOT ... замість того, щоб робити з'єднання в КОЖНІЙ peice sql, я б визначив вигляд, наприклад:
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO
Тому в майбутньому, якщо я хочу запросити profile_description на ідентифікатор користувача ... все, що мені потрібно зробити, це
SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
Цей код можна використовувати в процедурі, що зберігається, наприклад:
Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO
Тому пізніше я можу зателефонувати
Dbo.getDesc 25
і я отримаю опис для ідентифікатора користувача 25 де 25 - ваш параметр.
Очевидно, що БАГАТО більше, але це лише основна ідея.
Спочатку вам потрібно зрозуміти, що обидва – різні речі. Збережені процедури краще використовувати для операторів INSERT-UPDATE-DELETE. та Подання використовуються для операторів SELECT. і ви повинні використовувати обидва.
У виставах ви не можете змінювати дані.
Перегляди: Це віртуальна таблиця, що складається з одного або кількох рядків та стовпців з різних реальних таблиць бази даних. Це шаблон рядків та стовпців кількох таблиць. Ви не можете передавати будь-які параметри тут.
Збережені процедури: вони є набором попередньо виконаних SQL-заяв, у яких ви можете відправляти параметри як вхідні дані і отримувати вихідні дані.
Уявлення можуть використовуватися в процедурі, що зберігається, але процедура, що зберігається, не може використовуватися в Views …!
Процедура сховища використовується, коли простого SQL просто недостатньо. Процедури зберігання містять змінні, цикли та виклики інших процедур, що зберігаються. Це мова програмування, а не мова запитів.
Уявлення є статичними. Подумайте про них як про нові таблиці з певним макетом, а дані в них створюються на льоту, використовуючи запит, з яким ви його створили. Як і в будь-якій таблиці SQL, ви можете сортувати та фільтрувати її за допомогою WHERE , GROUP BY та ORDER BY .
Це залежить від того, що ви робите.
Це залежить від бази даних. Прості уявлення просто запускають запит та фільтрують результат. Але такі бази даних, як Oracle, дозволяють створити "матеріалізоване" уявлення, яке в основному є таблицею, яка автоматично оновлюється при зміні базових даних виду.
Матеріалізоване уявлення дозволяє створювати індекси в стовпцях уявлення (особливо на обчислених стовпцях, які не існують ніде у базі даних).
Я не розумію, про що ви кажете.
Основна відмінність полягає в тому, що коли ви запитуєте уявлення, це визначення вставляється у ваш запит. Процедура може давати результати запиту, але вона скомпільована і так швидко. Іншим варіантом є індексовані уявлення.
SQL View – це віртуальна таблиця, що базується на запиті SQL SELECT. Подання посилається на одну або кілька існуючих таблиць бази даних або на інші уявлення. Це миттєвий знімок бази даних, тоді як процедура, що зберігається, являє собою групу операторів Transact-SQL, складену в єдиний план виконання.
Перегляд – проста демонстрація даних, що зберігаються в таблицях бази даних, тоді як процедура, що зберігається, є групою операторів, які можуть бути виконані.
Подання швидше, оскільки воно відображає дані з таблиць, на які посилається, тоді як процедура сховища виконує sql-інструкції.
Перевірте цю статтю: Перегляд проти процедур, що зберігаються. Саме те, що ви шукаєте
@Patrick правильно з тим, що він сказав, але, щоб відповісти на ваші інші питання, View створить себе в пам'яті, і в залежності від типу Joins, Data і якщо буде зроблено якесь агрегування, це може бути досить голодний вигляд.
Збережені процедури виконують всю свою обробку або з використанням Temp Hash Table, наприклад #tmpTable1, або в пам'яті за допомогою @tmpTable1. Залежно від того, що ви хочете сказати.
Процедура, що зберігається, схожа на функцію, але називається її прямим ім'ям. замість функцій, які фактично використовуються всередині запиту.
Очевидно, більшу частину часу таблиці пам'яті швидше, якщо ви не отримуєте багато даних.
Махеш не зовсім правий, коли він припускає, що ви не можете змінювати дані у поданні. Отже, з погляду Патріка
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id
Я можу оновити дані … як приклад я можу зробити будь-який з цих …
Update vw_user_profile Set profile_description="Manager" where user_id=4
Update tbl_profile Set profile_description="Manager" where user_id=4
Ви не можете вставити в це уявлення, так як не всі поля у всій таблиці присутні, і я припускаю, що PROFILE_ID є первинним ключем і не може бути NULL. Однак іноді ви можете вставити INSERT у виставу …
Я створив уявлення для існуючої таблиці, використовуючи …
Create View Junk як SELECT * від
Insert in junk (Code,name) values ("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")
DELETE from Junk Where ID>4
І INSERT, і DELETE працювали у цьому випадку
Очевидно, ви не можете оновлювати будь-які поля, які агреговані або розраховані, але будь-яке уявлення, яке є просто прямим уявленням, має бути оновлюваним.
Якщо уявлення містить більше однієї таблиці, ви не можете вставляти або видаляти, але якщо уявлення є підмножиною однієї таблиці, ви зазвичай можете.
На додаток до наведених вище коментарів, я хотів би додати кілька зауважень про Views.
- Подання можуть використовуватися для приховування складності. Уявіть собі сценарій, в якому 5 людей працюють над проектом, але тільки один із них дуже гарний з базою даних, наприклад складними об'єднаннями. У такому сценарії він може створювати види, які можуть бути легко запрошені іншими членами команди, оскільки вони запитують одну таблицю.
- Безпека може бути легко реалізована Views. Припустимо, що ми співробітниктаблиці, що містить чутливі стовпці, такі як Зарплата , номер SSN. Ці стовпці не повинні відображатися для користувачів, яким не дозволено їх переглядати. У цьому випадку ми можемо створити уявлення, яке вибиратиме стовпці в таблиці, які не вимагають авторизації, такі як ім'я , вік тат. д., не піддаючи вразливі стовпці (наприклад, про зарплату і т. д., про які ми згадували раніше). Тепер ми можемо видалити дозвіл для прямого запиту до таблиці Employeeі просто зберегти дозвіл на читання у поданні. Таким чином ми можемо реалізувати безпеку за допомогою Views.
Оголошення процедури
CREATE PROCEDURE [({IN|OUT|INOUT} [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC]
END
Ключові слова
. IN (Input) – вхідний параметр
. OUT (Output) – вихідний параметр
. INOUT – вхідний та вихідний, а також поле (без параметрів)
. DYNAMIC RESULT SET показує, що процедура може відкрити вказану кількість курсорів, які залишаться відкритими після повернення з процедури
Примітки
Не рекомендується використовувати багато параметрів у процедурах, що зберігаються (насамперед великих чисел і символьних рядків) через перевантаження мережі та стека. На практиці у існуючих діалектах Transact-SQL, PL/SQL та Informix спостерігається істотна відмінність від стандарту, як у оголошенні та використанні параметрів, оголошенні змінних, так і у виклику підпрограм. Microsoft рекомендує застосовувати наступну апроксимацію для оцінки розміру КЕШу процедур, що зберігаються:
=(максимальна кількість одночасно працюючих користувачів)*(розмір найбільшого плану виконання)*1.25. Визначення розміру плану виконання сторінок можна зробити за допомогою команди: DBCC MEMUSAGE.
Виклик процедури
У багатьох існуючих СУБД виклик процедур, що зберігаються, виконується за допомогою оператора:
EXECUTE PROCEDURE [(][)]
Примітка: Виклик збережених процедур може бути зроблений з програми, іншої процедури, що зберігається, або в інтерактивному режимі.
Приклад оголошення процедури
CREATE PROCEDURE Proc1 AS // оголошуємо процедуру
DECLARE Cur1 200 // оголошуємо курсор
OPEN Cur1 // відкриваємо курсор
FETCH NEXT FROM Cur1 //зчитуємо дані з курсору
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 // закриваємо курсор
DEALLOCATE Cur1
EXECUTE Proc1 // запускаємо процедуру
Поліморфізм
Дві підпрограми з тим самим ім'ям можуть бути створені в одній і тій же схемі, якщо параметри цих двох підпрограм є такою мірою відмінними один від одного, щоб їх можна було розрізняти. Для того, щоб розрізняти дві підпрограми з тим самим ім'ям в одній схемі, кожній з них дається альтернативне і унікальне ім'я (specific name). Таке ім'я може бути зазначено, коли визначається підпрограма. При виклику підпрограм за наявності кількох однакових імен визначення потрібної підпрограми здійснюється за кілька кроків:
. Спочатку визначаються всі процедури із зазначеним ім'ям, а якщо таких немає, то всі функції із заданим ім'ям.
. Для подальшого аналізу залишаються ті підпрограми, стосовно яких даний користувач має привілей на виконання (EXECUTE).
. Їх відбираються ті, які мають число параметрів відповідає числу аргументів виклику. Перевіряються зазначені типи даних параметрів та їх позиції.
. Якщо залишилося більше однієї підпрограми, вибирається та, кваліфікаційне ім'я якої коротше.
На практиці Oracle поліморфізм підтримується для функцій, оголошених тільки в пакеті, [email protected]- У різних схемах, а в Sybase і MS SQL Server навантаження заборонено.
Видалення та зміна процедур
Для видалення процедури використовується оператор:
Для зміни процедури використовується оператор:
ALTER PROCEDURE [([{IN|OUT|INOUT}])]
BEGIN [ATOMIC]
END
Привілеї виконання процедур
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]
Системні процедури
Багато СУБД (включаючи SQL Server) мають певний набір вбудованих системних процедур, які можна використовувати в своїх цілях.
Збережені процедури
Предметом цього розділу є один з найпотужніших інструментів, пропонованих розробникам додатків баз даних InterBase для реалізації бізнес-логіки. і зменшити кількість коду, необхідного для виконання поставлених завдань Практично будь-яка досить складна програма баз даних не обходиться без використання процедур, що зберігаються.
Крім цих широко відомих переваг використання процедур, що зберігаються, загальних для більшості реляційних СУБД, що зберігаються процедури 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> =
(
Виглядає досить об'ємно і може бути навіть громіздко, але насправді все дуже просто Для того, щоб поступово освоїти синтаксис, давайте будемо розглядати приклади, що поступово ускладнюються.
Отже, ось приклад дуже простої процедури, що зберігається, яка приймає на вході два числа, складає їх і повертає отриманий результат:
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, то необхідно перед кожною командою створення ХП (те ж відносися до тригерів) міняти роздільник команд скрипту на інший символ, відмінний від точки з комою, а після тексту ХП відновлювати його. Команда 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, що дозволяє організувати розгалуження в залежності від виконання будь-яких \слів Його синтаксис схожий на більшість операторів розгалуження в мовах програмування високого рівня, на кшталт Паскаля та Сі.
Давайте розглянемо складніший приклад процедури, що зберігається, яка робить наступне.
- Обчислює середню ціну в таблиці Table_example (див. розділ "Таблиці Первинні ключі та генератори")
- Далі для кожного запису в таблиці робить наступну перевірку, якщо існуюча ціна (PRICE) більша за середню ціну, то встановлює ціну, рівну величині середньої ціни, плюс задається фіксований відсоток
- Якщо існуюча ціна менша або дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною.
- Повертає всі змінені рядки у таблиці.
Для початку визначимо ім'я ХП, а також вхідні та вихідні параметри Все це прописується в заголовку процедури, що зберігається
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
|
ID_PARENT_GOOD 0
|
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", яка присвячена обробці винятків, вирішенню помилкових ситуацій у процедурах, що зберігаються, і роботі з масивами.
У попередній статті даного циклу ми розглянули, як можна витягти дані з таблиць, модифікувати їх структуру, створювати, модифікувати та видаляти бази даних та об'єкти, що в них містяться. У цій статті ми поговоримо більш докладно про об'єкти, характерні для серверних СУБД: уявлення, тригери і процедури, що зберігаються.
У першій статті даного циклу, опублікованій у № 3'2000 нашого журналу, ми зазначали, що більшість сучасних серверних СУБД підтримують уявлення, тригери та процедури, що зберігаються. Уявлення також підтримуються багатьма настільними СУБД, наприклад Access, dBase, Clipper.
Слід зазначити, що тригери і процедури, що зберігаються, зазвичай пишуться на мовах програмування, що являють собою процедурні розширення мови SQL. Ці розширення містять оператори, дозволяють описувати алгоритми, наприклад do…while, if…then…else, відсутні у самій мові SQL (якщо пам'ятаєте, SQL - непроцедурний мову, і нею можна сформулювати завдання, але не можна описувати алгоритми його виконання). На відміну від мови SQL, що підпорядковується стандарту, його процедурні розширення ніяк не стандартизовані, і різні СУБД використовують різні синтаксичні конструкції для реалізації тих самих алгоритмічних конструкцій, але обговорення відмінностей у синтаксисі розширень SQL для різних СУБД виходить за рамки цієї статті.
Для ілюстрації того, як можна використовувати уявлення, тригери та процедури, що зберігаються, ми вибрали Microsoft SQL Server 7.0 і базу даних NorthWind, що входить до комплекту поставки цієї СУБД.
Перш ніж виконувати приклади, зверніть увагу на те, що реалізація та спосіб зберігання тригерів і процедур, що використовуються в СУБД, можуть відрізнятися від наведених у цій статті. Крім того, для створення серверних об'єктів слід мати відповідні дозволи, які надає адміністратор бази даних.
Відзначимо також, що деякі ODBC-драйвери не підтримують виклик процедур, що зберігаються з клієнтських додатків, навіть якщо такі підтримуються самої СУБД. Проте в цьому випадку процедури, що зберігаються, як і раніше можуть бути викликані з тригерів.
Давайте почнемо з уявлень, потім обговоримо процедури, що зберігаються, і закінчимо розділ оглядом тригерів.
Уявлення
Подання - це віртуальна таблиця, зазвичай містить набір колонок однієї чи кількох таблиць. Насправді уявлення містить не дані, лише SQL-запит типу SELECT, показує, які саме дані і з яких таблиць необхідно взяти при зверненні до цього представленню. З цієї точки зору уявлення - це запит, що зберігається.
У більшості випадків представлення використовується для забезпечення безпеки даних. Наприклад, деякі категорії користувачів можуть мати доступ до уявлення, але не таблиць, дані яких його формують; крім того, SQL-запит може містити параметр USER (ім'я, під яким зареєструвався користувач), і в цьому випадку дані, доступні при зверненні до подання, будуть залежати від імені користувача.
Нижче наведено основні характеристики уявлень:
- уявлення поводяться подібно до таблиць;
- уявлення не містять даних;
- Подання можуть використовувати дані більш ніж з однієї таблиці.
Для створення уявлення ми можемо використовувати SQL-пропозицію CREATE VIEW, його модифікації - пропозиція ALTER VIEW, а видалення його - пропозиція DROP VIEW.
Ми почнемо з оператора CREATE VIEW, що дозволяє створити уявлення для поточної бази даних.
Пропозиція CREATE VIEW
Синтаксис пропозиції для створення уявлення нагадує SQL-пропозицію SELECT з кількома додатковими ключовими словами. Нижче наведено його спрощений синтаксис:
CREATE VIEW view_name AS select_statement
Аргумент view_name вказує ім'я уявлення. Ключове слово , що використовується в Microsoft SQL Server, дозволяє приховати вихідний текст пропозиції CREATE VIEW таблиці syscomments.
Ключове слово AS вказує, який запит SELECT реально виконуватиметься при зверненні до подання. Зверніть увагу, що цей запит не може містити ключові слова ORDER BY, COMPUTE або COMPUTE BY, INTO і не може посилатися на тимчасову таблицю.
Для модифікації створеного раніше уявлення слід використовувати пропозицію ALTER VIEW, стисло описану в наступному розділі.
Пропозиція DROP VIEW
Ця пропозиція використовується для видалення уявлення з бази даних. При видаленні таблиці з бази даних видаляються і всі уявлення, що посилаються на неї. Використовуючи цю пропозицію, ми повинні вказати ім'я уявлення, що видаляється. Після того, як уявлення видалено, вся інформація про нього видаляється із системних таблиць.
Ще один випадок, коли виставу потрібно видалити, може виникнути за умови, що структура таблиць, на яких воно засноване, змінилася після створення уявлення. У цьому випадку можна видалити виставу, а потім створити її заново за допомогою пропозиції CREATE VIEW.
Створення та використання уявлень
Пропозиція CREATE VIEW використовується для створення уявлень, що дозволяють вилучати дані, які відповідають певним вимогам. Подання створюється в поточній базі даних і зберігається як окремий об'єкт.
Найкращий спосіб створити уявлення - створити запит SELECT і, перевіривши його, додати недостатню частину пропозиції CREATE VIEW. Давайте розглянемо вихідний текст уявлення Products by Category у базі даних NorthWind (листинг 1).
Перший рядок, виділений жирним шрифтом, є тим, чим відрізняється SQL-пропозиція для створення уявлення від звичайного запиту SELECT, що виконує роботу з вибору даних. Пропозиція SELECT, що міститься в цьому поданні, вибирає поля з двох таблиць - поле CategoryName з таблиці CATEGORIES і поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued з таблиці PRODUCTS. Після цього дані двох таблиць зв'язуються по полю CategoryID, і тільки ті продукти, які є на складі (див. критерій після ключового слова WHERE), включаються в результуючий набір даних. Результат звернення до цього подання показано на рис. 1 .
Тепер давайте створимо уявлення, яке показує всі території східного регіону. Це представлення виходить з наступному запиті (листинг 2).
Переконавшись у тому, що пропозиція SELECT повертає результати, які нам потрібні, ми додаємо оператор CREATE VIEW і привласнюємо ім'я EASTTERR, що створюється (листинг 3).
Замість створення тексту подання вручну можна використовувати візуальні інструменти, які зазвичай входять до складу СУБД. На рис. 2 показано, як те саме уявлення може бути створене за допомогою інструмента View Designer, який є складовою частиною Enterprise Manager, що входить до Microsoft SQL Server.
Верхня частина View Designer дозволяє вказати, як пов'язані таблиці та які поля відображатимуться у поданні. Нижче можна вказати псевдоніми таблиць і полів, обмеження їх значення, спосіб відображення. Далі наведено вихідний текст уявлення та результати його виконання.
Перш ніж ми закінчимо короткий огляд уявлень, трохи поговоримо про те, як отримати додаткову інформацію про них. У Microsoft SQL Server 7.0 ми можемо використовувати такі системні процедури, що зберігаються:
- для отримання відомостей про подання можна використовувати системну процедуру sp_help. Наприклад, sp_help EastTerr поверне відомості про щойно створене уявлення;
- для отримання вихідного тексту подання можна використовувати процедуру sp_helptext, що зберігається;
- для того щоб знайти список таблиць, від якого залежить представлення, можна використовувати системну процедуру sp_depends;
- для перейменування уявлення можна використовувати системну процедуру sp_rename, що зберігається.
У цьому розділі ми розглянули, як використовувати уявлення для отримання даних, які відповідають тим чи іншим критеріям. Проте повернемося до останнього прикладу. У базі даних NorthWind є чотири регіони, і для отримання списку територій усіх регіонів нам потрібні чотири різні уявлення. Це завдання можна було б спростити, якби ми могли передати значення RegionID як параметр. Це можна зробити за допомогою процедури, що зберігається, про що ми поговоримо в наступному розділі.
Збережені процедури
Процедура, що зберігається, - це скомпільований набір SQL-пропозицій, збережений в базі даних як іменований об'єкт і виконується як єдиний фрагмент коду. Збережені процедури можуть приймати та повертати параметри. Коли користувач створює процедуру, що зберігається, сервер компілює її і поміщає в кеш, що розділяється, після чого скомпільований код може бути застосований декількома користувачами. Коли програма використовує процедуру, що зберігається, вона передає їй параметри, якщо такі потрібні, і сервер виконує процедуру без перекомпіляції.
Збережені процедури дозволяють підвищити продуктивність програм. По-перше, порівняно із звичайними SQL-запитами, що надсилаються з клієнтської програми, вони вимагають менше часу для підготовки до виконання, оскільки вони вже скомпільовані та збережені. По-друге, мережевий трафік у разі також менше, ніж у разі передачі SQL-запроса, оскільки у мережі передається менша кількість даних. Рис. 3 ілюструє виклик процедури, що зберігається клієнтським додатком.
Збережені процедури автоматично перекомпілюються, якщо з об'єктами, на які вони впливають, зроблено будь-які зміни; інакше кажучи, вони завжди актуальні. Як уже було сказано вище, процедури, що зберігаються, можуть приймати параметри, що дозволяє різним додаткам використовувати одну і ту ж процедуру, застосовуючи різні набори вхідних даних.
Збережені процедури зазвичай використовуються для підтримки цілісності даних і реалізації бізнес-правил. У разі досягається додаткова гнучкість, оскільки, якщо бізнес-правила змінюються, можна змінити лише текст процедури, не змінюючи клієнтських додатків.
Для створення, зміни та видалення процедур існують спеціальні SQL-пропозиції - CREATE PROCEDURE, ALTER PROCEDURE та DROP PROCEDURE. Ми розглянемо їх у наступному розділі.
Пропозиція CREATE PROCEDURE
Пропозиція CREATE PROCEDURE використовується для створення процедури, що зберігається. Воно має наступний спрощений синтаксис:
CREATE PROC proc_name [(@parameter data_type) [= default] ] [...] AS sql_statements
Аргумент proc_name встановлює ім'я процедури, що зберігається, яке має бути унікальне в рамках поточної бази даних. Аргумент @parameter визначає параметр процедури. У пропозиції CREATE PROCEDURE можна визначити один або більше параметрів. Якщо для параметра немає значення за промовчанням, він має бути переданий користувачем (або клієнтським додатком) під час виклику процедури. У Microsoft SQL Server 7.0 кількість параметрів процедури, що зберігається, не повинна перевищувати 1024; за умовчанням вони можуть мати значення NULL.
Зазначимо, що деякі універсальні механізми доступу до даних можуть накладати додаткові обмеження на число параметрів процедур, що зберігаються. Наприклад, BDE-драйвер для Oracle 8 здатний працювати лише з процедурами, кількість параметрів яких не перевищує 10.
Аргумент data_type визначає тип даних для параметра. Ключове слово default може бути використане для встановлення значень за промовчанням – це може бути константа або NULL. Якщо вказано значення за замовчуванням, процедура може бути викликана без значення параметра. Якщо процедура використовує параметр з ключовим словом LIKE, значення за промовчанням може містити групові символи (%, _, і [^]).
Ключове слово OUTPUT показує, що це параметр, що повертається.
Ключове слово AS вказує дію, яку процедура повинна виконати у вигляді будь-якої кількості SQL-пропозицій та пропозицій на процедурному розширенні SQL, характерному для даного сервера.
Процедура, створена за допомогою пропозиції CREATE PROCEDURE, буде збережена у поточній базі даних. У Microsoft SQL Server імена процедур містяться у системній таблиці sysobjects, а вихідний текст - у таблиці syscomments.
Для зміни створеної раніше зберігається процедури слід використовувати пропозицію ALTER PROCEDURE, коротко описану в наступному розділі.
Пропозиція DROP PROCEDURE
Ця пропозиція використовується для видалення процедур, що зберігаються з бази даних. Пропозиція DROP PROCEDURE приймає один аргумент - ім'я процедури, що видаляється.
При видаленні процедури, що зберігається, відомості про неї видаляються з системних таблиць sysobjects і syscomments.
Створення та використання процедур, що зберігаються
У розділі, присвяченому уявленням, ми звертали увагу на те, що було б зручно, якби ми могли передати до представлення параметр, що містить значення RegionID для вибору одного з чотирьох регіонів у базі даних NorthWind. Давайте ще раз розглянемо запит, який повертає список територій регіону:
SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1
Щоб вибрати інший регіон, нам потрібно змінити умову в пропозиції WHERE в останньому рядку запиту. Отже, якщо ми використовуємо змінну (назвемо RegID), ми зможемо вибрати один з чотирьох регіонів без зміни інших частин запиту.
У базі даних NorthWind чотири регіони з номерами від 1 до 4. Це означає, що змінна RegID має бути цілого типу. Код процедури, що зберігається, наведено нижче:
CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID
Зверніть увагу на те, що ми залишили майже весь текст запиту SELECT незайманим (він виділений курсивом) і тільки додали пропозицію CREATE PROCEDURE з ім'ям новоствореної збереженої процедури (у першому рядку), оголошення параметра (у другому рядку) та ключове слово AS, що вказує початку пропозицій, реально виконують дії.
Результат виконання створеної процедури SQL Server Query Analyzer для RegID =2 показаний на рис. 3 .
Очевидно, що ми можемо застосовувати процедури, що зберігаються, не тільки для реалізації розширених версій уявлень або «інтелектуальних» запитів SELECT. Збережені процедури надають механізми, що дозволяють автоматизувати багато рутинних завдань.
У Microsoft SQL Server 7.0 ми також можемо використовувати системні збережені процедури для роботи зі звичайними процедурами, що зберігаються:
- sp_stored_procedures - показує список процедур, що зберігаються;
- sp_helptext - показує вихідний текст процедури, що зберігається;
- sp_depends - показує відомості про залежність збережених процедур;
- sp_procoption - встановлює опції процедур, що зберігаються або встановлює їх;
- sp_recompile – перекомпілює процедуру в момент її наступного виклику;
- sp_rename – змінює ім'я процедури.
Системні процедури, що зберігаються
Оскільки ми говоримо про Microsoft SQL Server, слід зазначити величезну кількість системних процедур, що зберігаються, реалізованих у ньому. Імена системних процедур, що зберігаються, починаються з SP_ або XP_ і зберігаються в базі даних master. Вище ми вже описували деякі з часто використовуваних системних процедур, що зберігаються.
Зверніть увагу, що тригери не повинні повертати користувачеві дані.
У пропозиції CREATE TRIGGER можна використовувати дві спеціальні таблиці. Наприклад, таблиці deleted і inserted мають таку ж структуру, як і таблиця, на яку визначено тригер, і містять старе і нове значення записів, змінених користувачем. Наприклад, ми можемо використовувати таку SQL-пропозицію для пошуку віддалених записів:
SELECT * FROM deleted
У табл. 3 показано вміст таблиць deleted та inserted для всіх можливих змін даних.
Для зміни наявного тригера слід використовувати пропозицію ALTER TRIGGER. Ми поговоримо про нього у наступному розділі.
Для початку нам потрібно додати до таблиці два нових поля, в яких будуть утримуватися ці відомості. Назвемо їх UpdatedBy (ім'я менеджера, що оновив запис останнім) та UpdatedWhen (час, коли було змінено запис). Потім створимо тригер з назвою KeepTrack. Ось його код:
CREATE TRIGGER KeepTrack ON Customers INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID
Як видно з вихідного тексту тригера, він виконується після кожної операції INSERT та UPDATE у таблиці Customers. Цей тригер буде зберігати ім'я менеджера (користувача бази даних) у полі Customers.UpdatedBy і дату та час зміни - у полі Customers.UpdatedWhen. Ці дані вилучаються з тимчасової таблиці, введеної.
Як бачимо, цей тригер дозволяє стежити за змінами та вставкою нових записів у таблиці.
Перед тим, як закінчити короткий огляд тригерів, ми повинні повідомити, де можна знайти відомості про тригери. Таблиця sysobjects зберігає інформацію про тригерах та його типи, а таблиця syscomments містить їх вихідний текст.
Висновок
У цій частині ми розглянули кілька типів об'єктів баз даних - процедури, уявлення і тригери, що зберігаються. Ми дізналися наступне:
- Уявлення - це віртуальна таблиця, зазвичай створювана як підмножина стовпців однієї чи кількох таблиць. Для створення уявлення застосовується пропозиція CREATE VIEW, для модифікації – пропозиція ALTER VIEW, а для видалення – пропозиція DROP VIEW.
- Процедура, що зберігається, - це скомпільований набір SQL-пропозицій, збережений в базі даних як іменований об'єкт і виконується як єдиний фрагмент коду. Для створення процедури, що зберігається, застосовується пропозиція CREATE PROCEDURE, для зміни - ALTER PROCEDURE, а для видалення - DROP PROCEDURE.
- Тригер - це спеціальний тип процедури, що зберігається, яка автоматично викликається, коли дані в певній таблиці додаються, видаляються або змінюються за допомогою SQL-пропозицій INSERT, DELETE або UPDATE. Тригери створюються за допомогою пропозиції CREATE TRIGGER. Для зміни тригера використовується пропозиція ALTER TRIGGER, а видалення - пропозиція DROP TRIGGER.
Комп'ютерПрес 12"2000
Збережені процедури SQL є виконуваний програмний модуль, який може зберігатися у вигляді різних об'єктів. Іншими словами, це об'єкт, у якому містяться SQL-інструкції. Ці процедури, що зберігаються, можуть бути виконані в клієнті прикладних програм, щоб отримати хорошу продуктивність. Крім того, такі об'єкти нерідко викликаються з інших сценаріїв або навіть будь-якого іншого розділу.
Вступ
Багато хто вважає, що вони схожі на процедури різних (відповідно, крім MS SQL). Мабуть, це справді так. Вони мають схожі параметри, можуть видавати схожі значення. Понад те, часом вони стикаються. Наприклад, вони поєднуються з базами даних DDL та DML, а також з функціями користувача (кодова назва – UDF).
Насправді ж процедури SQL, що зберігаються, володіють широким спектром переваг, які виділяють їх серед подібних процесів. Безпека, варіативність програмування, продуктивність - усе це приваблює користувачів, що працюють із базами даних, дедалі більше. Пік популярності процедур припав на 2005-2010 роки, коли вийшла програма від "Майкрософт" під назвою SQL Server Management Studio. З її допомогою працювати з базами даних стало набагато простіше, практичніше та зручніше. З року в рік такий набирав популярності серед програмістів. Сьогодні ж є абсолютно звичною програмою, яка для користувачів, які «спілкуються» з базами даних, стала нарівні з «Екселем».
При виклик процедури вона моментально обробляється самим сервером без зайвих процесів та втручання користувача. Після цього можна здійснювати будь-яке видалення, виконання, зміну. За все це відповідає DDL-оператор, який самотужки робить найскладніші дії з обробки об'єктів. Причому це відбувається дуже швидко, а сервер фактично не навантажується. Така швидкість та продуктивність дозволяють дуже швидко передавати великі обсяги інформації від користувача на сервер та навпаки.
Для реалізації цієї технології роботи з інформацією існує кілька мов програмування. До них можна віднести, наприклад, PL/SQL від Oracle, PSQL у системах InterBase та Firebird, а також класичний «майкрософтівський» Transact-SQL. Всі вони призначені для створення і виконання процедур, що зберігаються, що дозволяє у великих обробниках баз використовувати власні алгоритми. Це потрібне і для того, щоб ті, хто здійснює управління такою інформацією, могли захистити всі об'єкти від несанкціонованого доступу сторонніх осіб і, відповідно, створення, зміни або видалення тих чи інших даних.
Продуктивність
Ці об'єкти баз даних може бути запрограмовані різними шляхами. Це дозволяє користувачам вибирати тип використовуваного способу, який буде найбільш підходящим, що заощаджує сили та час. Крім того, процедура сама обробляється, що дозволяє уникнути величезних часових витрат на обмін між сервером та користувачем. Також модуль можна перепрограмувати і змінити в потрібний напрямок у будь-який момент. Особливо варто відзначити швидкість, з якою відбувається запуск збереженої процедури SQL: цей процес відбувається швидше за інших, схожих з ним, що робить його зручним і універсальним.
Безпека
Такий тип обробки інформації відрізняється від подібних процесів тим, що гарантує підвищену безпеку. Це забезпечується за рахунок того, що доступ інших користувачів до процедур може бути виключений повністю. Це дозволить адміністратору проводити операції з ними самостійно, не побоюючись за перехоплення інформації або несанкціонований доступ до бази даних.
Передача даних
Зв'язок між процедурою SQL, що зберігається, і клієнтським додатком полягає у використанні параметрів і значеннях, що повертаються. Останнім не обов'язково передавати дані в процедуру, що зберігається, проте ця інформація (в основному за запитом користувача) і переробляється для SQL. Після того як процедура, що зберігається, завершила свою роботу, вона відсилає пакети даних назад (але, знову ж таки, за бажанням) до додатка, що викликав його, використовуючи різні методи, за допомогою яких може бути здійснений як виклик збереженої процедури SQL, так і повернення, наприклад:
Передача даних за допомогою параметра Output типу;
Передача даних за допомогою оператора;
Надсилання даних за допомогою оператора вибору.
А тепер розберемося, як виглядає цей процес зсередини.
1. Створення EXEC-збереженої процедури в SQL
Ви можете створити процедуру в MS SQL (Managment Studio). Після того як створиться процедура, вона буде перерахована у програмований вузол бази даних, в якій процедура створення виконується оператором. Для виконання процедури SQL, що зберігаються, використовують EXEC-процес, який містить ім'я самого об'єкта.
При створенні процедури її назва з'являється першою, після чого виконується один або кілька параметрів, присвоєних йому. Параметри можуть бути необов'язковими. Після того, як параметр(и), тобто тіло процедури, будуть написані, потрібно провести деякі необхідні операції.
Справа в тому, що тіло може мати локальні змінні, розташовані в ній, і ці змінні локальні також по відношенню до процедур. Іншими словами, їх можна розглядати лише всередині тіла процедури Microsoft SQL Server. Збережені процедури у разі вважаються локальними.
Таким чином, щоб створити процедуру, нам потрібне ім'я процедури і щонайменше один параметр в якості тіла процедури. Зверніть увагу, що відмінним варіантом у такому разі є створення та виконання процедури з ім'ям схеми у класифікаторі.
Тіло процедури може мати будь-який вид наприклад, такі як створення таблиці, вставки одного або декількох рядків таблиці, встановлення типу і характеру бази даних і так далі. Проте тіло процедури обмежує виконання деяких операцій у ньому. Деякі з важливих обмежень наведені нижче:
Тіло не повинно створювати будь-якої іншої процедури, що зберігається;
Тіло має створити помилкове уявлення про об'єкт;
Тіло не повинно створювати жодних тригерів.
2. Встановлення змінної у тіло процедури
Ви можете зробити змінні локальними для тіла процедури, і вони будуть перебувати виключно всередині тіла процедури. Хорошою практикою є створення змінних на початку тіла процедури, що зберігається. Але також можна встановлювати змінні в будь-якому місці в тілі даного об'єкта.
Іноді можна побачити, що кілька змінних встановлені в одному рядку, і кожен змінний параметр відокремлюється комою. Також зверніть увагу, що змінна має префікс @. У тілі процедури можна встановити змінну, куди ви хочете. Наприклад, змінна @NAME1 може бути оголошена ближче до кінця тіла процедури. Для того щоб надати значення оголошеної змінної використовується набір особистих даних. На відміну від ситуації, коли оголошено більше однієї змінної в одному рядку, у такій ситуації використовується лише один набір особистих даних.
Часто користувачі запитують: "Як призначити кілька значень в одному операторі в тілі процедури?" Що ж. Питання цікаве, але зробити це набагато простіше, ніж ви думаєте. Відповідь: за допомогою таких пар, як "Select Var = значення". Ви можете використовувати ці пари, розділяючи їх комою.
У найрізноманітніших прикладах люди показують створення простої процедури, що зберігається, і виконання її. Проте процедура може приймати такі параметри, що процес, що викликає її, матиме значення, близькі до нього (але не завжди). Якщо вони збігаються, то всередині тіла розпочинаються відповідні процеси. Наприклад, якщо створити процедуру, яка прийматиме місто та регіон від абонента, що викликає, і повертати дані про те, скільки авторів відносяться до відповідного міста та регіону. Процедура буде вимагати таблиці авторів бази даних, наприклад, Pubs, до виконання цього підрахунку авторів. Щоб отримати ці бази даних, наприклад, Google завантажує сценарій SQL зі сторінки SQL2005.
У попередньому прикладі процедура приймає два параметри, які англійською умовно будуть називатися @State і @City. Тип даних відповідає типу, визначеному у додатку. Тіло процедури має внутрішні змінні @TotalAuthors (всього авторів), і ця змінна використовується для відображення їх кількості. Далі з'являється розділ вибору запиту, який все підраховує. Нарешті, підраховане значення відображається у вікні виводу за допомогою оператора друку.
Як у SQL виконати збережену процедуру
Є два способи виконання процедури. Перший шлях показує, передаючи параметри, як розділений комами список виконується після імені процедури. Допустимо, ми маємо два значення (як у попередньому прикладі). Ці значення збираються за допомогою змінних параметрів процедури @State та @City. У цьому вся способі передачі параметрів важливий порядок. Такий метод називається порядковою передачею аргументів. У другому способі параметри безпосередньо призначені, і в цьому випадку порядок не важливий. Цей другий спосіб відомий як передача іменованих аргументів.
Процедура може дещо відхилятися від типової. Так само, як і в попередньому прикладі, але тільки тут параметри зсуваються. Тобто параметр @City зберігається першим, а @State зберігається поруч із значенням за замовчуванням. Параметр за замовчуванням зазвичай виділяється окремо. Процедури SQL, що зберігаються, проходять як просто параметри. У цьому випадку, за умови, параметр UT замінює значення за замовчуванням СА. У другому виконанні проходить лише одне значення аргументу для параметра @ City, і параметр @ State приймає значення за замовчуванням СА. Досвідчені програмісти радять, щоб усі змінні за умовчанням розташовувалися ближче до кінця списку параметрів. В іншому випадку виконання не є можливим, і тоді ви повинні працювати з передачею іменованих аргументів, що довше і складніше.
4. Збережені процедури SQL Server: способи повернення
Існує три важливі способи відправлення даних у викликаній процедурі, що зберігається. Вони перераховані нижче:
Повернення значення процедури, що зберігається;
Вихід параметра процедур, що зберігаються;
Вибір однієї з процедур, що зберігаються.
4.1 Повернення значень збережених процедур SQL
У цій методиці процедура надає значення локальної змінної та повертає його. Процедура може безпосередньо повертати постійне значення. У наступному прикладі ми створили процедуру, яка повертає загальну кількість авторів. Якщо порівняти цю процедуру з попередніми, можна побачити, що значення для друку замінюється зворотним.
Тепер давайте подивимося, як виконати процедуру і вивести значення, яке їй повертається. Виконання процедури вимагає встановлення змінної та друку, яка проводиться після цього процесу. Зверніть увагу, що замість оператора друку можна використовувати Select-оператор, наприклад, Select @RetValue, а також OutputValue.
4.2 Вихід параметра процедур SQL, що зберігаються
Значення у відповідь може бути використане для повернення однієї змінної, що ми і бачили в попередньому прикладі. Використання параметра Output дозволяє процедурі відправити одне або кілька значень змінних для зухвалої сторони. Вихідний параметр позначається саме цим ключовим словом «Output» при створенні процедури. Якщо параметр заданий як вихідний параметр, то об'єкт процедури повинен надати йому значення. Збережені процедури SQL, приклади яких можна побачити нижче, у разі повертаються з підсумковою інформацією.
У нашому прикладі буде два вихідних імені: @ TotalAuthors і @ TotalNoContract. Вони вказуються у списку параметрів. Ці змінні надають значення усередині тіла процедури. Коли ми використовуємо вихідні параметри, абонент може бачити значення, встановлене всередині тіла процедури.
Крім того, у попередньому сценарії дві змінні оголошуються, щоб побачити значення, які встановлюють збережені процедури MS SQL Server у вихідному параметрі. Тоді процедура виконується шляхом подання нормального значення параметра CA. Наступні параметри є вихідними і, отже, оголошені змінні передаються у порядку. Зверніть увагу, що під час проходження змінних вихідне ключове слово також задається тут. Після того, як процедура виконана успішно, значення, які повертаються за допомогою вихідних параметрів, виводяться на вікно повідомлень.
4.3 Вибір однієї з процедур SQL, що зберігаються
Ця техніка використовується для повернення набору значень у вигляді таблиці даних (RecordSet) до зухвалої процедури, що зберігається. У цьому прикладі SQL процедура, що зберігається, з параметрами @AuthID запитує таблицю «Автори» шляхом фільтрації повертаються записів за допомогою цього параметра @AuthId. Оператор Select вирішує, що має бути повернено що викликає процедури, що зберігається. При виконанні процедури AuthId передається назад. Така процедура тут завжди повертає тільки один запис або взагалі жодної. Але процедура, що зберігається, не має жодних обмежень на повернення більше одного запису. Нерідко можна зустріти приклади, у яких повернення даних з використанням обраних параметрів з участю обчислених змінних відбувається шляхом надання кількох підсумкових значень.
На закінчення
Процедура, що зберігається, є досить серйозним програмним модулем, що повертає або передає, а також встановлює необхідні змінні завдяки клієнтському додатку. Оскільки процедура, що зберігається, виконується на сервері сама, обміну даними у величезних обсягах між сервером і клієнтським додатком (для деяких обчислень) можна уникнути. Це дозволяє знижувати навантаження на сервер SQL, що, звичайно ж, йде на руку їх власникам. Одним з підвидів є процедури T SQL, що зберігаються, проте їх вивчення необхідне тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні процедур, що зберігаються, проте це потрібно більше для тих, хто планує щільно зайнятися програмуванням, у тому числі професійно.