Збережені процедури sql: створення і використання
Збережені процедури SQL є виконуваний програмний модуль, який може зберігатися в базі даних у вигляді різних об`єктів. Іншими словами, це об`єкт, в якому містяться SQL-інструкції. Ці процедури, що можуть бути виконані в клієнті прикладних програм, щоб отримати хорошу продуктивність. Крім того, такі об`єкти нерідко викликаються з інших сценаріїв або навіть з будь-якого іншого розділу.
Вступ
Багато хто вважає, що вони схожі на процедури різних мов програмування високого рівня (Відповідно, крім MS SQL). Мабуть, це дійсно так. У них є схожі параметри, вони можуть видавати схожі значення. Більш того, в ряді випадків вони стикаються. Наприклад, вони поєднуються з базами даних DDL і DML, а також з функціями користувача (кодова назва - UDF).
Насправді ж процедури, що SQL мають широкий спектр переваг, які виділяють їх серед подібних процесів. Безпека, варіативність програмування, продуктивність - все це приваблює користувачів, що працюють з базами даних, все більше і більше. Пік популярності процедур припав на 2005-2010 роки, коли вийшла програма від "Майкрософт" під назвою «SQL Server Management Studio». З її допомогою працювати з базами даних стало набагато простіше, практичніше і зручніше. З року в рік такий спосіб передачі інформації набирав популярність серед програмістів. Сьогодні ж MS SQL Server є абсолютно звичною програмою, яка для користувачів, «спілкуються» з базами даних, встала нарівні з «Ексель».
При виклику процедури вона моментально обробляється самим сервером без зайвих процесів і взаємодії з користувачем. Після цього можна здійснювати будь-які дії з інформацією: видалення, виконання, зміна. За все це відповідає DDL-оператор, який поодинці робить складні дії по обробці об`єктів. Причому все це відбувається дуже швидко, а сервер практично не навантажується. Така швидкість і продуктивність дозволяють дуже швидко передавати великі обсяги інформації від користувача на сервер і навпаки.
Для реалізації даної технології роботи з інформацією існує кілька мов програмування. До них можна віднести, наприклад, PL / SQL від системи управління базами даних Oracle, PSQL в системах InterBase і Firebird, а також класичний «майкрософтовського» Transact-SQL. Всі вони призначені для створення і виконання збережених процедур, що дозволяє в великих обробниках баз використовувати власні алгоритми. Це потрібно і для того, щоб ті, хто здійснює управління такою інформацією, могли захистити всі об`єкти від несанкціонованого доступу сторонніх осіб і, відповідно, створення, зміни або видалення тих чи інших даних.
продуктивність
Ці об`єкти баз даних можуть бути запрограмовані різними шляхами. Це дозволяє користувачам вибирати тип використовуваного способу, який буде найбільш підходящим, що економить сили і час. Крім того, процедура сама обробляється, що дозволяє уникнути величезних витрат часу на обмін між сервером і користувачем. Також модуль можна перепрограмувати і змінити в потрібний напрямок в абсолютно будь-який момент. Особливо варто відзначити швидкість, з якою відбувається запуск збереженої процедури SQL: це процес відбувається швидше інших, схожих з ним, що робить його зручним і універсальним.
Безпека
Такий тип обробки інформації відрізняється від схожих процесів тим, що він гарантує підвищену безпеку. Це забезпечується за рахунок того, що доступ інших користувачів до процедур може бути виключений цілком і повністю. Це дозволить адміністратору проводити операції з ними самостійно, не побоюючись за перехоплення інформації або несанкціонований доступ до бази даних.
Передача даних
Зв`язок між збереженої процедурою SQL і клієнтським додатком полягає в використанні параметрів і значеннях. Останнім не обов`язково передавати дані в збережену процедуру, однак ця інформація (в основному за запитом користувача) і переробляється для SQL. Після того як процедура, що зберігається завершила свою роботу, вона відсилає пакети даних назад (але, знову ж таки, за бажанням) до викликав його з додатком, використовуючи різні методи, за допомогою яких може бути здійснений як виклик збереженої процедури SQL, так і повернення, наприклад:
Відео: 11a Основи запитів SQL Створення збереженої процедури
- передача даних за допомогою параметра типу Output;
- передача даних за допомогою оператора повернення;
- передача даних за допомогою оператора вибору.
А тепер розберемося, як же виглядає цей процес зсередини.
Відео: Тригери і збережені процедури в MS SQL Server (Triggers and stored procedures)
1. Створення EXEC-процедури, що в SQL
Ви можете створити процедуру в MS SQL (Managment Studio). Після того як створиться процедура, вона буде перерахована в програмований вузол бази даних, в якій процедура створення виконується оператором. Для виконання процедури, що SQL використовують EXEC-процес, який містить ім`я самого об`єкта.
При створенні процедури її назва з`являється першим, після чого проводиться один або кілька параметрів, наданих йому. Параметри можуть бути необов`язковими. Після того як параметр (и), тобто тіло процедури, будуть написані, потрібно провести деякі необхідні операції.
Справа в тому, що тіло може мати локальні змінні, розташовані в ній, і ці змінні є локальними також по відношенню до процедур. Іншими словами, їх можна розглядати тільки всередині тіла процедури Microsoft SQL Server. Збережені процедури в такому випадку вважаються локальними.
Таким чином, щоб створити процедуру, нам потрібно ім`я процедури і, щонайменше, один параметр як тіла процедури. Зверніть увагу, що відмінним варіантом в такому випадку є створення і виконання процедури з ім`ям схеми в класифікаторі.
Тіло процедури може мати будь-який вид з операторів SQL, наприклад, такі як створення таблиці, вставки одного або декількох рядків таблиці, встановлення типу і характеру бази даних і так далі. Проте тіло процедури обмежує виконання деяких операцій в ньому. Деякі з важливих обмежень перераховані нижче:
- тіло не повинно створювати будь-якої іншої процедури, що;
- тіло не повинно створити хибне уявлення про об`єкт;
- тіло не повинно створювати ніяких тригерів.
2. Встановлення змінної в тіло процедури
Ви можете зробити змінні локальними для тіла процедури, і тоді вони будуть перебувати виключно всередині тіла процедури. Доброю практикою є створення змінних на початку тіла збереженої процедури. Але також ви можете встановлювати змінні в будь-якому місці в тілі даного об`єкта.
Іноді можна помітити, що кілька змінних встановлені в одному рядку, і кожен змінний параметр відокремлюється комою. Також зверніть увагу, що змінна має префікс @. У тілі процедури ви можете встановити змінну, куди ви хочете. Наприклад, змінна @ NAME1 може оголошена ближче до кінця тіла процедури. Для того щоб привласнити значення оголошеної змінної використовується набір особистих даних. На відміну від ситуації, коли оголошено більше однієї змінної в одному рядку, в такій ситуації використовується тільки один набір особистих даних.
Часто користувачі задають питання: «Як призначити кілька значень в одному операторі в тілі процедури?» Що ж. Питання цікаве, але зробити це набагато простіше, ніж ви думаєте. Відповідь: за допомогою таких пар, як «Select Var = значення». Ви можете використовувати ці пари, розділяючи їх комою.
Відео: 1.15. Створення збереженої процедури
3. Створення збереженої процедури SQL
У самих різних прикладах люди показують створення простої процедури, що і виконання її. Однак процедура може приймати такі параметри, що викликає її процес буде мати значення, близькі до нього (але не завжди). Якщо вони збігаються, то всередині тіла починаються відповідні процеси. Наприклад, якщо створити процедуру, яка буде приймати місто і регіон від абонента і повертати дані про те, скільки авторів відносяться до відповідних місту та регіону. Процедура буде запитувати таблиці авторів бази даних, наприклад, Pubs, для виконання цього підрахунку авторів. Щоб отримати ці бази даних, наприклад, Google завантажує сценарій SQL зі сторінки SQL2005.
У попередньому прикладі процедура приймає два параметри, які англійською мовою умовно називатимуться @State і @City. Тип даних відповідає типу, визначеним у додатку. Тіло процедури має внутрішні змінні @TotalAuthors (всього авторів), і ця змінна використовується для відображення їх кількості. Далі з`являється розділ вибору запиту, який все підраховує. Нарешті, підрахована значення виводиться в вікні виводу за допомогою оператора друку.
Як в SQL виконати збережену процедуру
Є два способи виконання процедури. Перший шлях показує, передаючи параметри, як розділений комами список виконується після імені процедури. Припустимо, ми маємо два значення (як в попередньому прикладі). Ці значення збираються за допомогою змінних параметрів процедури @State і @City. У цьому способі передачі параметрів важливий порядок. Такий метод називається порядкова передача аргументів. У другому способі параметри вже безпосередньо призначені, і в цьому випадку порядок не важливий. Цей другий метод відомий як передача іменованих аргументів.
Процедура може трохи відхилятися від типової. Все так же, як і в попередньому прикладі, але тільки тут параметри зсуваються. Тобто параметр @City зберігається першим, а @State зберігається поряд із значенням за замовчуванням. Параметр за замовчуванням виділяється зазвичай окремо. Збережені процедури SQL проходять як просто параметри. У цьому випадку, за умови, параметр «UT» замінює значення за замовчуванням «СА». У другому виконанні проходить тільки одне значення аргументу для параметра @City, і параметр @State приймає значення за замовчуванням «СА». Досвідчені програмісти радять, щоб всі змінні за замовчуванням розташовувалися ближче до кінця списку параметрів. В іншому випадку виконання не представляється можливим, і тоді ви повинні працювати з передачею іменованих аргументів, що довше і складніше.
4. Збережені процедури SQL Server: способи повернення
Існує три важливих способу відправки даних в викликаної збереженій процедурі. Вони перераховані нижче:
- повернення значення збереженої процедури;
- вихід параметра збережених процедур;
Відео: Використання збережених процедур і функцій в 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, проте їх вивчення необхідно тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні збережених процедур, однак це потрібно більше для тих, хто планує щільно зайнятися програмуванням, в тому числі професійно.
