Доброго времени суток, уважаемые читатели. Продолжаем изучать основные элементы языка SQL и в этой статье разберемся с хранимыми процедурами. Напомню, что мы работаем в СУБД MySQL, если вы работаете в другой СУБД, то некоторые синтаксические конструкции могут отличаться, но суть остается такой же.
Общие сведения
Итак, хранимые процедуры в SQL — это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение — то, что мы делали в прошлых уроках вручную.
Особенностью процедур является то, что есть возможность передавать аргументы, (так же как и функциям в других языка) и выводить различные данные в зависимости от аргумента. Также, процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы.
Хранимые процедуры в MySQL
Хранимые процедуры в СУБД MySQL можно создать несколькими способами, мы кратко опишем 2 способа через утилиту phpmyadmin:
- Через консоль SQL запросов
Также как и все запросы SELECT, INSERT и т.д код создания хранимых процедур можно прописывать на вкладке SQL. 
Через специальную вкладку процедуры
Второй способ возможен тогда, когда мы выбрали определенную БД, и конкретно для нее появляется вкладка процедур, как на скриншоте ниже. 
Далее в этой вкладке следует нажать кнопку «Добавить процедуру».
Итак, где создавать процедуры описали, а что конкретно прописывать пока непонятно. Как раз к этому и перейдем.
Создание процедур в SQL
Чтобы создать процедуру необходимо воспользоваться оператором CREATE PROCEDURE. После оператора следует указать имя процедуры, а затем в круглых скобках аргументы, если они имеются, вместе с указанием типа данных каждого аргумента. Примерно это будет выглядеть так:
Ну и сразу разберем простой пример, напишем процедуру, которая будет показывать нам список покупателей из города, который мы передадим в качестве аргумента.
Напомню, что мы работаем с уже созданной БД и ее структуру с данными можно посмотреть по этим ссылкам на предыдущие уроки:
Итак, весь код создания процедуры приведем сразу:
Мы используем 1 способ создания процедуры — через консоль SQL для нашей базы данных. Поясним некоторые моменты кода выше:
- По умолчанию окончанием запроса служит точка с запятой. Оператор DELIMITER задает новый разделитель, то есть новый знак окончания запроса. Это необходимо для того, чтобы запрос создания процедуры заканчивался после ключевого слова END. В противном случае, запрос закончится на точке с запятой, после основного тела процедуры.
Процедура обязательно должна начинаться с BEGIN и заканчиваться END. Внутри процедуры используется уже знакомый запрос SELECT, который сравнивает город со значением аргумента.
Итак, процедура создана и хранится отдельно от таблиц или представлений. Посмотреть все процедуры можно в одноименной вкладке.
Вызов процедур в SQL
Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:
Таким образом, в выводе получим список покупателей, проживающих в Москве.
Операторы хранимых процедур
Итак, хранимые процедуры это достаточно мощный инструмент. И в SQL хранимые процедуры также могут использовать различные операторы, вложенные запросы и т.д. То есть, все, что мы изучали в предыдущих статьях по SQL.
Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как IF-ELSE, CASE и WHILE. Далее, приведем пример использования конструкции CASE.
Создать хранимую процедуру, которая выведет продавцов, оформивших заказы, по диапазонам. Если аргумент имеет значение «Маленькая суммы», то диапазон продаж от 0 до 1000, «Средние суммы» — от 1000 до 1500, «Большие суммы» — свыше 1500.
Вызвать созданную процедуру можно с тремя различными аргументами:
Соответственно, в выводе будут разные данные в зависимости от аргумента.
Примеры
1. Создать хранимую процедуру, которая определяет лучшего продавца по суммарному значению продаж за период дат, полученных в аргументах.
2. Создать хранимую процедуру, которая отобразит информацию о совершенных заказах, стоимость которых выше числа, заданного в аргументе.
Заключение
Итак, мы познакомились с хранимыми процедурами в SQL. Очевидно, что примеров на их использование можно придумать очень много. Конкретно эта статья посвящена созданию и освоению базовых принципов в работе с хранимыми процедурами в SQL программировании.
ОБЛАСТЬ ПРИМЕНЕНИЯ: 
SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
В этом разделе описывается, как выполнить хранимую процедуру SQL Server SQL Server при помощи среды SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL . This topic describes how to execute a stored procedure in SQL Server SQL Server by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL Transact-SQL .
Существует два способа выполнения хранимой процедуры. There are two different ways to execute a stored procedure. Первым и наиболее распространенным подходом является вызов процедуры приложением или пользователем. The first and most common approach is for an application or user to call the procedure. Второй подход — настройка автоматического выполнения процедуры при запуске экземпляра SQL Server SQL Server . The second approach is to set the procedure to run automatically when an instance of SQL Server SQL Server starts. Если процедура вызывается приложением или пользователем, то в вызове явно указывается ключевое слово Transact-SQL Transact-SQL EXECUTE или EXEC. When a procedure is called by an application or user, the Transact-SQL Transact-SQL EXECUTE or EXEC keyword is explicitly stated in the call. Процедуру также можно вызывать и выполнять без ключевого слова, если она является первой инструкцией в пакете Transact-SQL Transact-SQL . Alternatively, the procedure can be called and executed without the keyword if the procedure is the first statement in the Transact-SQL Transact-SQL batch.
В этом разделе In This Topic
Перед началом работы Before you begin:
Для выполнения хранимой процедуры используется: To execute a stored procedure, using:
Перед началом Before You Begin
Ограничения Limitations and Restrictions
При сопоставлении имен системных процедур используются параметры сортировки вызывающей базы данных. The calling database collation is used when matching system procedure names. Таким образом, в вызове процедур следует всегда использовать точный регистр имен системных процедур. Therefore, always use the exact case of system procedure names in procedure calls. Например, этот код завершится с ошибкой при выполнении в контексте базы данных, в параметрах сортировки которой учитывается регистр: For example, this code will fail if it is executed in the context of a database that has a case-sensitive collation:
Чтобы показать точные имена системных процедур, запросите представления каталога sys.system_objects и sys.system_parameters . To display the exact system procedure names, query the sys.system_objects and sys.system_parameters catalog views.
Если определяемая пользователем процедура имеет имя, совпадающее с системной процедурой, то такая определяемая пользователем процедура никогда не будет выполняться. If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.
Рекомендации Recommendations
Выполнение системных хранимых процедур Executing System Stored Procedures
Имена системных процедур начинаются с префикса sp_ . System procedures begin with the prefix sp_. Поскольку они логически отображаются во всех базах данных, определяемых и пользователем, и системой, то они могут выполняться из любой базы данных без полного указания имени процедуры. Because they logically appear in all user- and system- defined databases, they can be executed from any database without having to fully qualify the procedure name. Однако рекомендуется уточнять имена всех системных процедур указанием схемы sys во избежание конфликтов имен. However, we recommend schema-qualifying all system procedure names with the sys schema name to prevent name conflicts. В следующем примере демонстрируется рекомендуемый метод вызова системной процедуры. The following example demonstrates the recommended method of calling a system procedure.
Выполнение пользовательских хранимых процедур Executing User-defined Stored Procedures
При выполнении определяемой пользователем процедуры рекомендуется дополнительно указывать имя схемы. When executing a user-defined procedure, we recommend qualifying the procedure name with the schema name. Это позволяет немного увеличить производительность, поскольку компоненту Компонент Database Engine Database Engine не нужно выполнять поиск в нескольких схемах. This practice gives a small performance boost because the Компонент Database Engine Database Engine does not have to search multiple schemas. Также исключается выполнение неправильной процедуры в случае, если в нескольких схемах базы данных имеются процедуры с одним именем. It also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.
В следующем примере демонстрируется рекомендуемый метод выполнения определяемой пользователем процедуры. The following example demonstrates the recommended method to execute a user-defined procedure. Обратите внимание, что процедура принимает один входной параметр. Notice that the procedure accepts one input parameter. Сведения об указании входных и выходных параметров см. в статье Указание параметров. For information about specifying input and output parameters, see Specify Parameters.
Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine Database Engine производит поиск процедуры в следующем порядке. If a nonqualified user-defined procedure is specified, the Компонент Database Engine Database Engine searches for the procedure in the following order:
схема sys текущей базы данных; The sys schema of the current database.
Схема по умолчанию вызывающей программы при выполнении в пакете или в динамическом коде SQL. The caller’s default schema if it is executed in a batch or in dynamic SQL. Если неуточненное имя процедуры присутствует в тексте определения другой процедуры, в следующую очередь выполняется поиск в схеме, содержащей другую процедуру. Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema that contains this other procedure is searched next.
Схема dbo в текущей базе данных. The dbo schema in the current database.
Автоматическое выполнение хранимых процедур Executing Stored Procedures Automatically
Процедуры, помеченные для автоматического выполнения, выполняются каждый раз, когда запускается SQL Server SQL Server и в процессе запуска восстанавливается база данных master . Procedures marked for automatic execution are executed every time SQL Server SQL Server starts and the master database is recovered during that startup process. Настройка процедур для автоматического выполнения удобна для операций обслуживания базы данных и для постоянного выполнения процедур в фоновом процессе. Setting up procedures to execute automatically can be useful for performing database maintenance operations or for having procedures run continuously as background processes. Кроме того, автоматический запуск процедур может применяться для выполнения системных или служебных задач в базе данных tempdb, таких как создание глобальной временной таблицы. Another use for automatic execution is to have the procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. Это обеспечивает наличие такой временной таблицы при повторном создании базы данных tempdb во время запуска SQL Server SQL Server . This makes sure that such a temporary table will always exist when tempdb is re-created during SQL Server SQL Server startup.
Автоматически выполняемая процедура работает с теми же разрешениями, что и члены предопределенной роли сервера sysadmin . A procedure that is automatically executed operates with the same permissions as members of the sysadmin fixed server role. Любое сообщение об ошибке, сформированное такой процедурой, записывается в журнал ошибок SQL Server SQL Server . Any error messages generated by the procedure are written to the SQL Server SQL Server error log.
Ограничений на количество автоматически запускаемых процедур не существует, однако помните, что для выполнения каждой необходим один рабочий поток. There is no limit to the number of startup procedures you can have, but be aware that each consumes one worker thread while executing. Если необходимо выполнить несколько процедур при запуске, которые не должны выполняться параллельно, настройте одну процедуру на автоматический запуск, а вторую вызывайте в ее теле (в конце). If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. Таким образом будет задействован только один рабочий поток. This uses only one worker thread.
Не возвращайте никаких результирующих наборов из автоматически запускаемой процедуры. Do not return any result sets from a procedure that is executed automatically. Эта хранимая процедура выполняется SQL Server SQL Server , а не приложением или пользователем, и поэтому результирующие наборы нигде не обрабатываются. Because the procedure is being executed by SQL Server SQL Server instead of an application or user, there is nowhere for the result sets to go.
Установка, очистка и контроль автоматического выполнения Setting, Clearing, and Controlling Automatic Execution
Помечать процедуру для автоматического выполнения может только системный администратор (sa). Only the system administrator (sa) can mark a procedure to execute automatically. Кроме того, процедура должна находиться в базе данных master , принадлежать пользователю saи не иметь входных или выходных параметров. In addition, the procedure must be in the master database, owned by sa, and cannot have input or output parameters.
Используйте процедуру sp_procoption чтобы: Use sp_procoption to:
обозначить существующую процедуру как автоматически запускаемую; Designate an existing procedure as a startup procedure.
отменить выполнение процедуры при запуске SQL Server SQL Server . Stop a procedure from executing at SQL Server SQL Server startup.
безопасность Security
Permissions Permissions
Дополнительные сведения см. в разделе "Разрешения" статьи EXECUTE (Transact-SQL). For more information, see the "Permissions" section in EXECUTE (Transact-SQL).
Использование среды SQL Server Management Studio Using SQL Server Management Studio
Выполнение хранимой процедуры To execute a stored procedure
В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database Engine SQL Server Database Engine , разверните его, а затем разверните узел Базы данных. In Object Explorer, connect to an instance of the Компонент SQL Server Database Engine SQL Server Database Engine , expand that instance, and then expand Databases.
Разверните нужную базу данных, разверните узлы Программированиеи Хранимые процедуры. Expand the database that you want, expand Programmability, and then expand Stored Procedures.
Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру. Right-click the user-defined stored procedure that you want and click Execute Stored Procedure.
В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
Параметр Parameter
Указывает имя параметра. Indicates the name of the parameter.
Тип данных Data Type
Указывает тип данных параметра. Indicates the data type of the parameter.
Выходной параметр Output Parameter
Указывает, является ли этот параметр выходным. Indicates if this is an output parameter.
Передать значение NULL Pass Null Value
Передать значение NULL в качестве значения параметра. Pass a NULL as the value of the parameter.
Значение Value
Введите значение параметра, передаваемое ему при вызове процедуры. Type the value for the parameter when calling the procedure.
Чтобы выполнить хранимую процедуру, нажмите кнопку ОК. To execute the stored procedure, click OK.
Использование Transact-SQL Using Transact-SQL
Выполнение хранимой процедуры To execute a stored procedure
Установите соединение с компонентом Компонент Database Engine Database Engine . Connect to the Компонент Database Engine Database Engine .
На панели «Стандартная» нажмите Создать запрос. From the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере показано, как выполнить хранимую процедуру, которая принимает один параметр. This example shows how to execute a stored procedure that expects one parameter. В примере выполняется хранимая процедура uspGetEmployeeManagers со значением 6 , указанным в параметре @EmployeeID . The example executes the uspGetEmployeeManagers stored procedure with the value 6 specified as the @EmployeeID parameter.
Установка и отмена автоматического запуска процедуры To set or clear a procedure for executing automatically
Установите соединение с компонентом Компонент Database Engine Database Engine . Connect to the Компонент Database Engine Database Engine .
На панели «Стандартная» нажмите Создать запрос. From the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере показано, как использовать процедуру sp_procoption , чтобы задать автоматическое выполнение процедуры. This example shows how to use sp_procoption to set a procedure for automatic execution.
Отмена автоматического выполнения процедуры To stop a procedure from executing automatically
Установите соединение с компонентом Компонент Database Engine Database Engine . Connect to the Компонент Database Engine Database Engine .
На панели «Стандартная» нажмите Создать запрос. From the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере показано, как использовать процедуру sp_procoption , чтобы отменить автоматическое выполнение процедуры. This example shows how to use sp_procoption to stop a procedure from executing automatically.
В этом разделе описывается, как выполнить хранимую процедуру SQL Server 2016 при помощи среды SQL Server Management Studio или Transact-SQL.
Существует два способа выполнения хранимой процедуры. Первым и наиболее распространенным подходом является вызов процедуры приложением или пользователем. Второй подход — настройка автоматического выполнения процедуры при запуске экземпляра SQL Server . Если процедура вызывается приложением или пользователем, то в вызове явно указывается ключевое слово Transact-SQL EXECUTE или EXEC. Процедуру также можно вызывать и выполнять без ключевого слова, если она является первой инструкцией в пакете Transact-SQL .
В этом разделе
Перед началом работы выполните следующие действия.
Для выполнения хранимой процедуры используется:
Среда SQL Server Management Studio
Ограничения
При сопоставлении имен системных процедур используются параметры сортировки вызывающей базы данных. Таким образом, в вызове процедур следует всегда использовать точный регистр имен системных процедур. Например, этот код завершится с ошибкой при выполнении в контексте базы данных, в параметрах сортировки которой учитывается регистр:
Чтобы показать точные имена системных процедур, запросите представления каталога sys.system_objects и sys.system_parameters.
Если определяемая пользователем процедура имеет имя, совпадающее с системной процедурой, то такая определяемая пользователем процедура никогда не будет выполняться.
Рекомендации
Выполнение системных хранимых процедур
Имена системных процедур начинаются с префикса sp_. Поскольку они логически отображаются во всех базах данных, определяемых и пользователем и системой, то они могут выполняться из любой базы данных без полного указания имени процедуры. Однако рекомендуется уточнять имена всех системных процедур указанием схемы sys во избежание конфликтов имен. В следующем примере демонстрируется рекомендуемый метод вызова системной процедуры.
Выполнение пользовательских хранимых процедур
При выполнении определяемой пользователем процедуры рекомендуется дополнительно указывать имя схемы. Это позволяет немного увеличить производительность, поскольку компоненту Компонент Database Engine не нужно выполнять поиск в нескольких схемах. Также исключается выполнение неправильной процедуры в случае, если в нескольких схемах базы данных имеются процедуры с одним именем.
В следующем примере демонстрируется рекомендуемый метод выполнения определяемой пользователем процедуры. Обратите внимание, что процедура принимает один входной параметр. Сведения об указании входных и выходных параметров см. в статье Указание параметров.
Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine производит поиск процедуры в следующем порядке.
схема sys текущей базы данных;
Схема по умолчанию вызывающей программы при выполнении в пакете или в динамическом коде SQL. Если неуточненное имя процедуры присутствует в тексте определения другой процедуры, в следующую очередь выполняется поиск в схеме, содержащей другую процедуру.
Схема dbo в текущей базе данных.
Автоматическое выполнение хранимых процедур
Процедуры, помеченные для автоматического выполнения, выполняются каждый раз, когда запускается SQL Server и в процессе запуска восстанавливается база данных master . Настройка процедур для автоматического выполнения удобна для операций обслуживания базы данных и для постоянного выполнения процедур в фоновом процессе. Кроме того, автоматический запуск процедур может применяться для выполнения системных или служебных задач в базе данных tempdb, таких как создание глобальной временной таблицы. Это обеспечивает наличие такой временной таблицы при повторном создании базы данных tempdb во время запуска SQL Server.
Автоматически выполняемая процедура работает с теми же разрешениями, что и члены предопределенной роли сервера sysadmin . Любое сообщение об ошибке, сформированное такой процедурой, записывается в журнал ошибок SQL Server .
Ограничений на количество автоматически запускаемых процедур не существует, однако помните, что для выполнения каждой необходим один рабочий поток. Если необходимо выполнить несколько процедур при запуске, которые не должны выполняться параллельно, настройте одну процедуру на автоматический запуск, а вторую вызывайте в ее теле (в конце). Таким образом будет задействован только один рабочий поток.
| Совет |
|---|
Не возвращайте никаких результирующих наборов из автоматически запускаемой процедуры. Эта хранимая процедура выполняется SQL Server , а не приложением или пользователем, и поэтому результирующие наборы нигде не обрабатываются.
Установка, очистка и контроль автоматического выполнения
Помечать процедуру для автоматического выполнения может только системный администратор (sa). Кроме того, процедура должна находиться в базе данных master , принадлежать пользователю saи не иметь входных или выходных параметров.
Используйте процедуру sp_procoption чтобы:
обозначить существующую процедуру как автоматически запускаемую;
отменить выполнение процедуры при запуске SQL Server .
Безопасность
Разрешения
Дополнительные сведения см. в разделе "Разрешения" статьи EXECUTE (Transact-SQL).
Выполнение хранимой процедуры
В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database Engine, разверните его, а затем разверните узел Базы данных.
Разверните нужную базу данных, разверните узлы Программированиеи Хранимые процедуры.
Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру.
В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL.
Параметр
Указывает имя параметра.
Тип данных
Указывает тип данных параметра.
Выходной параметр
Указывает, является ли этот параметр выходным.
Передать значение NULL
Передать значение NULL в качестве значения параметра.
Значение
Введите значение параметра, передаваемое ему при вызове процедуры.
Чтобы выполнить хранимую процедуру, нажмите кнопку ОК.
Выполнение хранимой процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как выполнить хранимую процедуру, которая принимает один параметр. В примере выполняется хранимая процедура uspGetEmployeeManagers со значением 6 , указанным в параметре @EmployeeID .
Установка и отмена автоматического запуска процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption, чтобы задать автоматическое выполнение процедуры.
Отмена автоматического выполнения процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption, чтобы отменить автоматическое выполнение процедуры.
