Версия для печати

На главную страницу

Архитектура суррогатных ключей для затратных операций с базами данных.

Часть 1   Часть 2   Часть 3  Части 4,5   

Часть 4. Хранимая процедура: проверка наличия подчиненных строк

Код T-SQL в листинге 2 создает системную хранимую процедуру с именем sp_ExamineChildren. Хранимая процедура принимает два обязательных параметра, имя таблицы (@DBTable) и значение первичного ключа (@DBValue). Процедура возвращает результирующий набор, содержащий строку для каждой дочерней таблицы. Каждая строка включает имя дочерней таблицы и число строк в таблице, которые относятся к строке родительской таблицы, заданной параметрами. Эта хранимая процедура может использоваться для простого анализа данных. Агрегированный по числу строк столбец SUM поможет определить, имеет ли родительская строка какие-нибудь подчиненные строки. Если сумма больше нуля, то дочерние строки существуют, и родительская строка не должна удаляться.

Вы можете проверить наличие подчиненные строк в таблице Purchase с помощью этой хранимой процедуры:

EXECUTE sp _ ExamineChildren ' Purchase ',3

Ниже приведен запрос, который возвращает набор данных, содержащих детализированную информацию о закупках для заданного клиента. Запрос включает восемь таблиц с разнообразными родительско/дочерними связями. Результатом является ненормализованный набор данных, подходящий для отчета. Однако результат, возможно, окажется не сильно полезным администратору баз данных или разработчику, поскольку источник каждого столбца не присутствует в выводе. Проблему можно легко разрешить, в частности, за счет избыточности данных (повторять родительские данные с каждой дочерней строкой).

SELECT R.RegionName 
     , C.Name 
     , O.OrderNumber
     , O.OrderDate 
     , I.LineNumber 
     , I.Quantity 
     , V.Name 
     , P.Description 
     , S.Carrier 
     , S.TrackingNumber 
     , S.ShipDate
   FROM Customer AS C
   JOIN Region AS R
      ON C.RegionID = R.RegionID
   JOIN Purchase AS O
      ON C.CustomerID = O.CustomerID
   JOIN PurchaseItem AS I
      ON O.PurchaseID = I.PurchaseID
   JOIN Product AS P
      ON I.ProductID = P.ProductID
   JOIN Vendor AS V
      ON P.VendorID = V.VendorID
   JOIN PurchaseItemShipment AS E
      ON I.PurchaseItemID = E.PurchaseItemID
   JOIN Shipment AS S
      ON E.ShipmentID = S.ShipmentID
   WHERE C.CustomerID = 2
   ORDER BY O.OrderNumber
          , I.LineNumber

Часть 5: Хранимая процедура: проверка связанных данных

Код T-SQL в листинге 3 создает хранимую системную процедуру с именем sp_FetchRelatedRows. Процедура возвращает связанные данные аналогично вышеприведенному запросу, однако, она представляет данные в отдельных результирующих наборах для каждой рассматриваемой таблицы. Такой вывод может оказаться более полезным для администратора баз данных или разработчика, поскольку он или она занимаются решением проблемы. Все соответствующие строки всех соответствующих таблиц выдаются сразу. Строки, входящие в каждую таблицу, ограничиваются только теми, которые включены в связи родительских или дочерних таблиц со строками из одного или большего числа других наборов.

Хранимая процедура sp_FetchRelatedRows принимает семь параметров.

Первый параметр (@DBChain) – это список имен таблиц, разделенных вертикальной чертой. Каждая таблица кроме первой должна быть родителем или потомком предыдущей таблицы в списке. Как правило, цепочка таблиц представляет собой иерархию вниз от родителя к потомку. Когда задается таблица на самом нижнем уровне, должна быть задана таблица, которая соединяется с любой таблицей, находящейся выше в списке, начиная новую последовательность таблиц. При необходимости новая последовательность проходила бы тогда иерархию вверх или вниз.

Второй параметр (@DBWhere) является необязательным и используется как предложение WHERE в первой таблице.

Третий параметр (@DBAdmin) является необязательным и управляет тем, как и когда устанавливаются соединения между таблицами в цепочке, определенной параметром @DBChain. Значение по умолчанию ноль (0).

Если параметр @DBAdmin отрицателен, тогда таблицы соединяются по одной (подобно запросу выше). Этот вариант устраняет родительские строки, которые не имеют никаких дочерних строк (другие варианты этого не делают).

Если @DBAdmin – нуль, тогда каждая таблица соединяется с каждой предыдущей таблицей, для которой существует связь. Строки выбираются на основе последовательности(тей) до данной точки. Этот вариант (или предыдущий вариант) потребуется, если какая-нибудь последовательность таблиц запускается параллельно предыдущей последовательности.

Если @DBAdmin положителен, то каждая таблица соединяется только с ближайшей предыдущей таблицей, для которой существует связь. Строки выбираются на основе последовательности(тей) до данной точки.

Следующие два параметра (@DBField и @DBValue) являются необязательными и используются, чтобы ограничиться строками из первой таблицы на основе столбца с целым значением. Как правило, для этой цели используется столбец первичного или внешнего ключа, наряду с соответствующим значением.

Следующие два параметра (@PCField и @PCValue) являются необязательными и используются, чтобы ограничиться строками, выбранными из каждой таблицы на основании столбца с целым значением. Если используется эта пара параметров, указанный столбец должен существовать в каждой таблице.

Вы можете исследовать данные, используемые в запросе выше, с помощью вызова хранимой процедуры:

EXECUTE sp_FetchRelatedRows 'Customer|Region|Purchase|PurchaseItem|Product|Vendor|PurchaseItemShipment|Shipment', NULL, 0, 'CustomerID', 2

Вы можете легко включить дополнительную таблицу:

EXECUTE sp_FetchRelatedRows 'Customer|Region|Purchase|PurchaseItem|Product|Vendor|PurchaseItemShipment|Shipment|Payment', NULL, 0, 'CustomerID', 2

Хранимая процедура sp_FetchRelatedRows представлена здесь в контексте работы администратора баз данных или разработчика. Однако стоит упомянуть, что вывод процедуры может быть обработан очень изящно с помощью ADO.NET. Объект DataSet может быть заполнен всеми таблицами сразу с помощью единственного метода. Кроме того, общая процедура может построить все правильные связи между объектами DataTable в DataSet. Процесс создает мини базу данных, по которой можно перемещаться, используя методы GetParentRow и GetChildRows. Я не рекомендовал бы вызывать sp_FetchRelatedRows из рабочего приложения, без полного представления о возможных последствиях использования динамического кода T-SQL .

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

Часть 1   Часть 2   Часть 3  Части 4,5   

На главную страницу

  Версия для печати