Как создать процедуру в sql server

Procedures SQL Server

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

Create Procedure

Вы можете создавать свои собственные хранимые процедуры в SQL Server (Transact-SQL). Давайте посмотрим поближе.

Синтаксис

Синтаксис Procedures в SQL Server (Transact-SQL):

Параметры или аргументы

schema_name — имя схемы, которой принадлежит хранимая процедура.
procedure_name — имя для назначения этой процедуры в SQL Server.
@parameter — один или несколько параметров передаются в процедуру.
type_schema_name — схема, которая владеет типом данных, если это применимо.
Datatype — тип данных для @parameter .
VARYING — задается для параметров курсора, когда результирующий набор является выходным параметром.
default — значение по умолчанию для назначения параметру @parameter .
OUT – это означает, что @parameter является выходным параметром.
OUTPUT — это означает, что @parameter является выходным параметром.
READONLY — это означает, что @parameter не может быть перезаписана хранимой процедурой.
ENCRYPTION — это означает, что источник хранимой процедуры не будет сохранен как обычный текст в системных представлениях SQL Server.
RECOMPILE — это означает, что план запросов не будет кэшироваться для этой хранимой процедуры.
EXECUTE AS — устанавливает контекст безопасности для выполнения хранимой процедуры.
FOR REPLICATION — Это означает, что хранимая процедура выполняется только во время репликации.

Пример

Рассмотрим пример создания хранимой процедуры в SQL Server (Transact-SQL).
Ниже приведен простой пример процедуры:

Хранимые процедуры в T-SQL — создание, изменение, удаление

В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.

Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.

Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:

; ; ;
• Если Вы хотите освоить язык SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL.

Что такое хранимые процедуры в T-SQL?

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

Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT, например, как функцию уже не получится, т.е. процедуры запускаются отдельно.

В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).

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

Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.

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

Исходные данные для примеров

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

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

Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE

Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.

Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).

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

Вот код данной процедуры (его я также прокомментировал).

Запуск хранимой процедуры на T-SQL – команда EXECUTE

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

Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.

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

Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE

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

Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.

Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE

В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.

Например, давайте удалим созданную нами тестовую процедуру.

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

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

Использование хранимых процедур позволяет организовать бизнес-логику и логику управления данными на стороне базы данных.

В чем плюсы такого подхода?

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

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

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

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

Для создания хранимой процедуры требуется выполнить следующие шаги:

выбираем базу данных, переходим на вкладуку «Программирование/Хранимые процедуры»

Создаем хранимую процедуру через контекстное меню:

Видим вот такой код:

Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:

После этого нажимаем «Выполнить» (F5) и видим слева нашу хранимую процедуру:

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

После чего увидим результат выполнения нашей хранимой процедуры:

Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем «выполнить» или F5):

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

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

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

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

Параметры также могут быть выходными — т.е. их значение изменено в процедуре и возвращено в вызывающую сторону.

Использование if

Пример хранимой процедуры с условием if:

В данной процедуре выбираем максимальную сумму премии у студентов, в зависимости от результата выводим нужную строку. Также следует обратить внимание на выражение:

DECLARE применяется д ля определения переменных , после ключевого слова «DECLARE» указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @. В данном примере определяем переменную «@maxAward» с типом данных «money».

В нашем случае результат будет такой:

Циклы в хранимых процедурах SQL Server

Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:

Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.

Результатом выполнения данного кода будет:

Также следует обратить внимание на ключевое слово «PRINT», которое выводит результат нашего кода:

Инструкция OUTPUT

OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

OUTPUT может помочь в тех случаях, когда нужно проверить или узнать какие именно строки (записи) были добавлены, удалены или изменены, без дополнительных запросов на выборку (SELECT). Данная инструкция позволяет сохранить все изменения в отдельном месте, например, в таблице, благодаря этому мы можем узнать и работать, например, со списком всех идентификаторов, которые были сгенерированы и добавлены в таблицу.

Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

Преобразование типов данных для переменных

Функция CAST преобразует выражение одного типа к другому и имеет следующую форму:

Пример. Есть такой код:

Преобразуем числовое значение «award».

Результатом данного кода будет:

Также есть функция TRY_CAST для преобразования данных. Функция TRY_CAST пытается преобразовать выражение из одного типа данных в другой тип данных. Если преобразование не удалось, функция вернет NULL. В противном случае вернет преобразованное значение.

Пример, следующий код пытается преобразовать строку «test» к типу float:

Результатом данного кода будет:

Отличие TRY_CAST от CAST заключается в том, что если преобразование не удалось, то TRY_CAST вернет NULL, а CAST вызовет исключение.

Конкатенация строк

Функция CONCAT , которая выполняет конкатенацию, неявно преобразуя типы аргументов к строковому типу данных. Есть следующий код:

Результат данного кода будет следующий:

Данная функция склеивает firstName и lastName в один столбец.

Стандартные функции для работы с датой и временем

Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:

Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)

Работа с NULL через ISNULL, NULLIF

В условиях вы можете проверить равно ли какое то выражение через такую конструкцию:

Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись «неизвестно»:

Результат этого запроса ниже:

Последняя строка поле email было заменено на «неизвестно», т.к. имеет значение NULL.

Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:

возвращает NULL для первого столбца (4 и 4), потому что два входных значения одинаковы. Второй столбец возвращает первое значение (5), потому что два входных значения различны.

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

На мой взгляд, возможности объектов просмотра слишком малы и далеки от идеала. Главная их проблема — статичность. Чтобы получить новый результат (добавить или изменить критерий поиска) приходится изменять саму хранимую процедуру, что достаточно проблематично и большая часть преимуществ объектов просмотра просто теряется.

Хранимые процедуры – это именованный набор операторов Transact-SQL хранящийся на сервере. Хранимые процедуры – это метод выполнения повторяющихся задач и при этом обладают большими возможностями, чем объекты просмотра.

Сервер SQL поддерживает 5 типов встроенных процедур:

• системные хранимые процедуры – хранятся в базе данных master. Система хранит процедуры (определяющиеся по префиксу sp_) предоставляющие эффективные методы получения информации из системных таблиц. Они позволяют системному администратору выполнять администраторские задачи над базой данных, которые обновляют необходимые таблицы напрямую. Системные встроенные процедуры могут быть выполнены из любой базы данных;
• локальные хранимые процедуры – создаются в определенных пользовательских таблицах;
• временные хранимые процедуры – могут быть локальными с именами, начинающимися с единичного знака # или глобальными начинающимися со знака ## (как и локальные/глобальные временные таблицы). Локальные временные процедуры доступны только в единственной пользовательской сессии. Глобальные – доступны всем пользователям. Как и для таблиц, так и для процедур я не рекомендую использовать временные процедуры. Я еще не встречался с такой задачей, которую нельзя было решить без временных процедур;
• удаленные хранимые процедуры – устаревшая технология MS SQL Server. На данные момент эту задачу решают распределенные запросы;
• расширенные встроенные процедуры (содержат в имени префикс xp_) – разрабатываются в виде DLL (Dynamic Link Library, динамически подгружаемая библиотека) и выполняются вне окружения SQL Server. Обычно такие процедуры идентифицируются по префиксу xp_.

Хранимые процедуры в MS SQL Server похожи на процедуры в других языках программирования. Если вы имели опыт программирования на каком-либо языке и не понаслышке знаете о таком понятии как процедуры, то материал этой главы покажется вам слишком простым. Но уровень подготовки читателей может быть разным, поэтому я постараюсь описать все максимально простым и доступным языком.

Итак, процедура — это блок из одной или более команд. Это может быть не просто один запрос, а целая программа, с собственной логикой (операторы IF), циклами. Процедура может принимать заранее определенные переменные и использовать их в своих расчетах, благодаря чему, результат работы процедуры может быть динамическим, и будет зависеть от определенных условий и/или состояния получаемых переменных.

В процедуре вы можете:

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

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

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

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

Некоторые изменения в базе данных могут изменить план выполнения, делая его неэффективным или недействительным. Сервер определяет эти изменения и автоматически перекомпилирует план, когда возникает что-то из следующего:

• любые структурные изменения сделанные в таблице или в объекте просмотра ссылающемся в запросе (ALTER TABLE или ALTER VIEW);
• сгенерирована новая статистика с помощью оператора UPDATE STATISTIC;
• индекс, который использовался планом выполнения, удален;
• сделаны значительные изменения в ключах (операторы INSERT, DELETE).

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

3.2.1. Создание хранимых процедур

На этом теорию на время остановим и посмотрим, как на практике создаются хранимые процедуры. Для этого используется оператор CREATE PROCEDURE, который выглядит следующим образом:

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

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

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

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

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

Как видите, в начало запроса всего лишь добавляется две строки (хотя, можно написать и в одну). В первой мы пишем операторы CREATE PROCEDURE и имя процедуры, а во второй строке ключевое слово AS. После этого идет простой запрос SELECT, который выбирает данные.

3.2.2. Выполнение процедур

Чтобы разговор был более продуктивным, давайте рассмотрим, как можно выполнять процедуры. Для этого используется оператор EXECUTE, который выглядит следующим образом:

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

Результат выполнения команды:

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

3.2.3. Удаление процедур

Теперь посмотрим, как можно удалять процедуры. Для этого используется оператор DROP PROCEDURE, который позволяет удалять несколько процедур сразу. В общем виде этот оператор выглядит:

Изменение процедур рассмотрим чуть позже, потому что у нас итак достаточно много информации и нужно рассмотреть немного примеров, чтобы на практике закрепить все возможности процедур.

3.2.4. Использование параметров

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

Параметры перечисляются через запятую после имени процедуры в виде имя тип. Я выделил параметры отдельной строкой (вторая), которая идет после имени процедуры, но до ключевого слова AS. В данном примере только один параметр с именем @Famil и типом varchar длиной в 50 символов.

Имена параметров подчиняются тем же правилам именования, что и переменные и используются также. В данном примере, в секции WHERE происходит сравнение поля «vcName» с параметров @Famil.

Чтобы выполнить процедуру с параметром, нужно написать следующий запрос:

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

Необходимо заметить, что некоторые программы, например, Query Analyzer, не требуют писать оператор EXEC. Достаточно написать имя процедуры и перечислить параметры:

Но такой пример может сработать далеко не всегда, вернее, не во всех программах. Я рекомендую всегда писать вначале оператор EXECUTE или сокращенно EXEC.

Параметры нужно передавать в том же порядке, как они были указаны в объявлении, но можно сделать и отступление, если указывать их в виде имя=значение. Например, в процедуре GetPhones параметр называется @Famil. Это значит, что мы можем вызвать процедуру следующим образом.

В этом примере, после имени процедуры мы пишем имя параметра и присваиваем ему значение знаком равенства.

3.2.5. Преимущества хранимых процедур

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

Хранимые процедуры представляют множество преимуществ, среди которых можно выделить следующее:

• разделение кода — во время решения различных задач очень часто приходится выполнять одни и те же действия. Чтобы не писать один и тот же код в разных задачах, их можно вынести в отдельную процедуру
• разделение логики приложений с другими, таким образом, гарантируется совместимый доступ и модификация данных;
• если процедуры поддерживают все бизнес функции, которые нужны для выполнения пользователю, пользователь никогда не нуждается в прямом доступе к таблицам. Все можно делать через процедуры, которые могут выступать дополнительным гарантом целостности данных и безопасности базы данных;
• предоставляет механизм защиты. Пользователи могут получать право выполнять процедуры, даже если у них нет права на использование вьюшки или таблицы, на которую ссылается процедура;
• повышение производительности, за счет выполнения множество задач, как набор операторов Transact-SQL и хранения плана выполнения в кэше.

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

3.2.6. Практика создания и использования процедур

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

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

Итак, давайте создадим такую процедуру (см. листинге 3.1).

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

Для выполнения процедуры выполним следующий запрос:

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

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

Благодаря явному указанию имен, порядок может быть любым.

Процедуры могут быть вложенными (одна процедура может вызывать другую). Вложенные процедуры должны удовлетворять следующим условиям:

• процедуры могут быть вложены до 32 уровней. Если более 32 уровней, то происходит ошибка;
• текущей уровень вложенности хранится в системной переменной @@nestlevel;
• если первая процедура вызывает вторую, то вторая может получить доступ ко всем объектам первой, включая временные таблицы, потому что они в этот момент существуют;
• встроенные процедуры могут быть рекурсивными. Например, если процедура 1 вызвала процедуру 2, то процедура 2 может вызвать первую;

Давайте рассмотрим вызов пользовательской процедуры из другой. Для этого создадим следующий вариант процедуры добавления товара:

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

Желательно чтобы права на объекты, которые используются в процедуре и сама процедура принадлежали одному и тому же пользователю. Для исключения ситуации, когда владелец процедуры и таблицы, на которую ссылается процедура, различны, все объекты должны принадлежать dbo. Я уже не раз говорил об этом и напоминаю еще раз – без особой надобности не указывайте владельцев. Лучше всего будет, если объекты будут принадлежать пользователю dbo.

Старайтесь создавать процедуры так, чтобы они выполняли по одной задаче. Дело в том, что одна из задач может в сочетании с другими решениями. Например, если бы мы добавили проверку даты, которую мы сделали в процедуре AddGoods1 в процедуру AddGoods, то нельзя было бы добавить товар, с датой более текущей. А так как у нас каждая процедура выполняет небольшую задачу, пользователям можно дать возможность выполнять более защищенную AddGoods1, а администраторы могут иметь возможность работы с AddGoods и при особой надобности добавлять товары с любой датой.

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

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

Выполнив команду EXEC TestData, на экране появится таблица с одной только строкой. Но процедура все же выполняет запрос SELECT, а ведь можно обойтись и без него, если достаточно только вывести на экран строку. Для вывода на экран достаточно воспользоваться оператором PRINT:

3.2.7. Изменение процедур

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

Если вы хотите изменить процедуру, которая была создана с какими-нибудь опциями, например WITH ENCRYPTION, вы должны включить эти опции в опции ALTER PROCEDURE, для сохранения функциональности, которую предоставляет опция.

Во время выполнения оператора ALTER PROCEDURE изменяется только одна процедура. Если она ссылается на другие, то они не изменяются.

Оператор ALTER PROCEDURE в общем виде выглядит следующим образом:

Следующий пример изменяет процедуру AddGoods1:

Изменения произошли в последнем параметре — @Number. Я установил для него значение по умолчанию 1. Теперь при вызове можно указывать только три значения. Если количество не указано, то будет использоваться значение 1.

3.2.8. Использование процедур при вставке данных

Оператор INSERT может заполнять локальную таблицу результирующим набором, который возвращается из локальной или удаленной процедуры. Сервер SQL заполняет таблицу данными, которые возвращаются оператором SELECT в процедуре. Таблица должна существовать и типы данных должны совпадать.

Рассмотрим эту возможность. Для начала создадим процедуру, которая будет выбирать данные на определенную дату:

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

Данный запрос вставляет в таблицу товаров результат выполнения процедуры GetGoods. Количество и типы полей должны совпадать, а у нас они будут совпадать, потому что процедура выбирает данные из той же таблицы товаров данные за указанную дату.

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

3.2.9. Опции

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

1. RECOMPILE – указывает на то, что MS SQL Server не должен сохранять план выполнения, компиляция будет происходит при каждом выполнении;
2. ENCRYPTION – запись в таблице syscomments с текстом процедуры должна шифроваться.

Посмотрим, как можно использовать шифрование:

Опция WITH ENCRYPTION указывается после всех параметров процедуры, но до ключевого слова AS.

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

В колонке text вы увидите только бессмысленные символы. Для любой другой процедуры здесь будет ее текст, а для зашифрованной будут данные, которые не несут полезной информации.

Давайте посмотрим на запрос, который мы использовали для получения информации о процедуре. Здесь у нас выбираются данные из двух таблиц sysobjects и syscomments. В первой таблице находятся имена всех объектов базы данных, а в таблице syscomments находятся параметры объекта. Для хранимой процедуры здесь можно увидеть текст самой процедуры в поле «text», если он не зашифрован.