Збережені процедури в SQL | Огляд

У цьому блозі ми збираємося обговорити збережені процедури в SQL, які можна використовувати для збереження набору коду та повторного його використання, коли вам це потрібно. Збережені процедури схожі на представлення. Однак ви можете виконувати такі дії, як DROP, TRUNCATE, DELETE тощо за допомогою збережених процедур, які ви не можете виконати з представленнями.

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

Зміст

• Синтаксис для збережених процедур у SQL: без параметрів
• Синтаксис для збережених процедур у SQL: із параметрами
• Приклад сценарію збережених процедур у SSMS
• Другий зразок сценарію
• Третій зразок сценарію
• Розташування збережених процедур у SQL
• Створення збереженої процедури в SQL із параметрами
• Створення збереженої процедури зі значенням за замовчуванням
• Висновок

Синтаксис для збережених процедур у SQL: без параметрів

Щоб створити збережену процедуру без параметрів, потрібно почати з функції CREATE . Потім додайте функцію PROC , введіть назву процедури відразу після неї та додайте функцію AS .

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

Наприклад, якщо ми хочемо отримати дані з представлення, ми використовуємо « SELECT * FROM view_name ». З іншого боку, для збережених процедур ми використовуємо EXEC, що означає «виконати», а потім вводимо назву збереженої процедури .

Коли ми виконаємо збережену процедуру , вона також виведе інформацію на основі доданих вами інструкцій або команд.

Синтаксис для збережених процедур у SQL: із параметрами

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

Ви також можете використовувати параметр, який ви оголосили під час створення збереженої процедури у своїх командах. Це допоможе вам заощадити час на введення кількох значень. Наприклад, замість того, щоб вручну вводити customerID1 , customerID2 тощо, ми використали @custid або параметр, щоб уникнути довгого набору коду.

Зверніть увагу, що під час використання або включення параметра важливо використовувати символ @ . Ви також можете включити скільки завгодно параметрів, а не один. Ось як ми виконуємо збережені процедури з параметрами.

Як ви можете помітити, ми також використовували параметр зі значенням, рівним 1, при виконанні збереженої процедури. Це приведе лише інформацію про custid1 .

Приклад сценарію збережених процедур у SSMS

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

Для іменування під час створення збереженої процедури ми зазвичай використовуємо «usp» або «sp», щоб вказати, що це збережена процедура. Якщо вам цікаво, що означає «usp», це просто означає визначену користувачем збережену процедуру. 

Далі ми збираємося написати запити для обробки збереженої процедури під час її виконання.

Запити в попередньому прикладі просто видалять таблицю з назвою dbo.stageOrders . Потім він відтворить dbo.stageOrders з даними з таблиці Sales.SalesOrderHeader . 

Давайте створимо цю збережену процедуру , виділивши наступний код і натиснувши кнопку Виконати .

Після цього ви повинні побачити таке повідомлення.

Потім ми виконаємо збережену процедуру, запустивши EXEC usp_TEST .

Після виконання збереженої процедури usp_TEST dbo.stageOrders тепер має мати дані з Sales.SalesOrderHeader . Давайте перевіримо вміст dbo.stageOrders , виконавши наведену нижче команду.

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

Другий зразок сценарію

Переходимо до іншого прикладу. Цього разу я не хочу видаляти таблицю dbo.stageOrders і повторно створювати ту саму таблицю з даними, що надходять із таблиці Sales.SalesOrderHeader .

Натомість я просто хочу, щоб дані з таблиці Sales.SalesOrderHeader відображалися щоразу, коли я виконую збережену процедуру “ usp_TEST ” .

Щоб виконати це, мені потрібно внести зміни в нещодавно створену збережену процедуру за допомогою оператора ALTER .

У цьому прикладі ми використали оператор ALTER , який використовується для зміни нашої збереженої процедури. Ви також можете застосувати це до представлень і таблиць. Коли ми запустимо код на знімку екрана вище, він має змінити запити, які оброблятиме наша збережена процедура.

Цього разу таблиця не створюватиметься заново, як це було раніше. Якщо ми виконаємо “ usp_TEST ”, він просто перенесе дані з таблиці Sales.SalesOrderHeader .

Третій зразок сценарію

Давайте зробимо інший приклад. Цього разу ми повторно використаємо таблицю dbo.stageOrders, яку ми мали в нашому першому прикладі створення збереженої процедури в SSMS . Ми почнемо з цього прикладу, вибравши таблицю dbo.stageOrders. 

Результати після вибору всіх даних з dbo.stageOrders мають бути такими.

Тепер давайте очистимо таблицю dbo.stageOrders, виконавши usp_TEST . Ми можемо зробити це, додавши таку команду.

Після додавання команди TRUNCATE TABLE нам потрібно оновити usp_TEST .

Після цього має відобразитися повідомлення про те, що команди виконано успішно . Потім ми знову виконаємо usp_TEST .

Після виконання usp_TEST він покаже всі записи з Sales.SalesOrderHeader , а також очистить таблицю dbo.stageOrders . 

Щоб перевірити, чи таблиця dbo.stageOrders порожня, нам потрібно вибрати її за допомогою наступної команди та виконати.

Після запуску коду вище ми бачимо, що таблиця dbo.stageOrders тепер порожня. Це пов’язано з командою TRUNCATE TABLE , яку ми використовували для оновлення usp_TEST .

Розташування збережених процедур у SQL

Якщо ви хочете побачити, де зберігається збережена процедура, перейдіть на панель Object Explorer ліворуч і клацніть значок « + » перед базою даних, над якою ви працюєте.

Потім клацніть правою кнопкою миші « Програмованість» і виберіть «Оновити» .

Розгорніть папку або групу «Програмування» , клацнувши піктограму « + ». Потім розгорніть групу збережених процедур, виконавши той самий крок. Усередині групи збережених процедур ви повинні побачити dbo.usp_TEST .

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

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

Як бачите, перед оператором CREATE є кілька команд за замовчуванням . Ви можете просто видалити це, якщо хочете. 

Тепер ви знаєте, як перевірити, які команди виконує збережена процедура в SQL .

Створення збереженої процедури в SQL із параметрами

Далі ми створимо збережену процедуру з параметрами. Наприклад, ми збираємося використовувати наступний код для створення нової збереженої процедури.

У прикладі коду я використав той самий процес створення збереженої процедури під назвою usp_GetCustomer . Потім я додав параметр @CustomerID із типом введення INT .

Зауважте, що після того, як ви додали параметр під час створення збереженої процедури, вам слід завжди вказувати параметр, коли ви збираєтеся виконати команду.

Давайте подивимося, що станеться, якщо ми виконаємо usp_GetCustomer без надання параметра.

Після виконання usp_GetCustomer без параметра з’явилося повідомлення про помилку. Ось як це буде виглядати, якщо ми виконаємо usp_GetCustomer з параметром.

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

Створення збереженої процедури зі значенням за замовчуванням

Якщо ви хочете уникнути отримання повідомлення про помилку під час виконання збереженої процедури з параметром, ви можете встановити значення за замовчуванням, яке буде служити параметром за замовчуванням.

Наприклад, ми збираємося створити збережену процедуру під назвою usp_GetOrdersByYear .

Потім я додам параметр @OrderYear із типом введення « INT » і значенням за замовчуванням, яке дорівнює 2011 .

Якщо ми виконаємо usp_GetOrdersByYear без наданого параметра, відобразяться записи з 2011 роком .

З іншого боку, якщо ми виконуємо usp_GetOrdersByYear із заданим параметром 2014 , він має відображати записи з 2014 роком .

Ось як ви використовуєте збережені процедури у щоденних завданнях керування даними.

Висновок

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

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

Найважливіше те, що ви навчилися використовувати збережені процедури для зберігання наборів команд, щоб уникнути багаторазового запуску довгих наборів коду. Як останнє нагадування, не забудьте використовувати символ « @ » під час надання параметра.

Все найкраще,

Хафіз