Не создавайте временные таблицы
В стандартном SQL правом создания временных таблиц обладает только человек с полномочиями администратора. Некоторые языки от разработчиков игнорируют это правило, позволяя создавать временные таблицы в ходе выполнения программы. Не используйте эту возможность. Применяйте вместо временных таблиц подзапросы, производные таблицы или представления. Применение временных таблиц обычно является признаком плохого дизайна. Чаще всего их используют для хранения промежуточных данных, как мы в 1950-х годах использовали временные магнитные ленты.
Есть два основных вида обработки ошибок. В семействе Sybase/SQL Server применяется последовательная модель. После выполнения очередного выражения SQL задает значение глобальной переменной с кодом ошибки. Программист должен написать программу для анализа этой переменной и выполнения необходимых поправок.
В модели SQL/PSM используется модель прерываний. Существует глобальный параметр SQLSTATE (старый SQLCODE более не действует), который способен передавать в кэш несколько значений. Эти значения приводят к выполнению действий, заданных в выражениях WHENEVER, связанных с программным блоком. Обработка ошибок — трудное дело, поэтому в соответствующих местах модуля не скупитесь на комментарии.
Как можно больше действий выполняйте посредством выражений SQL, а не 4GL-nporpaммы. В идеале хранимая процедура должна содержать единственное SQL-выражение, возможно, с несколькими параметрами. Если выражений несколько, их последовательность лучше всего оформлять посредством конструкции “BEGIN [ATOMIC].. END”. Вы теряете очки с каждой конструкцией “IF..THEN..ELSE”, не говоря уже о циклах.
Не используйте курсоры
Обоснование
Курсор (cursor) представляет собой способ преобразования набора данных в последовательный файл для чтения в хост-языке. У курсоров в стандартном SQL масса возможностей, а в различных продуктах — и того больше.
Курсоры обладают плохой переносимостью и обычно работают существенно медленнее непроцедурных выражений SQL — медленнее на порядки величины. В целях безопасности ядро SQL предполагает, что внутри курсора может случиться все, что угодно, и потому размещает эту транзакцию на самом высоком уровне, блокируя других пользователей.
Зачем же они тогда вообще нужны? Главные причины — незнание основ SQL и старые привычки. Модель курсоров в SQL следует семантике ленточных файлов, от которой многие не в силах отказаться. Вот детальная аналогия:
ALLOCATE <имя курсора> = назначаем канал магнитофону
DECLARE <имя курсора> CURSOR FOR . = монтируем ленту и объявляем файл
OPEN <имя курсора> = открываем файл
FETCH <направление курсора> <имя курсора> INTO <локальные переменные>
= читаем в программе по одной записи за раз, затем перемещаем головку чтения/записи
CLOSE <имя курсора> = закрываем файл
DEALLOCATE <имя курсора> = освобождаем магнитофон
Если вы добавите к этому использование временных таблиц в качестве временных лент, то получите полный аналог ленточной системы образца середины 1950-х годов без каких бы то ни было следов реляционного мышления. В 2004 году пример такого подхода был опубликован в одной из групп новостей по SQL Server. Новичок написал один курсор для просмотра первой таблицы и размещения строк, удовлетворявших некоему критерию, во временную таблицу. Второй курсор просматривал вторую таблицу, упорядоченную по ключу; внутри этого цикла третий курсор просматривал временную таблицу, выбирал совпадающие строки и производил обновление. Это был классический образчик приемов пятидесятилетней давности, реализованных средствами SQL. Все 25 или около того выражений этого кода были в итоге заменены одним оператором UPDATE со скалярным выражением подзапроса. Этот оператор работал почти в тысячу раз быстрее.
Исключения
Курсор может пригодиться лишь в экстремальных обстоятельствах, например для исправления неудачно составленной таблицы, в которой так много дубликатов строк или неверных данных, что ее действительно нужно просматривать строка за строкой, прежде чем применить к ней оператор ALTER TABLE, чтобы исправить ошибки дизайна. Ниже перечислены возможные варианты применения курсоров.
1. С помощью курсоров можно создавать средства для работы с метаданными, но лучше этого не делать, ограничившись средствами, которые предлагает вам разработчик. Непосредственное вмешательство в информационные таблицы схемы чревато неприятностями.
2. С помощью курсоров в SQL можно решать NP-полные задачи, когда вас устраивает первый попавшийся ответ, попадающий в определенные пределы, например задачи о коммивояжере и рюкзаке. Но при работе с БД подобные задачи попадаются нечасто, и решать их лучше средствами процедурных языков и переборных алгоритмов.
3. В T-SQL и других продуктах, где до сих пор применяется физически непрерывное хранение даных, медиана гораздо быстрее рассчитывается при помощи курсора, чем при помощи решений, основанных на наборах данных. В продуктах с другими методами хранения или индексации медиана вычисляется тривиально.
4. Теоретически можно написать код, который будет работать хуже курсора. Рассмотрим в качестве примера слегка подчищенный код, опубликованный в группе новостей по SQL Server в ноябре 2004 года. У автора имелась таблица с миллионом строк, и ему надо было “кое-что сделать” с каждой из них. Других подробностей он не сообщил. В сообщении содержался псевдокод на диалекте T-SQL, который в переводе на стандартный SQL выглядел примерно так:
CREATE PROCEDURE TapeFileRoutine()
BEGIN
-- используем временную таблицу в качестве временной ленты
DECLARE maxrecs INTEGER;
DECLARE current_row INTEGER;
DECLARE temp_a INTEGER;
DECLARE temp_b INTEGER;
INSERT INTO ScratchTape (record_nbr, temp_a, temp_b)
SELECT {{автонумератор}}, coll, col2
FROM MyBigTable;
SET maxrecs = (SELECT COUNT(*) FROM ScratchTape);
SET current_row = 0;
WHILE (current_row < maxrecs)
DO
-- извлекаем значения
SELECT colj, col_2 INTO temp_a, temp_b
FROM ScratchTape
WHERE rec_id = current_row; -- здесь манипулируем данными
SET current_row = current_row + 1;
END WHILE;
END;
Да-да, вы наблюдаете алгоритм для работы с последовательным ленточным файлом, пришедший из середины XX века и реализованный средствами начала XXI века. Автор желал знать, удалось ли ему найти в этом фрагменте наиболее эффективный способ работы с данными. Ответ очевиден — даже курсор был бы лучше.
Вы удивитесь, узнав, как много новичков пытаются средствами SQL вернуться в эпоху магнитофонов. Но еще более достойна удивления реакция автора. На упреки он возразил, что код уже и так работает достаточно быстро. Проблемы с переносимостью, замедление работы на порядки величины, лишние строки программы, которые необходимо иметь в виду при ее обслуживании — все это он считал проблемами, выходящими за рамки его компетентности.