Опубликован: 02.02.2007 | Доступ: свободный | Студентов: 2374 / 243 | Оценка: 4.10 / 3.86 | Длительность: 26:44:00
Урок 27:

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

< Урок 26 || Урок 27: 12 || Урок 28 >
Аннотация: Хранимая процедура – это набор инструкций T-SQL, выполняемый как единое целое. Для создания хранимой процедуры используется инструкция CREATE PROCEDURE, а для выполнения хранимой процедуры – инструкция EXECUTE или соответствующая функция используемого приложением программного интерфейса или доступа к SQL Server. Рассматриваются многие типы хранимых процедур, их создание и выполнение. Также рекомендуется дополнительно использовать справочную систему Books Online.

Вы научитесь:

  • выполнять простые хранимые процедуры;
  • выполнять хранимые процедуры с входными параметрами;
  • выполнять хранимые процедуры с именованными параметрами;
  • выполнять хранимые процедуры с использованием ключевого слова DEFAULT;
  • выполнять хранимые процедуры с выходными параметрами;
  • выполнять хранимые процедуры с возвращаемыми значениями;
  • создавать простые хранимые процедуры;
  • создавать хранимые процедуры с входными параметрами;
  • создавать хранимые процедуры со значениями параметра по умолчанию;
  • создавать хранимые процедуры с выходными параметрами;
  • создавать хранимые процедуры с возвращаемыми значениями.

Мы рассмотрели SQL-сценарии, представляющие собой пакеты операторов Transact-SQL, хранимые в текстовом файле. SQL Server также поддерживает хранимые процедуры, которые представляют собой пакеты операторов Transact-SQL, сохраняемые сервером. Теперь познакомимся с созданием и использованием хранимых процедур.

Понятие о хранимых процедурах

Хранимые процедуры – не единственное средство выполнения операторов Transact-SQL. Мы уже сталкивались с SQL-сценариями и с возможностью передавать команды непосредственно из приложения. Однако хранимые процедуры обладают рядом преимуществ:

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

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

SQL-сценарии, с которыми мы работали, выполнялись независимо – у нас не было никакой возможности передать им какую-либо информацию, а единственная информация, которую они возвращали, отображалась в панелях сетки Grid или в панели сообщений Message Pane окна Query (Запрос). Хранимые процедуры предоставляют два метода взаимодействия с внешними процессами: через параметры и через возвращаемые значения.

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

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

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

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

Хранимые процедуры делятся на две группы: системные процедуры, создаваемые SQL Server, и пользовательские хранимые процедуры, которые вы создаете самостоятельно. Системные хранимые процедуры хранятся в главной базе данных. Все они начинаются с символов sp_.

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

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

В главной базе данных около сотни системных процедур. Многие из них предоставляют средства для программного выполнения задач администрирования, рассмотренных нами в части 1. Например, процедура sp_addlogin позволяет вам добавлять идентификатор учетной записи, а процедура sp_add_jobschedule дает возможность составлять расписание заданий, таких как резервное копирование базы данных.

Примечание. Детальная информация обо всех системных хранимых процедурах содержится в документации SQL Server Books Online.

Другие системные процедуры помогают вам управлять объектами базы данных. Например, процедура sp_rename дает возможность переименовывать объекты базы данных, а процедура sp_renamedb предоставляет средства для переименования базы данных.

Совет. Единственным способом переименования базы данных является использование системной процедуры sp_renamedb. Это действие не может быть выполнено в Enterprise Manager.

Важная группа системных процедур предоставляет информацию о текущем статусе системы: процедура sp_who предоставляет информацию о текущих пользователях и процессах; процедура sp_cursor_list предоставляет список текущих курсоров для данного соединения; процедура sp_helpdb предоставляет список всех текущих баз данных, обслуживаемых сервером, а также сообщает вам физическое местоположение файла данных и журнала транзакций для любой заданной базы данных. Вы также можете воспользоваться процедурой sp_help для получения информации об объектах базы данных. В эту информацию входят: имя, владелец и тип каждого объекта базы данных, сведения о системных и пользовательских типах данных, а также имена и параметры хранимых процедур.

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

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

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

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

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

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

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

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

EXECUTE имя_процедуры

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

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

  1. Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  2. Введите в окне запроса Query следующий оператор:
    EXECUTE sp_helpdb
  3. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты.

Внимание! Поскольку процедура sp_help отображает все базы данных, имеющиеся на текущем сервере, ваша структура может не совпадать с той, которая представлена на рисунке. В вашей панели сетки Grid Pane могут содержаться другие базы данных.

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

EXECUTE имя_процедуры параметр [ , параметр ...]

Использование Object Browser для работы с хранимыми процедурами

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

Для создания сценария EXECUTE для хранимой процедуры вы также можете воспользоваться командами скриптования из контекстного меню. Сценарий EXECUTE в Object Browser создает включения объявлений локальных переменных для возвращаемых значений и выходных параметров.

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

  1. Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer.
  2. Введите следующий оператор в окне Query (Запрос):
    EXECUTE sp_dboption 'Aromatherapy', 'read only'
  3. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результаты.

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

EXECUTE хранимая_процедура @имя_парам = значение [, @имя_парам = значение ...]

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

  1. Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer.
  2. Введите следующий оператор в окне Query (Запрос):
    EXECUTE sp_dboption @optname = 'read only',
    @dbname = 'Aromatherapy'
  3. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результаты.

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

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

Выполните хранимую процедуру с использованием ключевого слова DEFAULT

  1. Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer.
  2. Введите следующий оператор в окне Query (Запрос):
    EXECUTE sp_dboption DEFAULT, 'read only'
  3. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результаты.

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

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

  1. Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer.
  2. Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

  3. Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий TableValidation и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

  4. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит сценарий и отобразит результаты.

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

EXECUTE @имя_переменной = хранимая_процедура [, парам [, парам ...] ]

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

Выполните хранимую процедуру с возвращаемым значением

  1. Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

  2. Выделите сценарий ReturnValue и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

  3. Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результаты.

  4. Выберите вкладку Message (Сообщение). Query Analyzer отобразит результаты выполнения оператора PRINT, который выводит возвращаемое значение.

< Урок 26 || Урок 27: 12 || Урок 28 >
Инна Грушецкая
Инна Грушецкая
Сергей Чесноков
Сергей Чесноков