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

6.2. Возврат результатов хранимых процедур

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

через фактические параметры типов OUT и INOUT ;
путем формирования результирующего множества;
используя оператор RETURN .

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

Для того, чтобы проверить работу процедуры Get_number_absent необходимо выполнить следующую последовательность SQL-операторов:

В результате получим, у читателя с номером читательской карточки ( N_reader) 80 на руках находятся четыре книги.

В процедуре Get_number_absent используется только один параметр тира OUT . В общем случае параметров такого типа в процедурах может быть несколько. По этой причине результат, формируемый процедурой Get_number_absent можно получить и с использованием оператора RETURN . Данный оператор возвращает в качестве результата одно значение. Для демонстрации этой возможности создадим процедуру Num_absent на базе приведенной выше процедуры Get_number_absent, путем незначительной модификации последней:

Чтобы убедиться в работоспособности процедуры Num_absent следует воспользоваться такой последовательностью SQL-операторов:

Эта процедура выдает те же данные, что и Get_number_absent.

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

Результирующее множество формируется оператором SELECT . Этот оператор всегда формирует такое множество, если в нем не используется отсутствует служебное слово INTO . Данное служебное слово применяется для указания переменных, в которые будут записываться результаты выполнения оператора SELECT .

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

При описании параметров оператора RESULT их количество и типы должны соответствовать количеству и типам элементов списка выбора оператора SELECT . При этом имена этих параметров и элементов могут не совпадать друг с другом. Для проверки процедуры Get_list_absent выполните ее:

Теперь проверьте полученные результаты. Они будут представлены в окне Data утилиты ISQL. Содержимое результатов представлено в табл. 23.

Таблица 23. Результаты выполнения процедуры Get_list_absent(80)

Author	Title_book	City_pub-lish	Publisher	Year_pub-lish
Гмурман В.Е.	Теория вероятностей и математическая статистика.Учебное пособие для студентов ВТУЗов	Москва	Высшая школа	1972
Гмурман В.Е.	Руководство к решению задач по теории вероятностей и математической статистике.Учебное пошкола пособие для студентов ВТУЗов	Москва	Высшая школа	1979
Дектярев Ю.И.	Методы оптимизации	Москва	Советское радио	1980
Габасов Р.	Методы оптимизации	Минск	БГУ	1981

При выполнении процедуры, формирующей результирующее множество, создается временная таблица — курсор ( CURSOR ). В курсор записывается результирующее множество. В дальнейшем пользователь может обрабатывать данные курсора по-своему усмотрению. Это будет обсуждаться в следующем п.5.3.

Следует обратить особое внимание на использование оператора RESUME в связи с результирующим множеством. Он продолжает выполнение процедурыв среде утилиты ISQL в связи с ее обязательной остановкой после формирования такого множества.

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

Допустим, что процедура приостановила свое функционирование после формирования результирующего множества, и при этом не применялся оператор OPEN . Тогда процедура будет находиться в «зависшем» состоянии на сервере до тех пор, пока не будет выполнен оператор RESUME . Данный оператор закрывает курсор и продолжает работу процедуру до ее завершения или до формирования следующего результирующего множества.

Исходя из этого, следует отметить, что выполнение процедуры Get_list_absent не завершено. Она все еще находится в «зависшем» состоянии на сервере. Для ее завершения выполните оператор RESUME .

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

После вызова процедуры For_RESUME в утилите ISQL:

В окне Data будет представлено результирующее множество, сформированное в результате выполнения в процедуре For_RESUME оператора CALL Get_list_absent(80).

В окне Statistics будет выдано сообщение «. Procedure is executing.Use RESUME to continue.», означающее, что происходит выполнение процедуры приостановлено и для продолжения ее выполнения следует выполнить оператор RESUME .

Выполнение процедуры будет приостановлено. Об этом свидетельствует сообщение в окне Statistics . Кроме того в окне локального сервера (см. рис. 2) или окне Messages удаленного сервера (см. рис. 4) в данной программе при помощи первого оператора MESSAGE выводится только одно сообщение «_______Parameter =80». Сообщения , формируемые тремя другим операторами MESSAGE пока не выводятся, потому что выполнение процедуры For_RESUME приостановлено.

После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до тех пока не будет сформировано следующее результирующее множество. В данном случае это сделает оператор SELECT .

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

После выполнения оператора SELECT происходит следующее:

В окне Data будет представлено результирующее множество, сформированное оператором SELECT .

В окне Statistics опять будет предложено выполнить оператор RESUME для продолжения функционирования процедуры.

Об остановке работы процедуры свидетельствует сообщение в окне Statistics. Кроме того в окне сервера появится сообщение «____ Parameter =60«. Остановка процедуры произойдет, несмотря на то, что в данном случае результирующее множество будет пустое.

После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до следующего результирующее множество.

После его анализа выполним последний третий раз оператор RESUME . В результате этого получим:

Окне Data будет пустым.

В окне Statistics будет выдано сообщение о завершении процедуры «Procedure completed.».

В окне сервера появится сообщение «**** FINISH «. Его формирует последний оператор процедуры For_RESUME .

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

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

Источник

Как сохранить результат хранимой процедуры в таблице? — Microsoft SQL Server

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

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

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

Для начала давайте создадим тестовую хранимую процедуру, которая будет возвращать табличные данные. Все действия ниже я буду выполнять на Microsoft SQL Server 2016 Express, на текущий момент вышла уже 2017 версия SQL Server, о том, что нового в ней появилось, можете почитать в материале – «Обзор основных нововведений в Microsoft SQL Server 2017».

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

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

Как видите, процедура создана и возвращает табличные данные.

Способ 1 – Используем вызов процедуры в конструкции INSERT INTO

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

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

Способ 2 – Используем связанный сервер

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

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

Способ 3 – Используем конструкцию OPENROWSET

Этот способ подразумевает использование функции OPENROWSET и поставщика OLE DB. Для использования данного способа у Вас должен быть включен параметр Ad Hoc Distributed Queries.

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

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

У меня все, надеюсь, материал был Вам полезен, пока!

Источник

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

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

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

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

Если включить инструкцию SELECT в тело хранимой процедуры (но не SELECT. INTO или INSERT. SELECT), строки, указанные инструкцией SELECT, будут отправляться непосредственно клиенту. Для больших результирующих наборов выполнение хранимой процедуры не перейдет к следующей инструкции, пока результирующий набор не будет полностью передан клиенту. Для небольших результирующих наборов результаты будут буферизированы для возврата клиенту, а выполнение продолжится. Если при выполнении хранимой процедуры запускается несколько таких инструкций SELECT, клиенту отправляется несколько результирующих наборов. Такое поведение также применяется к вложенным пакетам TSQL, вложенным хранимым процедурам и пакетам TSQL верхнего уровня.

Примеры возврата данных с помощью результирующего набора

Приведенный ниже пример показывает хранимую процедуру, которая возвращает значения LastName и SalesYTD для всех строк SalesPerson, которые также отображаются в представлении vEmployee.

Возврат данных с помощью выходного параметра

Процедура может возвращать текущее значение параметра в вызываемой программе при завершении работы при указании ключевого слова OUTPUT для параметра в определении процедуры. Чтобы сохранить значение параметра в переменной, которая может быть использована в вызываемой программе, при выполнении процедуры вызываемая программа должна использовать ключевое слово OUTPUT. Дополнительные сведения о том, какие типы данных могут использоваться в качестве выходных параметров, см. в разделе CREATE PROCEDURE (Transact-SQL).

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

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

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

Входные значения также могут быть указаны для параметров OUTPUT при выполнении процедуры. Это позволяет хранимой процедуре получать значение из вызываемой программы, изменять его или выполнять операции с этим значением, а затем возвращать новое значение вызываемой программе. В предыдущем примере переменной @SalesYTDBySalesPerson может быть присвоено значение прежде, чем программа вызовет процедуру Sales.uspGetEmployeeSalesYTD . Эта инструкция передает значение переменной @SalesYTDBySalesPerson выходному параметру @SalesYTD . Далее в тексте процедуры значение можно использовать для вычислений, формирующих новое значение. Новое значение передается обратно из процедуры через выходной параметр, обновляя значение в переменной @SalesYTDBySalesPerson при завершении процедуры. Часто это называется «возможностью передачи по ссылке».

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

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

Transact-SQL в процедурах только выходные (OUTPUT) параметры могут иметь тип данных cursor . Если тип данных cursor указан для параметра, то как ключевое слово VARYING, так и ключевое слов OUTPUT должны быть указаны для этого параметра в определении процедуры. Параметр может быть указан только как выходной, однако если в объявлении параметра указано ключевое слово VARYING, типом данных должен быть cursor , при этом также следует указать ключевое слово OUTPUT.

Тип данных cursor не может быть связан с переменными приложения через интерфейсы API баз данных, таких как OLE DB, ODBC, ADO и DB-Library. Поскольку выходные параметры должны быть привязаны прежде, чем приложение сможет выполнить хранимую процедуру, хранимые процедуры с выходными параметрами типа cursor не могут быть вызваны из функций API базы данных. Эти процедуры могут быть вызваны из пакетов на языке Transact-SQL , процедур или триггеров, только если выходная переменная типа cursor присвоена локальной переменной Transact-SQL cursor языка типа .

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

Следующие правила относятся к выходным параметрам типа cursor при выполнении процедуры:

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

Непрокручиваемый курсор открыт в процедуре на результирующем наборе по имени RS из 100 строк.

Процедура выбирает первые 5 строк результирующего набора RS.

Процедура возвращает результат участнику.

Результирующий набор RS, возвращенный участнику, состоит из строк с 6 по 100 из набора RS, и курсор в участнике позиционирован перед первой строкой RS.

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

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

Пустой результирующий набор отличается от значения NULL.

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

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

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

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

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

Сначала создайте процедуру, которая объявляет и затем открывает курсор в таблице Currency.

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

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

Процедура может возвращать целочисленное значение, называемое кодом возврата, чтобы указать состояние выполнения процедуры. Код возврата для процедуры указывается при помощи инструкции RETURN. Как и выходные параметры, при выполнении процедуры код возврата необходимо сохранить в переменной, чтобы использовать это значение в вызывающей программе. Например, переменная @result типа данных int используется для хранения кода возврата из процедуры my_proc , например:

Коды возврата часто применяются в блоках управления потоком процедур для присвоения кода возврата каждой из возможных ошибок. Чтобы выяснить, произошла ли во время выполнения инструкции ошибка, запустите функцию @@ERROR после инструкции Transact-SQL. До появления обработки ошибок TRY/CATCH/THROW в TSQL для определения успеха или сбоя хранимых процедур иногда требовались коды возврата. Хранимые процедуры должны всегда выдавать сообщение при возникновении ошибки (которое при необходимости создается с помощью THROW/RAISERROR), не полагаясь в этом на код возврата. Кроме того, следует избегать использования кода возврата для возврата данных приложения.

Примеры кодов возврата

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

Значения кодов возврата Значение

0 Выполнено успешно.

1 Требуемое значение параметра не указано.

2 Требуемое значение параметра не допустимо.

3 Произошла ошибка при получении значения продаж.

4 Найдено значение NULL для продаж данного менеджера.

Следующий пример создает программу обработки кодов возврата, которые возвращаются процедурой usp_GetSalesYTD .

Источник