Плохой стиль программирования на SQL и процедурных языках

Как пример отсутствия познаний в применении реляционных подходов к программированию, рассмотрим сообщение, опубликованное в группе новостей comp.databases.ms-sqlserver в январе 2005 г. Тема сообщения — “Как искать в таблице отсутствующие записи” — уже говорит о том, что автор письма мыслит в терминах файловой системы, а не в терминах системы управления реляционными базами данных.
В описании таблицы содержался обычный для новичка столбец id, и не было ни ключа, ни каких-либо ограничений. В таблице содержались строки для каждого дня одного года, которые идентифицировались порядковым номером недели внутри года (от 1 до 53) и порядковым номером дня недели (от 1 до 7). Ниже представлена структура рассматриваемой таблицы. От оригинальной она отличается только именами атрибутов:

CREATE TABLE WeeklyReport (id INTEGER AUTONUMBER NOT NULL, - плохой SQL! week_nbr INTEGER NOT NULL, day_nbr INTEGER NOT NULL);

После удаления бесполезного столбца id и добавления ограничений мы получаем такую таблицу:

CREATE TABLE WeeklyReport (week_nbr INTEGER NOT NULL CHECK(week_nbr BETWEEN 1 AND 53), day_nbr INTEGER NOT NULL CHECK(day_nbr BETWEEN 1 AND 7), PRIMARY KEY(week_nbr, day_nbr));

Хотя на словах автор указал на множество ограничений, в определении таблиц он их применить не удосужился. У новичков таблица ассоциируется с файлом, а не с множеством. Для них единственным критерием, в соответствии с которым данные необходимо поместить в файл, является сам процесс записи в файл. А файл не может проверить достоверность содержащихся в нем данных. Введение в таблицу автонумерации симулирует нумерацию записей, свойственную последовательной файловой системе.
Сама задача заключалась в нахождении первого отсутствующего дня внутри каждой недели с последующим добавлением соответствующей строки. Если к записываемым данных и предъявлялись какие-либо требования, то в описании они представлены не были. Вот собственное решение автора задачи (оно переведено на стандартный SQL с диалекта T-SQL с небольшими изменениями в именах переменных и атрибутов):

CREATE FUNCTION InsertNewWeekDay (IN my_week_nbr_nbr INTEGER) RETURNS INTEGER LANGUAGE SQLBEGIN DECLARE my_day_nbr INTEGER; DECLARE result_day_nbr INTEGER; SET my_day_nbr = 1; xx: WHILE my_day_nbr < 8 DO IF NOT EXISTS (SELECT * FROM WeeklyReport WHERE day_nbr = my_day_nbr AND week_nbr = my_week_nbr_nbr) THEN BEGIN SET result_day_nbr = my_day_nbr; LEAVE xx; END; ELSE BEGIN SET my_day_nbr = my_day_nbr + 1; ITERATE xx; END; END IF; END WHILE;RETURN result_day_nbr; END;

Это типичная имитация оператора цикла FOR, который использовался во всех языках программирования третьего поколения. Однако если вы посмотрите на этот код в течение двух секунд, то увидите, что он плох и как программа на процедурном языке! Отсутствие программистских навыков не исправляется даже средствами SQL Больше того, последствия неудачного решения, вызванного стремлением подражать процедурным языкам программирования, в SQL только усугубляются. Оптимизаторы и компиляторы SQL не рассчитаны на оптимизацию кода, написанного в стиле, свойственном процедурным языкам. Даже если автор и решил придерживаться процедурного стиля программирования, он должен был следовать классическому построению кода. А для этого нужно было удалить лишние локальные переменные и избавиться от замаскированных операторов безусловного перехода GOTO:

CREATE FUNCTION InsertNewWeekDay (IN my_week_nbr INTEGER) RETURNS INTEGER LANGUAGE SQL BEGIN DECLARE answerjibr INTEGER;SET answer_nbr = 1; WHILE answer_nbr < 8 DO IF NOT EXISTS (SELECT * FROM WeeklyReport WHERE day_nbr = answer_nbr AND week_nbr = my_week_nbr) THEN RETURN answer_nbr; ELSE SET answer_nbr = answer_nbr + 1; END IF; END WHILE;RETURN CAST (NULL AS INTEGER); - приводит к ошибке END;

Здесь необходимо обратить внимание на следующее упущение автора. Он не объяснил, как следует обходиться с той неделей, в которой уже представлены все семь дней. В исходной структуре таблицы благодаря отсутствию ограничений в качестве номера дня и недели допускается любое целое значение. В исправленном описании любое значение номера дня недели, выходящее за рамки интервала от 1 до 7, нарушает ограничение для первичного ключа. Это, конечно, не лучшее решение, но, по крайней мере, оно позволяет удовлетворить очевидным требованиям с минимальными усилиями.
Сможем ли мы решить эту задачу без циклической структуры и получить чисто непроцедурное SQL-решение? Да, и здесь возможно несколько вариантов. Конечной целью поиска отсутствующего дня недели является вставка в таблицу строки. Так почему бы не сделать все это в рамках одной процедуры, вместо того чтобы осуществлять поиск в одной функции, а последующее добавление строки — на последующем процедурном шаге? Давайте будем мыслить на уровне процесса в целом, а не в рамках последовательных шагов.
Первое решение смотрится неуклюже и трудно обобщается. Но оно работает достаточно быстро, если оптимизатор обеспечивает вынос всех подзапросов из операторов WHEN и их обработку как единого целого. В данном решении локальные переменные уже не используются:

CREATE PROCEDURE InsertNewWeekDay (IN my_week_nbr INTEGER)LANGUAGE SQLINSERT INTO WeeklyReport (week_nbr, day_nbr)VALUES (my_week_nbr, (CASE WHEN 1 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 1 WHEN 2 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 2 WHEN 3 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 3 WHEN 4 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 4 WHEN 5 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 5 WHEN 6 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 6 WHEN 7 NOT IN (SELECT day_nbr FROM WeeklyReport WHERE week_nbr = my_week_nbr) THEN 7ELSE NULL END; -- NULL нарушает ограничение для первичного ключа

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

CREATE PROCEDURE InsertNewWeekDay (IN my_week_nbr INTEGER)LANGUAGE SQLINSERT INTO WeeklyReport (week_nbr, day_nbr) (SELECT my_week_nbr, MIN(n) FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) AS Weekdays(n) WHERE NOT EXISTS (SELECT * FROM WeeklyReport AS W WHERE W.weekjibr = my_week_nbr AND Weekdays.n = W.day_nbr));

Также вы можете использовать чистый набор операций над множествами. С их помощью можно убрать сразу все подмножество дней недели, уже присутствующих в таблице, а затем из остатка отобрать наименьшее значение:

CREATE PROCEDURE InsertNewWeekDay (IN my_week_nbr INTEGER)LANGUAGE SQLINSERT INTO WeeklyReport (week_nbr, day_nbr)SELECT my_week_nbr, MIN(n) FROM (VALUES (1),(2),(3),(4),(5),(6),(7) EXCEPT SELECT day_nbr FROM WeeklyReport AS W WHERE W.week_nbr = my_week_nbr) AS N(n);

Если в таблице присутствуют все семь дней недели, мы получим пустое множество, что приведет к попытке присваивания атрибуту day_nbr пустого значения NULL В свою очередь, это вызовет нарушение допустимых границ значений для первичного ключа.
Далее представлена третья обобщенная версия решения с использованием таблицы Sequence, в которой в общем случае может заключаться любой необходимый диапазон целочисленных значений. Только запомните, что наличие данной таблицы должно быть определено соответствующими инструкциями DDL

CREATE PROCEDURE InsertNewWeekDay (IN my_week_nbr INTEGER)LANGUAGE SQLINSERT INTO WeeklyReport (week_nbr, day_nbr)SELECT my_week_nbr, MIN(n) FROM (SELECT seq FROM Sequence WHERE seq <= 7 -- изменить на любое значение EXCEPT SELECT day_nbr FROM WeeklyReport AS W WHERE W.week_nbr = my_week_nbr) AS N(n);

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