7.2. Створення, модифікація та вилучення процедур, що зберігаються
Для створення збережуваної процедури застосовується команда CREATE PROCEDURE. За замовчуванням виконати створену процедуру може тільки власник БД. Він же може змінити дозвіл доступу і дозволити іншим користувачам працювати з цією процедурою. Ім'я збережуваної процедури може бути довжиною до 128 символів, включаючи символи # і ##. Синтаксис команди:
CREATE PROC[EDURE] назва_процедури [; число]
[ {@параметр тип_даних} [VARYING]
[= default_значення [OUTPUT]
]
[,... n]
[ WITH
{
RECOMPILE |
ENCRYPTION |
RECOMPILE, ENCRYPTION
}
]
[FOR REPLICATION]
AS
інструкції_SQL
Призначення параметрів:
назва_процедури – назва процедури повинна задовольняти правилам для ідентифікаторів, її довжина не може перевищувати 128 символів; для локальних тимчасових процедур перед ім'ям використовується знак #, а для глобальних тимчасових процедур – знаки ##;
число – необов'язкове ціле число, що використовується для угрупування кількох процедур під одним ім'ям;
@параметр тип_даних – список імен параметрів процедури з відповідним типом даних для кожного; таких параметрів може бути до 1024; як значення параметра дозволяється передавати NULL;
VARYING – ключове слово, яке визначає, що як вихідний параметр використовується результуючий набір – тільки для типу cursor;
default_значення – використовується у випадку, коли при виклику процедури параметр пропущений; повинно бути константою і може включати символи маски (% , _, [ ], ^, і значення NULL);
WITH RECOMPILE – ключові слова, які показують, що SQL Server не буде записувати план процедури в кеш, а буде створювати його щоразу при виконанні;
WITH ENCRYPTION – ключові слова, які показують, що SQL Server буде зашифровувати процедуру перед записом у системну таблицю sуscomments;
FOR REPLICATION – ключові слова, які показують, що ця процедура створюється тільки для реплікації; несумісні з ключовими словами WITH RECOMPILE;
AS – початок визначення тексту процедури;
інструкції_SQL – будь-який набір допустимих інструкцій SQL, обмежений тільки максимальним розміром збережуваної процедури – 128 Кб; неприпустимими є наступні інструкції:
CREATE DEFAULT;
CREATE PROCEDURE ;
CREATE RULE ;
CREATE TRIGGER;
CREATE VIEW.
Створити збережувану процедуру можна і за допомогою Enterprise Manager, виконавши наступну послідовність дій:
У вікні Microsoft Management Console виберіть сервер і БД.
Встановіть курсор миші на Stored Procedures, натисніть праву кнопку і у контекстному меню виберіть команду New Stored Procedure для виведення діалогового вікна Stored Procedure Properites. Можна також натиснути "розумну" кнопку New на панелі інструментів чи вибрати Stored Procedure у контекстному меню. У цьому діалоговому вікні можна вводити інструкції Transact-SQL (рис.7.1).
Рис. 7.1. Вікно для створення збережуваної процедури
Замість <PROCEDURE NAME> у діалоговому вікні Stored Procedure Properites необхідно вказати ім'я нової процедури, наприклад, ali_authors.
Після того, як текст процедури введений, необхідно перевірити її синтаксис. Для цього натисніть кнопку Check Syntax.
Після перевірки синтаксису процедури натисніть кнопку ОК, і процедура буде збережена в поточній БД.
В Enterprise Manager існує ще один спосіб створення нової збережуваної процедури – за допомогою майстра Stored Procedure Wizard. Розглянемо цей спосіб :
Натисніть кнопку Run Wizard на панелі інструментів і у діалоговому вікні Select Wizard виберіть Create Stored Procedures Wizard.
Виберіть БД для збереження процедури.
Наступне вікно майстра дозволяє вибрати таблиці, для яких створюються процедури (кількість таблиць обмежена лише кількістю таблиць у БД), та дії, які плануються виконувати за допомогою процедури: вставка, знищення або ж оновлення даних.
Натисніть кнопку «Далі» – з’являється вікно шаблонів збережуваних процедур.
Натиснувши кнопку «Edit», відкриваємо вікно редагування процедури.
Натиснувши кнопку «Edit SQL», маємо можливість переглянути код процедури, і якщо потрібно, то відредагувати його.
Після внесення змін натискаємо «Finish» та отримуємо повідомлення про кількість успішно створених процедур.