/JhlLf (№FETCH STA!Ub=C) BEGIN SET @i=0

SET (ai=DATALENGTH{(!)arrayvar)/15 WHILE (@i<01) BEGIN

SELECT Guitansf=SUBSTRING(Sarrayvdr ,(@T15;+I,15)

SET 01=01+1

FETCH с INTO tsarrayvar

CLOSE С DEALLOCATE С

Guitarist

EES PAUL Guitarist

BUDD GUY Guitarist

JEFF BECK Guitarist

JOE SATRIANI Guitarist

STEVE MILLER Guitarist

EDDIE VAN HALEN Guitarist

TOM SCHOLZ Guitarist

STEVE VAI Guitarist

ERIC CLAPTON Guitarist

SLASH Guitarist

JIMI HENDRIX Guitarist

JASON BECKER Guitarist

MICHAEL HARTMAN

Этот код открывает курсор по рабочей таблице, затем в цикле проходит массив в каждой записи. Для определения длины каждого .массива используется функция DATALENGTHO, а для выборки элементов массива используется цикл и оператор SELECT с индексным выражением, представлишым в предыдущем запросе. Хотя этот способ довольно гибкий, так как позволяет обрабатывать столько элементов, сколько нам необходимо, с минимальным количеством кода, у него есть один существенный недостаток: он возвращает несколько результирующих .множеств. Многие клиентские п1)иложения не знают, как обрабатывать множественные результирующие .множества, и поэтому могут заупря.миться при таких результатах запроса. Однако суп1ествуст несколько обходных путей. Вот одит; из них:

CREATE TABLE #results (Guitarist varchardS)) DECLARE Parrayvar varchar(8C00) DECLARE (8i int. gl int

DECLARE с CURSOR FOR SELECT arraycol FROM #array OPEN с

FETCH с INTO Parrayvar WHILE (OiaFETCHJTATUS-O) BEGIN SET @i-0

SET 01=DATALENGTH((aarrayvar)/I5 WHILE (0i<01) BEGIN

INSERT #results SELECT SUBSTRING((aarrayvar. (@i*I5)+l. 15)

SET ?i=(ai+l

FETCH с INTO (aarrayvar

CLOSE с DEALLOCATE с SELECT * FROH #results DROP TABLE #resulLs

Guitarist EES PAUL

BUDDY GUY* JEFF BECK JOE SATRIANI STEVE MILLER EDDIE VAN HALEN TOM SCHOLZ STEVE VAI ERIC CLAPTON SLASH

JIMI HENDRIX JASON BECKER MICHAEL HARTMAN

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

Другой, хоч'я и немного более ограниченный, способ обработки массива - генерировать оператор SELECT при обработке массива и после этого выполнять его:

DECLARE (Barrayvcr varchar(8000), (aselect stmnt varchar(8000) DECLARE Ok int. @i int. 01 int, (Эс int DECLARE с CURSOR FOR SELECT * FRCM #array SET (aselect stmnt=SELECT SET (ac=0

OPEN с

FETCH с INTO @k, gannayvar WHILE ((a0FETCH STATUS=O) BEGIN SET 01=0

SET (ai=DATALENGTH((aarrayvar)/I5 WHILE ((aj<(ai) BEGIN

SELECT (aselect stmnt=(aselect strnnt+Guitarisf+CAST((ac as

varchar)* = +0U0TENAME(RTRIM(SUBStRING((aanrayvar,((ai*15)+l, 15)),

)+,

SET (ai=0i+l

SET &c=<?c+l

FETCH с INTO (Эк, Oarrayvar

CLOSE с DEALLOCATE с

SELECT 0select stmnt=LEFT((!)selecL stmnt,DATALENGTH((aselect stmnt)-l) EXEC(0select stmnt)

(результаты сокращены)

GuitaristO Guitaristl Guitanst2 Guitarists Guitanst4 Guitarists

EES PAUL BUDDY GUY JEFF BECK JOE SATRIANI STEVE MILLER EDDIE VAN HALEN

Обратите внимание па использование функции QUOTENAME() для загслючения каждого значения массива в разделяющие символы, чтобы их можно было вернуть onejaropo.m SELECT. По умолчанию разде7гяющими си.мволами являются [ и ], но, как показал пример, вы можете применить и другие. Это процедура ограниченнее по сравнению с решением на основе времен?10й таблицы, потому что она ограничена максимальным раз.мером varchar. То есть, так как создаваемый на.ми оператор SELECT хранится в переменной типа varchar, он не .может иметь длину более 8000 байт. В то время как в других методах .можно использовать 8000 байт для каждой строки массива, в это.м методе суммарный размер всех строк массива ограничен 8000 байт.

Учитывая, что эта строка также должна хранить название столбца для каждого элемента (у многих клиентских приложений и.меются проблемы с обработкой безымянных столбцов), это значительное ограничение. Однако тот факт, что в этом методе создается, а затем запускается SQL-код, интересен сам гю себе. Вы, возможно, придумаете другие применения этого подхода, например перекрестные таблицы пере.мепиой длины, декомпозиция серий и последовательностей и так далее.

Модификация элементов массива

Один недостаток, свойственный хранению .массивов как строк, проявляется, когда необходимо модифицировать значения элементов. Если только вы не вносите простейшие модификации, изменение хоть одного значения, хоть целого измерения - сложный процесс. Довольно просто очистить массив в заданной записи - для этого необходимо всего лишь сделать что-то подобное: UPDATE #аггау SET arraycol = WHERE kl=l

Если вы думаете о массиве в каждой записи как о строке в большем массиве (который охватывает всю таблицу), вы можете считать это очисткой одной строки. А что, если нам необходимо очистить только второй элемент в каждой строке массива? Нам бы понадобилось сделать примерно следующее:

UPDATE #аггау

SET arraycol =

LEFTCarraycol.l*15)+SPACECl*15)+RIGHT(arraycol .DATALENGTHCarraycol )-(2*15)) (результаты сокращены)

Elementl Element2 Elements Element4 Elements

EES PAUL JEFF BECK JOE SATRIANI

STEVE MILLER TOM SCHOLZ

STEVE VAI SLASH JIMI HENDRIX JASON BECKER

Здесь используются несколько трудные для понимания вычисления, которые служат для корректной обработки элеме1{тов разной длины. Как и в предыдущих примерах, данный код для доступа к массиву умножает индекс элемента на его размер. Хотя, конечно, более компактно применить SPACE(15) вместо SPACE(1*15), использование этого выражения более гибко, так как его можно легко переделать для работы с другими элементами.

Заметьте, что с помощью этого метода мы можем устанавливать значение конкретного измерения, а le просто очищать его. Например, чтобы заполнить третий элемент в каждой строке массива некоторы.м значением, мы .можем использовать такой код:

UPDATE #array

SET arraycol =

LEFTCarraycol.(2*15))+MUDDY WATERS +RIGHT(arraycol.DATALENGTH(arraycol)-(3*15))

Чтобы ограничить изменения некоторой записью, добавьте к оператору UPDATE предложение WHERE, вот так: UPDATE #array SET arraycol =

LEFT(arraycol,(3*15))+MUDDY WATERS +

RIGHT(arraycol.CASE WHEN (DATALENGTHCarraycol)-(4*15))<0 THEN 0 ELSE DATALENGTH(arraycol)-(4*15) END) WHERE kl=2

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

Представление массивов как таблиц

Реализация виртуальных массивов с ирименеипем обычных таб;инд также представляет собой возможную альтернативу встроепноп поддержке массивов. В это.м способе 0Д1И1 или более столбцов используются как индексы .массива. Ecjhi массив одио.мерньйг, у нас будет всего один столбец-индекс. Если же многомерный -их будет несколько. Вот при.мер:

CREATE TABLE iirray (<1 int identity (G.i;. gjitanist vanchandS))

INSERT #array (gu-tarist) VALdESCLES PAUL):

INSERT #array (guitarist) VALUES(BUDDY GUY):

INSERT §d.rray (guitarist) VALUESCJEFF BECK),

INSE.RT farray (guitarist) VALUES;JOE SATRIANI):

INSERT jtarr&y (gutanst) VALUES(STEVE MILLER):

INSERT farray (guitarist) V.ALUES(EDDIE VAN HALENj:

INSERT #arrey (guitarist) VALUESCTOM SCHOLZ):

INSERT #arrey (guitarist) VALU£S(STEVE VAI):

INSERT #array (gL.itarist) VALUESCERIC CLAPTON);

INSERl #зггау (guitarist) VALUES(SLASH):

INSERT #array (guitarisL) VALUESCJIMI HENDRIX);

INSERT #array (guitarist) VALUES(JASON BECKER);

INSERT #array (guita-ist) VALUES(MICHAEL HARTMAN):

-- Пр/,сва/1ваем значение уретьему элементу массива

UPDATE Idrray

SET gun.arist-JOHN GMUENDER WHERE lcl=2 SELECT guitarist FROM #array

guitarist

EES PAUL BUDDY GUY JOHN GMUENDER JOE SATRIANI STEVE MILLER EDDIE VAN HALEN TOM SCHOLZ STEVE VAI ERIC CLAPTON SLASH

JIMI HENDRIX JASON BECKER MICHAEL HARTMAN

Данный код показывает простой способ э.муляции одномерного .массива с по.мощью таблицы. Обратите внимание иа начальное значение identity-столбца, это необходимо, чтобы получить .массив, в которо.м отсчет элементов начинается с нуля. Transact-SQL также требует указания значения приращения, если вы указа.И1 начальное значение, так что мы указали значение приравгеиия, равное единице. Этот код из.меняет значение третьего элемента (который имеет индекс, равный двум). Если убрать предложение WHERE, .можно присвоить значения все.м эле.мента.м виртуального .массива или полностью его очистить.

LITTLE BIT 0 LOVE

FIRE AND WATER

THE FARMER HAD A DAUGHTER

ALL RIGHT NOW

BAD COMPANY

SHOOTING STAR

FEEL LIKE MAKIN LOVE

ROCK AND ROLL FANTASY

SATISFACTION GUARANTEED

RADIOACTIVE

MONEY CANT BUY

TOGETHER

GOOD MORNING LITTLE SCHOOLGIRL HOOCH]E-COOCHIE MAN BORN UNDER A BAD SIGN THE HUNTER

Сортировка

в отличие от подхода с использованием varchar, отсортировать виртальньнг табличный массив довольно просто - достаточно добавить в запрос предложение ORDER BY. Удалять элементы также просто - все, что нам для этого необходимо, - оператор DELETE с предложением WHERE. Вставить элемент (в отличие от добавления в конец) значительно сложнее, так как мы используем identity-столбец как индекс массива. Однако это все равно возможно - лнбо с помощью SET IDENTITYJNSERT, либо убрав у столбца свойство identity. Добавить измерение можно также просто, как добавить столбец. Вот пример:

CREATE TABLE larray (band int. single int. title varchar(50))

INSERT #array VALUES(0,0.LITTLE BIT 0 LOVE);

INSERT hrray VALUES(0.1.EIRE AND WATER):

INSERT #array VALUES(0.2.THE FARMER HAD A DAUGHTER):

INSERT larray VALUES(0.3.ALL RIGHT NOW);

INSERT #array VALUESd ,0,BAD COMPANY);

INSERT #array VALUES(1.1,SHOOTING STAR);

INSERT #array VALUESd. 2,FEEL LIKE MAKIN LOVE);

INSERT #array VALUESd ,3,ROCK AND ROLL FANTASY):

INSERT larray VALUES(2,0,SATISFACTION GUARANTEED);

INSERT larray VALUES(2,1.RADIOACTIVE);

INSERT #array VALUES (2.2,MONEY CAN T BUY);

INSERT #array VALUES(2.3,TOGETHER);

INSERT #array VALUES(3,0,GOOD MORNING LITTLE SCHOOLGIRI); INSERT #array VALUES(3.1,HOOCKIE-COOCHIE MAN): INSERT #array VALUES(3.2.MUDDY WATER BLUES); INSERT larray VALUES(3.3,THE HUNTER):

-- Присваиваем значение третьему элементу в четверттой строке массива UPDATE #array

SET title=BORN UNDER A BAO SIGN-WHERE band=3 AND single=2

<

SELECT t itle FROM #array

title

Дaиllыii код создаем диумериыИ массив, .затем пз.меняег значение трет1>е10 э.че-мента в четверто!! строке. Так как индексы этого .массива - просто целочисленные столбцы, 5С)Ь-код, необходи.мый для манипуляций с .массиво.м, значительно более понятен. HanpiLviep, очистка заданного из.хгерення .массива - тривиальная задача:

WHERE band2 SELEC -FROM #a.--ay

bar;d sv.gb .ite

0 0 LITTLE BIT С LOVf С 1 FIRE AND WATER

С 2 THE FARMER HAD A DAUGHIER

G 3 ALL RIGHT NOW

1 0 BAD COMPANY

1 SfiODTING STAR

2 FEEL LIKE MAKIN LOVE

1 3 ROCK AND ROLL FANTAS~-

2 G 2 ! 2 2

3 G GOOD MORNING LITTLE SCHOOLGIRL 3 : HOOCHIE-COOCHIE MAN

3 2 MUDDY WATER BLUES

3 3 THE HUNTER

Данны11 код использует iipocroii оператор UPDATE с предложением WHERE, чтобы очистить третье из.меренне .массива.

Другая изящная особенность этого метода состоит в том, что суммарные значения столбцов и занисей .можно легко получить, при.меняя обычные агрегатные функции и предложение GROUP BY. Вот запрос, которьп! осуществляет различные агрегации, заденствуя индексы массива в качестве столбцов группировктг

CREATE TABLE #аггау (band int. single int. title varcinan(50))

I.NSERT #anray VALUES(O.GLITTLE BIT 0 LOvE):

INSERl #йггау VALUES(0.1.FIRE AND WATER):

INSERT #arnay VALUES(0.2.ALL RIGHT NOW);

INSERT §аггйу VALUES(1.0.BAD COMPANY);

INSERT #anray VALUES (i. I.SHOOTING STAR):

INSERT #anray VALUESd .2.FEEL LIKE MAKIN LOVE);

INSERT #array VALUES(1,3.ROCK AND ROLL FANTASV);

INSERT #array VALUESd ,4,BURNING SKY):

INSERT #annay VALUES(2,0,SAHSFACTION GUARANTEED);

INSERT tarray VALUES(2,I,RADIOACTIVE);

INSERT #згпау VALUES(2,2,MONEY CANT BUY);

INSERT #ап-ау VALUES(2,3,TOGETHER):

LNSERl §array VALUES(3,0,GOOD MORNING LIITLE SCHCOLGIRL):

INSERT iarray VALUES(3,1,HOOCHIE-COOCHIE MAN);

INSERT #annay VALUES(3,2,MUDDY WATER BLUES);

INSERl #arnay VALUES(3,3,THE HUNTER);

SELECT Band, NunnberOfSongsPerBand=COUNT(sing:ej

FROM #annay

GROUP BY Band

SELECT Band. Last Song (Alphabeticany) =MAX(title) FROM #array GROUP BY Band ORDER BY 2

SELECT Single. NumberOfBandsPerSingle=COUNT(Band) FROM #array GROUP BY Single

Band NumberOfSongsPerBand

Band Last Song (Alphabetically)

0 LITTLE BIT 0 LOVE

1 SHOOTING STAR 3 THE HUNTER

2 TOGETHER

Single NumberOfBandsPerSingle

Имейте в виду, что индексные столбцы в этом способе могут иметь типы, отличные от целых, так как мы обращаемся к ним с использованием предложения WHERE. В качестве индексов широко используются также типы datetime, GUID и bit. К индексам .можно обращаться с применением более сложных выражений, чем простейшее =i, где i - индекс массива. Для работы с массивом можно использовать предикаты LIKE, BETWEEN, IN и EXISTS, а также подзапросы.

Транспонирование измерений

Обмен местами измерений массива - также сравнительно простая задача, если применять этот .метод. Например, предположим, что у нас есть двумерный массив и мы хотим поменять местами его строки и столбцы. Как это сделать? С методом, использующим varchar, нам пришлось бы немало потрудиться. Однако это очень просто сделать с помощью табличного подхода и одной особенности оператора UPDATE. Вот код: DECLARE (31 int

UPDATE #array SET (3i=Band. Band=Single. Single=(ai

SELECT *

FROM #array

ORDER BY Band. Single

band single title

0 0 LimE BIT 0 LOVE

0 1 BAD COMPANY

С 2 SAIiSFACIiCN GuARANLEij

С 3 GCOD MORMNG LITTLE SCIiCCLGlRL

1 О FIRE AND WATER

1 I SHOOTING STAR

1 2 RADIOACTIVE

1 3 HOOCHIE-COOCHIE MAN

2 С ALL RIGHT NOW

2 I FEEL LIKE MAKir lCvE

2 MONEY CANT BL

2 3 MUDDY WATER BLUES

3 1 ROCK AND ROLL FANTASY 3 2 TOGETHER

3 3 THE HUNTER

4 1 BURNING SKY

Поскольку Transact-SQL обрабатывается слева направо, мы можем присвоить переменной @i значение band, чтобы поменять местами band (группа) и single (песня). Воз.можность присваивать значеиня локальной переменной с помощью UPDATE была изначально предназначена для улучшения производительности за счет у.меньшения времени блокировок. Эта воз.\к)жность была разработана, чтобы можно было выполнить UPDATE, после этого немедленно выбрать значение из той же са.мой таблицы в локальную нере.мешгую для дальнейшей обработки. В наше.м случае .мы нри.меняе.м эту возможность, а также то, что Tran,sact-SQL выполняется слева направо, для об.мена значений столбцов.

Может быть, когда-нибудь вoз^южнocть повторного использования нере.менной, значение которой было присвоено операторо.м UPDATE в том же са.мом операторе, из.менится, так как это гии-де сиепиально ие документировано. Как и в случае другн.х недоку.ментированных возможностей, применяйте данный способ, если только это действительно необходи.мо, и делайте это осторожно. Воз.можио, он не будет поддерживаться в будущей версии, так что не станьте слишком зависи.мы.мп от него.

За.метьте, что если вы хотите по.меиять .места.ми измерения только в результирующем множестве (ие из.меняя са.м массив), сделать это также легко:

SELECT BanG=single. Single-Eand. Title FRCM #аггау ORDER BY 1. 2

Band Single Title

С С LITTLE BIT С LOVE

0 1 BAD COMPANY

С 2 SATISFACTION GUARANTEED

0 3 GOOD MORNING LITTLE SCHOOLGIRL

1 0 FIRE AND WATER 1 1 SHOOTING STAR

1 2 RADIOACTIVE

1 3 HOOCHIE-COOCHIE MAN

2 0 ALL RIGHT NOW

2 1 FEEL LIKE MAKIN LOVE

2 2 MONEY CANT BUY

2 3 MUDDY WATER BLUES

3 I ROCK AND ROLL FANTASY 3 2 TOGETHER

THE HUNTER BURNING SKY

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

Обеспечение целостности массивов

Существует несколько изящных способов, которые можно применять, чтобы гарантировать правильность значений индексов массивов. Один из них заключается в создании по ним уникальных ограничений (unique constraints). Это можно сделать с помощью ограничения PRIMARY KEY или UNIQUE KEY по соответствующим столбцам. Например, мы могли бы вот так изменить оператор CREATE TABLE, приведенный ранее:

CREATE TABLE #аггау (band int. single int. title varchar(50) PRIMARY KEY (band, single))

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

Изменение отображения массивов

Большинство методов, которые мы исполь.зопали для изменения массивов на основе varchar, можно применять и для табличных массивов. Наиболее гибкий из этих методов заключается в заполнении временной таблицы значениями. Однако табличные .массивы предоставляют нам дрз'гой вариант, содержавши намного меньше кода, который к тому же проще применять:

SELECT Free=MAX(CASE band WHEN О THEN title ELSE NULL END), BadCompany=MAX(CASE band WHEN 1 THEN title ELSE NULL END), TheFirm=MAX(CASE band WHEN 2 THEN title ELSE NULL END), Solo=MAX(CASE band WHEN 3 THEN title ELSE NULL END)

FROM #array a GROUP BY a,single

Free

BadCompany

TheFirm

Solo

LITTLE BIT 0 LOVE

FIRE AND WATER ALL RIGHT NOW NULL NULL

COMPANY

SATISFACTION GUARANTEED GOOD MORNING LITTLE

SHOOTING STAR

FEEL LIKE MAKIK LOVE

ROCK AND ROLL FANTASY BURNING SKY

RADIOACTIVE MONEY CANT BUY TOGETHER NULL

SCHOOLGIRL HOOCH IE-COOCHIE MAN

MUDDY WATER BLUES

THE HUNTER NULL

Этот способ использует афегат, чтобы скрыть выбор столбца title для каждой группы (band), так что мы можем сделать однообразным результирующее множество с помощью GROUP BY. Мы проводим группировку но единственному столбцу, потому что он предоставляет нам подобие утгкального идентификатора, который необходим для объединения элементов массива. Чтобы это понять,