Хранимые процедуры в SQL | Обзор

В этом блоге мы собираемся обсудить хранимые процедуры в SQL, которые вы можете использовать для сохранения набора кода и повторного его использования, когда вам это нужно. Хранимые процедуры аналогичны представлениям. Однако вы можете выполнять обычные табличные операции , такие как DROP, TRUNCATE, DELETE и т. д., с хранимыми процедурами, которые вы не можете выполнять с представлениями.

Хранимые процедуры также предварительно скомпилированы, поэтому они работают быстрее, чем представления. Это также сводит к минимуму количество данных, отправляемых на сервер базы данных.

Оглавление

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

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

Чтобы создать хранимую процедуру без параметров, нужно начать с функции CREATE . Затем добавьте функцию PROC , сразу введите имя процедуры и добавьте функцию AS .

Создание хранимой процедуры в похоже на создание таблиц и представлений. Однако разница заключается в процессе получения данных.

Например, если мы хотим получить данные из представления, мы используем « SELECT * FROM имя_представления ». С другой стороны, для хранимых процедур мы используем 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

Если вы хотите увидеть, где сохраняется хранимая процедура в SQL , перейдите на панель обозревателя объектов с левой стороны и щелкните значок « + » перед базой данных, с которой вы работаете.

Затем щелкните правой кнопкой мыши «Программируемость» и выберите «Обновить» .

Разверните папку или группу «Программируемость» , щелкнув значок « + ». Затем разверните группу «Хранимые процедуры», выполнив тот же шаг. Внутри группы «Хранимые процедуры» вы должны увидеть 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 годом .

Именно так вы используете хранимые процедуры в повседневных задачах управления данными.

Предложение HAVING в агрегатных функциях SQL
ISNULL Функция SQL в операторах case
Извлечение данных SQL с использованием OFFSET и FETCH

Заключение

Приняв все во внимание, вы узнали, что такое хранимая процедура в SQL и ее назначение. Мы также обсудили инструкцию ALTER , которая используется для внесения изменений или обновлений в существующую хранимую процедуру.

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

Самое главное, вы научились использовать хранимые процедуры для хранения наборов команд, чтобы избежать повторного выполнения длинных наборов кода. И последнее напоминание: не забывайте использовать символ « @ » при указании параметра.

Всего наилучшего,

Хафиз