Курсоры в SQL Server

Этот пост – просто хранилище для кода, который я часто беру для реализации курсоров.

DECLARE @vin NVARCHAR(17)

DECLARE myCursor CURSOR
    FOR SELECT vin FROM VEHICLE
OPEN myCursor
FETCH NEXT FROM myCursor into @vin;
WHILE @@FETCH_STATUS = 0
    BEGIN
        print @vin
        FETCH NEXT FROM myCursor into @vin;
    END
CLOSE myCursor
DEALLOCATE myCursor

 

Определяем установочный каталог приложения

Существует, безусловно, несколько способов узнать куда именно установлена та или иная программа. Рассмотрим тот, который использует нативные функции MSI.

Воспользуемся функцией MsiGetComponentPath, определённой в компоненте MSI.dll. Нам понадобится Product Id Приложения, и Component Id какого-либо из его компонентов. Оба - гуиды, которые известны для некоторых приложений, и поддаются гуглёжке. Для других придётся вскрывать инсталлятор чем-то вроде Orca.

Импортируем нативный метод в C#-код: 

        [DllImport("MSI.DLL", CharSet = CharSet.Auto)]
        private static extern UInt32 MsiGetComponentPath(
            string szProduct,
            string szComponent,
            StringBuilder lpPathBuf,
            ref int pcchBuf);

Далее создаём метод-обёртку для более удобного использования: 

        public static string GetInstallationPath(string productId, string componentId)
        {
            string componentPath = GetComponentPath(productId, componentId);

            if (String.IsNullOrEmpty(componentPath))
            {
                return String.Empty;
            }
            return Path.GetDirectoryName(componentPath);
        }

Можем пользоваться: 

        private string path = GetInstallationPath(
            "{B7BC7C6F-9A4E-4973-BE84-ECA8E3427C97}",
            "{4093449C-7949-4E96-B071-2AADB06EFC12}");

Обращаем внимание на формат GUID - они должны быть именно такие, с фигурными скобками.

Задача разузлования деталей на SQL

Задача приведена в учебнике Дейта "Введение в системы баз данных" под номером 4.2.

Допустим есть следующая таблица PART_STRUCTURE:

CREATE TABLE Study.dbo.PART_STRUCTURE
(MAJOR_P NVARCHAR(3),
MINOR_P NVARCHAR(3),
QTY INT,
PRIMARY KEY (MAJOR_P, MINOR_P))

INSERT INTO Study.dbo.PART_STRUCTURE
(MAJOR_P, MINOR_P, QTY)
values('P1', 'P2', 2),
('P1', 'P3', 4),
('P2', 'P3', 1),
('P2', 'P4', 3),
('P3', 'P5', 9),
('P4', 'P5', 8),
('P5', 'P6', 3)

Требуется написать на языке SQL программу для получения списка всех компонентов данной детали на всех имеющихся уровнях.

Поскольку по сути мы выполняем обход иерархической древовидной структуры, применим рекурсию:

Применим "Recursive Queries Using Common Table Expressions", поскольку в следующей статье на технете [1] встречаем выражение "Returning hierarchical data is a common use of recursive queries" – именно то, что нам надо для решения задачи.

Итак, CTE или Common Table Expressions – инструмент Transact-SQL, их базовое использование описано в [2]:

В итоге получилось следующее:

WITH DETAIL_CTE (MINOR_PARTS_NAMES)
AS(    SELECT MINOR_P FROM PART_STRUCTURE WHERE MAJOR_P='P1'
    UNION ALL
    SELECT MINOR_P FROM PART_STRUCTURE PS INNER JOIN DETAIL_CTE DC
    ON PS.MAJOR_P=DC.MINOR_PARTS_NAMES
)SELECT * FROM DETAIL_CTE

Этот запрос выдаёт состав деталей на каждом из уровней, но не выдаёт их количество, и, что хуже, не показывает на каком конкретно уровне находится деталь.

Изменим код таким образом, чтобы для каждого типа деталей разузлования отображался уровень разузлования:

WITH DETAIL_CTE (MINOR_PARTS_NAMES, LEVEL)
AS(    SELECT MINOR_P, 1 as LEVEL FROM PART_STRUCTURE WHERE MAJOR_P='P1'
    UNION ALL
    SELECT MINOR_P, DC.LEVEL+1 FROM PART_STRUCTURE PS INNER JOIN DETAIL_CTE DC
    ON PS.MAJOR_P=DC.MINOR_PARTS_NAMES
)SELECT * FROM DETAIL_CTE

Первый подзапрос CTE задаёт начальное значение счётчика уровня, равное 1 (не ноль, т.к. считаем нулевым уровнем уровень самой детали, подвергаемой разузлованию)

На рекурсивном подъёме (или спуске?) мы инкрементируем это значение при каждом рекурсивном вызове, таким образом получая номер уровня.

Теперь мы видим какой тип детали находится на каком уровне. Добавим отображение количества деталей по каждому из уровней разузлования:

WITH DETAIL_CTE (LEVEL, MINOR_PARTS_NAMES, QTY)
AS(SELECT 1 as LEVEL, MINOR_P, QTY FROM PART_STRUCTURE WHERE MAJOR_P='P1'
UNION ALL
SELECT DC.LEVEL+1, MINOR_P, PS.QTY FROM PART_STRUCTURE PS INNER JOIN DETAIL_CTE DC
ON PS.MAJOR_P=DC.MINOR_PARTS_NAMES
)SELECT * FROM DETAIL_CTE WHERE LEVEL=1

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

LEVEL    MINOR_PARTS_NAMES    QTY
1                            P2                                  2
1                            P3                                  4
2                            P5                                  9
3                            P6                                  3
2                            P3                                  1
2                            P4                                  3
3                            P5                                  8
4                            P6                                  3
3                            P5                                  9
4                            P6                                  3

Ручная проверка показывает, что дерево восстановлено верно, так что результата мы добились, и даже в довольно читаемой форме.

Ссылки:

[1] – Recursive Queries Using Common Table Expressions, Microsoft Technet article
[2] – CTEs, Microsoft Technet article