Как стать автором
Обновить

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

Время на прочтение 11 мин
Количество просмотров 79K
Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL. Думаю, большинство здесь изложенного может пригодиться только в приложениях со сложной SQL логикой и объемными процедурами. Не берусь утверждать, что данные методы самые эффективные. Это всего лишь то, что я использую в своей работе. Всё это работает под Microsoft SQL Server 2008.
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.

Пусть процедура, из которой нам нужно получить данные будет такой:
create procedure Proc1
as
begin
	select 1 p1, 'b' p2
end

1 Метод


Один из самых простых методов. Используем конструкцию insert ... exec ...
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
insert #t1
exec Proc1
select * from #t1

Плюсы и минусы:

  • Передаваемые поля перечисляются 2 раза (это внутренний select, внешнее создание таблицы и insert). Плюс перечисление полей происходит при каждом новом аналогичном вызове. (Я добавляю данный критерий, т.к. при большом количестве правок и множестве мест вызова процедуры, процесс изменения выводимых данных становится очень трудоемким)
  • Имеет серьезное ограничение – мы можем получить только одну таблицу
  • Для работы процедуры в режиме простого вывода не требуются дополнительные действия, достаточно запустить exec Proc1 без insert


2 Метод


С помощью записи в ранее созданную таблицу. Здесь придется добавлять insert в процедуру:
create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end

По сути мы перенесли строку insert внутрь процедуры.
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
exec Proc1
select * from #t1

Плюсы и минусы:

  • Передаваемые поля перечисляются 2 раза, и еще по одному перечислению на каждое новое использование
  • Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от Proc1 таблицы, либо определять, когда их выводить внутри Proc1. Например, по признаку не существования таблицы для вставки:

alter procedure Proc1
as
begin
	declare @show bit
	if object_id(N'tempdb..#t1',N'U') is null 
	begin
		set @show = 1
		create table #t1(p1 int, p2 varchar(max))
	end

	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
	
	if (@show = 1)
	begin
		select * from #t1
	end
end

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

3 Метод


По сути, является доработкой второго метода. Чтобы упростить поддержку создаем пользовательский тип таблицы. Выглядит это примерно так:
create type tt1 as table(p1 int, p2 varchar(max))
go
create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end
go
-- используем:
declare @t1 tt1
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
select *
into #t1
from @t1

exec Proc1
select * from #t1

Плюсы и минусы:

  • Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
  • Для организации непосредственного вывода результата также требуются дополнительные действия
  • Есть небольшие сложности с созданием индексов и ограничений, т.к. их мы не можем создать с помощю конструкции select ... into


4 Метод


Усложнение третьего метода, позволяющее создавать таблицу с ограничениями и индексами. В отличии от предыдущего работает под Microsoft SQL Server 2005.
create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end
go
create procedure Proc1_AlterTable
as
begin
	alter table #t1 add p1 int, p2 varchar(max)
	alter table #t1 drop column delmy
end
go
-- используем:
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(delmy int)
exec Proc1_AlterTable
exec Proc1
select * from #t1

Однако обычно временная колонка delmy не используется, вместо неё таблица создается просто с одним первым столбцом (здесь с p1).

Плюсы и минусы:

  • Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
  • Для непосредственного вывода результата также требуются дополнительные действия
  • Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции alter table #t1, и процесс ожидает полного завершения Proc1 (не Proc1_AlterTable!) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)


5 Метод


Этот метод использует предварительно созданные процедуры. Он основан на включении динамического SQL-запроса в запускаемую процедуру. Однако является достаточно простым в использовании.

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

1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin

2. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect). Если процедура не создает результирующего набора, то действие не требуется
3. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin

Для нашего примера мы получаем:
create procedure Proc1
as
begin
	if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
	exec util.InclusionBegin

		select 1 p1, 'b' p2
	into #Output1

	exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin
end

Запуск осуществляется так:
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun'

select * from #InclusionOutput1

', 1, '#InclusionOutput'
exec Proc1

Поскольку генерируемый SQL это не всегда хорошо, то приведенный пример лучше подходит для небольших инструкций. Если кода достаточно много, то можно либо вынести его в отдельную процедуру и из динамической части осуществлять только exec вызов, либо перезалить данные в новые временные таблицы. В последнем случае, конечно, происходит ещё одно «лишнее» копирование, но часто бывает так, что на этом этапе мы можем предварительно сгруппировать результат и выбрать только нужные поля для дальнейшей обработки (например, если в каком-либо случае не требуется все возвращаемые данные).

Функции util.InclusionRun передаются 3 параметра:
  • @sql – SQL-скрипт, который выполниться внутри вызываемой процедуры
  • @notShowOutput – если = 1, то блокировать вывод таблиц, начинающихся с #Output
  • @replaceableTableName – (по умолчанию = '#Output') задать префикс в имени таблиц используемых в @sql, для замены его на соответствующую #Output* таблицу в скрипте. Например, если задать #InclusionOutput, и в процедуре созданы две таблицы #Output55 и #Output0A, то в @sql можно обратиться к #Output55 как к #InclusionOutput1, а к #Output0A как к #InclusionOutput2


Работа построена таким образом, что запуск Proc1, без предварительного запуска util.InclusionRun приводит к естественной работе процедуры с выводом всех данных, которые она выводила до обработки.

Нюансы использования:

  • Накладывает ограничения на использование инструкции return в процедуре, т.к. перед ней необходим запуск util.InclusionEnd
  • Выводящие результат select'ы из запускаемых процедур выводят результат раньше, чем даже те #Output-таблицы, которые были созданы до их вызова (это логично, т.к. вывод происходит только в util.InclusionEnd)


Плюсы и минусы:

  • Передаваемые поля перечисляются один раз, при этом каждое новое использование не добавляет сложности
  • Для непосредственного вывода результата не требуется никаких действий
  • Необходимо помнить и учитывать нюансы использования
  • Из-за дополнительных процедур выполняется больше инструкций, что может снизить быстродействие при частых вызовах (я думаю, что при запуске реже одного раза в секунду этим можно пренебречь)
  • Возможно, может усложнить понимание кода для сотрудников не знакомых с данным методом: процедура приобретает два exec-вызова и неочевидность того, что все #Output-таблицы будут выведены
  • Позволяет легко организовать модульное тестирование без внешних инструментов


Демонстрация использования:

Скрытый текст
Код:
if object_id('dbo.TestInclusion') is not null drop procedure dbo.TestInclusion
go
create procedure dbo.TestInclusion 
        @i int
as
begin
        if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
        exec util.InclusionBegin 

        if object_id('tempdb..#tmp2', 'U') is not null drop table #tmp2 
        select @i myI
        into #tmp2
        
        if object_id('tempdb..#tmp3', 'U') is not null drop table #tmp3
        select @i + 1 myI
        into #tmp3

        select *
        into #Output0 --На вывод (выводится в util.InclusionEnd)
        from #tmp2
        union all
        select *
        from #tmp3

        select 'процедура TestInclusion' alt
        into #OutputQwerty --На вывод (выводится в util.InclusionEnd)
        
        exec util.InclusionEnd --выводит все таблицы начинающиеся с #Output в порядке из создания после запуска util.InclusionBegin 
end
go
set nocount on
set ansi_warnings off
if object_id('tempdb..#ttInclusionParameters', 'U') is not null drop table #ttInclusionParameters
go
select 'Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"'
exec dbo.TestInclusion 2
go
select 'Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5'
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun '

        select sum(myI) testSum
        from #InclusionOutput1
        
', 1, '#InclusionOutput'
exec dbo.TestInclusion 2


Результат:
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"

myI
-----------
2
3

alt
-----------------------
процедура TestInclusion


------------------------------------------------------------------------------------------------------
Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5

testSum
-----------
5



Сами функции:

Скрытый текст
if not exists(select top 1 null from sys.schemas where name = 'util')
begin
	exec ('create schema util')
end
go
alter procedure util.InclusionBegin
as
begin
/*
	Инструкция для использования:
	1. Обработка процедуры данные которой необходимо использовать
	1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin 
	1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
	1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
	2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
	Дополнительно см. коментарии внутри util.InclusionRun
*/
	set nocount on
	set ansi_warnings off
	declare @lvl int
	
	if object_id('tempdb..#ttInclusionParameters', 'U') is not null
	begin
		select @lvl = max(lvl)
		from #ttInclusionParameters
		
		--Добавляем null задание, для предотвращения запуска скрипта во вложенных процедурах с данным механизмом
		if (@lvl is not null)
		begin
			insert #ttInclusionParameters(lvl, pr)
			select @lvl+1 lvl, null pr
		end
	end

	if object_id('tempdb..#ttInclusion', 'U') is not null
	begin
		--запоминаем все уже существующие таблицы #Output, чтобы в util.InclusionEnd не выводить их
		insert #ttInclusion(lvl, i)
		select isnull(@lvl, 0), so.object_id i
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and not exists (select top 1 null from #ttInclusion where i = so.object_id)
	end
	
end
GO

go
alter procedure util.InclusionEnd
as
begin
/*
	Инструкция для использования:
	1. Обработка процедуры данные которой необходимо использовать
	1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin 
	1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
	1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
	2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
	Дополнительно см. коментарии внутри util.InclusionRun
*/
	set nocount on
	set ansi_warnings off
	----------------------------------------------------------------------------------------------------
	--считываем параметры
	declare @lvl int
		, @p0 varchar(max) --(@sql) sql скрипт который необходимо выполнить
		, @p1 varchar(max) --(@notShowOutput) если равно '1' хотя бы у одного из существующих вложенности заданий, то НЕ выводим #Output, иначе селектим их
		, @p2 varchar(max) --(@replaceableTableName) заменяемый префекс таблицы
	
	if object_id('tempdb..#ttInclusionParameters', 'U') is not null
	begin
		--считываем глобальные параметры
		select	@p1 = max(val)
		from #ttInclusionParameters
		where pr = 1
		
		--находим уровень на котором наше задание (max(lvl) - это уровень с null который мы добавили в util.InclusionBegin)
		select @lvl = max(lvl) - 1
		from #ttInclusionParameters
		
		if @lvl is not null
		begin
			--считываем
			select	@p0 = max(case when pr = 0 then val end)
				,	@p2 = max(case when pr = 2 then val end)
			from #ttInclusionParameters
			where lvl = @lvl 
			having max(pr) is not null
			
			--удаляем задание на скрипт, а если его нет, то только null-задание 
			delete #ttInclusionParameters
			where lvl >= @lvl and (lvl > @lvl or @p0 is not null)
		end
	end
	
	----------------------------------------------------------------------------------------------------
	--выбираем все созданные таблицы #Output
	if object_id('tempdb..#InclusionOutputs', 'U') is not null drop table #InclusionOutputs
	create table #InclusionOutputs(i int, tableName varchar(max), num int)
	
	if object_id('tempdb..#ttInclusion', 'U') is not null
	begin
		insert #InclusionOutputs(i, tableName, num)
		select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and so.name like '#Output%'
			and not exists (select top 1 null from #ttInclusion where i = so.object_id and lvl <= isnull(@lvl, lvl))
		
		--очищаем список созданных таблиц, которые принадлежат обрабатываемому уровню
		delete #ttInclusion 
		where lvl <= @lvl
	end
	else
	begin
		insert #InclusionOutputs(i, tableName, num)
		select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and so.name like '#Output%'
	end
	
	----------------------------------------------------------------------------------------------------
	--Выполнение заданий (если его не было - вывод всех #Output)
	declare @srcsql varchar(max)
	
	--Выполняем заданный скрипт в util.InclusionRun
	if (@p0 is not null and @p0 <> '')
	begin
		--заменяем псевдонимы @replaceableTableName
		if (@p2 is not null and @p2 <> '')
		begin
			select @p0 = replace(@p0, @p2 + cast(num as varchar(10)), replace(tableName, '#', '#<tokenAfterReplace>'))
			from #InclusionOutputs
			order by num desc
			
			select @p0 = replace(@p0, '<tokenAfterReplace>', '')
		end
		
		--добавляем в скрипт
		select @srcsql = isnull(@srcsql + ' ' + char(13), '')
			+ @p0 + ' ' + char(13)
	end
	
	--Выводим созданные #Output таблицы
	if (@p1 is null or @p1 <> '1') --если равно 1, то не выполняем!
	begin
		--отступ от прошлого скрипта
		select @srcsql = isnull(@srcsql + ' ' + char(13), '')

		--добавляем в скрипт
		select @srcsql = isnull(@srcsql + ' ', '') +
			'select * from ' + tableName
		from #InclusionOutputs
		order by num asc
	end
	
	if (@srcsql is not null)
	begin
		exec (@srcsql)
	end
	
end
go
alter procedure util.InclusionRun 
	@sql varchar(max), --sql скрипт который выполниться внутри вызываемой процедуры (содержащей util.InclusionEnd)
	@notShowOutput bit, --если = 1, то блокировать вывод таблиц начинающихся с #Output
	@replaceableTableName varchar(100) = '#Output' -- задать префикс в имени таблиц используемых в @sql, для замены его на соответствующую #Output* таблицу в скрипте. 
		-- Например, если задать #InclusionOutput, и в процедуре созданы две таблицы #Output55 и #Output0A, 
		-- то в @sql можно обратиться к #Output55 как к #InclusionOutput1, а к #Output0A как к #InclusionOutput2	
as
begin
	set nocount on
	set ansi_warnings off

	if object_id('tempdb..#ttInclusionParameters', 'U') is null 
	begin
		print 'Процедура util.InclusionRun не выполнена, т.к. для неё не созданна таблица #ttInclusionParameters!  '
		return
	end
	
	declare @lvl int
	select @lvl = isnull(max(lvl), 0) + 1
	from #ttInclusionParameters
	
	insert #ttInclusionParameters(lvl, pr, val)
	select @lvl, 0, @sql
	union all
	select @lvl, 1, '1'	where @notShowOutput = 1
	union all
	select @lvl, 2, @replaceableTableName
	
end




Другие методы


Можно воспользоваться передачей параметра из функции (OUTPUT) и на основе его значения восстановить таблицу. Например, можно передать курсор или XML.
На эту тему существует статья.
Использовать курсор для этой задачи я не вижу смысла, только если изначально требуется именно курсор. А вот XML выглядит перспективным. Здесь очень интересные результаты тестов на производительность.
Интересно услышать какие вы используете способы упрощения этой задачи :)

UPD 31.03.2014: Скорректировал пост по идеям из комментариев
Теги:
Хабы:
+3
Комментарии 13
Комментарии Комментарии 13

Публикации

Истории

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн