Иногда приходится сожалеть о том, что в Transact-SQL Microsoft SQL Server нет такой структуры как Array (массив). Многие программисты SQL поддержат меня. Массив - одна из наиболее общих и часто используемых структур программирования. Действительно, T-SQL не предусматривает полностью функциональную структуру массива. Однако SQL 2000 ввел новую возможность, называемую переменной табличного типа, которая позволяет имитировать массив и/или использоваться вместо курсора в SQL Server.
В этой статье я детально остановлюсь на том, как можно использовать такой "массив" в хранимых процедурах, что в значительной степени расширяет функциональные возможности программирования на языке SQL.
Я разбил свой подход на три части.
1. Создание массива в хранимых процедурах.
2. Передача и возвращение табличных переменных в/из хранимых процедур.
3. Использование 'массива' вместо курсоров.
Мой метод, прежде всего, основан на использовании переменных табличного типа, которые стали доступным, начиная с SQL Server 2000. Если Вы все еще работаете в версиях SQL Server 6.5 или 7, части 1 и 3 этой статьи могут все таки оказаться полезными для Вас. Однако Вы должны будете использовать временную таблицу (с префиксом #) вместо табличной переменной.
Часть 2 посвящена использованию XML, что является причиной, по которой этот подход окажется бесполезным в любой среде разработки, предшествующей SQL 2000. ВТ (временные таблицы) также могут использоваться в версии 2000, однако используя временную таблицу, Вы увеличиваете взаимодействие с дисками, так как временные таблицы создаются базе данных TempDb в противоположность ТП (табличным переменным), создаваемым в памяти. Другими словами, хранимые процедуры, оперирующие временными таблицами медленнее тех, которые используют ТП. Для того чтобы SQL Server создал ВТ, система должна
1. Создать таблицу в системной базе данных TempDb
2. Ввести данные в таблицу (insert)
3. Извлечь данные (select)
4. Удалить таблицу (drop)
Подобный процесс требуется и для ТП, но все операции проводятся 100-процентно в памяти. Я недавно экспериментировал с этими вариантами, и выяснил, что та же самая хранимая процедура, использующая ТП, выполнялась вдвое быстрей по сравнению с хранимой процедурой, которая использовала временные таблицы.
Чтобы смоделировать массив (ТП), мы нуждаемся в структуре, подобной array, известной в большинстве сред программирования. Объявление табличной переменной выглядит следующим образом:
Declare @tbl table ( )
Данный оператор создает эквивалент array. Тип этого "массива" зависит от типа данных столбца. Например,
Declare @tbl table (FirstName varchar(20), LastName varchar(20))
создает массив строкового типа (string). Другие типы данных, такие как int, float, datetime, money и т.д., также могут использоваться. Нужно помнить, что SQL Server не имеет функциональности (подобной VB и некоторым другим средам программирования) по неявному преобразованию типов данных. Это означает, что данные, которые Вы намереваетесь использовать для заполнения массива, должны быть отформатированы согласно спецификации каждого объявленного типа данных. Соответственно могут использоваться функции Convert и Cast для форматирования данных.
Чтобы иметь "совершенный" массив с полными функциональными возможностями, у нас отсутствует индекс. Чтобы восполнить этот недостаток, я использую дополнительный столбец с типом данных Int и свойством Identity. Определение Identity требует 2 аргумента: начальное значение и приращение. Таким образом, мы можем создать массив с отсчетом от 0 или 1. Следующий массив обладает полными функциональными возможностями:
Declare @tbl table (RowId int identity(1,1), ItemName varchar(100))
Теперь мы можем заполнить этот массив (используя базу данных Northwind) следующим образом:
Insert @tbl
Select ProductName
FROM Products
WHERE ProductName like 'M%'
Select * from @tbl
GO
-- Результат:
RowId ItemName
---------------------------------------
1 Mishi Kobe Niku
2 Mascarpone Fabioli
3 Maxilaku
4 Manjimup Dried Apples
5 Mozzarella di Giovanni
[Как видно, в этом примере столбец RowID обеспечивает функциональность индекса, а ItemName хранит фактические значения.]
Очевидно, что ТП является структурой программирования, эквивалентной МАССИВУ.
Мы продемонстрировали создание массива в SQL Server программным образом. Наша главная задача состоит в том, чтобы использовать массив в хранимых процедурах, включая передачу ТП в качестве аргумента. Наряду с этим, я хотел бы детально остановиться еще на одной возможности передачи всего списка значений в хранимую процедуру через аргумент и использования его внутри процедуры. Для этого я использую XML. Однако эта структура стала доступной, только начиная с версии 2000 SQL Server. Когда требуется подобная операция? Например, если Вы намереваетесь передать в процедуру более одного значения, но не знаете заранее, сколько всего значений будут передаваться. Рассмотрим запрос:
Select * from customers WHERE Region IN ('OR','CA','WA')
Оператор IN в предложении WHERE может использовать переменное число значений и возвращает результирующий набор в соответствии с этим множественным критерием.
Передача такого списка значений в хранимую процедуру может стать проблемой. В прежние времена я использовал так называемые "составные запросы", или "динамический SQL". Подход заключается в построении строки запроса, которая затем компилируется и выполняется при помощи оператора EXECUTE. Например:
CREATE PROCEDURE CustomerByRegion
@List varchar(100)
AS
declare @sql varchar(1000)
set @sql = 'select * from Customers where Region IN (' + @List + ')'
execute (@sql)
-- Вызов процедуры
declare @List varchar(100)
set @List = '''OR'', ''CA'', ''WA'''
execute CustomerByRegion @List
Этот метод имеет некоторые ограничения. Переменная типа Varchar не может содержать более чем 8000 символов, а переменная типа Text/nText недопустима для объявления локальных переменных. Давайте рассмотрим сценарий, когда XML используется, чтобы передать список значений в хранимую процедуру. Из этого списка мы можем СОЗДАТЬ 'МАССИВ' (ТП), а затем использовать этот Массив, в данном случае в операторе IN - части предложения WHERE оператора SQL.
Create Procedure ShowOrders
@XMLData text
/* тип данных TEXT позволяет нам принимать большую строку по сравнению с типом Varchar, который имеет ограничение в 8000 символов. */
AS
Set Nocount ON
Declare @hDoe int
/* число, необходимое системе, чтобы распознать документ XML */
Declare @tbl table (ProductName varchar(100))
/* объявление ТП ('Array') */
Exec sp_xml_preparedocument @hDoc Output, @XMLdata
/* Подготовка строки XML для дальнейшей обработки */
Insert @tbl select XML_Emp.ProductName
From OPENXML(@hdoc,'root/products')
With Products XML_Emp
Рассмотрим блок вставки более подробно. Мы наполнили массив, используя строку XML. Операторы OPENXML и OPENXML WITH создают представление набора строк (ROWSET VIEW ), используя документ XML. Полученный результат может быть загружен непосредственно в ТП или ВТ. Оператор WITH позволяет использовать существующее имя таблицы или создание новой схемы. Я рекомендовал бы второй вариант, так как это улучшает удобочитаемость и обслуживание. Кроме того, если Вы используете предложение WHERE, первый вариант может генерировать ошибку.
-- Вариант 1
Insert @tbl
select XML_Emp.ProductName
From OPENXML(@hdoc,'root/products')
With Products XML_Emp
-- Вариант 2:
Insert @tbl
select ProductName
From OPENXML(@hdoc,'root/products')
With (ProductName VARCHAR(20))
Теперь после передачи строк XML в ТП, мы можем использовать полученный результат в предложении IN.
USE NorthWind
CREATE PROC GetRegions_XML
@empdata text
AS
DECLARE @hDoc int
DECLARE @tbl TABLE(state VARCHAR(20))
exec sp_xml_preparedocument @hDoc OUTPUT, @empdata
INSERT @tbl
SELECT StateName
FROM OPENXML(@hDoc, 'root/States')
WITH (StateName VARCHAR(20))
EXEC sp_xml_removedocument @hDoc
SELECT * FROM Suppliers
WHERE Region IN (SELECT * FROM @tbl)
/*
declare @s varchar(100)
set @s = '<root><States StateName = "LA"/>
<States StateName = "MI"/>
<States StateName = "OR"/></root>'
exec GetRegions_XML @s
*/
Использование курсоров
Каждый знает о том, что курсоры съедают память. Именно поэтому рекомендуют использовать их как последнее средство, когда нет никаких других способов решить проблему. Это справедливо. Прошло довольно времени с тех пор, как я последний раз использовал курсор в моей практике программирования на T-SQL. И это понятно, т.к. с тех пор появилась намного лучшая альтернатива: использование ТП, которая великолепно работает и не так расточительна к ресурсам.
Одна из основных причин использования курсора - функциональные возможности, которые позволяют работать с каждой отдельной строкой из данного результирующего набора. При кодировании подобной функциональной возможности с помощью ТП-массива нам потребуется идентификатор строки или ID для каждой строки. Использование свойства Identity очень удобно для этого. Свойство Identity может служить уникальным идентификатором для каждой строки и позволит сымитировать функциональность Fetch для построчной обработки.
Ниже приведена сравнительная таблица для двух хранимых процедур, одна из которых использует традиционный курсор, и вторая - ТП. Вы можете обратить внимание, что хранимая процедура на основе курсора содержит меньше строк кода. Однако деятельность и ресурсы, используемые SQL Server, намного выше по сравнению с использованием ТП. Действительно, объявление курсора и использование операторов Fetch является компактным и прямым. В случае использования ТП, определенно используется больше кода, но это дает программисту больше возможностей по управлению и экономит ресурсы SQL Server и процессорное время, что, безусловно, является очень важной проблемой для крупных транзакционных приложений с большим числом пользователей.
-- Курсор Transact-SQL
CREATE PROC SQL_Cursor
AS
/*Локальные переменные */
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50)
/*создаем курсор*/
DECLARE MyCursor CURSOR FOR
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*открываем курсор*/
OPEN MyCursor
/*получить значения строки*/
FETCH MyCursor INTO @CompName, @ContName
/*выполнить операции с отдельной строкой */
PRINT 'T_SQL cursor row / ' + @ContName + ' / ' + @CompName
/*цикл*/
WHILE @@FETCH_STATUS = 0
BEGIN
/*поулчить значения строки*/
FETCH MyCursor INTO @CompName, @ContName
/*выполнить операции с отдельной строкой*/
PRINT 'T_SQL cursor row / ' + @ContName + ' / ' + @CompName
END
/*закрываем курсор*/
CLOSE MyCursor
/*удаляем описание курсора*/
DEALLOCATE MyCursor
-- Имитатор курсора
CREATE PROC CursorSimulator
AS
/*Подготовить табличную переменную для получения результирующего набора*/
DECLARE @tbl TABLE(
RowID INT IDENTITY(1, 1),
CompanyName VARCHAR(100),
ContactName VARCHAR(50))
/*Локальные переменные*/
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50),
@count int, /*создаем локальный @@fetch_status*/
@iRow int /*указатель строки (индекс)*/
/* создаем имититор массива */
INSERT @tbl
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*получаем верхнюю границу массива (наибольший номер ID)*/
SET @count = @@ROWCOUNT
/*инициализация счетчика индекса */
SET @iRow = 1
/*цикл*/
WHILE @iRow <= @count
BEGIN
/*получить значения строки*/
SELECT @ContName = CompanyName, @CompName = ContactName
FROM @tbl
WHERE RowID = @iRow
/*выполнить операции с отдельной строкой*/
PRINT 'My cursor row / ' + @ContName + ' / ' + @CompName
/*перейти к следующей строке*/
SET @iRow = @iRow + 1
END
Несмотря на тот факт, что имеется больше способов решить проблему, методы, которые я предложил, очевидно, расширяют наши возможности в использовании новых структур программирования SQL Server 2000.
Alex Grinberg (оригинал: The ARRAY In SQL Server 2000)
Перевод: Моисеенко С.И.
Оригинал перевода