→ Sql створення процедури синтаксису. Створення процедури, що зберігається

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

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

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. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа у певному діапазоні.

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

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

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

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

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

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

Тригери

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Зберігається процедура (англ. stored procedure) – це іменований програмний об'єкт БД. У SQL Server є процедури, що зберігаються декількох типів.

Системні процедури, що зберігаються (англ. system stored procedure) поставляються розробниками СУБД і використовуються для виконання дій із системним каталогом або отримання системної інформації. Їхні назви зазвичай починаються з префіксу "sp_". Запускаються процедури всіх типів, що зберігаються, за допомогою команди EXECUTE, яку можна скоротити до ЄХЕС. Наприклад, процедура sp_helplogins, що зберігається, запущена без параметрів, формує два звіти про імена облікових записів (англ. logins) та відповідних їм у кожній БД користувачах (англ. users).

EXEC sp_helplogins;

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

Таблиця 10.6

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

Користувачеві доступне створення збережених процедур у користувацьких БД і БД для тимчасових об'єктів. В останньому випадку процедура, що зберігається, буде тимчасової.Так само як у випадку з тимчасовими таблицями, назва тимчасової процедури, що зберігається, повинна починатися з префікса "#", якщо це локальна тимчасова збережена процедура, або з "##" - якщо глобальна. Локальна тимчасова процедура може використовуватися тільки в рамках з'єднання, в якому її створили, глобальна – і в інших з'єднаннях.

Програмовані об'єкти SQL Server можуть створюватися як за допомогою засобів Transact-SQL, так і за допомогою збірок (англ. assembly) у середовищі CRL (Common Language Runtime) платформи Microsoft.Net Framework . У цьому підручнику розглядатиметься лише перший спосіб.

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

CREATE (PROC I PROCEDURE) proc_name [ ; number ]

[(gparameter data_type )

[“default] |

[WITH [ ,...n ] ]

[FOR REPLICATION]

AS ([BEGIN]sql_statement[;][...n][END])

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

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

EXECUTE AS визначає контекст безпеки, в якому має бути виконана процедура. Далі вказується одне із значень f CALLER | SELF | OWNER | "user_name"). CALLER є значенням за промовчанням і означає, що код буде виконуватись у контексті безпеки користувача, що викликає цей модуль. Відповідно, користувач повинен мати дозволи не тільки на сам програмований об'єкт, але і на інші об'єкти БД, що ним зачіпаються. EXECUTE AS SELF означає використання контексту користувача, що створює або змінює програмований об'єкт. OWNER вказує, що код буде виконуватись у контексті поточного власника процедури. Якщо для неї не визначено власника, то мається на увазі власник схеми, до якої вона належить. EXECUTE AS "user_name" дозволяє явно вказати ім'я користувача (в одинарних лапках).

Для процедури можуть бути вказані параметри. Це локальні змінні, які використовуються передачі значень в процедуру. Якщо параметр оголошено з ключовим словом OUTPUT (або скорочено OUT), він є вихідним: задане йому в процедурі значення після її закінчення може бути використане програмою, що викликала процедуру. Ключове слово READONLY означає, що значення параметра не може бути змінено всередині процедури, що зберігається.

Параметрам можуть бути призначені значення за замовчуванням, які будуть використані, якщо під час виклику процедури значення параметра не буде вказано у явному вигляді. Розглянемо приклад:

CREATE PROC surma (@а int, @b int=0,

©result int OUTPUT) AS

SET @result=0a+0b

Ми створили процедуру з трьома параметрами, причому у параметра @b значення за замовчуванням =0, а параметр @result - вихідний: через нього повертається значення в програму, що викликала. Дія, що виконуються, досить прості – вихідний параметр отримує значення суми двох вхідних.

При роботі в SQL Server Management Studio створену процедуру, що зберігається, можна знайти в розділі програмованих об'єктів БД (англ. Programmability) у підрозділі для процедур, що зберігаються (рис. 10.2).

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

Мал. 10.2.

DECLARE @з int;

EXEC summa 10,5, @ OUTPUT;

PRINT 0c; – буде виведено 15

DECLARE Gi int = 5;

– під час виклику використовуємо значення за замовчуванням

EXEC summa Gi, DEFAULT, 0с OUTPUT;

PRINT 0c; – буде виведено 5

Розглянемо тепер приклад із аналізом коду повернення, з яким закінчується процедура. Нехай треба підрахувати, скільки у таблиці Bookl книг, виданих у заданому діапазоні років. При цьому якщо початковий рік виявився більшим за кінцевий, процедура повертає "1" і підрахунок не проводить, інакше – рахуємо кількість книг і повертаємо 0:

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear) ;

Розглянемо варіант виклику даної процедури, в якому код повернення зберігається в цілісній змінній 0ret, після чого аналізується його значення (в даному випадку це буде 1). Функція CAST, що використовується в операторі PRINT, служить для перетворення значення цілісної змінної Gres до рядкового типу:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Початковий рік більший за кінцевий"

PRINT "Кількість книг" + CAST(Gres as varchar(20))

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

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

Наступний приклад ілюструє як ці можливості, і питання, пов'язані з областю видимості часових об'єктів. Наведена нижче процедура, що зберігається, перевіряє наявність тимчасової таблиці #ТаЬ2; якщо цієї таблиці немає, створює її. Після цього таблицю #ТаЬ2 заносяться значення двох стовпців, і вміст таблиці виводиться оператором SELECT:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2-№1

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

CREATE TABLE dbo. # Tab2 (id int, name varchar (30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo. # Tab2; -№2

У наведеному прикладі оператор SELECT відпрацює двічі: перший раз – усередині процедури, вдруге – із фрагмента коду, що викликає, (зазначений коментарем "№ 2").

Перед другим викликом процедури видалимо тимчасову таблицю #ТаЬ2. Тоді однойменна тимчасова таблиця буде створена з процедури, що зберігається:

DROP TABLE dbo. # Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo. # Tab2; -№2

У цьому випадку дані виведе лише оператор SELECT, який знаходиться всередині процедури (з коментарем "Ха 1"). Виконання SELECT "№ 2" призведе до помилки, оскільки створена в процедурі, що зберігається, тимчасова таблиця на момент повернення з процедури буде вже видалена з бази tempdb.

Видалити процедуру можна за допомогою оператора DROP PROCEDURE. Його формат наведено нижче. Одним оператором можна видалити кілька процедур, що зберігаються, перерахувавши їх через кому:

DROP (PROC I PROCEDURE) ( procedure ) [

Наприклад, видалимо раніше створену процедуру summa:

DROP PROC summa;

Внести зміни до існуючої процедури (а фактично – перевизначити її) можна за допомогою оператора ALTER PROCEDURE (додаток

стимо скорочення PROC). Крім ключового слова ALTER, формат оператора практично збігається з форматом CREATE PROCEDURE. Наприклад, змінимо процедуру dbo. rownum, встановивши їй опцію виконання у контексті безпеки власника:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner - опція, що встановлюється

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

У деяких випадках може виникнути необхідність динамічного формування команди та виконання її на сервері БД. Це завдання також може вирішуватись за допомогою оператора ЄХЕС. У наведеному нижче прикладі виконується вибірка записів з таблиці Bookl за умовою рівності атрибута Year значенням, що задається змінною:

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

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

Дещо змінимо попередній приклад:

DECLARE 0у varchar(100);

SET 0у = "2ТОВ"; – це ми отримали від користувача

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

DECLARE 0у varchar(100);

SET 0у = "2000; DELETE FROM dbo.Book2"; – ін'єкція

EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);

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

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE = 0y",

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

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

Вступ

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

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

За

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

Проти

  • Підвищення навантаження на сервер баз даних у зв'язку з тим, що більшість роботи виконується на серверній частині, а менша - на клієнтській.
  • Доведеться багато чого навчити. Вам знадобиться вивчити синтаксис MySQL виразів для написання своїх процедур, що зберігаються.
  • Ви дублюєте логіку своєї програми в двох місцях: серверний код і код для процедур, що зберігаються, тим самим ускладнюючи процес маніпулювання даними.
  • Міграція з однієї СУБД на іншу (DB2, SQL Server та ін) може призвести до проблем.

Інструмент, у якому я працюю, називається MySQL Query Browser, він досить стандартний для взаємодії з базами даних. Інструмент командного рядка MySQL – це ще один чудовий вибір. Я розповідаю вам про це з тієї причини, що всіма улюблений phpMyAdmin не підтримує виконання процедур, що зберігаються.

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

Крок 1: Ставимо обмежувач

Обмежувач - це символ або рядок символів, який використовується для вказівки клієнту MySQL, що ви завершили написання виразу SQL. Цілу вічність обмежувачем був символ крапки з комою. Тим не менш, можуть виникнути проблеми, оскільки в процедурі, що зберігається, може бути кілька виразів, кожен з яких повинен закінчуватися точкою з комою. У цьому уроці я використовую рядок “//” як обмежувач.

Крок 2: Як працювати з процедурами, що зберігаються

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

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World!"; END//

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

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

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

  • Language: з метою забезпечення переносимості, за умовчанням вказано SQL.
  • Deterministic: якщо процедура постійно повертає той самий результат, і приймає одні й самі параметри. Це для реплікації та процесу реєстрації. Значення за замовчуванням - NOT DETERMINISTIC.
  • SQL Security: під час дзвінка відбувається перевірка прав користувача. INVOKER - це користувач, що викликає процедуру, що зберігається. DEFINER – це “творець” процедури. Значення за промовчанням - DEFINER.
  • Comment: з метою документування, значення за замовчуванням - ""

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

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

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter", @parameter_var);

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

MySQL має вираз ALTER PROCEDURE для зміни процедур, але він підходить для зміни лише деяких характеристик. Якщо вам потрібно змінити параметри або тіло процедури, слід видалити та створити її заново.

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

DROP PROCEDURE IF EXISTS p2;

Це проста команда. Вираз IF EXISTS відловлює помилку, якщо такої процедури не існує.

Крок 3: Параметри

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

  • CREATE PROCEDURE proc1 (): пустий список параметрів
  • CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один вхідний параметр. Слово IN необов'язкове, тому що параметри за замовчуванням – IN (вхідні).
  • CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один параметр, що повертається.
  • CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, що одночасно входить і повертається.

Природно, можна задавати кілька параметрів різних типів.

Приклад параметра IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//

Приклад параметра OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Тим є"; END //

Приклад параметра INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //

Крок 4: Змінні

Зараз я навчу вас створювати змінні та зберігати їх усередині процедур. Ви повинні оголошувати їх явно на початку блоку BEGIN/END, разом із типами даних. Як тільки ви оголосили змінну, ви можете використовувати її там, де змінні сесії, літерали або імена колонок.

Синтаксис оголошення змінної виглядає так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Давайте оголосимо кілька змінних:

DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Робота зі змінними

Як тільки ви оголосили змінну, ви можете встановити значення за допомогою команд SET або SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT (str, paramstr), today FROM table2 WHERE b> = 5; END //

Крок 5: Структури керування потоками

MySQL підтримує конструкції IF, CASE, ITERATE, LEAVE LOOP, WHILE та REPEAT для управління потоками в межах збереженої процедури. Ми розглянемо, як використовувати IF, CASE та WHILE, оскільки вони найчастіше використовуються.

Конструкція IF

За допомогою конструкції IF ми можемо виконувати завдання, що містять умови:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0"; END IF; END //

Конструкція CASE

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

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

Конструкція WHILE

Технічно існує три види циклів: цикл WHILE, цикл LOOP і цикл REPEAT. Ви також можете організувати цикл за допомогою техніки програмування “Дарта Вейдера”: вираз GOTO. Ось приклад циклу:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Крок 6: Курсори

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

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

DECLARE cursor-name CURSOR FOR SELECT...; /*Оголошення курсору та його заповнення */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Що робити, коли більше немає записів*/ OPEN cursor-name; /*Відкрити курсор*/ FETCH cursor-name INTO variable [, variable]; /*Призначити значення змінної, що дорівнює поточному значенню стовпця*/ CLOSE cursor-name; /*Закрити курсор*/

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

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //

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

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

Висновок

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

Ви повинні підрахувати, які переваги дасть вам використання процедур, що зберігаються у вашому конкретному додатку, і тільки потім створювати лише необхідні процедури. Загалом я використовую процедури; на мою думку, їх варто впроваджувати в проекти внаслідок їхньої безпеки, обслуговування коду та загального дизайну. До того ж, не забувайте, що над процедурами MySQL досі ведеться робота. Чекайте на покращення, що стосуються функціональності та покращень. Прошу, не соромтеся ділитися думками.

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

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

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

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

Збережені процедури можна також використовувати для наступних цілей:

    створення журналу логів про дії з таблицями баз даних.

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

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

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

Збережені процедури створюються за допомогою інструкції CREATE PROCEDUREяка має наступний синтаксис:

CREATE PROC proc_name [((@param1) type1 [VARYING] [= default1] )] (, …) AS batch | EXTERNAL NAME method_name Угоди щодо синтаксису

Параметр schema_name визначає ім'я схеми, яка призначається власником створеної процедури, що зберігається. Параметр proc_name визначає ім'я процедури, що зберігається. Параметр @param1 є параметром процедури (формальним аргументом), тип даних якого визначається параметром type1. Параметри процедури є локальними в межах процедури, подібно до того, як локальні змінні є локальними в межах пакета. Параметри процедури - це значення, які передаються об'єктом процедурі для використання в ній. Параметр default1 визначає значення за промовчанням для відповідного параметра процедури. (Значенням за замовчуванням також може бути NULL.)

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

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

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

За промовчанням використовувати інструкцію CREATE PROCEDURE можуть лише члени зумовленої ролі сервера sysadmin та зумовленої ролі бази даних db_owner або db_ddladmin. Але члени цих ролей можуть надавати це право іншим користувачам за допомогою інструкції GRANT CREATE PROCEDURE.

У прикладі нижче показано створення простої процедури, що зберігається для роботи з таблицею Project:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@% INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

Як говорилося раніше, для поділу двох пакетів використовується інструкція GO. Інструкцію CREATE PROCEDURE не можна поєднувати з іншими інструкціями Transact-SQL в одному пакеті. Збережена процедура IncreaseBudget збільшує бюджети для всіх проектів на певну кількість відсотків, що визначається за допомогою @percent. У процедурі також визначається значення кількості відсотків за умовчанням (5), яке застосовується, якщо під час виконання процедури цей аргумент відсутній.

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

На відміну від основних збережених процедур, які завжди зберігаються в поточній базі даних, можливе створення тимчасових процедур, що зберігаються, які завжди поміщаються в тимчасову системну базу даних tempdb. Одним з приводів для створення тимчасових процедур, що зберігаються, може бути бажання уникнути повторюваного виконання певної групи інструкцій при з'єднанні з базою даних. Можна створювати локальні або глобальні часові процедури. Для цього ім'я локальної процедури визначається з одинарним символом # (#proc_name), а ім'я глобальної процедури - з подвійним (##proc_name).

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

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

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. Угоди щодо синтаксису

Крім параметра return_status, всі параметри інструкції EXECUTE мають таке ж логічне значення, як і однойменні параметри інструкції CREATE PROCEDURE. Параметр return_status визначає цілісну змінну, в якій зберігається стан повернення процедури. Значення параметру можна присвоїти, використовуючи або константу (value), або локальну змінну (@variable). Порядок значень іменованих параметрів не є важливим, але значення неіменованих параметрів мають надаватися в тому порядку, в якому вони визначені в інструкції CREATE PROCEDURE.

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

Коли інструкція EXECUTE є першою інструкцією пакета, можна опустити ключове слово EXECUTE. Проте буде надійніше включати це слово у кожен пакет. Використання інструкції EXECUTE показано на прикладі нижче:

USE SampleDb; EXECUTE IncreaseBudget 10;

Інструкція EXECUTE у цьому прикладі виконує збережену процедуру IncreaseBudget, яка збільшує бюджет усіх проектів на 10%.

У прикладі нижче показано створення процедури, що зберігається для обробки даних в таблицях Employee і Works_on:

Процедура ModifyEmpId у прикладі ілюструє використання процедур, що зберігаються, як частина процесу забезпечення цілісності посилання (в даному випадку між таблицями Employee і Works_on). Подібну процедуру, що зберігається, можна використовувати всередині визначення тригера, який власне і забезпечує посилальну цілісність.

У прикладі нижче показано використання в процедурі пропозиції OUTPUT:

Цю процедуру можна запустити на виконання за допомогою наступних інструкцій:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Видалено співробітників: " + convert(nvarchar(30), @quantityDeleteEmployee);

Ця процедура підраховує кількість проектів, над якими зайнятий співробітник з табельним номером @empId, та надає отримане значення параметру ©counter. Після видалення всіх рядків для даного табельного номера з таблиць Employee та Works_on обчислене значення надається змінною @quantityDeleteEmployee.

Значення параметра повертається процедурі, що викликає, тільки в тому випадку, якщо вказана опція OUTPUT. У прикладі вище процедура DeleteEmployee передає викликає процедурі параметр @counter, отже, процедура, що зберігається, повертає значення системі. Тому параметр @counter необхідно вказувати як у опції OUTPUT при оголошенні процедури, так і в інструкції EXECUTE під час її виклику.

Пропозиція WITH RESULTS SETS інструкції EXECUTE

У SQL Server 2012 для інструкції EXECUTE вводиться пропозиція WITH RESULTS SETS, за допомогою якого при виконанні певних умов можна змінювати форму результуючого набору процедури, що зберігається.

Наступні два приклади допоможуть пояснити цю пропозицію. Перший приклад є вступним прикладом, який показує, як може виглядати результат, коли опущена пропозиція WITH RESULTS SETS:

Процедура EmployeesInDept - це проста процедура, яка відображає табельні номери та прізвища всіх співробітників, які працюють у певному відділі. Номер відділу є параметром процедури, і його потрібно вказати під час її виклику. Виконання цієї процедури виводить таблицю із двома стовпцями, заголовки яких збігаються з найменуваннями відповідних стовпців таблиці бази даних, тобто. Id та LastName. Щоб змінити заголовки стовпців результату (а також їх тип даних), SQL Server 2012 застосовує нову пропозицію WITH RESULTS SETS. Застосування цієї пропозиції показано у прикладі нижче:

USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Прізвище] CHAR(20) NOT NULL));

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

Як можна бачити, запуск процедури, що зберігається з використанням пропозиції WITH RESULT SETS в інструкції EXECUTE дозволяє змінити найменування і тип даних стовпців результуючого набору, що видається даною процедурою. Таким чином, ця нова функціональність надає велику гнучкість у виконанні збережених процедур та приміщенні їх результатів у нову таблицю.

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

Компонент Database Engine також підтримує інструкцію ALTER PROCEDUREдля модифікації структури процедур, що зберігаються. Інструкція ALTER PROCEDURE зазвичай застосовується для зміни інструкцій Transact-SQL всередині процедури. Усі параметри інструкції ALTER PROCEDURE мають таке ж значення, як і однойменні параметри інструкції CREATE PROCEDURE. Основною метою використання цієї інструкції є уникнення перевизначення існуючих прав процедури, що зберігається.

Компонент Database Engine підтримує тип даних CURSOR. Цей тип даних використовується для оголошення курсорів у процедурах, що зберігаються. Курсор- це конструкція програмування, що застосовується для зберігання результатів запиту (зазвичай набору рядків) та надання користувачам можливості відображати цей результат рядково.

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

Збережені процедури та середовище CLR

SQL Server підтримує загальномовне середовище виконання CLR (Common Language Runtime), яка дозволяє розробляти різні об'єкти баз даних (зберігаються процедури, що визначаються користувачем функції, тригери, що визначаються користувачем статистичні функції та типи даних користувача), застосовуючи мови C# і Visual Basic. Середовище CLR також дозволяє виконувати ці об'єкти, використовуючи систему загального середовища виконання.

Середовище CLR дозволяється та забороняється за допомогою опції clr_enabledсистемної процедури sp_configure, яка запускається на виконання інструкцією RECONFIGURE. У прикладі нижче показано, як можна за допомогою системної процедури sp_configure дозволити використання середовища CLR:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

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

    Створити процедуру, що зберігається на мові C# або Visual Basic, а потім скомпілювати її, використовуючи відповідний компілятор.

    Використовуючи інструкцію CREATE ASSEMBLYстворити відповідний виконуваний файл.

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

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

Спочатку створіть необхідну програму в середовищі розробки, наприклад Visual Studio. Скомпілюйте готову програму в об'єктний код за допомогою компілятора C# або Visual Basic. Цей код зберігається у файлі динамічної бібліотеки (.dll), який є джерелом для інструкції CREATE ASSEMBLY, що створює проміжний код, що виконується. Далі виконайте інструкцію CREATE PROCEDURE, щоб зберегти код у вигляді об'єкта бази даних. Нарешті, запустіть процедуру виконання, використовуючи вже знайому нам інструкцію EXECUTE.

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

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; Public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.Command count(*) as "Кількість співробітників" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

У цій процедурі реалізується запит на підрахунок числа рядків у таблиці Employee. У директивах using на початку програми вказуються простори імен, необхідних її виконання. Застосування цих директив дозволяє вказувати у вихідному коді імена класів без явної вказівки відповідних просторів імен. Далі визначається клас StoredProcedures, для якого застосовується атрибут SqlProcedure, який інформує компілятор про те, що цей клас є процедурою, що зберігається. Всередині класу класу визначається метод CountEmployees(). З'єднання з системою баз даних встановлюється за допомогою екземпляра класу SqlConnection. Щоб відкрити з'єднання, застосовується метод Open() цього екземпляра. А метод CreateCommand()дозволяє звертатися до екземпляра класу SqlCommnd, якому передається потрібна SQL-команда

У наступному фрагменті коду:

Cmd.CommandText = "select count(*) as "Кількість співробітників" + "from Employee";

використовується інструкція SELECT для підрахунку кількості рядків у таблиці Employee та відображення результату. Текст команди вказується, присвоюючи властивості CommandText змінної cmd екземпляр, який повертається методом CreateCommand(). Далі викликається метод ExecuteScalar()екземпляра SqlCommand. Цей метод повертає скалярне значення, яке перетворюється на цілий тип даних int і присвоюється змінної rows.

Тепер можна скомпілювати цей код, використовуючи середовище Visual Studio. Я додав цей клас до проекту з ім'ям CLRStoredProcedures, тому Visual Studio скомпілює однойменну збірку з розширенням *.dll. У прикладі нижче показаний наступний крок у створенні процедури, що зберігається: створення виконуваного коду. Перш ніж виконувати код у цьому прикладі, необхідно дізнатись розташування скомпілюваного dll-файлу (зазвичай знаходиться в папці Debug проекту).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

Інструкція CREATE ASSEMBLY приймає як введення керований код і створює відповідний об'єкт, для якого можна створювати збережені процедури середовища CLR, що визначаються користувачем функції та тригери. Ця інструкція має наступний синтаксис:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM (dll_file) Угоди щодо синтаксису

У assembly_name вказується ім'я збірки. У необов'язковому реченні AUTHORIZATION вказується ім'я ролі як власника цієї збірки. У пропозиції FROM вказується шлях, де знаходиться завантаження збірка.

Пропозиція WITH PERMISSION_SETє дуже важливою пропозицією інструкції CREATE ASSEMBLY і завжди має бути вказано. У ньому визначається набір прав доступу, що надаються коду збирання. Набір прав SAFE є обмежуючим. Код складання, який має ці права, не може звертатися до зовнішніх системних ресурсів, таких як файли. Набір прав EXTERNAL_ACCESS дозволяє коду складання звертатися до певних зовнішніх системних ресурсів, а набір прав UNSAFE надає необмежений доступ до ресурсів як усередині, так і поза системою бази даних.

Щоб зберегти інформацію про код зборки, користувач повинен мати можливість виконати інструкцію CREATE ASSEMBLY. Власником складання є користувач (або роль), який виконує цю інструкцію. Власником складання можна зробити іншого користувача, використовуючи пропозицію AUTHORIZATION інструкції CREATE SCHEMA.

Компонент Database Engine також підтримує інструкції ALTER ASSEMBLY та DROP ASSEMBLY. Інструкція ALTER ASSEMBLYвикористовується для оновлення збирання до останньої версії. Ця інструкція також додає або видаляє файли, пов'язані з монтажем. Інструкція DROP ASSEMBLYвидаляє вказану збірку та всі пов'язані з нею файли з поточної бази даних.

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

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

Інструкція CREATE PROCEDURE у прикладі відрізняється від такої ж інструкції у прикладах раніше тим, що вона містить параметр EXTERNAL NAME. Цей параметр вказує на те, що код створюється середовищем CLR. Ім'я у цій пропозиції складається з трьох частин:

assembly_name.class_name.method_name

    assembly_name - вказує ім'я збирання;

    class_name – вказує ім'я загального класу;

    method_name - необов'язкова частина, що вказує ім'я методу, який задається всередині класу.

Виконання процедури CountEmployees показано на прикладі нижче:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count - Поверне 7

Інструкція PRINT повертає кількість рядків у таблиці Employee.

 

 
Це цікаво: