Цитата дня!

"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 не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены”.

Регулятор отскока Manitou Nixon '05

Чертеж кноба-регулятора, в простонародии - крутилки отскока, от Manitou Nixon. Также прилагается болт. Шаг резьбы болта ориентировочно - 1.25мм (но это на глаз...).

Подходит под Stance (проверено лично). По словам экспертов ХБ - также регулятор должен подходить под Sherman и Travis... по идее, и для Gold Label тоже...



Кноб. Вид сверху.

Кноб. Вид сбоку.

Кноб. Сечение.

Болт. Вид сверху.

Болт. Вид сбоку.

3D-1.

3D-2.

Self referencing many-to-many на MS SQL Server и Access. Особенности. Часть 2.

...продолжение

Особенности проектирования (SQL Server)

      Итак, в этом посте будут затронуты особенности построения БД с использованием связи self referencing many-to-many на SQL Server.

      Все исходные данные и условия те же, что и в первой части.

      Допустим, мы создали две таблицы (пока без ограничений FK):

USE [stones1]

GO

CREATE TABLE [dbo].[stones](

      [id] [smallint] NOT NULL,

      [name] [nvarchar](35) NOT NULL,

      CONSTRAINT [PK_stones_1] PRIMARY KEY ([id] ASC)

      )

GO

CREATE TABLE [dbo].[stf](

      [stone_id] [smallint] NOT NULL,

      [form_id] [smallint] NOT NULL,

 CONSTRAINT [PK_stf] PRIMARY KEY([stone_id] ASC,[form_id] ASC)

)

GO

Теперь наложим ограничения FK на поля отношения STF:

USE [stones1]

GO

ALTER TABLE [dbo].[stf]  WITH CHECK ADD  CONSTRAINT [FK_stf_stones_id1] FOREIGN KEY([stone_id])

REFERENCES [dbo].[stones] ([id])

ON DELETE CASCADE

ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[stf]  WITH CHECK ADD  CONSTRAINT [FK_stf_stones_form1] FOREIGN KEY([form_id])

REFERENCES [dbo].[stones] ([id])

ON DELETE CASCADE

ON UPDATE CASCADE

GO

      При попытке выполнить эти пакеты вы получите сообщение об ошибке следующего вида:

Или, если задавать FK в конструкторе – то приблизительно такое сообщение:

      Как видим – SQL Server не позволяет спроектировать БД подобным образом :( …

      Согласно информации по референсу http://support.microsoft.com/kb/321843/en-us?fr=1, (далее практически дословно) вы получаете ошибку  1785 потому, что в СУБД SQL Server таблица не может появляться более одного раза  в  списке всех каскадных ссылочных действий, которые являются результатом вызова инструкций DELETE или UPDATE.

     Более продвинутые в MS SQL гуру на RSDN (http://www.rsdn.ru/forum/db/3081247.flat.aspx) обьясняют это спецификой алгоритма определения циклов от мелкомягких, и это безобразие тянется еще с 2000 года, и менять никто ничего принципиально не собирается (конечно, лезть в старый код и переписывать рабочий  алгоритм, со всеми вытекающими… я б сам не стал :))

Кто виноват, и что делать?

     Очевидно, что это упущение разработчиков мелкомягких. Ведь поддерживание  целостности данных на максимально возможном уровне – одна из основных задач СУБД и она определена в стандарте ANSI/ISO. В данном случае, стараниями разработчиков, этот максимум занижен путем наложенного ограничения функционала СУБД (видимо, перестарались). Можно конечно возразить, что конкретные реализации условий целостности отличаются в разных СУБД, но “отличная от иной реализация” и “отсуствие реализации” – это разные вещи.

      Решение данной проблемы саппорт целиком и полностью возлагает на наши плечи :) (в том-же референсе предлагается использовать триггеры в случае необходимости обесппечения целостности и максимальной гибкости). То же решение предлагается и на RSDN, и на SQL.RU (http://www.sql.ru/forum/actualthread.aspx?tid=595772).

Каковы будут наши дальнейшие действия?

      Сначала необходимо все-таки наложить ограничения внешнего ключа на отношение STF. Для этого необходимо убрать из вышеприведеного листинга для FK правила обновления\удаления и выполнить запрос. После этого, не мудрствуя лукаво,  пишем триггеры.

     Итак, для успешного удаления записи из таблицы STONES мы должны сначала обработать таблицу STF, так как у нас наложены ограничения внешних ключей с  правилами обновления и удаления RESTRICT (по умолчанию), и в случае непосредственной попытки выполнить инструкцию DELETE - мы получим сообщение об ошибке.

Простейший возможный сценарий работы триггера можно составить таким образом:

 - Проверить количество связанных дочерних записей в таблице STF и в случае успеха – удалить.

 - Проверить количество связанных родительских записей в таблице STF и в случае успеха – также удалить.

 - Удалить запись таблицы STONES

USE [stones1]

GO

CREATE TRIGGER [dbo].[trg_stones_IOD] ON [dbo].[stones]

INSTEAD OF DELETE

AS

BEGIN

      SET NOCOUNT ON

IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.stone_id=deleted.id)>0

      BEGIN

            DELETE stf FROM stf, deleted WHERE stf.stone_id=deleted.id

      END

     

      IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.form_id=deleted.id)>0

      BEGIN

            DELETE stf FROM stf, deleted WHERE stf.form_id=deleted.id

      END

   

    DELETE stones FROM stones,deleted WHERE stones.id=deleted.id

   

END

GO

      Триггер для обновления INSTEAD OF UPDATE пишется аналогичным образом и здесь я его приводить не буду.

      Вот и все. Мы добились требуемой архитектуры БД с использованием связи many-to-many и каскадным удалением\обновлением данных.