Лекция 21: Создание хранимых процедур и управление этими процедурами

В этой лекции вы ознакомитесь с хранимыми процедурами Microsoft SQL Server 2000, а также с их использованием. Сначала мы рассмотрим типы хранимых процедур, используемых в SQL Server. Затем вы узнаете, как создавать ваши собственные хранимые процедуры и управлять этими процедурами, а также как определять параметры и переменные. Для создания хранимых процедур вы можете использовать четыре метода. В этой лекции описывается, как использовать язык Transact-SQL (T-SQL), утилиту SQL Server Enterprise Manager и мастер создания хранимых процедур Create Stored Procedure Wizard. Четвертый метод создания хранимых процедур – использование SQL Distributed Management Objects (SQL-DMO) – не рассматривается здесь, поскольку он относится к прикладному программированию. Как вы увидите, для всех трех методов, описанных в этой лекции, требуется, чтобы вы использовали операторы T-SQL, поэтому уделите особое внимание описанию первого метода в разделе "Использование оператора CREATE PROCEDURE".

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

Хранимая процедура – это набор операторов T-SQL, который компилируется системой SQL Server в единый "план исполнения". Этот план сохраняется в кэш-области памяти для процедур при первом выполнении хранимой процедуры, что позволяет использовать этот план повторно; системе SQL Server не требуется снова компилировать эту процедуру при каждом ее запуске. Хранимые процедуры T-SQL аналогичны процедурам в других языках программирования в том смысле, что они допускают входные параметры и возвращают выходные значения в виде параметров или сообщения о состоянии (успешное или неуспешное завершение). Все операторы процедуры обрабатываются при вызове процедуры. Хранимые процедуры используются для группирования операторов T-SQL и любых логических конструкций, необходимых для выполнения задачи. Поскольку хранимые процедуры сохраняются в виде процедурных блоков, они могут использоваться различными пользователями для согласованного повторяемого выполнения одинаковых задач и даже в различных приложениях. Хранимые процедуры также позволяют поддерживать единый подход к управлению задачей, что помогает обеспечивать согласованное и корректное внедрение любых деловых правил.

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

Использование хранимых процедур может повысить производительность и в других отношениях. Например, использование хранимых процедур для проверки условий сервера может повысить производительность за счет снижения количества данных, которые должны передаваться между клиентом и сервером, и снижения объема обработки, выполняемой на клиентской машине. Для проверки какого-либо условия из хранимой процедуры можно включить в хранимую процедуру условные операторы (например, конструкции IF и WHILE, см. "Расширенное описание T-SQL" ). Логика этой проверки будет обрабатываться на сервере с помощью хранимой процедуры, поэтому вам не потребуется программировать эту логику в самом приложении а серверу не нужно будет возвращать промежуточные результаты клиенту для проверки данного условия. Вы можете также вызывать хранимые процедуры из сценариев, пакетных заданий и интерактивных командных строк с помощью операторов T-SQL, показанных в примерах далее.

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

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

Дополнительная информация. Чтобы найти информацию об использовании курсоров и глобальных курсоров, выполните поиск "Cursors" (Курсоры) во вкладке Search (Поиск) системы Books Online и найдите темы "Cursors" (в Transact-SQL Reference) и "DECLARE CURSOR (T-SQL)".

Имеется три типа хранимых процедур: системные хранимые процедуры, расширенные хранимые процедуры и простые определяемые пользователем хранимые процедуры. Системные хранимые процедуры предоставляет SQL Server, и они имеют префикс sp_. Они используются для управления SQL Server и вывода на экран информации о базах данных и пользователях. Системные хранимые процедуры были введены в "Введение в Transact-SQL и SQL Query Аnalyzer" . Расширенные хранимые процедуры являются динамически подключаемыми библиотеками (DLL), которые может динамически загружать и выполнять SQL Server. Обычно их пишут на C или C++, и они исполняют процедуры, внешние относительно SQL Server. Расширенные хранимые процедуры имеют префикс xp_. Простые определяемые пользователем хранимые процедуры создаются пользователем и настраиваются для выполнения тех задач, которые требуются данному пользователю.

Примечание. Вы не должны использовать префикс sp_ при создании простых определяемых пользователем хранимых процедур. Если SQL Server обнаруживает хранимую процедуру, имеющую префикс sp_, то он сначала ищет эту хранимую процедуру в главной базе данных (master). И если вы создадите, например, хранимую процедуру с именем sp_myproc в базе данных MyDB, то SQL Server сначала будет искать эту процедуру в главной базе данных, а уж затем в пользовательских базах данных. Разумнее назвать процедуру просто myproc.

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

Как уже говорилось, расширенные хранимые процедуры – это библиотеки DLL, которые динамически загружает и выполняет SQL Server. Они выполняются непосредственно в адресном пространстве SQL Server, и вы можете программировать их, используя интерфейс прикладного программирования (API) SQL Server Open Data Services.

Расширенные хранимые процедуры пишут вне SQL Server. Закончив разработку расширенной хранимой процедуры, вы регистрируете ее в SQL Server с помощью операторов T-SQL или через Enterprise Manager.

Дополнительная информация. Дополнительную информацию о расширенных процедурах и примеры см. в SQL Server Books Online.