Просмотр исходного текста страницы Построковая обработка sql выборки в сценарии

[[Практики|Наверх]]

__TOC__


== Введение ==

Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.

Возьмем к примеру следующую таблицу notsorted с двумя столбцами '''id''' и '''name:'''  
 

<center>
 {| class="simple" border="1" style="text-align:center" style="center"
 |+ style="background:#FFCC00"|'''notsorted'''
 |id
 |name
 |-
 | 0 
 |Иванов Иван
 |-
 | 3 
 |Петров Петр
 |-
 | 4 
 |Сергеев Сергей
 |-
 | 5 
 |Дмитриев Дмитрий
 |-
 | 6 
 |Алексеев Алексей
 |}
</center>


Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт. 

== Для таблицы с числовым полем ==


[[Файл:Обработка таблицы 3.PNG | center]]


Начальное значение переменной id_before присваивается "-1000", далее с помощью sql-запроса получаем самую первую строку (значения '''id''', ''' name'''), у которой '''id > id_before'''. Записываем '''id''' в переменную '''id_after'''. В этом же sql-запросе получаем количество строк, которые попадают под это условие. Вид запроса: 

 select top 1 @id=id, @name=name from notsorted where id > @id_before
 set @rowcount=@@rowcount

Если количество строк, у которых условие '''id > id_before''' равно нулю (т.е. таких строк нет), то это является индикатором конца цикла. Таким образом в проверке на завершение происходит сравнение rowcount с нулем. Если строки нашлись, то присваиваем '''id_before'''  текущее значение '''id_after'''. Далее совершаем необходимые действия со строкой. 

'''Результат:'''


[[Файл:Обработка таблицы 3-1.PNG | center]]


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


== Для таблицы со строковыми полями ==

[[Файл:Обработка таблицы 4.PNG | center]]


Начальному значению счетчика '''i''' присваиваем '''0'''. Конечное значение счетчика '''ilast''' равняется количеству строк в таблице, которые необходимо обработать, и находится с помощью sql-запроса:

 select @ilast = count(*) from notsorted

Затем счетчик инкрементируется и входит в цикл. Условием окончания цикла является прохождения ilast строк, таким образом если '''i > ilast''', то цикл заканчивает выполняться. Далее необходимо выбрать необходимую строку. Предполагая, что оперируем только со строковым полем предлагается следующий алгоритм. Строки сортируются лексикографически по убыванию, затем выбираются первые '''i''' строк. Затем выбирается самая последняя строка (полученная выборка сортируется лексикографически по возрастанию и выбирается первая запись). Таким образом происходит доступ к элементу. 

Так как нет возможности формировать sql-запрос динамически (добавив в него переменные), запрос формируется отдельно в строке '''sql''' (компонент присвоение). Вид формируемой строки: 

 'Select Top 1 name as name1
 From (
 	Select Top ' +Str([i])+ ' name
 	From notsorted
 	Order by name desc
 ) a
 Order by name asc'
 
Выполнить данный запрос можно с помощью компонента '''sql-запрос'''. При выполнении нужное значение, как видно из запроса, сохраняется в строку '''name1'''. Необходимо в сценарии заранее создать переменную с идентичным названием. Строка запроса (в параметрах определяем '''@sql''' как строку с запросом '''sql''').  Фактически, "строка" преобразовывается в "SQL-запрос".

 exec sp_executesql @sql 

'''Результат:''' 

[[Файл:Обработка таблицы 4-1.PNG | center]]


'''Недостатки:''' сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.

'''Достоинства:''' в таблице не обязательно иметь числовое поле - выборку можно сделать '''по строковым полям'''. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.


== Метод с for xml path ==

[[Файл:postrokova9_3.jpg | center]]


1)Из таблицы получаем xml структуру и записываем её в переменную text_xml. Присвоение происходит неявным образом, для этого необходимо чтобы была создана переменная с именем, которое совпадает с именованием возвращаемого столбца.

Текст запроса:
<pre>
select cast( ( select * from oktell.dbo.notsorted for xml path ('') ) as xml ) as "text_xml"
</pre>

2)Подсчитываем количество тегов id и записываем результат в cnt_id. 

3)Устанавливаем счетчик итераций i=1. 

4)Далее входим в цикл:
:4.1)Получаем i-ый тег id
:4.2)Получаем i-ый тег name
:4.3)Выводим результат в уведомлении, на данном шаге с результатами можно произвести какие либо действия.
:4.4)Производим инкремент i на 1
:4.5)Проверяем условие i<=cnt_id, если перебрали еще не все теги то идем на шаг 4.1, иначе на шаг 5
5)Завершаем сценарий, выводим уведомление.


[[Файл:postrokova9_3_1.jpg | center]]

Метод является универсальным и не зависит от вида данных.

== Файлы ==

* [http://wiki.oktell.ru/images/6/66/%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_1.zip Для таблицы с числовым полем]

* [http://wiki.oktell.ru/images/6/6d/%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_2.zip Для таблицы со строковыми полями]

*[http://wiki.oktell.ru/images/f/f8/Svc_%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_for_xml_path.oscr Метод с for xml path]