Хранимые процедуры для чего нужны
Хранимые процедуры
Создание и выполнение процедур
То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
Создадим хранимую процедуру для извлечения данных из этой таблицы:
Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.
После имени процедуры должно идти ключевое слово AS.
Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:
И мы сможем управлять процедурой также и через визуальный интерфейс.
Выполнение процедуры
Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :
Удаление процедуры
Для удаления процедуры применяется команда DROP PROCEDURE :
Хранимые процедуры в T-SQL — создание, изменение, удаление
В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT, например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express. Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
Данные есть, теперь давайте переходить к созданию хранимых процедур.
Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.
Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).
В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.
Вот код данной процедуры (его я также прокомментировал).
Запуск хранимой процедуры на T-SQL – команда EXECUTE
Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам).
Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.
Вот несколько разных, но эквивалентных способов запуска хранимых процедур, в частности нашей тестовой процедуры.
Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE. Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.
Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.
Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.
Например, давайте удалим созданную нами тестовую процедуру.
При удалении хранимых процедур стоит помнить о том, что, если на процедуру будут ссылаться другие процедуры или SQL инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
Хранимые процедуры: описание, лимиты и примеры
В этой статье я расскажу о хранимых процедурах. Как их использовать и зачем, лимиты, примеры крутых процедуры которые я использую.
Хранимые процедуры позволяют исполнять код на стороне сервера API аналогично методу execute, но без передачи кода процедуры по сети.
Хранимая процедура — это заданный алгоритм, позволяющий Вам реализовать быстрый вызов сразу нескольких методов API, аналогично принципу работы метода execute, но без передачи кода по сети (в запросе необходимо указывать только имя процедуры и необходимые параметры).
Создавать новые хранимые процедуры Вы можете на соответствующей вкладке раздела редактирования Вашего приложения.
Код для хранимых процедур пишется на VKScript языке.
VKScript для метода execute получается не всегда маленький, и передавать его в запросе очень жирно. Так же для хранимых процедур реализован удобный клиент прямо в Вконтакте. Дергать процедуру по ее названию, в этом случае передавать code уже не нужно. Не используя хранимые процедуры при отправке запросов с клиента, ваш код отправляемый к API, можно посмотреть.
Отправляя код с браузера к API, его может посмотреть любой человек. При отправке только названия хранимой процедуры и параметров, код будет не доступен.
Назначение хранимых процедур в языке T-SQL (Microsoft SQL Server)
Приветствую Вас на сайте Info-Comp.ru! Из данного материала Вы узнаете, для чего нужны хранимые процедуры в языке T-SQL, мы рассмотрим основные возможности хранимых процедур и преимущества их использования.
Хранимые процедуры в T-SQL
Хранимая процедура – это объект базы данных, который хранит набор инструкций языка T-SQL, реализующих определённый алгоритм.
Хранимые процедуры используются в языке T-SQL для реализации бизнес логики, каких-то алгоритмов, расчетов, а также для написания различных инструкций администрирования баз данных и сервера, которые периодически необходимо выполнять.
Иными словами, хранимые процедуры – это своего рода программы внутри базы данных, которые хранят реализованный нами алгоритм, и в случае запуска этих программ выполняют этот алгоритм.
Примечание! В данном материале речь идет о пользовательских хранимых процедурах, которые создаем мы с Вами, т.е. программисты T-SQL.
Назначение хранимых процедур в T-SQL
Чтобы ответить на вопрос: для чего нужны хранимые процедуры? и определить их назначение, необходимо рассмотреть возможности, которые нам предоставляют хранимые процедуры, и те преимущества, которые мы получим, если будем их использовать. Именно это и определяет назначение хранимых процедур, т.е. для чего они созданы.
Возможности хранимых процедур
Преимущества хранимых процедур
Повышение безопасности
Использование хранимых процедур повышает безопасность по нескольким причинам:
Устранение дублирования кода
SQL код, включающий многошаговые операции с базой данных, который многократно используется в приложении в разных местах, можно вынести в хранимую процедуру, тем самым устранить необходимость копирования одного и того же кода. Как результат, у Вас уменьшится общий объем кода за счет отсутствия повторяющихся участков кода.
Легкое сопровождение кода
Когда весь SQL код и заложенный в нем алгоритм вынесен в хранимые процедуры, а клиентское приложение лишь осуществляет вызов этих процедур, то сопровождать такое приложение становится гораздо легче. Ведь в случае возникновения необходимости внести изменение в алгоритм работы с данными, который заложен в хранимой процедуре, достаточно один раз внести изменение в базу данных в код хранимой процедуры. Иными словами, нет необходимости вносить изменения в само клиентское приложение, перекомпилировать и осуществлять обновление.
Кроме этого вынесение всего SQL кода в хранимые процедуры снизит уровень несогласованности кода клиентского приложения, и таких ситуаций возникать не будет, когда в одном месте SQL код выполняет одни изменения в базе данных, а вроде бы тот же самый SQL код, но в другом месте, выполняет совершенно другие изменения в базе данных. С хранимыми процедурами Вы будете вносить изменения только в одном месте – в исходном коде хранимой процедуры.
Повышение производительности
Microsoft SQL Server компилирует хранимую процедуру и создает план выполнения один раз, а затем повторно использует этот план выполнения. Это приводит к повышению производительности в случае многократного вызова хранимой процедуры, ведь теперь обработчику запросов не нужно создавать новый план и поэтому обработка процедуры занимает меньше времени.
Примечание! Если в таблицах и в данных, с которыми работает хранимая процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана наоборот может вызвать замедление работы процедуры. В этом случае необходимо принудительно перекомпилировать процедуру, и тем самым создать новый план выполнения.
Сокрытие сложности
В разработке приложения нередко участвуют достаточно много разработчиков, а если говорить о крупных информационных системах, то разработчики могут исчисляться и сотнями. При этом одни программисты разрабатывают интерфейс клиентского приложения, а другие разрабатывают базу данных и всю логику работы с ней.
И в таких ситуациях разработчикам, которые работают с интерфейсом клиентского приложения, совсем необязательно знать алгоритм выполнения определённых действий в базе данных, который заложен в SQL инструкциях. Более того, они не должны его знать.
Хранимые процедуры как раз и скрывают алгоритм действий в базе данных и всю его сложность. Иными словами, если, например, разработчику клиентского приложения потребовалось использовать функционал добавления нового пользователя этого приложения, то он должен знать только название хранимой процедуры и описание ее параметров, а что конкретно она там делает и как именно создает пользователя, он знать не должен.
Использование хранимых процедур T-SQL является преимуществом и для разработчиков интерфейса приложения, так как в этом случае им просто легче программировать, им не нужно писать запутанные SQL инструкции, они могут просто вызывать хранимые процедуры, более того, так у них формируется полноценная абстракция их классов, в случае с ООП.
Снижение сетевого трафика между клиентами и сервером
Использование хранимых процедур позволяет существенно сократить сетевой трафик между сервером и клиентом, так как по сети отправляется только вызов на выполнение хранимой процедуры. Иными словами, без инкапсуляции кода, предоставляемой процедурой, по сети нам бы пришлось пересылать все строки кода, которых может быть достаточно много.
Подведение итогов
Давайте подведем итог на основе всего вышесказанного и сформулируем итоговое назначение хранимых процедур в языке T-SQL.
Хранимые процедуры (компонент Database Engine)
обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;
содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур;
возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).
Преимущества хранимых процедур
В следующем списке описываются преимущества использования процедур.
Снижение сетевого трафика между клиентами и сервером
Команды в процедуре выполняются как один пакет кода. Это позволяет существенно сократить сетевой трафик между сервером и клиентом, поскольку по сети отправляется только вызов на выполнение процедуры. Без инкапсуляции кода, предоставляемой процедурой, по сети бы пришлось пересылать все отдельные строки кода.
Большая безопасность
Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.
Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.
При вызове процедуры через сеть виден только вызов на выполнение процедуры. Следовательно, злоумышленники не смогут видеть имена объектов таблиц и баз данных, внедрять свои инструкции Transact-SQL или выполнять поиск важных данных.
Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». Поскольку входные данные параметра обрабатываются как литеральные значения, а не как исполняемый код, злоумышленнику будет труднее вставить команду в инструкции Transact-SQL в процедуре и создать угрозу безопасности.
Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код. Дополнительные сведения см. в статье SQL Server Encryption.
Повторное использование кода
Если какой-то код многократно используется в операции базы данных, то отличным решением будет произвести его инкапсуляцию в процедуры. Это устранит необходимость излишнего копирования того же кода, снизит уровень несогласованности кода и позволит осуществлять доступ к коду любым пользователям или приложениям, имеющим необходимые разрешения.
Более легкое обслуживание
Если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры. Уровень приложения остается незатронутым изменениями в схемах баз данных, связях или процессах.
повышение производительности.
По умолчанию компиляция процедуры и создание плана выполнения, используемого для последующих выполнений, производится при ее первом запуске Поскольку обработчику запросов не нужно создавать новый план, обычно обработка процедуры занимает меньше времени.
Если в таблицах или данных, на которые ссылается процедура, произошли значительные изменения, то наличие предварительно скомпилированного плана может вызвать замедление работы процедуры. В этом случае перекомпиляция процедуры и принудительное создание нового плана выполнения может улучшить производительность.
Типы хранимых процедур
Временные процедуры
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.
SQL Server поддерживает системные процедуры, обеспечивающие интерфейс между SQL Server и внешними программами для выполнения различных действий по обслуживанию системы. Эти расширенные процедуры имеют префикс xp_. Полный список расширенных хранимых процедур см. в статье Основные расширенные хранимые процедуры (Transact-SQL).
Расширенные хранимые процедуры в будущих версиях SQL Serverбудут удалены. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Вместо них рекомендуется создавать процедуры CLR. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.