Цитата дня!

"Don't even try to rememeber it all. Remember that what you have seen is possible."

Rob Vieira

Professional Microsoft SQL Server 2008 Programming

SQL хранимая процедура для трансформации таблицы. Динамический запрос.

В данном посте я немного затрону вопросы создания T-SQL хранимых процедур и динамических запросов.

В одном из моих предыдущих постов - http://vasyniuk.blogspot.com/2012/03/pivot-unpivot.html я рассказывал об обработке результирующих наборов данных с использованием оператора T-SQL PIVOT. В данном посте я хочу развить эту тему и перевести ее на более высокий уровень – на уровень использования хранимых процедур SQL.

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

Очевидно, что в таком случае необходима автоматизация процесса преобразования наборов данных.  Единственный разумный вариант работы с такой таблицей – динамическая обработка ее столбцов в запросе с использованием оператора PIVOT.

Чтобы извлечь максимальную выгоду из такой ситуации, я буду использовать не прямой запроc, а хранимую процедуру Transact-SQL. Сразу скажу, что разработка такой процедуры с точки зрения программирования под MS SQL Server – несет в себе много скрытых препятствий для неопытных разработчиков. Создается впечатление, что в Microsoft попытались максимально усложнить жизнь разработчикам, наполнив T-SQL большим количеством всевозможных ограничений.

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

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

Сценарий разработки

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

CREATE PROC [dbo].[piv_StatTable] @temp_table dbo.EStatTT READONLY

AS

     SET NOCOUNT ON

     DECLARE @rows varchar(8000),@querry varchar(8000)

     CREATE TABLE #df(element_name nvarchar(4),period_id tinyint, value smallint)

     INSERT #df SELECT * FROM @temp_table

     SELECT @rows=COALESCE(@rows+',['+CAST(period_id AS varchar)+']',

                                        '['+CAST(period_id AS varchar)+']')

     FROM @temp_table

     GROUP BY period_id

    

     SET @querry='SELECT * FROM #df PIVOT (MAX(value) FOR period_id IN('+@rows+')) AS tbl_pivot'

     EXEC(@querry)

    

     DROP TABLE #df

GO

Разбор полетов

На входе в процедуру передается переменная @temp_table типа dbo.EStatTT. Тип данных EStatTT – это определяемый пользователем табличный тип. Табличный тип данных TABLE – относительно молодой поддерживаемый тип в MS SQL Server по сравнению с такими привычными многим типами как int или varchar. Оперировать этим типом данных можно, допустим, в UDF - возвращая таблицу данных, как результат выполнения функции. В контексте передачи переменных хранимым процедурам тип TABLE использовать не получится, хотя хотелось бы.

Ограничение: для этого необходимо сначала создать собственный тип данных - определяемый пользователем табличный тип (по аналогии с определением класса на C#).

После объявления такого типа данных его можно использовать для передачи переменных в качестве параметров хранимым процедурам (ссылка на MSDN в конце поста).

CREATE TYPE [dbo].[EStatTT] AS TABLE(

     [element_name] [nvarchar](4) NULL,

     [period_id] [tinyint] NULL,

     [value] [smallint] NULL

)

GO

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

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

Зачем необходима временная таблица #df?

Как видно из листинга, #df используется в строковой переменной @querry при составлении тела динамического запроса.

Ограничение: мы не можем использовать переменную @temp_table подобным образом – это своего рода ограничение видимости локальных переменных.

@temp_table – является локальной переменной, объявление и инициализация которой будут происходить вне пакета разрабатываемой хранимой процедуры. В динамических запросах нельзя использовать локальные переменные, которые были объявлены до вызова самого запроса. Собственно, именно по этой причине мы вынуждены использовать временную таблицу, которая заполняется данными из @temp_table путем выполнения простого запроса INSERT…SELECT.

Переменная строкового типа @rows содержит строку сформированную с помощью оператора COALESCE из значений столбца “period_id” таблицы, которую мы передаем процедуре. С первого взгляда оператор инициализации переменной @rows может показаться немного запутанным, но в нем нет ничего экстраординарного.

Оператор COALESCE – это один из CASE-производных операторов, который упрощает синтаксис Transact-SQL. Подробно о нем можно прочитать в MSDN - http://msdn.microsoft.com/ru-ru/library/ms190349.aspx. Чтобы было понятнее для чего он служит в данном случае, можно провести аналогию с оператором C# “??”:

string rows = (rows + ",[" + period_id.ToString() + "]") ?? ("[" + period_id.ToString() + "]");

Уникальность значений обеспечивается путем использования инструкции GROUP BY в конце запроса выборки.

Ну и, собственно, сам динамический запрос.

В переменной @querry формируется тело запроса с использованием оператора PIVOT. Для динамического создания новых столбцов таблицы используются значения вышеупомянутой строки @rows. Завершает все инструкция EXECUTE, в которую в качестве параметра передается строка @querry.

Использование

      Как правильно использовать созданную хранимую процедуру? Посмотрим на скриншот:

       Сценарий использования процедуры должен быть построен следующим образом:

1)    Для начала необходимо объявить переменную UDTT (User defined table type) табличного типа, о котором я писал выше – инструкция DECLARE.

2)    Затем - подготовить таблицу для передачи в хранимую процедуру (произвести инициализацию этой переменной, наполнив таблицу данными). За это отвечают два составных оператора INSERT…SELECT.

3)    После этого следует вызов хранимой процедуры с таблицей-параметром в операторе EXECUTE.

Ссылки

MSDN table-valued parameters - http://msdn.microsoft.com/en-us/library/bb510489.aspx

SQL.RU динамические запросы - http://www.sql.ru/faq/faq_topic.aspx?fid=104

Профессиональное руководство по SQL Server: хранимые процедуры, XML, HTML

The guru's guide to SQL Server stored procedures, XML, and HTML
Автор: Хендерсон (Henderson)

“Вместо того чтобы просто показывать различные трюки и тонкости синтаксиса, эта книга обучает философии программирования в Transact-SQL. Она объясняет, как применять эту философию для создания собственных способов кодирования и решения повседневных проблем.”

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

Общее впечатление

     Хорошая книга, но с небольшими недостатками. Читать легко и интересно несмотря на небольшую беспорядочность или хаотичность структуры материала, причем интересно читать даже “левые” главы :) (кроме 24, 25 и 26).

Целевая аудитория

     Книга ориентирована на опытных разработчиков. В первую очередь - для тех, кто хорошо знаком с MS SQL и Transact-SQL. Думаю, для новичков она будет сложна и, как следствие, скучна.

Особенности

     Упор в книге автор делает все таки на хранимые процедуры.

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

   Материал книги ориентирован на версии SQL Server 2000 и 2005, вследствие чего информация местами устарела.

     Немного огорчили главы 24, 25 и 26. Это уже перебор и, имхо, не допустимо в технической литературе. Либо автор был морально истощен под конец написания книги, либо - решил показать всю многогранность своего таланта… L

     Еще один недостаток – автор затрагивает  достаточное количество “левых” тем в узкоспециализированной (как должно быть по логике вещей судя по названию) книге. Местами возникает ощущение, что читаешь какой-то обычный учебник по информатике для студентов 1-го курса, где поверхностно описываются разные технологии.

По главам

   1 глава посвящена хранимым процедурам. Среди прочих описываются системные процедуры: sp_helptext (просмотр исходного кода процедуры), sp_spaceused (построение отчета об утилизации дискового пространства БД). Рассказывается об особенностях использования настроек и параметров окружения SQL Server. Временные, системные и внутренние хранимые процедуры.

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

   3 глава - конкретные советы с примерами. Вся “соль” то ценное, что может быть в любой книге. Рассматриваются паттерны проектирования путем проведения аналогии с  эквивалентными идиомами T-SQL.

     5 глава содержит много теоретических сведений по проектированию. Детально на примере рассматривается 3 стадии разработки проектов: анализ (построение модели бизнесс-процессов), проектирование (нормализированная E-R-модель), разработка. Есть словарь терминов E-R-моделирования. Описаны 5-нормальных форм. На примере рассмотрена работа с пакетом CASE-средств моделирования Grandite  Silverrun BPM, ERX, RDM.

     Глава 11 содержит довольно интересную информацию о трансформации данных БД в HTML, я не сталкивался с подобными приемами использования T-SQL.

     12 глава - введение в XML. Довольно обширная глава с описанием стандарта XML. Автор рассказывает об XML, его особенностях, об валидации DTD и XML Schema, об использовании XSLT для преобразования XML в степени достаточной для того, чтоб разобраться что к чему. Затрагиваются вопросы эффективности использования XML в сравнении с HTML. Есть ссылки на литературу по XML и описание программных инструментальных средств для работы с XML/DTD/XSLT.

     В главе посвященной автоматизации и COM  автор не упомянул о настройке конфигурации SQL Server-а для выполнения OA-процедур, и если вы будете пытаться выполнить код из листингов  примеров – то, скорее всего, получите сообщение об ошибке.

     Глава 21 – хранимые процедуры для администрирования. Интересные примеры хранимых процедур в связке с использованием xp_cmdshell и применением специальных приемов и особенностей программирования T-SQL. Дается очень неплохое описание некоторых не документированных процедур, но, к сожалению, большинство из них актуальны для SQL Server версий 2000-2005.

    22 глава. Полезным дополнением к материалу книги является описание не документированных команд DBCC (порядка 35 штук), не документированных Transact-SQL функций и флагов трассировки SQL Server.

     Очень хороша глава 23. Она посвящена разработке dll-библиотек расширенных хранимых процедур, что можно трактовать, как работу с хранимыми процедурами на практически самом низком уровне (ниже только ASM J). Присутствуют: листинги кода *.dll–ок процедур на С/C++; описание способа подключения откомпилированных процедур к SQL Server; примеры использования.

Натяжитель DMR c петухом.

Чертеж натяжителя DMR под 10мм горизонтальные дропауты.


Натяжитель (правая сторона - с петухом).



Натяжитель - сечение A-A.



Адаптер. Вид сверху.

Адаптер. Вид сбоку.

Штифт резьбовой.

Гайка.

Шайба.



3D-1.



3D-2.



Трансформация результирующих наборов данных. Операторы PIVOT и UNPIVOT.

     Иногда при работе с БД может возникнуть необходимость изменения порядка отображения результирующего набора данных (результата выполнения выражения возвращающего табличное значение). Например, таблицу-результат работы оператора выборки или результат выполнения пользовательской функции можно экспортировать в  *.csv файл и далее работать с ним используя MS Excel.

      Зачем может понадобиться изменять набор данных перед выполнением операции экспорта? Как вы увидите далее на примере, проектирование реляционных БД имеет свои нюансы и, зачастую, то, как данные содержатся в таблицах БД диаметрально противоположно тому, как они должны содержаться в таблицах Excel или Word для обеспечения удобства работы с ними.

      Итак – пример. Допустим, имеется некая UDF, результатом работы которой является таблица статистических данных, состоящая из трех столбцов (1-й результирующий набор на скриншоте).

Скриншот 1.

      Лично мне было удобно использовать данные в виде 1-го результирующего набора для построения диаграмм в MS Word. Но что делать, если есть необходимость отобразить их в виде привычной многим таблицы (в виде 2-го результирующего набора на скриншоте)?

      Очевидно, что их необходимо своего рода “транспонировать” по аналогии с операцией транспонирования матрицы, но, как видите это должно быть не совсем транспонирование в его традиционном понимании.

      Для выполнения таких задач служит оператор Transact-SQL – PIVOT. Как написано MSDN (http://msdn.microsoft.com/ru-ru/library/ms177410.aspx): ”Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных.”

      Конкретно для нашего примера мы видим, что значения столбца “period_id” после выполнения оператора PIVOT преобразуются в поля результирующего набора, значения этих полей берутся из исходного столбца “value” и все это группируется по полю “element_name”. Важно отметить, что в операторе PIVOT я использовал агрегатную функцию MAX(). Использование этой функции в данном случае обусловлено лишь синтаксическими особенностями оператора PIVOT, которые требуют использования статистической функции.

      Как выполнить обратное по отношению к результату, полученному с использованием оператора PIVOT, преобразование результирующего набора данных, когда необходимо “транспонировать” столбцы таблицы-результата выполнения запроса в строки? Для проведения подобных манипуляций используется оператор UNPIVOT.

Скриншот 2.

      На 2-ом скриншоте видно, что результатом выполнения 2-ой инструкции SELECT, в которой использован оператор UNPIVOT, является набор данных, в котором поля исходного набора (результата выполнения UDF): pid1…4 - стали соответствующими строками сформированного поля “pids”. Также появилось поле “ods”, содержащее прежние значения столбцов pid1…4.

      Для полной ясности еще одна цитата из MSDN (по тому же референсу): “оператор UNPIVOT не является в точности обратным оператору PIVOT. Оператор PIVOT производит статистическую обработку и слияние нескольких строк в единую выходную строку. Оператор UNPIVOT не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены”.