Главная » Microsoft SQL Server, Базы данных » Получение данных из хранимой процедуры

0

SQL Server предлагает четыре способа получения данных из хранимой процедуры. Пакет может вернуть данные из инструкции SELECT или команды raiseerror. Хранимые процедуры используют эти методы пакетов и добавляют к ним два собственных: выходные переменные и команду output.

Выходные параметры

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

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

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

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

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

2.              В пакете вызывается хранимая процедура с указанием имени выходного параметра @ProdName.

3.              В хранимой процедуре локальная переменная @ProdName, соответствующая переданному выходному параметру, создается в заголовке; ее изначальное значение равно null.

Теперь следует подготовить данные для выходного параметра @ProductName.

4.              Инструкция SELECT в теле хранимой процедуры присваивает переменной @ProductName значение Basic Box Kite 21 inch, соответствующее товару с кодом 10 01.

5.              Хранимая процедура завершает выполнение, и управление передается вызывающему пакету; при этом полученное значение @ProductName передается его одноименной локальной переменной.

6.              Вызывающий пакет с помощью команды print выводит полученное значение на экран. Ниже приведен текст хранимой процедуры.

USE OBXKites; go

CREATE PROC GetProductName (

@ProductCode CHAR(10),

@ProductName VARCHAR(25) OUTPUT )

AS

SELECT @ProductName = ProductName FROM dbo.Product WHERE Code = @ProductCode;

А это текст вызывающего пакета:

USE OBXKITES;

DECLARE @ProdName VARCHAR(25);

EXEC GetProductName ‘1001’, @ProdName OUTPUT;

PRINT @ProdName;

Будет получен следующий результат:

Basic Box Kite 21 Inch

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

Использование команды return

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

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

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

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

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

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

CREATE PROC IsItOK (

@ОК VARCHAR(10) )

AS

IF @0K = ‘OK1 RETURN 0 ELSE

RETURN -100;

А вот пример вызывающего пакета:

DECLARE @ReturnCode INT;

EXEC @ReturnCode = IsITOK ‘OK';

PRINT @ReturnCode;

EXEC @ReturnCode = IsItOK ‘NotOK';

PRINT @ReturnCode;

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

о

-100

Маршруты и область определения возвращаемых данных

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

?               Команда return и выходные параметры передают значения локальным переменным вызывающего пакета.

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

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

Рис. 21.2. Маршруты и область определения возвращаемых данных зависят от используемого метода

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

На рис. 21.2 процедура Б может выполнить любые инструкции DML относительно любой временной таблицы, созданной процедурой А. После завершения процедуры Б эти данные станут доступны процедуре А.

rv     С любым возвращаемым набором данных SQL Server по умолчанию будет по-

vcZL     сылать сообщение о количестве обработанных или возвращенных строк. Это

^ ^Й/Ь  всего лишь небольшой нюанс, но при неформальном тестировании я обнару-

жил> что это замедляет выполнение запроса на 17%.

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

AS

SET NoCount ON

Более полную информацию о конфигурировании подключений вы получите в главе 34.

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

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

Функция Openquery () посылает сквозной запрос внешнему источнику данных для удаленного выполнения. Если в функции openquery () указана хранимая процедура, то ее результаты передаются на локальный сервер.

Дополнительная Подробно о функции openquery () см. в главе 15.

информация

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

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

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

SELECT * FROM OpenQuery(

NOLI

‘EXEC OBXKites.dbo.pProductCategory_Fetch’)

WHERE ProductCategoryDescription Like ‘%stuff%’

Результат выполнения запроса:

ProductCategoryName ProductCategoryDescription

OBX                              OBX stuff

Toy                                Kids stuff

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии