Створення збереженої процедури передбачає вирішення наступних завдань:
визначення типу створюваної процедури, що: тимчасова або призначена для користувача. Крім цього, можна створити свою власну системну збережену процедуру, призначивши їй ім'я з префіксом sp_ і помістивши її в системну базу даних. Така процедура буде доступна в контексті будь-якої бази даних локального сервера;
· Планування прав доступу. При створенні збереженої процедури слід враховувати, що вона буде мати ті ж права доступу до об'єктів бази даних, що і створив її користувач;
· Визначення параметрів процедури. Подібно процедурам, які входять до складу більшості мов програмування, збережені процедури можуть мати вхідними та вихідними параметрами;
· Розробка коду збереженої процедури. Код процедури може містити послідовність будь-яких команд SQL, включаючи виклик інших процедур.
Створення нової та зміна наявної процедури, що здійснюється за допомогою наступної команди:
Розглянемо параметри даної команди.
Використовуючи префікси sp_, #, ##, створювану процедуру можна визначити як системної або тимчасовою. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона повинна бути розміщена. Таким чином, щоб розмістити створювану збережену процедуру в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла збереженої процедури до об'єктів тієї ж бази даних можна використовувати укорочені імена, т. Е. Без вказівки імені бази даних. Коли ж потрібно звернутися до об'єктів, розташованих в інших базах даних, вказівка імені бази даних обов'язково.
Номер в імені - це ідентифікаційний номер збереженої процедури, однозначно визначає її в групі процедур. Для зручності управління процедурами логічно однотипні процедури, можна групувати, привласнюючи їм однакові імена, але різні ідентифікаційні номери.
Для передачі вхідних і вихідних даних в створюваній збереженій процедурі можуть використовуватися параметри, імена яких, як і імена локальних змінних, повинні починатися з символу @. В одній збереженій процедурі можна задати безліч параметрів, розділених комами. У тілі процедури не повинні застосовуватися локальні змінні, чиї імена збігаються з іменами параметрів цієї процедури.
Для визначення типу даних, який буде мати відповідний параметр збереженої процедури, годяться будь-які типи даних SQL, включаючи певні користувачем. Однак тип даних CURSOR може бути використаний тільки як вихідний параметр збереженої процедури, тобто із зазначенням ключового слова OUTPUT.
Наявність ключового слова OUTPUT означає, що відповідний параметр призначений для повернення даних з збереженої процедури. Однак це зовсім не означає, що параметр не підходить для передачі значень в збережену процедуру. Вказівка ключового слова OUTPUT наказує сервера при виході з процедури, що привласнити поточне значення параметра локальної змінної, яка була вказана при виклику процедури в якості значення параметра. Відзначимо, що при вказівці ключового слова OUTPUT значення відповідного параметра при виклику процедури може бути поставлено лише за допомогою локальної змінної. Не дозволяється використання будь-яких виразів або констант, допустимий для звичайних параметрів.
Ключове слово VARYING застосовується спільно з параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча безліч.
Ключове слово DEFAULT є значення, яке буде приймати відповідний параметр за замовчуванням. Таким чином, при виклику процедури можна не вказувати явно значення відповідного параметра.
Так як сервер кешируєт план виконання запиту і компілює код, при наступному виклику процедури будуть використовуватися вже готові значення. Однак в деяких випадках все ж потрібно виконувати перекомпіляцію коду процедури. Вказівка ключового слова RECOMPILE наказує системі створювати план виконання процедури при кожному її виклику.
Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної процедури, як статті в публікацію.
Ключове слово AS розміщується на початку власне тіла збереженої процедури, тобто набору команд SQL, за допомогою яких і буде реалізовуватися ту чи іншу дію. У тілі процедури можуть застосовуватися практично всі команди SQL, оголошуватися транзакції, встановлюватися блокування і викликатися інші процедури, що зберігаються. Вихід з процедури, що можна здійснити за допомогою команди RETURN.
Видалення збереженої процедури здійснюється командою:
DROP PROCEDURE [. n]
Виконання процедури,
Для виконання процедури використовується команда:
[[EXEC [UTE] імя_процедури [; номер]
[OUTPUT] | [DEFAULT]] [. n]
Якщо виклик збереженої процедури не є єдиною командою в пакеті, то присутність команди EXECUTE обов'язково. Більш того, ця команда потрібна для виклику процедури з тіла іншої процедури або тригера.
Використання ключового слова OUTPUT при виклику процедури дозволяється тільки для параметрів, які були оголошені при створенні процедури з ключовим словом OUTPUT.
Коли ж при виклику процедури для параметра вказується ключове слово DEFAULT, то буде використано значення за замовчуванням. Природно, вказане слово DEFAULT дозволяється тільки для тих параметрів, для яких визначено значення за замовчуванням.
З синтаксису команди EXECUTE видно, що імена параметрів можуть бути опущені при виклику процедури. Однак в цьому випадку користувач повинен вказувати значення для параметрів в тому ж порядку, в якому вони перераховувалися при створенні процедури. Присвоїти параметру значення за замовчуванням, просто пропустивши його при перерахуванні можна. Якщо ж потрібно опустити параметри, для яких визначено значення за замовчуванням, досить явної вказівки імен параметрів при виклику збереженої процедури. Більш того, таким способом можна перераховувати параметри і їх значення в довільному порядку.
Відзначимо, що при виклику процедури вказуються або імена параметрів зі значеннями, або тільки значення без імені параметра. Їх комбінування не допускається.
Приклад. Процедура без параметрів. Розробити процедуру для отримання назв і вартості товарів, придбаних Івановим.
CREATE PROC my_proc1
AS Вартість, Кліент.Фамілія
FROM Клієнт INNER JOIN
(Товар INNER JOIN Угода
3.3.2 Поняття і види каскадних впливів. Організація виконання каскадних впливів
Щоб запобігти втраті посилальної цілісності, використовується механізмкаскадних змін. Він складається в забезпеченні наступних вимог:
• необхідно заборонити зміну поля зв'язку в запису дочірньої таблиці без синхронного зміни полів зв'язку в батьківській і дочірньої таблицях; зазвичай ініціатива зміни поля зв'язку реалізується в запису батьківської таблиці;
• при зміні поля зв'язку в запису батьківської таблиці, слід синхронно змінити значення полів зв'язку у відповідних записах дочірньої таблиці;
• при видаленні запису в батьківській таблиці, слід видалити відповідні записи в дочірньої таблиці.
Дані зміни або видалення в записах дочірньої таблиці при зміні (видаленні) записи батьківської таблиці називаютсякаскаднимі змінами і каскадних вилученнями.
Зауваження 1. Існує інший різновид каскадного видалення: при видаленні батьківського записи в записах дочірніх таблиць значення полів зв'язку обнуляються. Цей різновид застосовується рідко.
Зауваження 2. Зазвичай занесення записів в дочірню таблицю здійснюється так: вибирається значення батьківської записи (наприклад, зі списку), значення поля зв'язку фіксується і потім автоматично заноситься в поля зв'язку дочірніх записів. Метод, коли користувач вручну заносить значення полів зв'язку в дочірні записи, непопулярний: користувач може внести однакове за змістом, але різний по написанню значення ( "Цукор", "цукор"). Багато рідше практикується спосіб введення дочірніх записів без вказівки значення поля зв'язку. Потім записи батьківської і дочірніх таблиць "зв'язуються".
Каскадні зміни можуть блокуватися: або одночасно зміни і видалення, або зміни або видалення окремо. Необхідність дозволу чи заборону каскадних змін зазвичай реалізується в СУБД при визначенні зв'язків між таблицями. Власне, таким чином і відбувається створення посилальної цілісності. Зазвичай в СУБД для реалізації посилальної цілісності в дочірній таблиці створюють зовнішній ключ (див. Нижче), який посилається на батьківську таблицю, і вказують вид каскадних впливів. В подальшому СУБД сама при необхідності реалізує каскадні впливу даного виду для зазначених таблиць.
3.4 Сортування, пошук і фільтрація даних в БД і вибірках
3.4.1 Різні методи сортування, пошуку і фільтрації даних