Предметна область: Інформатика, кібернетика та програмування
Розмір файлу: 87 KB
Роботу скачали: 18 чол.
Процедура - це найбільш часто використовувана в базах даних програмна структура, що представляє собою оформлений особливим чином сценарій (вірніше, пакет), який зберігається в базі даних, а не в окремому файлі. Збережені процедури відрізняються від сценаріїв тим, що в них допускається використання вхідних і вихідних параметрів, а також значень, що повертаються, які фактично не можуть використовуватися в звичайному сценарії.
Процедура є просто ім'я, пов'язане з програмним кодом T-SQL, який зберігається і виконується на сервері. Вона може містити практично будь-які конструкції або команди, виконання яких підтримується в SQL Server. Процедури можна використовувати для зміни даних, повернення скалярних значень або цілих результуючих наборів. Збережені процедури, є основним інтерфейсом, який повинен використовуватися додатками для звернення до будь-яких даних в базах даних. Збережені процедури дозволяють не тільки управляти доступом до бази даних, але також ізолювати код бази даних для спрощення обслуговування.
Як серверні програми збережені процедури мають ряд переваг.
- Збережені процедури зберігаються в компілювати вигляді, тому виконуються швидше, ніж пакети або запити.
- Виконання обробки даних на сервері, а не на робочій станції, значно знижує навантаження на локальну мережу.
- Збережені процедури мають модульний вид, тому їх легко впроваджувати і змінювати. Якщо клієнтську програму викликає збережену процедуру для виконання деякої операції, то модифікація процедури в одному місці впливає на її виконання у всіх користувачів.
- Збережені процедури можна розглядати як важливий компонент системи безпеки бази даних. Якщо все клієнти здійснюють доступ до даних за допомогою збережених процедур, то прямий доступ до таблиць може бути заборонений, і всі дії користувачів будуть перебувати під контролем. Що ще важливіше, збережені процедури приховують від користувача структуру бази даних і дозволяють йому виконання тільки тих операцій, які запрограмовані в збереженій процедурі.
Управління збереженими процедурами Збережені процедури управляються за допомогою інструкцій мови визначення даних (DDL) CREATE, ALTER і DROP. Загальний синтаксис T-SQL коду для створення збереженої процедури має такий вигляд:
CREATE PROC | PROCEDURE
Структура цього оператора відповідає основному синтаксису CREATE
Процедури можуть мати будь-яке число вхідних параметрів (@parametr) заданого типу даних (data_type), які використовуються всередині процедури як локальні змінні. При виконанні процедури для кожного з оголошених формальних параметрів повинні бути передані фактичні значення. Або ж для вхідного параметра може бути визначено значення за замовчуванням (default), яке повинно бути константою або дорівнювати NULL. В цьому випадку процедуру можна виконати без вказівки значення відповідного аргументу. Застосування вхідних параметрів необов'язково.
Можна також вказати вихідні параметри (помічені як OUTPUT), що дозволяють збереженій процедурі повернути одне або кілька скалярних значень в підпрограму, з якої вона була викликана. При створенні процедур можна задати три параметри. При створенні процедури з параметром ENCRYPTION SQL Server шифрує визначення процедури. При завданні параметра RECOMPILE SQL Server перекомпілюються збережену процедуру при кожному її запуску. Параметр EXECUTE AS визначає контекст безпеки для процедури.
В кінці визначення процедури, що слідом за ключовим словом AS має бути приведене безпосередньо тіло процедури (sql_statements) у вигляді коду з однієї або декількох інструкцій мови T-SQL.
Інструкція DROP видаляє збережену процедуру з бази даних. Інструкція ALTER змінює вміст всієї процедури, що. Для внесення змін краще використовувати інструкцію ALTER, а не комбінацію інструкцій видалення і створення, так як останній метод видаляє всі дозволи.
Приклад процедури, без параметрів
Щоб протестувати нову процедуру, відкрийте новий запит SQL Server і виконайте наступний код.
Застосування вхідних параметрів
Процедура надає певні процедурні можливості (а якщо вона застосовується в інфраструктурі .NET, такі можливості стають дуже значними), а також забезпечує підвищення продуктивності, але в більшості обставин збережена процедура не дозволяє домогтися багато чого, якщо не передбачена можливість передати їй деякі дані, що вказують на те, які дії повинні бути виконані з її допомогою. Зокрема основна проблема, пов'язана з попередньою збереженої процедурою (spr_getOrders), полягає в її статичності. Якщо користувачам буде потрібно інформація про замовлення за інший період часу, то ця процедура їм не допоможе. Тому необхідно передбачити можливість передачі в неї відповідних вхідних параметрів, які дозволили б динамічно змінювати період вибірки.
@parameter_name [AS] datatype [= default | NULL]
У наступному прикладі збережена процедура spr_getOrders доповнюється двома вхідними параметрами, що дозволяють явно вказати період вибірки.
ALTER PROCEDURE [dbo]. [Spr_getOrders] @dateBegin datetime. @dateEnd datetime AS SELECT IdOrd, IdCust, OrdDate FROM [Order] WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd)
У наступних трьох прикладах продемонстровані виклики збережених процедур і передача їм параметрів з використанням вихідного порядку і імен:
Застосування вихідних параметрів
Вихідні параметри дозволяють збереженій процедурі повертати дані викликає програмі. Для визначення вихідних параметрів використовується ключове слово OUT [PUT], яке обов'язково як при визначенні процедури, так і при її виклику. У самій збереженій процедурі вихідні параметри є локальними змінними. У зухвалій процедурі або пакеті вихідні змінні повинні бути попередньо визначені, щоб отримати результуючі значення. Коли виконання збереженої процедури завершується, поточне значення параметра передасться локальної змінної викликає програми.
У наступному прикладі вихідний параметр використовується для повернення унікального ідентифікатора знову доданого товару.
CREATE PROCEDURE spr_addProduct @Description nvarchar (100), @InStock int = 0, @IdProd int OUT AS
INSERT Product ([Description], InStock) VALUES (@Description, @InStock)
SET @IdProd = @@ IDENTITY
DECLARE @IdProd int
EXEC spr_addProduct @Description = N 'Новий товар', @IdProd = @IdProd OUTPUT
SELECT @IdProd as N '@IdProd'
Зверніть увагу на те, що при виклику процедури передані значення не для всіх параметрів. Параметр InStock є необов'язковим, оскільки для нього зазначено значення за замовчуванням у вигляді нуля, яке і буде використано, в разі якщо для нього не буде явно надано інше значення. При цьому якби виклик збереженої процедури і передача значень відбувалися з використанням позиційних параметрів, то довелося б заповнювати кожну позицію в списку параметрів, щонайменше, до того, як зустрівся б останній параметр, для якого має бути передбачено значення.
Підтвердження успішного або невдалого завершення роботи за допомогою значень, що повертаються. Використання команди RETURN.
Будь-яка викликається на виконання збережена процедура повертає значення, незалежно від того, чи передбачений в ній повернення значення чи ні. За замовчуванням після успішного завершення процедури СУБД SQL Server автоматично повертає значення, рівне нулю.
Щоб передати деякий повертається значення з збереженої процедури назад в викликає код, досить застосувати оператор RETURN:
RETURN [<Целое число>]
Зверніть увагу на те, що повертається значення повинно бути обов'язково цілочисельним.
Значення, що повертаються призначені виключно для вказівки на успішне або невдале завершення процедури, що і дозволяють навіть позначити ступінь або характер успіху або невдачі. Використання повертається для повернення фактичних даних, таких як ідентифікаційне значення або дані про кількість рядків, порушених збереженої процедурою, розглядається як неприпустима практика програмування. Значення, що повертається 0 вказує на успішне виконання процедури і встановлено за замовчуванням. Компанія Microsoft зарезервувала значення від -99 до -1 для службового користування. Розробникам для повернення стану помилки користувачеві рекомендується використовувати значення -100 і менше.
Однією з найбільш важливих особливостей оператора RETURN є те, що його виконання призводить до безумовного завершення роботи і виходу з збереженої процедури. Це означає, що, незалежно від місцезнаходження оператора RETURN в коді збереженій процедурі, після його виконання більше не буде виконана жодна рядок коду. Під безумовним завершенням роботи мається на увазі, що діяння, передбачене оператором RETURN, здійснюється незалежно від того, в якому місці коду він виявляється. З іншого боку, допускається наявність в коді процедури, що кількох операторів RETURN, а виконання цих операторів відбувається, тільки якщо до цього призводить звичайна структура управління процесом виконання коду.
У попередньому прикладі при спробі додавання в таблицю Product інформації про новий товар з дублюючим назвою могла статися помилка, оскільки по найменуванню товару організовано обмеження унікальності. Розширимо збережену процедуру spr_addProduct, передбачивши попередню перевірку на наявність зазначеного товару в базі і індикацію про успішність операції через вихідний параметр.
ALTER PROCEDURE [dbo]. [Spr_addProduct] @Description nvarchar (100), @InStock int = 0, @IdProd int OUT AS
IF EXISTS (SELECT * FROM Product WHERE [Description] = @Description) RETURN -100
INSERT Product ([Description], InStock) VALUES (@Description, @InStock)
SET @IdProd = @@ IDENTITY
При виклику процедури, що, якщо очікується вихідне значення, команда EXEC повинна використовувати целочисленную змінну:
EXEC @ локальная_переменная = імя_хранімой_процедури;
DECLARE @return_value int, @IdProd int
EXEC @return_value = spr_addProduct @Description = N 'Новий товар'. @IdProd = @IdProd OUTPUT
IF @return_value = 0 BEGIN PRINT 'Товар успішно доданий' SELECT @IdProd as N '@IdProd' END
ELSE BEGIN PRINT 'При додаванні товару сталася помилка' SELECT 'Return Value' = @ return _ value END
Завдання для самостійної роботи: Створіть збережені процедури, що реалізують такі дії:
Повернення списку всіх замовлень містять заданий товар (по IdProd) .Визначення кількості клієнтів, які не мають жодного замовлення. Результат повинен повертатися через вихідний параметр.Удаленіе з бази даних інформації про певний клієнта (по IdCust). Якщо з даним клієнтом є пов'язані записи (замовлення) видалення повинно бути скасовано. Значення, що повертається повинно визначати успішність виконання операції.
Мета моєї роботи: вивчити значення прізвищ мого генеалогічного древа, дізнатися як жили, чим займалися мої предки, ким вони були за професією. Завдання: з'ясувати, хто є моїми предками; встановити, де вони проживали і чим займалися; вивчити їх біографію, значення прізвищ мого роду.