Что такое хранимая процедура sql
Хранимые процедуры
Создание и выполнение процедур
То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
Создадим хранимую процедуру для извлечения данных из этой таблицы:
Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.
После имени процедуры должно идти ключевое слово AS.
Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:
И мы сможем управлять процедурой также и через визуальный интерфейс.
Выполнение процедуры
Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :
Удаление процедуры
Для удаления процедуры применяется команда DROP PROCEDURE :
Хранимые процедуры (компонент 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 :
Пожалуйста, оставляйте ваши отзывы по текущей теме статьи. Мы крайне благодарны вам за ваши комментарии, подписки, лайки, отклики, дизлайки!
Дайте знать, что вы думаете по этой теме в комментариях. Мы очень благодарим вас за ваши комментарии, лайки, подписки, отклики, дизлайки!
Хранимые процедуры в SQL
Доброго времени суток, уважаемые читатели. Продолжаем изучать основные элементы языка SQL и в этой статье разберемся с хранимыми процедурами. Напомню, что мы работаем в СУБД MySQL, если вы работаете в другой СУБД, то некоторые синтаксические конструкции могут отличаться, но суть остается такой же.
Общие сведения
Итак, хранимые процедуры в SQL — это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение — то, что мы делали в прошлых уроках вручную.
Особенностью процедур является то, что есть возможность передавать аргументы, (так же как и функциям в других языка) и выводить различные данные в зависимости от аргумента. Также, процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы.
Хранимые процедуры в MySQL
Хранимые процедуры в СУБД MySQL можно создать несколькими способами, мы кратко опишем 2 способа через утилиту phpmyadmin:
Также как и все запросы SELECT, INSERT и т.д код создания хранимых процедур можно прописывать на вкладке SQL. 
Второй способ возможен тогда, когда мы выбрали определенную БД, и конкретно для нее появляется вкладка процедур, как на скриншоте ниже. 
Далее в этой вкладке следует нажать кнопку «Добавить процедуру».
Итак, где создавать процедуры описали, а что конкретно прописывать пока непонятно. Как раз к этому и перейдем.
Создание процедур в SQL
Чтобы создать процедуру необходимо воспользоваться оператором CREATE PROCEDURE. После оператора следует указать имя процедуры, а затем в круглых скобках аргументы, если они имеются, вместе с указанием типа данных каждого аргумента. Примерно это будет выглядеть так:
Ну и сразу разберем простой пример, напишем процедуру, которая будет показывать нам список покупателей из города, который мы передадим в качестве аргумента.
Напомню, что мы работаем с уже созданной БД и ее структуру с данными можно посмотреть по этим ссылкам на предыдущие уроки:
Итак, весь код создания процедуры приведем сразу:
Мы используем 1 способ создания процедуры — через консоль SQL для нашей базы данных. Поясним некоторые моменты кода выше:
Процедура обязательно должна начинаться с BEGIN и заканчиваться END. Внутри процедуры используется уже знакомый запрос SELECT, который сравнивает город со значением аргумента.
Итак, процедура создана и хранится отдельно от таблиц или представлений. Посмотреть все процедуры можно в одноименной вкладке.
Вызов процедур в SQL
Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:
Таким образом, в выводе получим список покупателей, проживающих в Москве.
Операторы хранимых процедур
Итак, хранимые процедуры это достаточно мощный инструмент. И в SQL хранимые процедуры также могут использовать различные операторы, вложенные запросы и т.д. То есть, все, что мы изучали в предыдущих статьях по SQL.
Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как IF-ELSE, CASE и WHILE. Далее, приведем пример использования конструкции CASE.
Создать хранимую процедуру, которая выведет продавцов, оформивших заказы, по диапазонам. Если аргумент имеет значение «Маленькая суммы», то диапазон продаж от 0 до 1000, «Средние суммы» — от 1000 до 1500, «Большие суммы» — свыше 1500.
Вызвать созданную процедуру можно с тремя различными аргументами:
Соответственно, в выводе будут разные данные в зависимости от аргумента.
Примеры
1. Создать хранимую процедуру, которая определяет лучшего продавца по суммарному значению продаж за период дат, полученных в аргументах.
2. Создать хранимую процедуру, которая отобразит информацию о совершенных заказах, стоимость которых выше числа, заданного в аргументе.
Заключение
Итак, мы познакомились с хранимыми процедурами в SQL. Очевидно, что примеров на их использование можно придумать очень много. Конкретно эта статья посвящена созданию и освоению базовых принципов в работе с хранимыми процедурами в SQL программировании.