Недавно я вел в Европе углубленный курс по T-SQL, и один из учащихся, Николас Барро из компании Avanade France SAS, предложил небольшую интересную задачу. Она была не очень сложной, но любопытной, и надеюсь, вы тоже получите удовольствие, работая над ней.

Задача, предложенная Николасом, состоит в подготовке произвольной буквенной циклической, допускающей пропуски, последовательности со значениями в форме LLL, где L — буква латинского алфавита от A до Z (26 символов). Компания Николаса использует значение последовательности как флаг совпадения для сопоставления, например, между платежом и счетом-фактурой. Первое сформированное значение должно быть AAA, а последующие формируются так, как если бы буквы были цифрами с основанием 26:

AAA
AAB
…
AAY
AAZ
ABA
ABB
…
ZZY
ZZZ

В целом мы получаем 17 576 уникальных значений. После того как последовательность достигает максимального значения ZZZ, она возвращается к значению AAA. За исключением перехода, обеспечивающего цикличность, значения должны возрастать, но не обязательно идти подряд, то есть допустимы пропуски в целях повышения производительности.

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

CREATE OR ALTER PROC
  dbo.NextValForMySequence
  @val CHAR(3) OUTPUT
AS
<ваш программный код здесь>
GO

Используйте приведенный ниже программный код для тестирования процедуры:

DECLARE @myval AS CHAR(3);
EXEC dbo.NextValForMySequence
  @val = @myval OUTPUT;
SELECT @myval;

Выходные данные при многократном выполнении должны быть такими, как показано на рисунке 1.

 

Желаемые выходные данные
Рисунок 1. Желаемые выходные данные

 

Хранение значения как строки символов и блокирование последовательности

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

В листинге 1 приводится программный код для создания и заполнения таблицы значением ZZZ, то есть значением, непосредственно предшествующим (при реализации цикличности) первому, которое нужно сформировать, — AAA.

Следующий ход был предложен Полом Уайтом. Обратите внимание на использование двоичной сортировки для столбца. Дело в том, что нам нужны только прописные буквы без акцентуации от A до Z. При использовании словарного порядка без учета регистра шаблон [A-Z] представляет следующие символы (упорядоченные), как показано на рисунке 2.

 

Шаблон [A-Z] без учета регистра
Рисунок 2. Шаблон [A-Z] без учета регистра

 

В...

Это не вся статья. Полная версия доступна только подписчикам журнала. Пожалуйста, авторизуйтесь либо оформите подписку.
Купить номер с этой статьей в PDF