Масиви в SQL Server 2000 (исходники), Різне, Програмування, статті

Іноді доводиться шкодувати про те, що в 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)
Переклад: Моісеєнко С.І.
Оригінал перекладу 


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*