Построковая обработка sql выборки в сценарии
Содержание
Введение
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
Возьмем к примеру следующую таблицу notsorted с двумя столбцами id и name:
id | name |
0 | Иванов Иван |
3 | Петров Петр |
4 | Сергеев Сергей |
5 | Дмитриев Дмитрий |
6 | Алексеев Алексей |
Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт.
Для таблицы с числовым полем
Начальное значение переменной 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. Далее совершаем необходимые действия со строкой.
Результат:
Недостатки: Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано только одно из этих значений (в связи с алгоритмом).
Для таблицы со строковыми полями
Начальному значению счетчика 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
Результат:
Недостатки: сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.
Достоинства: в таблице не обязательно иметь числовое поле - выборку можно сделать по строковым полям. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.
Метод с for xml path
1)Из таблицы получаем xml структуру и записываем её в переменную text_xml. Присвоение происходит неявным образом, для этого необходимо чтобы была создана переменная с именем, которое совпадает с именованием возвращаемого столбца.
Текст запроса:
select cast( ( select * from oktell.dbo.notsorted for xml path ('') ) as xml ) as "text_xml"
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)Завершаем сценарий, выводим уведомление.