Збережені процедури в T-SQL - створення, зміна, видалення

1. Що таке збережені процедури в T-SQL?
2. Приклади роботи з збереженими процедурами в Microsoft SQL Server
3. Створення збереженої процедури на T-SQL - інструкція CREATE PROCEDURE
4. Запуск збереженої процедури на T-SQL - команда EXECUTE
5. Зміна процедури, що на T-SQL - інструкція ALTER PROCEDURE
6. Видалення збереженої процедури на T-SQL - інструкція DROP PROCEDURE

У Microsoft SQL Server для реалізації і автоматизації своїх власних алгоритмів (розрахунків) можна використовувати збережені процедури, тому сьогодні ми з Вами поговоримо про те, як вони створюються, змінюються і видаляються.

Але спочатку трохи теорії, щоб Ви розуміли, що таке збережені процедури і для чого вони потрібні в T-SQL.

Примітка! Початківцям програмістам рекомендую наступні корисні матеріали на тему T-SQL:

Що таке збережені процедури в T-SQL?

Збережені процедури - це об'єкти бази даних, в яких закладено алгоритм у вигляді набору SQL інструкцій. Іншими словами, можна сказати, що збережені процедури - це програми всередині бази даних. Збережені процедури використовуються для збереження на сервері повторно використовуваного коду, наприклад, Ви написали якийсь алгоритм, послідовний розрахунок або багатокрокових SQL інструкцію, і щоб кожен раз не виконувати всі інструкції, що входять до даний алгоритм, Ви можете оформити його у вигляді збереженої процедури. При цьому, коли Ви створюєте процедуру SQL, сервер компілює код, а потім, при кожному запуску цієї процедури SQL сервер вже не буде повторно його компілювати.

Для того щоб запустити збережену процедуру в SQL Server, необхідно перед її назвою написати команду EXECUTE, також можливо скорочене написання даної команди EXEC. Викликати збережену процедуру в інструкції SELECT , Наприклад, як функцію вже не вийде, тобто процедури запускаються окремо.

У збережених процедурах, на відміну від функцій, вже можна виконувати операції модифікації даних такі як: UNSERT, UPDATE, DELETE. Також в процедурах можна використовувати SQL інструкції практично будь-якого типу, наприклад, CREATE TABLE для створення таблиць або EXECUTE, тобто виклик інших процедур. Виняток становить кілька типів інструкцій таких як: створення або зміна функцій, уявлень, тригерів, створення схем і ще кілька інших подібних інструкцій, наприклад, також не можна в збереженій процедурі перемикати контекст підключення до бази даних (USE).

Процедура може мати вхідні параметри і вихідні параметри, вона може повертати табличні дані, дозволяють залишати нічого, тільки виконувати закладені в ній інструкції.

Збережені процедури дуже корисні, вони допомагають нам автоматизувати або спростити багато операцій, наприклад, Вам постійно потрібно формувати різні складні аналітичні звіти з використанням зведених таблиць, тобто оператора PIVOT. Щоб спростити формування запитів з цим оператором (як Ви знаєте, у PIVOT синтаксис досить складний), Ви можете написати процедуру, яка буде Вам динамічно формувати зведені звіти, наприклад, в матеріалі « Динамічний PIVOT в T-SQL »Представлено приклад реалізації даної можливості у вигляді збереженої процедури.

Приклади роботи з збереженими процедурами в Microsoft SQL Server

Вихідні дані для прикладів

Всі приклади нижче будуть виконані в Microsoft SQL Server 2016 Express . Для того щоб продемонструвати, як працюють збережені процедури з реальними даними, нам потрібні ці дані, давайте їх створимо. Наприклад, давайте створимо тестову таблицю і додамо в неї кілька записів, припустимо, що це буде таблиця, яка містить список товарів з їх ціною.

--Інструкція створення таблиці CREATE TABLE TestTable ([ProductId] INT IDENTITY (1,1) NOT NULL, [CategoryId] INT NOT NULL, [ProductName] VARCHAR (100) NOT NULL, [Price] MONEY NULL) GO - Інструкція додавання даних INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (1, 'Миша', 100), (1, 'Клавіатура', 200), (2, 'Телефон', 400) GO --Запрос на вибірку SELECT * FROM TestTable

Дані є, тепер давайте переходити до створення збережених процедур.

Створення збереженої процедури на T-SQL - інструкція CREATE PROCEDURE

Збережені процедури створюються за допомогою інструкції CREATE PROCEDURE, після даної інструкції Ви повинні написати назву Вашої процедури, потім у разі потреби в дужках визначити вхідні і вихідні параметри. Після цього Ви пишіть ключове слово AS і відкриваєте блок інструкцій ключовим словом BEGIN, закриваєте даний блок словом END. Усередині даного блоку Ви пишіть все інструкції, які реалізують Ваш алгоритм або якийсь послідовний розрахунок, іншими словами, програмуєте на T-SQL.

Для прикладу давайте напишемо збережену процедуру, яка буде додавати новий запис, тобто новий товар в нашу тестову таблицю. Для цього ми визначимо три входять параметри: @CategoryId - ідентифікатор категорії товару, @ProductName - найменування товару та @Price - ціна товару, даний параметр буде у нас необов'язковий, тобто його можна буде не передавати в процедуру (наприклад, ми не знаємо ще ціну), для цього в його визначенні ми поставимо значення за замовчуванням. Ці параметри в тілі процедури, тобто в блоці BEGIN ... END можна використовувати, так само як і звичайні змінні (як Ви знаєте, змінні позначаються знаком @). У разі якщо Вам потрібно вказати вихідні параметри, то після назви параметра вказуйте ключове слово OUTPUT (або скорочено OUT).

У блоці BEGIN ... END ми напишемо інструкцію додавання даних, а також в завершенні процедури інструкцію SELECT, щоб збережена процедура повернула нам табличні дані про товари в зазначеній категорії з урахуванням нового, тільки що доданого товару. Також в цій збереженій процедурі я додав обробку вхідного параметра, а саме видалення зайвих пробілів на початку і в кінці текстового рядка з метою виключення ситуацій, коли випадково занесли кілька прогалин.

Ось код даної процедури (його я також прокоментував).

--Создаем процедуру CREATE PROCEDURE TestProcedure (--Входящіе параметри @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) AS BEGIN --Інструкціі, що реалізують Ваш алгоритм --Обработка входять параметрів --Удаленіе зайвих прогалин на початку і в кінці текстового рядка SET @ProductName = LTRIM (RTRIM (@ProductName)); --Добавляем новий запис INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Возвращаем дані SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Запуск збереженої процедури на T-SQL - команда EXECUTE

Запустити збережену процедуру, як я вже зазначав, можна за допомогою команди EXECUTE або EXEC. Вхідні параметри передаються в процедури шляхом простого їх перерахування і вказівки відповідних значень після назви процедури (для вихідних параметрів також потрібно вказувати команду OUTPUT). Однак назва параметрів можна і не вказувати, але в цьому випадку необхідно дотримуватися послідовність вказівки значень, тобто вказувати значення в тому порядку, в якому визначені вхідні параметри (це відноситься і до вихідних параметрів).

Параметри, які мають значення за замовчуванням, можна і не вказувати, це так звані необов'язкові параметри.

Ось кілька різних, але еквівалентних способів запуску збережених процедур, зокрема нашої тестової процедури.

--1. Викликаємо процедуру без вказівки ціни EXECUTE TestProcedure @CategoryId = 1, @ProductName = 'Тестовий товар 1' --2. Викликаємо процедуру із зазначенням ціни EXEC TestProcedure @CategoryId = 1, @ProductName = 'Тестовий товар 2', @Price = 300 --3. Викликаємо процедуру, не вказуючи назву параметрів EXEC TestProcedure 1, 'Тестовий товар 3', 400

Зміна процедури, що на T-SQL - інструкція ALTER PROCEDURE

Внести зміни в алгоритм роботи процедури можна за допомогою інструкції ALTER PROCEDURE. Іншими словами, для того щоб змінити вже існуючу процедуру, Вам достатньо замість CREATE PROCEDURE написати ALTER PROCEDURE, а все інше змінювати в разі потреби.

Припустимо, нам необхідно внести зміни в нашу тестову процедуру, скажімо, параметр @Price, тобто ціну, ми зробимо обов'язковою, для цього приберемо значення за замовчуванням, а також представимо, що у нас пропала необхідність в отриманні результуючого набору даних, для цього ми просто приберемо інструкцію SELECT з збереженої процедури.

--Ізменяем процедуру ALTER PROCEDURE TestProcedure (--Входящіе параметри @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Інструкціі, що реалізують Ваш алгоритм --Обработка входять параметрів --Удаленіе зайвих прогалин на початку і в наприкінці текстового рядка SET @ProductName = LTRIM (RTRIM (@ProductName)); --Добавляем новий запис INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Видалення збереженої процедури на T-SQL - інструкція DROP PROCEDURE

У разі необхідності можна видалити збережену процедуру, це робиться за допомогою інструкції DROP PROCEDURE.

Наприклад, давайте видалимо створену нами тестову процедуру.

DROP PROCEDURE TestProcedure

При видаленні збережених процедур варто пам'ятати про те, що, якщо на процедуру будуть посилатися інші процедури або SQL інструкції, після її видалення вони будуть завершуватися з помилкою, так як процедури, на яку вони посилаються, більше немає.

У мене все, сподіваюся, матеріал був Вам цікавий і корисний, поки!