Збережена процедура
Збережена процедура (англ. stored procedure, також звана proc, storp, sproc, StoPro, StoredProc, StoreProc, sp або SP) — підпрограма, доступна застосункам, які мають доступ до системи керування реляційними базами даних (СКРБД). Такі процедури зберігаються у словнику даних бази.
До типових застосувань збережених процедур належать валідація даних[en] (вбудована до бази даних) та механізми контролю доступу. Крім того, збережені процедури можуть збирати та централізувати логіку, яку спочатку було реалізовано в застосунках. Для збереження часу та пам'яті, об'ємні чи складні обробки, що вимагають виконання декількох операторів SQL, може бути зібрано до збережених процедур, і усі застосунки можуть викликати ці процедури. Можна використовувати вкладені збережені процедури шляхом виклику одних процедур з інших.
Збережені процедури подібні до користувацьких функцій[en] (англ. user-defined function, UDF). Основною відмінністю є те, що користувацькі функції повертають результат, та можуть використовуватися як інші функції в запитах та операторах SQL, тоді як збережені процедури не завжди явно повертають результат, та представляють собою окремий виклик. Викликається оператором CALL.[1]
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
ред.- Накладні витрати
- Оскільки оператори збережених процедур зберігаються безпосередньо у базі даних, вони можуть позбавляти всіх або частини накладних витрат на компіляцію, що зазвичай необхідно в ситуаціях, коли програмні застосунки надсилають рядкові (динамічні) запити SQL до бази даних. (Проте, більшість систем баз даних реалізують кеші операторів та інші методи уникання повторної компіляції динамічних операторів SQL.) Також, уникаючи деякого попередньо скомпільованого SQL, оператори ускладнюють побудову оптимального плану виконання через те, що не всі аргументи оператора SQL надаються під час компіляції. Залежно від конфігурації та налаштувань баз даних, можна спостерігати змішані результати продуктивності збережених процедур у порівнянні зі звичайними запитами та користувацькими функціями.
- Уникання мережевого трафіку
- Основною перевагою збережених процедур є те, що вони можуть запускатися безпосередньо в рушії бази даних. У виробничій системі це зазвичай означає, що процедури запускаються цілком на спеціалізованому сервері баз даних, який має прямий доступ до даних. Перевагою є те, що витрат на мережеві комунікації можна уникнути повністю. Це стає важливішим для складніших послідовностей операторів SQL.
- Інкапсулювання бізнес-логіки
- Збережені процедури дозволяють програмістам вбудовувати бізнес-логіку як ППІ до бази даних, що може спрощувати керування даними та зменшувати необхідність кодування логіки деінде у клієнтських програмах. Це може призводити до зниження ймовірності пошкодження даних збійними клієнтськими програмами. За допомогою збережених процедур система баз даних може забезпечувати цілісність і узгодженість даних.
- Делегування прав доступу
- У багатьох системах збереженим процедурам можуть надаватися такі права доступу до бази даних, яких користувачі, що виконують ці процедури, безпосередньо не мають.
- Деякий захист від атак SQL-ін'єкціями
- Збережені процедури можуть використовуватися для захисту від атак ін'єкціями. Параметри збережених процедур розглядаються як дані, навіть якщо нападник вставляє команди SQL. Також деякі СКБД перевіряють типи параметрів. Однак, збережена процедура, що генерує динамічний SQL з використанням входу, все одно вразлива до SQL-ін'єкцій за відсутності належних запобіжних заходів.
Інші застосування
ред.В деяких системах збережені процедури можуть використовуватися для контролю за управлінням транзакціями; в інших збережені процедури запускаються всередині транзакції так, що останні ефективно їх обгортають. Збережені процедури також можуть викликатися з тригера бази даних або обробника умови. Наприклад, збережена процедура може спрацьовувати при вставці до заданої таблиці чи оновленні її поля, і виконуватиметься код всередині збереженої процедури. Також, написання збережених процедур як обробників умов дозволяє адміністраторам баз даних відстежувати помилки в системі з більшою деталізацією шляхом їх використання для відловлювання помилок і запису деякої інформації для аудиту до бази даних або зовнішнього ресурсу, наприклад, файлу.
Порівняння з функціями
ред.- Функція є підпрограмою, написаною для виконання деяких обчислень.
- Скалярна функція повертає лише одне значення (або NULL), тоді як таблична функція повертає (реляційну) таблицю, що складається з 0 чи більше рядків, які, у свою чергу, з 1 чи більше стовпчиків.
- Функції повинні повертати значення (використовуючи ключове слово
RETURN), але від збережених процедур це не вимагається. - Збережені процедури можуть використовувати ключове слово
RETURN, але без передавання жодного значення. - Функції можуть використовуватися в операторах
SELECT, за умови, що вони не маніпулюють даними. Процедури ж до операторівSELECTвключено бути не може. - Збережені процедури можуть повертати кілька значень, використовуючи параметр
OUT, або не повертати їх взагалі. - Збережена процедура заощаджує час компілювання запитів.
- Збережена процедура є об'єктом бази даних.
- Збережена процедура є матеріальним об'єктом.
Порівняння з підготовленими операторами
ред.Підготовлені оператори[en] приймають на вхід звичайний оператор або запит, і параметризують його, так, що пізніше можуть використовуватися різні значення літералів. Як і збережені процедури, вони зберігаються на сервері задля ефективності, та надають деякий захист від атак SQL-ін'єкціями. Простіші та більш декларативні, підготовлені оператори зазвичай не пишуться з використанням процедурної логіки, і не можуть оперувати змінними. Через простий інтерфейс і реалізацію на боці клієнта підготовлені оператори є ширше переносними між СКБД.
Недоліки
ред.- Мови збережених процедур часто специфічні для постачальника. Зміна постачальників баз даних зазвичай вимагає переписування наявних збережених процедур.
- Мови збережених процедур різних постачальників мають різні рівні витонченості.
- Наприклад, pgpsql у Postgres має більше мовних можливостей (особливо з розширеннями), ніж Microsoft T-SQL.[джерело?]
- Засоби підтримки написання та зневаджування збережених процедур часто не такі добрі, як для інших мов програмування, але це різниться між постачальниками та мовами.
- Наприклад, і PL/SQL, і T-SQL мають виділені ІСР та зневаджувачі. PL/PgSQL можна зневаджувати з різних ІСР.
- Зміни збережених процедур важче відстежувати в межах систем керування версіями, ніж зміни іншого коду. Зміни повинні відтворюватися як сценарії для включення до історії проекту, а різниці у процедурах може бути важче правильно зливати та відстежувати.
Примітки
ред.- ↑ Call Procedure (англійською) . IBM. Архів оригіналу за 7 травня 2020. Процитовано 24 березня 2022.
Посилання
ред.- Збережені процедури в MySQL. MySQL. Архів оригіналу за 26 лютого 2017. Процитовано 4 лютого 2017.
- Огляд підтримки процедурної мови PostgreSQL. PostgreSQL. Архів оригіналу за 5 травня 2016. Процитовано 4 лютого 2017.
- Використання збереженої процедури в Sybase ASE. Архів оригіналу за 5 лютого 2017. Процитовано 4 лютого 2017.
- Процедури PL/SQL. Стенфордський університет. Архів оригіналу за 3 грудня 2016. Процитовано 4 лютого 2017.
- Довідник мови PL/SQL баз даних Oracle. Oracle Corporation. Архів оригіналу за 28 жовтня 2011. Процитовано 4 лютого 2017.