Какой командой вызываются процедуры sql

IgorSikors.github.io

В PL/SQL существует несколько конструкций для разбиения кода на модули:

• Процедура. Программа, которая осуществляет одно или несколько действий и вызывается как исполняемый оператор PL/SQL. Передавать данные процедуре и получать их можно с помощью списка параметров.
• Функция. Программа, которая возвращает одно значение и используется как выражение PL/SQL. Для передачи информации функции используется ее список параметров. Параметры также могут использоваться для возврата информации из функции, но обычно это считается проявлением плохого стиля программирования.
• Триггер базы данных. Набор команд, который вызывается при выполнении некоторого события (подключение к базе данных, модификация строки таблицы или DDL-операция).
• Пакет. Именованный набор процедур, функций, типов и переменных. Пакет не является модулем (скорее, это мета-модуль), но он тесно связан с реализацией модульного подхода.
• Объектный тип или экземпляр объектного типа. Эмуляция объектно-ориентированного класса в Oracle. Объектный тип инкапсулирует состояние и поведение данных, комбинируя их (как реляционная таблица) с правилами (процедурами и функциями, которые манипулируют этими данными).

Процедуры

Процедура представляет собой модуль, выполняющий одно или несколько действий. Поскольку вызов процедуры в PL/SQL является отдельным исполняемым оператором, блок кода PL/SQL может состоять только из вызова процедуры. Процедуры относятся к числу ключевых компонентов модульного кода, обеспечивающих оптимизацию и повторное использование программной логики. Общий формат процедуры PL/SQL выглядит так:

Основные элементы этой структуры:

• схема — имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
• имя — имя процедуры.
• параметр — необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
• AUTHID — определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором — с правами вызывающего.
• объявления — объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
• исполняемые команды — команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений — необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

Вызов процедуры

Процедура вызывается как исполняемая команда PL/SQL. Другими словами, ее вызов должен заканчиваться точкой с запятой (;) и может предшествовать другим командам SQL либо PL/SQL (если таковые имеются) в исполняемом разделе блока PL/SQL или следовать за ними:

Если процедура не имеет параметров, она может вызываться с пустыми круглыми скобками или без них:

Заголовок процедуры

Часть определения процедуры, предшествующая ключевому слову IS, называется заголовком процедуры, или сигнатурой. Заголовок предоставляет программисту всю информацию, необходимую для вызова процедуры:

• Имя процедуры.
• Условие AUTHID (если имеется).
• Список параметров (если имеется).
• Список ACCESSIBLE BY (если имеется — новая возможность Oracle Database 12c).

В идеале программист при виде заголовка процедуры должен понять, что делает эта процедура и как она вызывается. Заголовок процедуры apply_discount из предыдущего раздела выглядит так:

Он состоит из типа модуля, имени и списка из двух параметров.

Тело процедуры

В теле процедуры содержится код, необходимый для реализации этой процедуры; тело состоит из объявления, исполняемого раздела и раздела исключений этой процедуры. Все, что следует за ключевым словом IS, образует тело процедуры. Разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите процедуру командой END.

Метка END

Вы можете указать имя процедуры за завершающим ключевым словом END:

Имя служит меткой, явно связывающей конец программы с ее началом. Привыкните к использованию метки END. Она особенно полезна для процедур, занимающих несколько страниц или входящих в серию процедур и функций в теле пакета.

Функции

Функция представляет собой модуль, который возвращает значение командой RETURN (вместо аргументов OUT или IN OUT). В отличие от вызова процедуры, который представляет собой отдельный оператор, вызов функции всегда является частью исполняемого оператора, то есть включается в выражение или служит в качестве значения по умолчанию, присваиваемого переменной при объявлении.

Возвращаемое функцией значение принадлежит к определенному типу данных. Функция может использоваться вместо выражения, которое имеет тот же тип данных, что и возвращаемое ею значение.

Функции играют важную роль в реализации модульного подхода. К примеру, реализацию отдельного бизнес-правила или формулы в приложении рекомендуется оформить в видефункции. Вместо того чтобы писать один и тот же запрос снова и снова («Получитьимя работника по идентификатору», «Получить последнюю строку заказа из таблицы order для заданного идентификатора компании» и т. д.), поместите его в функцию и вызовите эту функцию в нужных местах. Такой код создает меньше проблем с отладкой, оптимизацией и сопровождением.

Структура функции Функция имеет почти такую же структуру, как и процедура, не считая того, что ключевое слово RETURN в ней играет совершенно другую роль:

Основные элементы этой структуры:

• схема — имя схемы, которой будет принадлежать функция (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания функции в другой схеме.
• имя — имя функции.
• параметр — необязательный список параметров, которые применяются для передачи данных в функцию и возврата информации из нее в вызывающую программу.
• возвращаемый_тип — задает тип значения, возвращаемого функцией. Возвращаемый тип должен быть указан в заголовке функции.
• AUTHID — определяет, с какими разрешениями будет вызываться функция: создателя (владельца) или текущего пользователя. В первом случае (используется по умолчанию) применяется модель прав создателя, во втором — модель прав вызывающего.
• DETERMINISTIC — определяет функцию как детерминированную, то есть возвращаемое значение полностью определяется значениями ее аргументов. Если включить эту секцию, ядро SQL сможет оптимизировать выполнение функции при ее вызове в запросах.
• PARALLEL_ENABLE — используется для оптимизации и позволяет функции выполняться параллельно в случае, когда она вызывается из команды SELECT.
• PIPELINED — указывает, что результат табличной функции должен возвращаться в итеративном режиме с помощью команды PIPE ROW.
• RESULT_CACHE — указывает, что входные значения и результат вызова функции должен быть сохранен в кэше результатов.
• ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к функции программными модулями, перечисленными в круглых скобках.
• AGGREGATE — используется при определении агрегатных функций.
• EXTERNAL — определяет функцию с «внешней реализацией» — то есть написанную на языке C.
• объявления — объявления локальных идентификаторов этой функции. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• исполняемые команды — команды, выполняемые функцией при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений — необязательные обработчики исключений для функции. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

Возвращаемый тип

Функция PL/SQL может возвращать данные практически любого типа, поддерживаемого PL/SQL, — от скаляров (единичных значений вроде даты или строки) до сложных структур: коллекций, объектных типов, курсорных переменных и т. д. Несколько примеров использования RETURN:

Хранимые процедуры

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (stored procedure).

То есть по сути хранимые процедуры представляют набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.

Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.

И еще один важный аспект — производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.

Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC .

Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.

Например, пусть в базе данных есть таблица, которая хранит данные о товарах:

Создадим хранимую процедуру для извлечения данных из этой таблицы:

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures :

SQL-Ex blog

Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще

Если вы слышали о хранимых процедурах в SQL Server, но точно не знаете, что это такое и как это использовать наилучшим образом, то эта статья даст вам необходимую информацию и позволит начать работать с хранимыми процедурами.

Здесь вы узнаете, что из себя представляет хранимая процедура, как ее создать и применить. Мы поговорим о параметрах хранимой процедуры, входных и выходных, и вкратце обсудим обработку ошибок и безопасность для хранимых процедур.

Что необходимо для выполнения примеров

Примеры в этой статье выполняются к учебной базе данных AdventureWorks. Получив практику на учебной базе данных, вы сможете легко применить эти примеры к вашей базе данных. Как обычно, остерегайтесь экспериментировать с вашей производственной системой.

Что такое хранимая процедура SQL?

В простейшем виде хранимая процедура — это не более чем набор операторов Transact-SQL (T-SQL), который хранится в базе данных и может выполняться при вызове хранимой процедуры по имени. Имя присваивается хранимой процедуре при ее создании. Для краткости хранимую процедуру часто называют хп (SP).

Особенности хранимых процедур

• Хранимые процедуры могут принимать входные параметры.
• Хранимые процедуры могут возвращать выходные параметры.
• Хранимые процедуры содержат программные операторы.
• Хранимые процедуры могут возвращать значение состояния, показывающее успешность или сбой (и почему он произошел).
• Нет ограничения на размер хранимой процедуры
• Имеется четыре типа хранимых процедур:
  1. Определяемые пользователем
  2. Временные
  3. Системные
  4. Расширенные определяемые пользователем

Определения четырех типов хранимых процедур

Определяемая пользователем хранимая процедура

Наиболее часто используются пользовательские хранимые процедуры. Такая процедура может создаваться в пользовательской базе данных или в любой системной базе данных за исключением базы данных Resource. Мы подробней поговорим о пользовательских хранимых процедурах позже в этой статье и рассмотрим ряд примеров.

Временная хранимая процедура

Временная хранимая процедура — это вид пользовательской хранимой процедуры, которая очень похожа на временные таблицы. Временные хранимые процедуры хранятся в базе данных «tempdb», видной на вкладке «System Databases». Эти временные хранимые процедуры могут использоваться как локальные или глобальные.

Временные локальные хранимые процедуры видны только на текущем подключении пользователя и всегда будут иметь префикс # (знак фунта или хэштега).

Временные глобальные хранимые процедуры видны на любом пользовательском подключении и имеют префикс ## (двойной знак фунта).

Поскольку они являются «временными», эти хранимые процедуры пропадают, когда закрывается подключение SQL.

Системные хранимые процедуры

Вы обнаружите, что довольно часто используете системные хранимые процедуры. Полезно познакомиться с ними, чтобы не изобретать колесо. Эти хранимые процедуры включены по умолчанию в установку SQL Server и всегда будут начинаться с префикса «sys.sp_». Вам могут пригодиться самые распространенные системные хранимые процедуры, включая sys.sp_addUser, sys.sp_addLogin, sys.sp_addRole, sys.sp_change_users_login и т.д.

1. Databases (базы данных)
2. System Databases (системные базы данных)
3. Tempdb
4. Programmability (программирование)
5. Stored Procedures (хранимые процедуры)
6. System Stored Procedures (системные хранимые процедуры)

Расширенные хранимые процедуры пользователя

Расширенные хранимые процедуры пользователя используются для создания внешних процедур на языках типа C, C#, VB и т.д. Они реализуются как DLL, которые SQL Server может загрузить и выполнять динамически.

Однако, согласно Microsoft, расширенные хранимые процедуры пользователя будут удалены из будущих версий SQL Server. Поэтому не рекомендуется использовать их в текущих или будущих разработках. Вам следует запланировать удаление или модификацию приложений, использующих эти хранимые процедуры.

Зачем использовать хранимые процедуры

Хранимые процедуры имеют много преимуществ. Он уменьшают сетевой трафик, поскольку только имя передается по сети от приложения на сервер. Правда, мы можем передавать еще несколько параметров, но передача имени хранимой процедуры и нескольких параметров не так сильно загружает сеть как передача всего кода всякий раз, когда нужно этот код выполнить.

Хранимые процедуры усиливают безопасность. Код в хранимой процедуре определяет, какие действия производятся над объектами базы данных. Это означает, что при всяком вызове хранимой процедуры один и тот же блок кода выполняется одним и тем же способом. Вы не должны беспокоиться об ошибках в коде, повторяя набор одного и того же кода всякий раз, когда вам нужно выполнить его.

Мы можем предоставить пользователям разрешение на использование (вызов) хранимой процедуры или же запретить пользователям ее вызывать. Замечание: пользователь не должен иметь разрешение или доступ к таблице, чтобы вызвать хранимую процедуру, которая будет менять данные в этой таблице. Это позволит пользователям добавлять данные в таблицу для создания записей данных, не имея к ним доступа на просмотр, удаление или изменение любых данных в таблице, помимо тех, на которые вы дали им разрешения.

Создав хранимую процедуру, вы выполняете ее, создается план выполнения запроса, который сохраняется и повторно используется. Если вы создаете базисный шаблон вашего кода SQL или набираете код вручную всякий раз, когда вам его нужно выполнить, SQL должен создавать новый план выполнения запроса. Это замедляет время выполнения вашего кода и потребляет больше ресурсов. Поскольку мы сохраняем и повторно используем существующий план выполнения, хранимая процедура может выполняться значительно быстрее и использовать меньше ресурсов, т.к. нет необходимости создавать план выполнения. Просто загружается план, созданный ранее.

Создание простой хранимой процедуры

Теперь, когда мы знаем немного больше о преимуществах хранимых процедур, давайте создадим базовый тестовый пример для работы. В коде примера, приведенного ниже, мы создаем хранимую процедуру с именем uspGetEmployees. Я уверен, что вы можете догадаться, что подразумевает часть «Get Employees», но что такое «usp» в имени? Вы можете именовать хранимые процедуры как вам нравится, но отраслевым стандартом является использование соглашения, которое мы здесь применили. «usp» — это сокращение от «User Stored Procedure» (пользовательская хранимая процедура).

Видно, что блок кода выше очень похож на создание представления (VIEW) в SQL Server. Вы просто предваряете оператор SELECT текстом «CREATE PROCEDURE «.

Замечание. При создании хранимой процедуры вы можете сократить слово «PROCEDURE» до «PROC»; любой вариант будет работать.

Выполнение хранимых процедур

При выполнении хранимой процедуры вам просто нужно напечатать команду EXECUTE (или EXEC) с последующим именем хранимой процедуры, как в примере ниже.

Замечание. В большинстве случаев вам не нужно использовать команду EXEC для выполнения хранимой процедуры. Вы можете напечатать имя хранимой процедуры и выполнить ее. Вы получите одинаковые результаты в любом случае. Для простоты мы будем использовать команду EXEC на всем протяжении статьи.

Изменение/модификация хранимой процедуры

Вы можете использовать команду «ALTER PROC» или «ALTER PROCEDURE» для изменения функциональности хранимой процедуры. Скажем, мы хотим также получать отчество из таблицы «Person.Person» в базе данных AdventureWorks. Вместо создания новой хранимой процедуры мы можем модифицировать имеющуюся, как в примере ниже.

Давайте выполним хранимую процедуру снова и посмотрим на результаты.

Видно, что единственным отличием в результатах этого множества и предыдущего является наличие отчества.

ОК, довольно просто. Давайте продолжим. Теперь мы добавим предложение WHERE и будем использовать предикат IS NOT NULL для фильтрации сотрудников, у которых присутствует отчество (не стоит NULL).

Давайте опять выполним эту процедуру и посмотрим на результаты.

Удаление хранимой процедуры

Удаление хранимой процедуры в SQL подобно удалению таблицы, представления и т.д. Вы просто вызываете команду «DROP PROCEDURE» или «DROP PROC» с именем процедуры.

Если хранимая процедура не используется, она почти не потребляет ресурсов, и нет особых причин удалять их только потому, что вы хотите освободить ресурсы. Если вы сомневаетесь в необходимости удаления хранимой процедуры, создайте шаблон с содержимым хранимой процедуры, чтобы вам не пришлось переписывать весь этот код позднее.

Входные параметры хранимой процедуры

Что такое входной параметр? В простейшем виде это переменная. Когда мы добавляем переменную к хранимой процедуре, мы считаем ее входным параметром, а не переменной, хотя это одно и то же. Просто считайте это соглашением о наименовании, чтобы отделить одно от другого.

Когда мы добавляем входной параметр в хранимую процедуру, она помещается после команды «CREATE PROC» и выше команды «AS». И, как и для любой переменной, имя параметра должно начинаться с символа @ и последующим типом данных. В следующем примере мы изменяем нашу хранимую процедуру, добавляя переменную и присваивая ей значение по умолчанию.

Давайте выполним хранимую процедуру и посмотрим на результаты.

Что если я захочу вызвать процедуру с другим именем в качестве параметра? Тогда вам просто нужно указать эту переменную после имени хранимой процедуры, как в примере ниже. Тогда будет использоваться указанное значение вместо значения по умолчанию.

Что если вы не уверены, как пишется имя? Хорошо, тогда опять изменим нашу хранимую процедуру. Теперь мы заменим равенство » sql»>ALTER PROCEDURE uspGetEmployees
@lastname VARCHAR(40) = ‘abel’
AS
SELECT
BusinessEntityID
, FirstName
, LastName
FROM Person.Person
WHERE LastName LIKE @lastname;
GO

Это позволит нам использовать подстановочные знаки в параметре и вернуть, например, каждого, чья фамилия начинается с «Ab».

Хранимые процедуры с несколькими входными параметрами

Добавление нескольких параметров следует тем же принципам, что и команда с единственным параметром, только они должны разделяться запятой. В примере ниже мы добавляем параметр «FirstName» и ссылку на него в предложении WHERE. Мы начинаем с удаления значений по умолчанию, которые присваивались параметрам в предыдущих примерах. Значения параметров будут передаваться пользователем при вызове хранимой процедуры.

Теперь при выполнении хранимой процедуры нам нужно включать в вызов значения обоих параметров. В этом примере мы вернем все строки, у которых фамилия начинается с «Ab», а имя — с «K».

Когда используются несколько параметров, их значения при выполнении хранимой процедуры должны передаваться в том же порядке, в каком он перечислялись при создании хранимой процедуры. Однако это можно обойти. В нашем примере, если я захочу изменить порядок имени и фамилии и указать имя перед фамилией при выполнении процедуры, я могу это сделать путем указания имен переменных, как в примере ниже.

Обычно вы не будете этого делать. Но если вы не уверены в порядке перечисления параметров, вы можете использовать этот метод, чтобы гарантировать получение желаемых результатов. В противном случае, если вы поменяете порядок параметров при вызове хранимой процедуры без указания имен параметров, запрос не вернет правильный результат. Поскольку сначала будет выполняться поиск по фамилии, а затем по имени — в порядке нашего примера, то запрос ничего не вернет, т.к. нет сотрудника в таблице с фамилией Kim и именем Abercrombie.

Выходные параметры хранимой процедуры

Выходные параметры чуть сложнее. Мы начнем с очень простого примера, чтобы вы могли понять суть создания выходного параметра хранимой процедуры. Блок кода ниже вы можете легко скопировать и вставить в свой редактор запросов. В этом примере я добавил номера строк, чтобы было легче ссылаться при объяснении каждого шага в примере с выходными параметрами.

1. Мы создаем новую хранимую процедуру с именем «myRowCount».
2. Добавляем входной параметр с именем «@lname» и типом данных «VARCHAR(40)».
3. Теперь давайте добавим выходной параметр с именем «@numrows», т.к. мы хотим подсчитать число строк, возвращаемых хранимой процедурой. Тип данных переменной — INT, значение по умолчанию — 0, и нам необходимо указать, что это выходной параметр, с помощью ключевого слова OUTPUT. Это даст знать SQL, что эта переменная будет использоваться для вывода данных.
4. Ключевое слово «AS» говорит создать хранимую процедуру, содержащую последующий код.
5. Наш основной оператор SELECT.
6. Предложение FROM указывает таблицы, откуда будут извлекаться данные.
7. Предложение WHERE устанавливает фильтры для оператора SELECT.
8. На этой строке мы устанавливаем (присваиваем) для «@numrows» значение «@@ROWCOUNT». Когда вы видите двойной символ @, это признак системной переменной, которых имеется несколько. Познакомиться с системными переменными можно в документации. Поскольку @@ROWCOUNT получает значение при каждом выполнении запроса, мы захватим число, сгенерированное при выполнении нашей хранимой процедуры, и загрузим его в выходной параметр @numrows.

1. Здесь мы объявляем переменную, чтобы принять значение из выходного параметра процедуры.
   В этом примере мы называем новую переменную «@retrows», сокращение от «return rows». Мы должны назначить ей тип данных, который соответствует выходной переменной, которую мы создали в хранимой процедуре.
2. Здесь мы выполняем нашу хранимую процедуру с помощью команды «EXEC myRowCount», и нам нужно предоставить значение входному параметру. В данном примере мы хотим вернуть все строки, для которых фамилия начинается с «B». Далее на той же строке мы хотим получить значение в нашу переменную «@numrows», которую мы создали в хранимой процедуре, и передать это значение в нашу новую переменную, которую мы описали в операторе DECLARE выше. Опять же нам нужно добавить ключевое слово OUTPUT, чтобы напомнить компилятору об этой переменной.
3. Теперь мы создаем последний оператор SELECT, в котором получаем значение (число строк) из переменной «@retrows» и именуем результирующий набор «Rows».

Обработка ошибок в хранимых процедурах

Здесь мы опять изменим хранимую процедуру uspGetEmployees. С помощью функции @@ROWCOUNT, о которой говорилось в предыдущем разделе, мы будем возвращать сообщение «No Records Found», если ничего не будет возвращено. Это упрощенный, но эффективный взгляд на обработку ошибок в хранимых процедурах.

Здесь мы выполним хранимую процедуру дважды. Сначала вернем все строки, фамилии которых начинаются с «Ak»; потом выполним хранимую процедуру, которая вернет сообщение об ошибке. Но первое, что нужно сделать, — это изменить процедуру.

Теперь давайте выполним нашу хранимую процедуру для поиска фамилий, которые, как мы знаем, есть в таблице Person.Person.

Теперь выполним ту же процедуру с именем, которого нет в таблице Person.Person.

Несмотря на то, что это был примитивный пример, он даст вам направление работы в тестовом и рабочем окружении. Экспериментируйте и вносите изменения. Делайте обработку ошибок интуитивно понятной.

Создание, изменение, удаление и выполнение хранимых процедур SQL Server

Данный материал является переводом оригинальной статьи «MSSQLTips : Rick Dobson : Create, Alter, Drop and Execute SQL Server Stored Procedures».

У начинающих осваивать SQL Server могут присутствовать навыки проектирования и ручного запуска сценариев T-SQL, однако не все начинающие DBA понимают, как упаковать свои сценарии T-SQL для удобного повторного использования. В этой статье мы приведём примеры, иллюстрирующие основы создания, изменения и запуска хранимых процедур, чтобы упростить повторное использование кода T-SQL. Кроме этого, мы кратко опишем использование входных и выходных параметров, а также значений кодов возврата, связанных с хранимыми процедурами.

Обзор хранимых процедур SQL Server

Хранимая процедура — это сохраненный блок кода T-SQL, например запрос для вывода списка строк в таблице. Блок кода T-SQL можно сохранить в файле сценария T-SQL. Вы также можете сохранить код из файла сценария в хранимой процедуре.

Сохранение кода в хранимой процедуре, а не в файле сценария дает несколько преимуществ. Вот несколько примеров:

• Вам не нужно открывать код в хранимой процедуре, чтобы запустить ее код T-SQL. Тогда как, пользователям необходимо открыть файл сценария с его кодом, чтобы запустить код.
• Хранимые процедуры также предлагают средство ограничения доступа к базовым таблицам для запроса. Предоставляя доступ к запуску хранимых процедур без разрешения на чтение или запись в базовые таблицы, вы можете защитить данные, но по-прежнему обеспечить видимость данных в базовых таблицах с помощью хранимой процедуры.
• Вы можете использовать входные параметры с хранимыми процедурами, чтобы изменять работу кода внутри хранимой процедуры. Хотя, файлы сценариев позволяют использовать локальные переменные для изменения наборов, возвращаемых запросами, они должны предоставлять свой код, чтобы вы могли изменять локальные переменные во время выполнения.
• Обретя навыки сегментирования программного решения на части, на основе хранимых процедур, вы упрощаете изменение кода с течением времени. Добавляя код в виде коротких модульных сценариев, каждый сценарий можно будет легче читать, поддерживать и даже повторно использовать в других приложениях. Решения, основанные на файлах SQL со сценариями для запросов, могут становиться все более длинными, трудными для чтения и обслуживания, поскольку в решение продолжают вноситься последовательные изменения.

Хранимые процедуры вводят некий уровень абстракции, которого нет при сохранении кода в файле сценария. Следовательно, если у вас есть простое решение, используемое одним пользователем, которому требуется доступ к базовым источникам данных для запроса (или набора запросов), то файл сценария может быть даже лучше, поскольку он упрощает решение.

Многие блоки кода T-SQL можно запускать из хранимой процедуры. Обычно первоначальную версию кода тестируют внутри файла сценария T-SQL, а затем копируют код в тело оболочки хранимой процедуры.

После создания или изменения хранимой процедуры, содержащей один или несколько операторов SELECT , вы можете вызвать хранимую процедуру с помощью оператора EXEC . Следовательно, вы можете думать о хранимой процедуре как о контейнере, который облегчает повторное использование в нем кода T-SQL.

Создание новой хранимой процедуры SQL Server

Многие администраторы баз данных знакомы с созданием таблицы с помощью оператора CREATE TABLE . Точно так же администраторы-разработчики могут создать хранимую процедуру с помощью оператора CREATE PROC или CREATE PROCEDURE . Так же, как оператор CREATE TABLE добавляет таблицу в базу данных, оператор CREATE PROC добавляет хранимую процедуру в базу данных. Итак, вам нужно начать с новой или существующей базы данных, когда вы хотите создать хранимую процедуру, поскольку хранимая процедура фактически хранится в базе данных.

Если у вас есть соответствующие разрешения, вы можете использовать оператор CREATE DATABASE , чтобы создать новую базу данных для хранения таблиц и других типов объектов, таких как хранимые процедуры.

Следующий сценарий создает базу данных с именем CodeModuleTypes. Его первый оператор определяет главную базу данных как базу данных по умолчанию. Его второй оператор создает базу данных. В зависимости от ваших потребностей могут быть гораздо более сложные версии оператора CREATE DATABASE. Если не указано иное, простой оператор создания БД, подобный приведенному ниже, будет использовать настройки по умолчанию из БД model, которая является одной из стандартных баз данных, устанавливаемых вместе с SQL Server.

После того, как у вас появилась база данных, такая как CodeModuleTypes, вы можете вызвать оператор CREATE PROC в этой базе данных.

Следующий сценарий демонстрирует синтаксис, который можно использовать для создания вашей первой хранимой процедуры. Хранимая процедура в приведенном ниже коде отображает набор результатов со всеми столбцами для каждой строки из таблицы Employee в схеме HumanResources демонстрационной базы данных AdventureWorks2014. Ранее мы описали, как загрузить копию этой демонстрационной БД.

Вы можете думать о схеме, как о способе логической группировки объектов базы данных, таких как таблицы и хранимые процедуры. Эти логические группировки позволяют избежать конфликтов имен между объектами с одинаковыми именами в разных схемах. Любая база данных может иметь несколько схем. В нашем примере все хранимые процедуры обозначены, как принадлежащие схеме dbo базы данных CodeModuleTypes.

Приведенный ниже оператор CREATE PROC состоит из трех частей.

• Оператор CREATE PROC называет хранимую процедуру (и её схему, если вы явно указываете её).
• Ключевое слово as действует как маркер, обозначающий, что код определения хранимой процедуры вот-вот начнется.
• Код T-SQL, определяющий работу хранимой процедуры. В этом примере определяющим кодом является оператор SELECT для таблицы Employee в схеме HumanResources базы данных AdventureWorks2014.

После создания хранимой процедуры ее можно запустить с помощью оператора EXEC , подобно примеру, приведенному ниже. Именно этот оператор возвращает набор результатов со всеми столбцами для каждой строки из таблицы Employee.

Вот отрывок из вывода, созданного предыдущим скриптом.

• На панели результатов показаны первые одиннадцать столбцов из первых семнадцати строк с данными о 290 сотрудниках компании AdventureWorks.
• Если вы хотите обработать строки, отображаемые оператором SELECT в хранимой процедуре, вам нужно будет сохранить строки набора результатов в какой-либо другой таблице или объекте SQL Server. Затем обработайте результаты в этом объекте.

Удаление хранимой процедуры SQL Server

Предыдущий сценарий создания хранимой процедуры завершится ошибкой, если хранимая процедура uspMyFirstStoredProcedure в схеме dbo уже существует. Одним из способов решения этой проблемы является удаление предыдущей версии хранимой процедуры, а затем повторный запуск сценария для создания новой версии хранимой процедуры. Вы можете удалить предыдущую версию хранимой процедуры с помощью оператора DROP PROC или DROP PROCEDURE.

Вместо того, чтобы позволить оператору CREATE PROC завершиться с ошибкой при наличии предыдущей версии хранимой процедуры, обычно проверяют, существует ли уже хранимая процедура, и удаляют ее, чтобы избежать ошибки. Следующий фрагмент можно использовать для удаления предыдущей версии хранимой процедуры uspMyFirstStoredProcedure, если она уже существует. В зависимости от ваших требований и кода, определяющего хранимую процедуру, может быть полезно переименовать существующую в настоящее время хранимую процедуру вместо ее удаления.

Изменение существующей хранимой процедуры SQL Server

Следующий блок кода демонстрирует оператор ALTER PROC . Этот оператор отличается от оператора CREATE PROC тем, что он может работать только с существующей хранимой процедурой.

Этот сценарий предназначен для запуска сразу после предыдущего сценария, удаляющего uspMyFirstStoredProcedure, если он уже существует.

Первые два оператора в следующем блоке кода — это операторы CREATE PROC и EXEC , которые могут создавать новую копию хранимой процедуры и запускать uspMyFirstStoredProcedure. Поскольку весь код представлен в виде одного блока, требуются три ключевых слова GO , которые не потребовались бы, если бы весь блок кода был сегментирован на четыре отдельных блока кода:

• Начальный оператор CREATE PROC должен сопровождаться ключевым словом GO , чтобы оператор CREATE PROC завершился перед первым оператором EXEC .
• Затем начальный оператор EXEC должен сопровождаться ключевым словом GO , чтобы оператор ALTER PROC был первым оператором в его пакете.
• Наконец, за оператором ALTER PROC должно следовать ключевое слово GO , чтобы оператор ALTER PROC завершился до последнего оператора EXEC .

Оператор ALTER PROC состоит из трех частей:

• Имя объекта после ALTER PROC должно совпадать с именем существующей хранимой процедуры, которую вы хотите изменить.
• Ключевое слово as действует как разделитель, отделяющий объявления ALTER PROC от нового кода T-SQL, определяющего измененную версию хранимой процедуры.
• Новый код внутри оператора ALTER PROC соединяет таблицу Person из схемы Person с таблицей Employee из схемы HumanResources в базе данных AdventureWorks2014.

Вот результат выполнения оператора EXEC в предыдущем сценарии:

• Столбцы BusinessEntityID и JobTitle взяты из таблицы Employee.
• Столбцы FirstName и LastName взяты из таблицы Person.

Помимо очевидной разницы между созданием новой хранимой процедуры и изменением существующей хранимой процедуры, оператор ALTER PROC отличается от оператора CREATE PROC другими важными особенностями. Например, оператор ALTER PROC сохраняет все параметры безопасности, связанные с существующей сохраненной процедурой, в то время, как оператор CREATE PROC не сохраняет эти параметры. Таким образом, оператор ALTER PROC подойдет лучше, чем оператор CREATE PROC , если все, что нужно сделать, это изменить код в существующей хранимой процедуре.

Начиная с SQL Server 2016 SP1, Microsoft представила новую инструкцию CREATE или ALTER для модулей кода, таких как хранимые процедуры, представления и определяемые пользователем функции. Вы можете получить представление о функциональности этого нового оператора из предыдущей статьи MSSQLTips.com.

Входные параметры хранимой процедуры SQL Server

Входной параметр позволяет разработчику изменять способ работы хранимой процедуры во время выполнения. Обычно входной параметр в предложении where оператора SELECT используется для управления строками, отображаемыми при запуске хранимой процедуры. Можно заглянуть на страницу руководства MSSQLTips.com для демонстрации того, как использовать входные параметры в предложении where .

Вот простой пример, основанный на изменении uspMyFirstStoredProcedure, который демонстрирует использование входного параметра.

Входной параметр с именем @jobtitle назван непосредственно перед ключевым словом as .

На входной параметр имеется ссылка в предложении where оператора SELECT .

Оператор EXEC , который следует за оператором ALTER PROC , присваивает значение входному параметру @jobtitle во время выполнения. Значением параметра является строка nvarchar («Production Supervisor»).

Вот вкладка «Results«, на которой показаны выходные данные оператора SELECT внутри uspMyFirstStoredProcedure. На вкладке показан 21 сотрудник, чьи должности начинаются с «Production Supervisor». Вы можете изменить содержимое вкладки «Results», используя другое строковое значение в операторе EXEC .

Параметры вывода хранимых процедур SQL Server

Выходной параметр передает скалярное значение из хранимой процедуры в вызывающую ее инструкцию EXEC . Некоторое предыдущее освещение этой темы можно найти на этой странице руководства. Кроме того, в последующих статьях этой серии по хранимым процедурам будут представлены многочисленные подробные примеры, демонстрирующие, как программировать входные параметры, выходные параметры и значения кода возврата с помощью хранимых процедур.

Если вы просто хотите передать одно значение, такое как сумма или количество, из хранимой процедуры, вы можете сделать это с помощью выходного параметра. Следующий оператор ALTER PROC иллюстрирует один из способов реализации такого рода задач.

В нашем примере оператор ALTER PROC снова изменяет uspMyFirstStoredProcedure.

Входной параметр @jobtitle из предыдущей версии хранимой процедуры сохраняется.

Кроме того, перед ключевым словом as добавляется спецификация выходного параметра:

• Имя выходного параметра — @jobtitlecount .
• Тип данных для параметра — int , потому что он предназначен для хранения значения счетчика, но вы также можете использовать bigint в качестве типа данных, если это необходимо.
• Ключевое слово out завершает спецификацию параметра, чтобы указать, что этот параметр возвращает значение после запуска хранимой процедуры.

Оператор SELECT состоит из вложенного внутреннего оператора выбора внутри внешнего операторе выбора:

• Оператор внутреннего выбора возвращает строку для каждого сотрудника, JobTitle которого начинается со значения входного параметра.
• Внешний оператор select подсчитывает количество строк, возвращаемых внутренним оператором select , и присваивает счет выходному параметру @jobtitlecount .

После выполнения оператора ALTER PROC можно вызвать недавно измененную версию uspMyFirstStoredProcedure и отобразить значение выходного параметра. Следующий сценарий показывает, как этого добиться.

Перед вызовом инструкции EXEC для запуска uspMyFirstStoredProcedure объявите локальную переменную @jobtitlecount для получения значения выходного параметра из хранимой процедуры.

• Значение параметра вывода появляется в операторе EXEC с завершающим ключевым словом вывода OUTPUT . Это ключевое слово указывает, что значение параметра передается из хранимой процедуры в инструкцию EXEC .
• Оператор присваивания (=) передает значение выходного параметра в локальную переменную @jobtitlecount .

Оператор SELECT после оператора EXEC отображает значение локальной переменной @jobtitlecount , которая получила значение выходного параметра.

Значения кода возврата хранимой процедуры SQL Server

Хранимые процедуры могут иметь значения кода возврата, которые всегда имеют тип данных int .

Далее приведём сценарий для установки нулевого или единичного кода возврата внутри хранимой процедуры. Если в столбце существует критерий строки поиска, основанный на входном параметре, то возвращаемое значение устанавливается равным единице. В противном случае возвращаемое значение устанавливается равным нулю.

• Входной параметр имеет имя @jobtitle .
• Критерий предложения where в операторе SELECT : Employee.JobTitle, например ‘%’ + @jobtitle + ‘%’ .
• Когда инструкция SELECT с предложением where возвращает хотя бы одну строку, возвращаемое значение устанавливается равным единице. В противном случае возвращаемое значение устанавливается равным нулю.
• Условие EXISTS определяет, будет ли возвращена хотя бы одна строка из оператора SELECT .
• Предложение return возвращает значение кода возврата и завершает хранимую процедуру.

Следующий сценарий демонстрирует синтаксис для сбора значения кода возврата из хранимой процедуры и показывает некоторый образец кода для обработки значения кода возврата. Скрипт вызывает uspMyFirstStoredProcedure для двух разных значений @jobtitle — sals или sales . Ни одно значение столбца JobTitle не содержит sals , но хотя бы одно значение JobTitle содержит sales .

Сначала в коде объявляются две локальные переменные с именами @jobtitle и @exists .

Локальная переменная @jobtitle используется в инструкции EXEC для передачи строки поиска в uspMyFirstStoredProcedure.

Локальная переменная @exists используется для сбора значения кода возврата из uspMyFirstStoredProcedure. Оператор присваивания внутри оператора EXEC заполняет локальную переменную @exists значением кода возврата.

Оператор потока управления if . else после оператора EXEC обрабатывает возвращаемое значение из хранимой процедуры.

• Если @exists равно нулю, инструкция SELECT сообщает, что нет JobTitle со значением строки поиска во входном параметре.
• Если @exists равно единице, оператор SELECT сообщает, что существует по крайней мере одно значение JobTitle с входным параметром.

Ниже, хранимая процедура выполняется дважды. Первоначальное выполнение предназначено для поисковой строки со значением sals . Второе выполнение — для значения sales в строке поиска.

Вот результат предыдущего скрипта. Вы можете использовать его для подтверждения работы кода, чтобы оценить, содержит ли хотя бы одно значение в столбце строку поиска.

Несколько наборов результатов из хранимой процедуры SQL Server

Следующий сценарий снова демонстрирует, как использовать входной параметр в операторе ALTER PROC . Имя входного параметра перед ключевым словом as — @jobtitle . Эта демонстрация отличается тем, что включает в себя два отдельных оператора SELECT . Первый оператор SELECT возвращает набор результатов, состоящий из всех строк, JobTitle которых начинается со значения входного параметра. Вторая инструкция SELECT возвращает скалярное значение, которое представляет собой количество сотрудников в таблице Employee, JobTitle которых начинается со значения входного параметра.

Оператор EXEC после оператора ALTER PROC вызывает uspMyFirstStoredProcedure. Литеральное строковое значение nvarchar («Production Supervisor») после имени хранимой процедуры является значением входного параметра.

Вот короткий сценарий для вызова предыдущей хранимой процедуры.

Вот вкладка «Results», на которой показаны выходные данные двух операторов SELECT внутри хранимой процедуры с именем uspMyFirstStoredProcedure.

На верхней панели отображается двадцать один сотрудник, чьи должности начинаются с «Production Supervisor».

На нижней панели отображается скалярное значение с количеством сотрудников, чьи должности начинаются с «Production Supervisor».

Как вы понимаете, можно изменить содержимое вкладки «Результаты», используя другое буквальное строковое значение в операторе EXEC .