Хранимые процедуры sql что это
Хранимые процедуры
Создание и выполнение процедур
То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
Создадим хранимую процедуру для извлечения данных из этой таблицы:
Поскольку команда 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 инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
Хранимые процедуры (компонент 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. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.
Т.к. подобный запрос мы будем использовать каждый раз, когда нам необходимо будет добавить нового покупателя, то вполне уместно оформить его в виде процедуры:
Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на «//», чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :
Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:
Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):
Как было сказано в начале урока, процедуры позволяют объединить последовательность запросов. Давайте посмотрим, как это делается. Помните в уроке 11 мы хотели узнать, на какую сумму нам привез товар поставщик «Дом печати»? Для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. А если мы захотим узнать, на какую сумму нам привез товар другой поставщик? Придется составлять новые запросы, объединения и т.д. Проще один раз написать хранимую процедуру для этого действия.
Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:
А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:
Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:
Проверим работу процедуры, с разными входными параметрами:
Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.
Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу: 
Как видите, сложные запросы или их последовательность действительно проще один раз оформить в хранимую процедуру, а дальше просто обращаться к ней, указывая необходимые параметры. Это значительно сокращает код и делает работу с запросами более логичной.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Изучаем хранимые процедуры MySQL
Существует два вида подпрограмм: хранимые процедуры и функции, возвращающие значения, которые используются в других операторах SQL ( например, pi() ).
Хранимые процедуры MySQL-основные преимущества
Создание процедуры в MySQL
Проверка версии MySQL
Следующая команда выводит версию MySQL :
Проверка привилегий текущего пользователя
Выбор базы данных
Теперь выберите базу данных « hr » и выведите список таблиц:
Выбор разделителя
Теперь выполните следующую команду, чтобы снова установить « ; » в качестве разделителя:
Пример процедуры в MySQL
Инструменты для создания процедур в MySQL
Инструмент командной строки MySQL
Выберите из меню « Пуск » « Клиент командной строки MySQL »:
Вы увидите на экране следующее окно:
После авторизации можно будет получить доступ к командной строке MySQL :
Теперь можно писать и запускать собственные процедуры, смотрите следующий пример:
MySQL Workbench (5.3 CE)
Выберите в меню « Пуск » « MySQL Workbench »:
После этого вы увидите на экране следующее окно:
Введите свои учетные данные:
После авторизации на экране появится новое окно, и с помощью панели просмотра объектов можно будет выбрать базу данных:
После этого кликните правой кнопкой мыши по пункту « Routines » и на экране появится новое всплывающее окно:
После этого на экране появится следующее окно, в котором можно создать собственную процедуру MySQL :
В этом окне можно просмотреть скрипт и применить его в базе данных:
Теперь нажмите на кнопку « Finish » и запустите процедуру:
Вызов процедуры в MySQL
Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Синтаксис следующий:
Давайте выполним процедуру:
SHOW CREATE PROCEDURE
Давайте осуществим MySQL вызов хранимой процедуры:
MySQL: блоки характеристик
В синтаксисе оператора CREATE PROCEDURE допустимо использование блоков, которые описывают характеристики процедуры. Блоки указываются после скобок, но перед телом процедуры. Эти блоки являются необязательными.
COMMENT
LANGUAGE
NOT DETERMINISTIC
Это информационная характеристика. Процедура считается « детерминированной «, если она всегда дает тот же результат для одних и тех же входных параметров, иначе она является « не детерминированной «.
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
READS SQL DATA — процедура содержит операторы, которые считывают данные ( например, SELECT ), но не содержит операторов, которые записывают данные.
Все перечисленные блоки характеристик имеют значения по умолчанию. Следующие два оператора дают одинаковый результат:
MySQL: составные операторы
Составной оператор представляет собой блок, который может содержать другие блоки: объявления переменных, обработчиков состояний и курсоров, конструкции управления потоками данных, циклы и условные тесты. В версии MySQL 5.6 существуют следующие составные операторы:
Синтаксис составного оператора BEGIN … END
список_операторов: один или несколько операторов, завершающихся точкой с запятой ( ; ). Сам по себе список операторов не является обязательным, поэтому пустой оператор BEGIN END является действительным.
Метки операторов
При применении меток применяются следующие правила:
Оператор DECLARE
Используется для определения различных локальных элементов при MySQL создании хранимой процедуры. Например, локальных переменных, условий, обработчиков, курсоров. DECLARE используется только внутри составного оператора BEGIN … END и должен находиться в его начале перед всеми остальными операторами.
Для объявлений существуют следующие правила:
Переменные в хранимых программах
Хранимые программы используют оператор DECLARE для определения локальных переменных. Процедуры и функции могут при объявлении принимать параметры, которые обмениваются значениями между подпрограммой и вызывающим ее агентом.
Пример: Локальные переменные
Теперь выполните процедуру:
Пример: пользовательские переменные
В хранимых процедурах MySQL обращение к пользовательским переменным происходит через символ амперсанда (@) перед именем пользовательской переменной ( например, @x и @y ). В следующем примере показано использование пользовательских переменных внутри хранимой процедуры:
MySQL: параметры процедуры
Ниже приводится синтаксис CREATE PROCEDURE для параметров:
Процедура MySQL: пример параметра IN
Чтобы выбрать первые две строки из таблицы « jobs » выполните следующую команду:
Теперь выберите первые пять строк из таблицы « jobs «:
Процедура MySQL: пример параметра OUT
Процедура MySQL: Пример параметра INOUT
Теперь проверяем количество сотрудников мужского и женского пола в указанной таблице:
MySQL: Операторы управления потоком
MySQL: Оператор IF
Осуществите MySQL вызов хранимой процедуры:
MySQL: Оператор CASE
Пояснение: первый синтаксис
Пояснение: второй синтаксис
У нас есть таблица под названием ‘ jobs ‘ со следующими записями:
Подсчитаем количество сотрудников, удовлетворяющих следующим условиям:
Для этого мы используем следующую процедуру ( MySQL хранимой процедуры пример создан в MySQL Workbench 5.2 CE ):
Количество сотрудников, чья зарплата превышает 10000:
Количество сотрудников, чья зарплата меньше, чем 10000:
Количество сотрудников, чья зарплата равна 10000:
MySQL: оператор ITERATE
MySQL: оператор LEAVE
MySQL: оператор LOOP
Используется, чтобы задать повторное выполнение списка операторов. Синтаксис следующий:
В приведенной ниже процедуре строки вставляются в таблицу ‘ number ‘ до тех пор, пока х меньше, чем num ( число заданное пользователем через параметр IN ). Каждый раз сохраняется случайное число:
Теперь выполните MySQL хранимую процедуру:
MySQL: оператор REPEAT
REPEAT исполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец оператора:
список_операторов — список из одного или нескольких операторов, каждый из которых разделяется точкой с запятой ( ; ).
условие_поиска — выражение.
Оператор REPEAT может иметь метки.
Четные числа — это числа, которые могут быть разделены на 2 без остатка. В следующей процедуре пользователь задает число через параметр IN и получает сумму четных чисел от 1 до установленного числа:
Теперь выполните хранимую процедуру MySQL :
MySQL: оператор RETURN
MySQL: оператор WHILE
Оператор WHILE выполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец цикла. Каждый оператор заканчивается точкой с запятой ( ; ). Синтаксис следующий:
Оператор WHILE может иметь метки.
Нечетные числа — это числа, которые не делятся на 2 без остатка. В следующей процедуре пользователь передает через параметр IN число и получает сумму нечетных чисел от 1 до заданного числа:
Теперь выполните MySQL хранимую процедуру:
MySQL: ALTER PROCEDURE
MySQL: DROP PROCEDURE
MySQL: курсоры
Курсор представляет собой структуру управления, которая позволяет обрабатывать записи в базе данных. Курсоры используются для обработки отдельных строк, возвращаемых в ответ на запросы системой базы данных. Курсор включает строки в набор результатов, чтобы последовательно их обработать.
В процедурах SQL курсор позволяет определить результирующий набор ( набор строк данных ) и выполнить сложную логику построчно. Используя те же механизмы, процедура SQL также может определить набор результатов и вернуть его непосредственно вызывающему агенту или в клиентское приложение.
Объявление курсора
Открытие курсора
После объявления мы открываем объявленный курсор:
Выборка данных в переменные
Закрытие курсора
Этот оператор закрывает ранее открытый курсор. Если курсор не открыт, возникает ошибка:
Хранимая процедура MySQL начинается с объявления трех переменных. При этом порядок имеет значение. Первыми объявляются переменные. После этого объявляются условия, затем – курсоры и обработчики. Если вы поместите их в неправильном порядке, то получите сообщение об ошибке:
Теперь выполните процедуру:
Управление доступом для встроенных программ
MySQL использует следующие правила для управления атрибутом объекта DEFINER :
Пожалуйста, оставляйте ваши отзывы по текущей теме статьи. Мы крайне благодарны вам за ваши комментарии, подписки, лайки, отклики, дизлайки!
Дайте знать, что вы думаете по этой теме в комментариях. Мы очень благодарим вас за ваши комментарии, лайки, подписки, отклики, дизлайки!