Збережена процедура (англ. stored procedure, також звана proc, storp, sproc, StoPro, StoredProc, StoreProc, sp або SP) — підпрограма, доступна застосункам, які мають доступ до системи керування реляційними базами даних (СКРБД). Такі процедури зберігаються у словнику даних бази.
До типових застосувань збережених процедур належать валідація даних[en] (вбудована до бази даних) та механізми контролю доступу. Крім того, збережені процедури можуть збирати та централізувати логіку, яку спочатку було реалізовано в застосунках. Для збереження часу та пам'яті, об'ємні чи складні обробки, що вимагають виконання декількох операторів SQL, може бути зібрано до збережених процедур, і усі застосунки можуть викликати ці процедури. Можна використовувати вкладені збережені процедури шляхом виклику одних процедур з інших.
Збережені процедури подібні до користувацьких функцій[en] (англ. user-defined function, UDF). Основною відмінністю є те, що користувацькі функції можуть використовуватися як інші вирази в операторах SQL, тоді як збережені процедури повинні викликатися оператором CALL
.
CALL procedure(...)
або
EXECUTE procedure(...)
Збережені процедури можуть повертати результатні набори, тобто, результати оператора SELECT
. Такі результатні набори можливо оброблювати з використанням курсорів, іншими збереженими процедурами, шляхом пов'язування визначника результатного набору, або застосунками. Збережені процедури також можуть містити оголошені змінні для обробки даних, і курсори, що дозволяють організовувати цикли по рядках таблиці. До операторів керування потоком збережених процедур зазвичай належать IF
, WHILE
, LOOP
, REPEAT
та CASE
, а також інші. Збережені процедури можуть отримувати змінні, повертати результати чи змінювати змінні та повертати їх, залежно від способу та місця оголошення змінної.
Реалізація Редагувати
Точна і правильна реалізація збережених процедур різниться від однієї системи баз даних до іншої. Більшість основних постачальників баз даних у тому чи іншому вигляді їх підтримують. Залежно від системи баз даних, збережені процедури можливо реалізовувати різними мовами програмування, наприклад SQL, Java, C або C++. Збережені процедури, написані мовами, відмінними від SQL, можуть мати або не мати можливості виконувати власне оператори SQL.
Збільшення прийняття збережених процедур спричинило впровадження процедурних елементів до мови SQL у стандартах SQL:1999 та SQL:2003 у частині SQL/PSM. Це зробило SQL імперативною мовою програмування. Більшість систем баз даних надають власницькі чи специфічні розширення постачальника понад SQL/PSM. Стандартна специфікація збережених процедур Java[en] існує як SQL/JRT[en].
Система баз даних | Мова реалізації |
---|---|
CUBRID[en] | Java |
DB2 | SQL PL[en] (близька до стандарту SQL/PSM) або Java |
Firebird | PSQL (Fyracle також підтримує частини Oracle PL/SQL) |
Informix | SPL або Java |
Microsoft SQL Server | Transact-SQL та різні мови .NET Framework |
MySQL | власні збережені процедури, що близько дотримуються стандарту SQL/PSM |
NuoDB[en] | SQL або Java |
Oracle | PL/SQL або Java |
PostgreSQL | PL/pgSQL[ru], може також використовувати власну функційну мову, як-от PL/Perl або PL/PHP |
SAP HANA[ru] | SQL або R |
Sybase ASE[en] | Transact-SQL |
Порівняння з динамічною SQL Редагувати
Інші застосування Редагувати
В деяких системах збережені процедури можуть використовуватися для контролю за управлінням транзакціями; в інших збережені процедури запускаються всередині транзакції так, що останні ефективно їх обгортають. Збережені процедури також можуть викликатися з тригера бази даних або обробника умови. Наприклад, збережена процедура може спрацьовувати при вставці до заданої таблиці чи оновленні її поля, і виконуватиметься код всередині збереженої процедури. Також, написання збережених процедур як обробників умов дозволяє адміністраторам баз даних відстежувати помилки в системі з більшою деталізацією шляхом їх використання для відловлювання помилок і запису деякої інформації для аудиту до бази даних або зовнішнього ресурсу, наприклад, файлу.
Порівняння з функціями Редагувати
- Функція є підпрограмою, написаною для виконання деяких обчислень.
- Скалярна функція повертає лише одне значення (або NULL), тоді як таблична функція повертає (реляційну) таблицю, що складається з 0 чи більше рядків, які, у свою чергу, з 1 чи більше стовпчиків.
- Функції повинні повертати значення (використовуючи ключове слово
RETURN
), але від збережених процедур це не вимагається. - Збережені процедури можуть використовувати ключове слово
RETURN
, але без передавання жодного значення. - Функції можуть використовуватися в операторах
SELECT
, за умови, що вони не маніпулюють даними. Процедури ж до операторівSELECT
включено бути не може. - Збережені процедури можуть повертати кілька значень, використовуючи параметр
OUT
, або не повертати їх взагалі. - Збережена процедура заощаджує час компілювання запитів.
- Збережена процедура є об'єктом бази даних.
- Збережена процедура є матеріальним об'єктом.
Порівняння з підготовленими операторами Редагувати
Підготовлені оператори[en] приймають на вхід звичайний оператор або запит, і параметризують його, так, що пізніше можуть використовуватися різні значення літералів. Як і збережені процедури, вони зберігаються на сервері задля ефективності, та надають деякий захист від атак SQL-ін'єкціями. Простіші та більш декларативні, підготовлені оператори зазвичай не пишуться з використанням процедурної логіки, і не можуть оперувати змінними. Через простий інтерфейс і реалізацію на боці клієнта підготовлені оператори є ширше переносними між СКБД.
Недоліки Редагувати
- Мови збережених процедур часто специфічні для постачальника. Зміна постачальників баз даних зазвичай вимагає переписування наявних збережених процедур.
- Мови збережених процедур різних постачальників мають різні рівні витонченості.
- Наприклад, pgpsql у Postgres має більше мовних можливостей (особливо з розширеннями), ніж Microsoft T-SQL.[джерело?]
- Засоби підтримки написання та зневаджування збережених процедур часто не такі добрі, як для інших мов програмування, але це різниться між постачальниками та мовами.
- Наприклад, і PL/SQL, і T-SQL мають виділені ІСР та зневаджувачі. PL/PgSQL можна зневаджувати з різних ІСР.
- Зміни збережених процедур важче відстежувати в межах систем керування версіями, ніж зміни іншого коду. Зміни повинні відтворюватися як сценарії для включення до історії проекту, а різниці у процедурах може бути важче правильно зливати та відстежувати.
Примітки Редагувати
Посилання Редагувати
- . MySQL. Архів оригіналу за 26 лютого 2017. Процитовано 4 лютого 2017.
- . PostgreSQL. Архів оригіналу за 5 травня 2016. Процитовано 4 лютого 2017.
- . Архів оригіналу за 5 лютого 2017. Процитовано 4 лютого 2017.
- . Стенфордський університет. Архів оригіналу за 3 грудня 2016. Процитовано 4 лютого 2017.
- . Oracle Corporation. Архів оригіналу за 28 жовтня 2011. Процитовано 4 лютого 2017.