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). Если с данным клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции.


 

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

74324. Взаимосвязь (объединение) систем передачи и распределения ЭЭ 46.5 KB
  В качестве примера рассмотрим упрощенную принципиальную схему передачи и распределения электроэнергии в крупном промышленном районе показывающую взаимную связь между электростанциями центрами электропитания...
74325. Характеристика устройств автоматики и управления в системах передачи и распределения ЭЭ 32.5 KB
  Характеристика устройств автоматики и управления в системах передачи и распределения ЭЭ. Широко используются устройства режимной и противоаварийной автоматики которые наряду с быстродействующими защитами значительно повышают надежность работы всей системы передачи и распределения ЭЭ. Условия работы и возросшие масштабы современных систем передачи и распределения ЭЭ требуют применения автоматического регулирования взаимосвязанных и разобщенных объектов в составе автоматизированных систем диспетчерского и технологического управления АСДТУ...
74326. Провода и тросы ВЛ. Требования к ним, характеристики материалов, стандартный ряд сечений проводов 44 KB
  Провода и тросы ВЛ. Провода предназначены для передачи электроэнергии. С этой целью применяют провода из наиболее дешевых металлов алюминия стали специальных сплавов алюминия. Хотя медь обладает наибольшей проводимостью медные провода изза высокой стоимости и необходимости для других целей в новых линиях не используются.
74327. Изоляция ВЛ. Типы изоляторов. Номинальное напряжение и изоляция ВЛ 29 KB
  Изоляторы предназначены для изоляции и крепления проводов. По конструкции способу закрепления на опоре изоляторы разделяют на штыревые и подвесные. Штыревые изоляторы применяются для линий напряжением до 10 кВ и редко для малых сечений 35 кВ. Подвесные изоляторы используются на ВЛ напряжением 35 кВ и выше.
74328. Линейная арматура ВЛ 69 KB
  Поддерживающие зажимы применяют для подвески и закрепления проводов ВЛ на промежуточных опорах с ограниченной жесткостью заделки рис. На анкерных опорах для жесткого крепления проводов используют натяжные гирлянды и зажимы натяжные и клиновые рис. Поддерживающая гирлянда рис.
74329. Кабельные линии (КЛ) эл.передачи. типы кабелей, виды кабельной канализации 34 KB
  Кабельная линия КЛ линия для передачи электроэнергии состоящая из одного или нескольких параллельных кабелей выполненная каким-либо способом прокладки. При этом концы жил кабелей освобождают от изоляции и заделывают в соединительные зажимы. На концах кабелей применяют концевые муфты или концевые заделки.
74330. Токопроводы, шинопроводы и внутренние проводки 32 KB
  Токопроводы шинопроводы и внутренние проводки Токопроводом называют линию электропередачи токоведущие части которой выполнены из одного или нескольких жестко закрепленных алюминиевых или медных проводов или шин и относящихся к ним поддерживающих и опорных конструкций и изоляторов защитных оболочек коробов.
74331. Характеристика передачи ЭЭ переменным током 47.5 KB
  Поэтому повышение напряжения при токах в несколько тысяч ампер возможно только с помощью явления электромагнитной индукции и трансформаторов что создает возможность для последующей эффективной передачи электроэнергии переменным током. Потребление электроэнергии производится на относительно низком напряжения сотни тысячи вольт. Доставка ЭЭ от электростанции к электроприемникам в общем случае осуществляется сетями различного класса номинального напряжения т. представлена принципиальная упрощенная схема передачи и распределения ЭЭ...
74332. Характерные значения удельных (погонных) параметров схем замещения и электрических режимов воздушных и кабельных линий электропередачи и соотношения между ними 496 KB
  Волновые параметры реальной линии волновое сопротивление ZB и коэффициент распространения волны γо определяются через ее удельные погонные отнесенные к 1 км параметры: где β0 коэффициент затухания α0 коэффициент изменения фазы фазовый угол. Удобно определять параметры Побразной схемы замещения линии через удельные погонные сопротивления Zo=RojX0 Ом км и проводимости Yo=g0jb0 См км. При этом равномерную распределенность параметров линии по длине учитывают приближенно с помощью поправочных коэффициентов по формулам Z...