Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL. Не берусь утверждать, что данные методы самые эффективные. Это всего лишь то, что я использую в своей работе. Всё это разрабатывалось под Microsoft SQL Server 2008 R2, но, думаю, должно работать и под 2005.
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.
Пусть процедура, из которой нам нужно получить данные будет такой:
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.
Пусть процедура, из которой нам нужно получить данные будет такой:
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(p1, p2) exec Proc1 select * from #t1
Плюсы и минусы:
- Передаваемые поля перечисляются 3 раза (это внутренний
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-3 раза, и еще по одному перечислению на каждое новое использование
- Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от
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 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-3 раза, при этом каждое новое использование не добавляет сложности
- Для непосредственного вывода результата также требуются дополнительные действия
- Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции
alter table #t1, и процесс ожидает полного завершенияProc1(неProc1_AlterTable!) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)
4 Метод
Модификация третьего, избавляемся от блокировок. Для этого сразу создаем таблицу с помощью табличной функции.
create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end go create function Proc1_AlterTable() returns table as return ( select cast(null as int) p1, cast(null as varchar(max)) p2 where 1=2 ) go -- используем: if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 select * into #t1 from Proc1_AlterTable() exec Proc1 select * from #t1
Плюсы и минусы:
- Передаваемые поля перечисляются 2-3 раза, при этом каждое новое использование не добавляет сложности
- Для организации непосредственного вывода результата также требуются дополнительные действия
- Есть небольшие сложности с созданием индексов и ограничений, т.к. их мы уже не можем поместить в
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 выглядит перспективным. Здесь очень интересные результаты тестов на производительность.
Интересно услышать какие вы используете способы упрощения этой задачи.
ссылка на оригинал статьи http://habrahabr.ru/post/217649/
Добавить комментарий