Збережена процедура - об'єкт бази даних, що є набором SQL-інструкцій, який компілюється один раз і зберігається на сервері. Збережені процедури дуже схожі на звичайні процедури мов високого рівня, у них можуть бути вхідні та вихідні параметри та локальні змінні, в них можуть проводитись числові обчислення та операції над символьними даними, результати яких можуть надаватися змінним та параметрам. У процедурах, що зберігаються, можуть виконуватися стандартні операції з базами даних (як DDL, так і DML). Крім того, в процедурах, що зберігаються, можливі цикли і розгалуження, тобто в них можуть використовуватися інструкції управління процесом виконання.
Збережені процедури схожі на функції, що визначаються користувачем (UDF). Основна відмінність полягає в тому, що функції користувача можна використовувати як і будь-яке інше вираження в SQL запиті, в той час як процедури, що зберігаються, повинні бути викликані за допомогою функції CALL:
CALL процедура (…)
EXECUTE процедура(…)
Збережені процедури можуть повертати безліч результатів, тобто результати запиту SELECT. Такі множини результатів можуть оброблятися, використовуючи курсори, іншими процедурами, що зберігаються, повертаючи покажчик результуючого множини, або ж додатками. Збережені процедури можуть також містити оголошені змінні для обробки даних та курсорів, які дозволяють організувати цикл кількох рядків у таблиці. Стандарт SQL надає для роботи вирази IF, LOOP, REPEAT, CASE та багато інших. Процедури, що зберігаються, можуть приймати змінні, повертати результати або змінювати змінні і повертати їх, залежно від того, де змінну оголошено.
Реалізація процедур, що зберігаються, варіюється від однієї СКБД до іншої. Більшість великих постачальників баз даних підтримують в тій чи іншій формі. Залежно від СУБД, процедури, що зберігаються, можуть бути реалізовані різними мовами програмування, таких, як SQL, Java, C або C++. Збережені процедури, написані не на SQL, можуть самостійно виконувати SQL-запити, а можуть і не виконувати.
За
Поділ логіки з іншими програмами. Збережені процедури інкапсулюють функціональність; це забезпечує зв'язність доступу до даних та управління ними між різними програмами.
Ізоляція користувачів від таблиць бази даних. Це дозволяє давати доступ до процедур, що зберігаються, але не до самих даних таблиць.
Забезпечує механізм захисту. Відповідно до попереднього пункту, якщо ви можете отримати доступ до даних лише через процедури, що зберігаються, ніхто інший не зможе стерти ваші дані через команду SQL DELETE.
Поліпшення виконання як наслідок скорочення мережного трафіку. За допомогою процедур, що зберігаються, безліч запитів можуть бути об'єднані.
Проти
Підвищення навантаження на сервер баз даних у зв'язку з тим, що більшість роботи виконується на серверній частині, а менша - на клієнтській.
Доведеться багато чого навчити. Вам знадобиться вивчити синтаксис MySQL виразів для написання своїх процедур, що зберігаються.
Ви дублюєте логіку своєї програми в двох місцях: серверний код і код для процедур, що зберігаються, тим самим ускладнюючи процес маніпулювання даними.
Міграція з однієї СУБД на іншу (DB2, SQL Server та ін) може призвести до проблем.
Призначення та переваги збережених процедур
Збережені процедури дозволяють підвищити продуктивність, розширюють можливості програмування та підтримують функції безпеки даних.
Замість зберігання запиту, що часто використовується, клієнти можуть посилатися на відповідну процедуру, що зберігається. При викликі процедури, що зберігається, її вміст відразу ж обробляється сервером.
Крім виконання запиту, збережені процедури дозволяють також проводити обчислення і маніпуляцію даними - зміна, видалення, виконувати DDL-оператори (не у всіх СУБД!) і викликати інші процедури, що зберігаються, виконувати складну транзакційну логіку. Один-єдиний оператор дозволяє викликати складний сценарій, який міститься в процедурі, що дозволяє уникнути пересилання через мережу сотень команд і, особливо, необхідності передачі великих обсягів даних з клієнта на сервер.
У більшості СУБД при першому запуску процедури, що зберігається, вона компілюється (виконується синтаксичний аналіз і генерується план доступу до даних). Надалі її обробка здійснюється швидше. У СУБД Oracle виконується інтерпретація процедурного коду, що зберігається, що зберігається в словнику даних. Починаючи з версії Oracle 10g підтримується так звана природна компіляція (native compilation) збереженого процедурного коду Сі і потім в машинний код цільової машини, після чого при виклику процедури відбувається пряме виконання її скомпілюваного об'єктного коду.
Можливості програмування
Створену процедуру можна викликати в будь-який момент, що забезпечує модульність і стимулює повторне використання коду. Останнє полегшує супровід бази даних, оскільки вона стає ізольованою від мінливих бізнес-правил. Модифікувати процедуру, що зберігається відповідно до нових правил, можна в будь-який момент. Після цього всі програми, що її використовують, автоматично прийдуть у відповідність до нових бізнес-правил без безпосередньої модифікації.
Безпека
Використання процедур, що зберігаються, дозволяє обмежити або взагалі виключити безпосередній доступ користувачів до таблиць бази даних, залишивши користувачам тільки дозволи на виконання процедур, що зберігаються, що забезпечують непрямий і строго регламентований доступ до даних. Крім того, деякі СУБД підтримують шифрування тексту (wrapping) процедури, що зберігається.
Ці функції безпеки дозволяють ізолювати від користувача структуру бази даних, що забезпечує цілісність та надійність бази.
Знижується ймовірність таких дій як «впровадження SQL-коду», оскільки добре написані процедури, що зберігаються, додатково перевіряють вхідні параметри перед тим, як передати запит СУБД.
Реалізація процедур, що зберігаються
Збережені процедури зазвичай створюються за допомогою мови SQL або конкретної реалізації в обраній СУБД. Наприклад, для цих цілей в СУБД Microsoft SQL Server існує мова Transact-SQL, Oracle - PL/SQL, InterBase і Firebird - PSQL, PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 – SQL/PL (англ.), в Informix – SPL. MySQL досить близько слідує стандарту SQL:2003, її мова схожа на SQL/PL.
У деяких СУБД можливе використання процедур, що зберігаються, написаних на будь-якій мові програмування, здатному створювати незалежні виконувані файли, наприклад, на C++ або Delphi. У термінології Microsoft SQL Server такі процедури називаються розширеними процедурами, що зберігаються, і є просто функціями, що містяться в Win32-DLL. А, наприклад, Interbase і Firebird для функцій, викликаних з DLL/SO, визначено іншу назву - UDF (User Defined Function). У MS SQL 2005 з'явилася можливість написання процедур, що зберігаються на будь-якій мові.NET, а від розширених процедур, що зберігаються, в майбутньому планується відмовитися. СУБД Oracle, у свою чергу, допускає написання процедур, що зберігаються на мові Java. В IBM DB2 написання збережених процедур і функцій на звичайних мовах програмування є традиційним способом, що підтримується з самого початку, а процедурне розширення SQL було додано до цієї СУБД тільки в пізніх версіях, після його включення до стандарту ANSI. Також процедури на Java та С підтримує Informix.
У СУБД Oracle процедури, що зберігаються, можуть об'єднуватися в так звані пакети (англ. packages). Пакет складається з двох частин - специфікації (англ. package specification), в якій вказується визначення процедури, що зберігається, і тіла (англ. package body), де знаходиться її реалізація. Таким чином, Oracle дозволяє відокремити інтерфейс програмного коду від його реалізації.
У СУБД IBM DB2 процедури, що зберігаються, можна об'єднувати в модулі.
Синтаксис
CREATE PROCEDURE `p2` ()
SQL SECURITY DEFINER
COMMENT "A procedure"
SELECT "Hello World!";
Перша частина коду створює процедуру, що зберігається. Наступна – містить необов'язкові параметри. Потім іде назва і, нарешті, тіло самої процедури.
4 характеристики процедури, що зберігається:
Language: з метою забезпечення переносимості, за умовчанням вказано SQL.
Deterministic: якщо процедура постійно повертає той самий результат, і приймає одні й самі параметри. Це для реплікації та процесу реєстрації. Значення за замовчуванням - NOT DETERMINISTIC.
SQL Security: під час дзвінка відбувається перевірка прав користувача. INVOKER - це користувач, що викликає процедуру, що зберігається. DEFINER – це “творець” процедури. Значення за промовчанням - DEFINER.
Comment: з метою документування, значення за замовчуванням - ""
Виклик збереженої процедури
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , "string parameter", @parameter_var);
Зміна процедури, що зберігається
MySQL має вираз ALTER PROCEDURE для зміни процедур, але він підходить для зміни лише деяких характеристик. Якщо вам потрібно змінити параметри або тіло процедури, слід видалити та створити її заново.
Вилученнязберігаєтьсяпроцедури
DROP PROCEDURE IF EXISTS p2;
Це проста команда. Вираз IF EXISTS відловлює помилку, якщо такої процедури не існує.
Параметри
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): один параметр, що одночасно входить і повертається.
Синтаксис оголошення змінної виглядає так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Збережені процедури 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, що зберігаються, проте їх вивчення необхідне тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні процедур, що зберігаються, проте це потрібно більше для тих, хто планує щільно зайнятися програмуванням, у тому числі професійно.
Зберігається процедура- це спеціальний тип пакету інструкцій 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.
Зберігається процедура (англ. 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
[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, як цього вимагає процедура. Параметру можна призначити як постійне значення, а й значення інший змінної.
Мета роботи– навчитися створювати та використовувати збережені процедури на сервері БД.
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. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа у певному діапазоні.
