15817

Microsoft SQL Server 2005. Хранимые процедуры

Лекция

Информатика, кибернетика и программирование

Microsoft SQL Server 2005. Хранимые процедуры Хранимые процедуры Хранимая процедура это наиболее часто используемая в базах данных программная структура представляющая собой оформленный особым образом сценарий вернее пакет который хранится в базе данных а не в отдельном ...

Русский

2013-06-18

87 KB

18 чел.

Microsoft SQL Server 2005. Хранимые процедуры

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

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

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

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

 Управление хранимыми процедурами Хранимые процедуры управляются посредством инструкций языка определения данных (DDL) CREATE, ALTER и DROP. Общий синтаксис T-SQL кода для создания хранимой процедуры имеет следующий вид:

 CREATE PROC | PROCEDURE <procedure_name> [ <@parameter> <data_type> [ = <default> ] [ OUT | OUTPUT ] ] [ ,...n ] AS [ BEGIN ] <sql_statements> [ END ] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE_AS_Clause ]

 Структура этого оператора соответствует основному синтаксису CREATE <Object Туре> <Object Name>, лежащему в основе любого оператора CREATE. Единственная отличительная особенность состоит в том, что в нем допускается использовать ключевое слово PROCEDURE или PROC. Оба эти варианта являются допустимыми: PROC является лишь сокращением от PROCEDURE.
Каждая процедура должна иметь уникальное в рамках базы данных имя (procedure_name), соответствующее правилам для идентификаторов объектов.

 Процедуры могут иметь любое число входных параметров (@parametr) заданного типа данных (data_type), которые используются внутри процедуры как локальные переменные. При выполнении процедуры для каждого из объявленных формальных параметров должны быть переданы фактические значения. Или же для входного параметра может быть определено значение по умолчанию (default), которое должно быть константой или равняться NULL. В этом случае процедуру можно выполнить без указания значения соответствующего аргумента. Применение входных параметров необязательно.

 Можно также указать выходные параметры (помеченные как OUTPUT), позволяющие хранимой процедуре вернуть одно или несколько скалярных значений в подпрограмму, из которой она была вызвана. При создании процедур можно задать три параметра. При создании процедуры с параметром ENCRYPTION SQL Server шифрует определение процедуры. При задании параметра RECOMPILE SQL Server перекомпилирует хранимую процедуру при каждом ее запуске. Параметр EXECUTE AS определяет контекст безопасности для процедуры.
В конце определения хранимой процедуры вслед за ключевым словом AS должно быть приведено непосредственно тело процедуры (sql_statements) в виде кода из одной или нескольких инструкций языка T-SQL.

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

 Пример хранимой процедуры без параметров

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

 CREATE PROCEDURE spr_getOrders AS SELECT IdOrd, IdCust, OrdDate FROM [Order] WHERE (OrdDate >= '01.01.2010') RETURN

 Чтобы протестировать новую процедуру, откройте новый запрос SQL Server и выполните следующий код.

 EXEC spr_getOrders

 Команда EXECUTE или сокращенно EXEC выполняет указанную хранимую процедуру.
В данном случае хранимая процедура вернет все строки из таблицы Order, в которых значение поля OrdDate больше 1 января 2010 года, в соответствии с содержащимся в нем запросом на выборку.

 Применение входных параметров

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

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

 @parameter_name [AS] datatype [= default|NULL]

 Правила определения входных параметров во многом аналогичны объявлению локальных переменных. Каждый из параметров должен начинаться с символа @. Для хранимой процедуры он является локальной переменной. Как и все локальные переменные, параметры должны объявляться с допустимыми встроенными или определяемыми пользователями типами данных СУБД SQL Server.

 Значительные различия между объявлениями параметров хранимых процедур и объявлениями переменных начинают впервые обнаруживаться, когда дело касается значений, заданных по умолчанию. Прежде всего, при инициализации переменным всегда присваиваются NULL-значения, а на параметры это правило не распространяется. В действительности, если в объявлении параметра не предусмотрено заданное по умолчанию значение, то подразумевается, что этот параметр должен быть обязательным и что при вызове хранимой процедуры должно быть указано его начальное значение. Чтобы задать предусмотренное по умолчанию значение, необходимо добавить знак равенства (=) после обозначения типа данных, а затем указать применяемое по умолчанию значение. Благодаря этому пользователи получают возможность при вызове хранимой процедуры принимать решение о том, следует ли задать другое значение параметра или воспользоваться значением, предусмотренным по умолчанию.

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

 ALTER PROCEDURE [dbo].[spr_getOrders] @dateBegin datetime, @dateEnd datetime AS SELECT IdOrd, IdCust, OrdDate FROM [Order] WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd) 

RETURN

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

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

 EXEC spr_getOrders '01.01.2010', '01.07.2010'

 EXEC spr_getOrders @dateBegin = '01.01.2010', @dateEnd = '01.07.2010'

EXEC spr_getOrders '01.01.2010', @dateEnd = '01.07.2010' 

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

 Применение выходных параметров

Выходные параметры позволяют хранимой процедуре возвращать данные вызывающей программе. Для определения выходных параметров используется ключевое слово OUT[PUT], которое обязательно как при определении процедуры, так и при ее вызове. В самой хранимой процедуре выходные параметры являются локальными переменными. В вызывающей процедуре или пакете выходные переменные должны быть предварительно определены, чтобы получить результирующие значения. Когда выполнение хранимой процедуры завершается, текущее значение параметра передастся локальной переменной вызывающей программы.

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

 CREATE PROCEDURE spr_addProduct @Description nvarchar(100), @InStock int = 0, @IdProd int OUT AS

INSERT Product([Description], InStock) VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN

 Пример вызова:

 DECLARE     @IdProd int

EXEC spr_addProduct  @Description = N'Новый товар', @IdProd = @IdProd OUTPUT

SELECT      @IdProd as N'@IdProd' 

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

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

 Любая вызываемая на выполнение хранимая процедура возвращает значение, независимо от того, предусмотрен ли в ней возврат значения или нет. По умолчанию после успешного завершения процедуры СУБД SQL Server автоматически возвращает значение, равное нулю.
Чтобы передать некоторое возвращаемое значение из хранимой процедуры обратно в вызывающий код, достаточно применить оператор RETURN:

 RETURN [<Целое число>]

 Обратите внимание на то, что возвращаемое значение должно быть обязательно целочисленным.

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

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

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

 ALTER PROCEDURE [dbo].[spr_addProduct] @Description nvarchar(100), @InStock int = 0, @IdProd int OUT AS

IF EXISTS (SELECT * FROM Product WHERE [Description] = @Description) RETURN -100

INSERT Product([Description], InStock) VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN 0

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

 EXEC @локальная_переменная = имя_хранимой_процедуры;

 DECLARE     @return_value int, @IdProd int

EXEC  @return_value = spr_addProduct @Description = N 'Новый товар', @IdProd = @IdProd OUTPUT

IF @return_value = 0 BEGIN PRINT 'Товар успешно добавлен' SELECT @IdProd as N'@IdProd' END

ELSE BEGIN PRINT 'При добавлении товара произошла ошибка'SELECT 'Return Value' = @return_value END 

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

 Возврат списка всех заказов содержащих заданный товар (по IdProd).Определение количества клиентов, не имеющих ни одного заказа. Результат должен возвращаться через выходной параметр.Удаление из базы данных информации об определенном клиенте (по IdCust). Если с данным клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции.


 

А также другие работы, которые могут Вас заинтересовать

46020. РАСЧЕТ ЦИФРОВОЙ РАДИОРЕЛЕЙНОЙ ЛИНИИ СВЯЗИ 6.51 MB
  Выбор высот антенн и антенных опор является одной из основных задач при проектировании или реконструкции цифровых радиорелейных линий. От правильного выбора высот антенн в конечно итоге зависят затраты на строительство и качество каналов передачи
46023. Основы отношений со СМИ в правовом поле. Ответственность за нарушение прав журналиста и редакции 23.49 KB
  Под массовой информацией понимаются предназначенные для неограниченного круга лиц печатные аудио аудиовизуальные и иные сообщения и материалы; под средством массовой информации понимается периодическое печатное издание радио теле видеопрограмма кинохроникальная программа иная форма периодического распространения массовой информации; Статья 4. Недопустимость злоупотребления свободой массовой информации Не допускается использование средств массовой информации в целях совершения уголовно наказуемых деяний для разглашения сведений...
46025. СЕРТИФІКАЦІЯ ТОВАРІВ І ПОСЛУГ 345 KB
  Товар (процес, послуга), представлений на сертифікацію, відбирається і досліджується органом з сертифікації за певною визначеною схемою (типові випробування зразків продукції, які відібрані в торгівлі або підприємстві виробника, дослідження системи, якості продукції на виробництві та ін.).
46026. Сбытовая политика 26.5 KB
  Сбытовая стратегия – это долго и среднесрочные решения по формированию и изменению сбытовых каналов а также процессов физического перемещения товаров во времени и пространстве в рыночных условиях. Задача сбытовой политики – управление конкурентоспособностью товара путем управлением каналами сбыта планирование длины ширины и типа посредников; организация и контроль канала управлением товародвижением планирование процессов хранения товаров процессов грузовой обработки товара процессов транспортировки; организация и контроль процессов...