Хранимые процедуры
Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (stored procedure).
То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
И еще один важный аспект — производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.
Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC .
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );
Создадим хранимую процедуру для извлечения данных из этой таблицы:
USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products
Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.
После имени процедуры должно идти ключевое слово AS.
Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:
USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;
После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures :
И мы сможем управлять процедурой также и через визуальный интерфейс.
Выполнение процедуры
Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :
EXEC ProductSummary
Удаление процедуры
Для удаления процедуры применяется команда DROP PROCEDURE :
DROP PROCEDURE ProductSummary
Хранимые процедуры в SQL
Хранимая процедура в SQL представляет собой набор команд, которые выполняют определенные действия. Хранимые процедуры похожи на функции в программировании и создаются для того, чтобы можно было повторно использовать набор команд. Они могут принимать параметры и выполнять операции, когда мы их вызываем.
Создание хранимой процедуры
Хранимая процедура создается с помощью оператора CREATE PROCEDURE, за которым следует необходимый набор SQL-команд. Например:
SQL Server
SQL Хранимая процедура для SQL Server
Хранимая процедура — это подготовленный SQL-код, который можно сохранить, чтобы этот код можно было повторно использовать снова и снова.
Поэтому, если у вас есть SQL-запрос, который вы пишете снова и снова, сохраните его как хранимую процедуру, а затем просто вызовите ее для выполнения.
Вы также можете передать параметры хранимой процедуре, чтобы она могла действовать на основе передаваемых значений параметров.
Синтаксис хранимой процедуры
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Выполнение хранимой процедуры
EXEC procedure_name;
Демо база данных
Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:
Пример хранимой процедуры
Следующая инструкция SQL создает хранимую процедуру с именем «SelectAllCustomers» , которая выбирает все записи из таблицы «Customers»:
Пример
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
Выполните описанную выше хранимую процедуру следующим образом:
Пример
EXEC SelectAllCustomers;
Хранимая процедура с одним параметром
Следующая инструкция SQL создает хранимую процедуру, которая выбирает клиентов из определенного города из таблицы «Customers»:
Пример
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
Выполните описанную выше хранимую процедуру следующим образом:
Пример
EXEC SelectAllCustomers @City = «London»;
Хранимая процедура с несколькими параметрами
Настройка нескольких параметров очень проста. Просто перечислите каждый параметр и тип данных, разделенные запятой, как показано ниже.
Следующая инструкция SQL создает хранимую процедуру, которая выбирает клиентов из определенного города с определенным почтовым кодом из таблицы «Customers»:
Пример
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
Выполните описанную выше хранимую процедуру следующим образом:
Пример
EXEC SelectAllCustomers @City = «London», @PostalCode = «WA1 1DP»;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.
Назначение хранимых процедур в языке T-SQL (Microsoft SQL Server)
Приветствую Вас на сайте Info-Comp.ru! Из данного материала Вы узнаете, для чего нужны хранимые процедуры в языке T-SQL, мы рассмотрим основные возможности хранимых процедур и преимущества их использования.
Хранимые процедуры в T-SQL
Хранимая процедура – это объект базы данных, который хранит набор инструкций языка T-SQL, реализующих определённый алгоритм.
Хранимые процедуры используются в языке T-SQL для реализации бизнес логики, каких-то алгоритмов, расчетов, а также для написания различных инструкций администрирования баз данных и сервера, которые периодически необходимо выполнять.
Иными словами, хранимые процедуры – это своего рода программы внутри базы данных, которые хранят реализованный нами алгоритм, и в случае запуска этих программ выполняют этот алгоритм.
Примечание! В данном материале речь идет о пользовательских хранимых процедурах, которые создаем мы с Вами, т.е. программисты T-SQL.
Назначение хранимых процедур в T-SQL
Чтобы ответить на вопрос: для чего нужны хранимые процедуры? и определить их назначение, необходимо рассмотреть возможности, которые нам предоставляют хранимые процедуры, и те преимущества, которые мы получим, если будем их использовать. Именно это и определяет назначение хранимых процедур, т.е. для чего они созданы.
Возможности хранимых процедур
Хранимые процедуры позволяют обрабатывать входные параметры и возвращать выходные параметры
Хранимые процедуры могут включать как одну, так и несколько инструкций языка T-SQL
Хранимые процедуры возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении
В хранимых процедурах можно вызывать другие хранимые процедуры и использовать функции
В хранимых процедурах можно использовать обработку ошибок (TRY CATCH, RAISERROR)
В хранимых процедурах можно использовать транзакции
В хранимых процедурах можно использовать табличные переменные и временные таблицы
В хранимых процедурах можно использовать динамический SQL код
Хранимые процедуры могут сформировать и вернуть несколько результирующих наборов данных
Преимущества хранимых процедур
Повышение безопасности
Использование хранимых процедур повышает безопасность по нескольким причинам:
- Предоставляя пользователю доступ к хранимой процедуре, мы можем не предоставлять прямой доступ к объектам, над которыми будут выполняться операции в процедуре. Ведь процедура сама проверяет, какие из операций могут выполняться, и защищает объекты базы данных;
- При удаленном вызове хранимой процедуры доступна только внешняя информация о вызове (название хранимой процедуры и название параметров). Имена таблиц и других объектов базы данных не видны, тем самым происходит сокрытие информации и мы скрываем детали реализации. Таким образом, пользователи хранимой процедуры или злоумышленники не смогут внести неправомерные изменения в алгоритм, выполняемый хранимой процедурой;
- Использование параметров в хранимых процедурах помогает предотвратить атаки типа «SQL-инъекция». Так как входные параметры обрабатываются как литеральные значения, а не как исполняемый код, то злоумышленнику становится труднее вставить и выполнить вредоносную инструкцию.
Устранение дублирования кода
SQL код, включающий многошаговые операции с базой данных, который многократно используется в приложении в разных местах, можно вынести в хранимую процедуру, тем самым устранить необходимость копирования одного и того же кода. Как результат, у Вас уменьшится общий объем кода за счет отсутствия повторяющихся участков кода.
Легкое сопровождение кода
Когда весь SQL код и заложенный в нем алгоритм вынесен в хранимые процедуры, а клиентское приложение лишь осуществляет вызов этих процедур, то сопровождать такое приложение становится гораздо легче. Ведь в случае возникновения необходимости внести изменение в алгоритм работы с данными, который заложен в хранимой процедуре, достаточно один раз внести изменение в базу данных в код хранимой процедуры. Иными словами, нет необходимости вносить изменения в само клиентское приложение, перекомпилировать и осуществлять обновление.
Кроме этого вынесение всего SQL кода в хранимые процедуры снизит уровень несогласованности кода клиентского приложения, и таких ситуаций возникать не будет, когда в одном месте SQL код выполняет одни изменения в базе данных, а вроде бы тот же самый SQL код, но в другом месте, выполняет совершенно другие изменения в базе данных. С хранимыми процедурами Вы будете вносить изменения только в одном месте – в исходном коде хранимой процедуры.
Повышение производительности
Microsoft SQL Server компилирует хранимую процедуру и создает план выполнения один раз, а затем повторно использует этот план выполнения. Это приводит к повышению производительности в случае многократного вызова хранимой процедуры, ведь теперь обработчику запросов не нужно создавать новый план и поэтому обработка процедуры занимает меньше времени.
Примечание! Если в таблицах и в данных, с которыми работает хранимая процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана наоборот может вызвать замедление работы процедуры. В этом случае необходимо принудительно перекомпилировать процедуру, и тем самым создать новый план выполнения.
Сокрытие сложности
В разработке приложения нередко участвуют достаточно много разработчиков, а если говорить о крупных информационных системах, то разработчики могут исчисляться и сотнями. При этом одни программисты разрабатывают интерфейс клиентского приложения, а другие разрабатывают базу данных и всю логику работы с ней.
И в таких ситуациях разработчикам, которые работают с интерфейсом клиентского приложения, совсем необязательно знать алгоритм выполнения определённых действий в базе данных, который заложен в SQL инструкциях. Более того, они не должны его знать.
Хранимые процедуры как раз и скрывают алгоритм действий в базе данных и всю его сложность. Иными словами, если, например, разработчику клиентского приложения потребовалось использовать функционал добавления нового пользователя этого приложения, то он должен знать только название хранимой процедуры и описание ее параметров, а что конкретно она там делает и как именно создает пользователя, он знать не должен.
Использование хранимых процедур T-SQL является преимуществом и для разработчиков интерфейса приложения, так как в этом случае им просто легче программировать, им не нужно писать запутанные SQL инструкции, они могут просто вызывать хранимые процедуры, более того, так у них формируется полноценная абстракция их классов, в случае с ООП.
Снижение сетевого трафика между клиентами и сервером
Использование хранимых процедур позволяет существенно сократить сетевой трафик между сервером и клиентом, так как по сети отправляется только вызов на выполнение хранимой процедуры. Иными словами, без инкапсуляции кода, предоставляемой процедурой, по сети нам бы пришлось пересылать все строки кода, которых может быть достаточно много.
Подведение итогов
Давайте подведем итог на основе всего вышесказанного и сформулируем итоговое назначение хранимых процедур в языке T-SQL.