Выпуск 6

Здравствуйте, уважаемые коллеги. Представляю Вашему вниманию новый выпуск рассылки.

Содержание рассылки:

• Новости
• Дополнения к предыдущему выпуску
• Оператор SELECT
• Оператор INSERT
• Оператор UPDATE
• Операторы DELETE и TRUNCATE TABLE

На сайт Sybase.com выложен новый EFB 9.0.1252 для платформ Windows и Netware. Размер 54 мб.Интересное в патче:

• Добавлена возможность указывать имена полей или алиасов вместо порядкового номера поля в ORDER BY для запросов с UNION, INTERSECT и EXCEPT.
• В EXECUTE IMMEDIATE добавлена новая опция WITH RESULT SET {ON | OFF}, позволяющая указать, будет ли динамический SQL возвращать набор данных. Эта опция позволяет точно контролировать выполнение динамического SQL. Но самое главное - она позволяет ASA узнать при компиляции хранимых процедур, будут ли они возвращать набор данных. До этого патча любое использование оператора динамического SQL в хранимой процедуре приводило к тому, что ASA считала ее процедурой, возвращающей данные, хотя реально, процедура могла и не возвращать данные, если динамический SQL ничего не возвращал.

Есть и вторая интересная новость: вышел долгожданный драйвер для ASA, позволяющий в Borland Delphi напрямую работать с ASA через dbExpress. Размер 55 кб.

Некоторые вещи были обнаружены мной после выхода предыдущего выпуска рассылки:

• Хранимые процедуры и пользовательские функции оказывается в ASA на самом деле полностью идентичны и воспринимаются ASA, как хранимые процедуры. Если посмотреть на системную таблицу SYSPROCEDURE, где хранятся их описания, то видно, что никакого флага принадлежности обьекта к процедуре или функции в этой таблице нет. Если Вам необходимо динамически сгенерить скрипт удаления функций из базы данных, то Вы можете применить оператор DROP PROCEDURE. Так же процедуры, не возвращаюшие наборы данных, могут быть использованны в качестве функций, но я бы не рекомендовал это делать, так как эта возможность явно не документированна.
• Мной была обнаружена ошибка критическая ошибка сервера, возникающая в случае, если в таблицу заноситься запись с NULL значением в поле NOT NULL, в триггере BEFORE этому полю присваивается NOT NULL значение и существует триггер AFTER. Без триггера AFTER ошибки не происходит. В близжайшее время я выложу этот баг для команды разработчиков ASA, думаю в следующем EFB эта ошибка будет исправлена. В дополнение, если у кого то есть собственная коллекция обнаруженных багов ASA, не исправленных в текущей версии EFB, то можно высылать их описания мне по почте. Можно будет включить в рассылку описания обнаруженных русскоязычными разработчиками багов, централизованно отсылать их в Sybase и отслеживать исправления ошибок в EFB. Более подробно со мной пообщаться можно по почтовому ящику ascrus@mail.ru

Оператор выборки данных в ASA обладает достаточно большой функциональностью. Я не буду рассматривать его реализацию на диалекте TSQL, она полностью совместима с реализацией SELECT в MSSQL и ASE. Гораздо интересней будет описать отличия его реализации на WatcomSQL от TSQL:

• Расширенная реализация JOIN
  Поддерживаются 3 вида способов соединения таблиц в JOIN - по связи таблиц (KEY JOIN) , по совпадающим именам полей (NATURAL JOIN) и по определенным связям таблиц (обычные JOIN стандарта ANSISQL):

  -- Запрос 1 - обычный INNER JOIN SELECT * FROM fin_code c   INNER JOIN fin_data d ON d.code = c.code; -- Запрос 2 - обычный LEFT JOIN с фильтром по году SELECT * FROM fin_code c   LEFT JOIN fin_data d ON d.code = c.code AND d.year = 2000; -- KEY INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   KEY JOIN fin_data d; -- KEY INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   JOIN fin_data d; -- KEY LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   KEY LEFT JOIN fin_data d ON d.year = 2000; -- KEY LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   LEFT JOIN fin_data d WHERE d.year = 2000; -- LEFT JOIN, не аналогичен запросу 2, так как -- пропущено ключевое слово KEY и в ON указано -- условие соединения. В данном случае запрос -- является ошибочным SELECT * FROM fin_code c   LEFT JOIN fin_data d ON d.year = 2000; -- NATURAL INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   NATURAL JOIN fin_data d; -- NATURAL LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   NATURAL LEFT JOIN fin_data d ON d.year = 2000;

  Из вышеприведенных примеров видно, что если ключевое слово ON для JOIN не указано, то такое обьединение таблиц будет восприниматься в запросе как KEY JOIN, ASA попробует связать таблицы по FOREIGN KEY и если внешний ключ не будет обнаружен, то ASA возбудит ошибку. Если Вы хотите, чтобы связь поисходила по совпадающим именам полей, то всегда необходимо писать ключевое слово NATURAL, хотя я бы не рекомендовал такой рискованный способ соединения таблиц в запросе, так как любое переименование полей в таблице приведет к неправильной работе таких запросов, где она участвует. При явном указании ключевых слов KEY или NATURAL можно писать дополнительные условия соединения в ON. В отличие от NATURAL, KEY JOIN наоборот позволяет не только упростить читабельность запросов, но и при изменении структуры таблиц (переименование полей, изменение внешнего ключа и т.д.) сохранить работоспособность обьединения в запросах.

  KEY и NATURAL JOIN можно так же использовать для соединения в запросах таблиц с представлениями, (для KEY JOIN представления не должны содержать групповые операции). Однако я бы не рекомендовал пользоваться этой возможностью - в отличие от таблиц, для представлений предпочтительней явное соединение. Это гарантирует, что соединение произойдет по указанным Вами полям, в случае же с KEY JOIN поля для соединения будут выбраны ASA неявно для Вас, что будет больше напоминать гадание на кофейной гуще и гарантировать проблемы при изменении скрипта представления.
• Возможность работы с алиасами
  Не так редко приходиться в запросах возвращать вычисляемое выражение, используя его в разных местах запроса:

  /* Сумма продаж продукции на 16.03.2000 с их оценкой, но без учета продукции, сумма которой превышает 1500 */ SELECT p.name, Sum(p.unit_price * p.quantity) as Summa,   CASE WHEN sum(p.unit_price * p.quantity) >= 1000 THEN 'Отлично'     ELSE 'Так себе' END as Descript FROM product p   KEY JOIN sales_order_items i ON i.ship_date = '2000-03-16' GROUP BY p.name HAVING Sum(p.unit_price * p.quantity) <= 1500

  В вышеприведенном запросе выражение, возвращающее колонку Summa используется три раза, что не удобно с точки зрения читабельности запросов и простоты его написания. В WatcomSQL в запросе разрешается обращаться к алиасу возвращаемой колонки наравне с полями.

  Перепишем вышепереведенный запрос:

  /* Сумма продаж продукции на 16.03.2000 с их оценкой, но без учета продукции, сумма которой превышает 1500 */ SELECT p.name, Sum(p.unit_price * p.quantity) as Summa,   CASE WHEN Summa >= 1000 THEN 'Отлично'     ELSE 'Так себе' END as Descript FROM product p   KEY JOIN sales_order_items i ON i.ship_date = '2000-03-16' GROUP BY p.name HAVING Summa <= 1500

  Запрос гораздо легче пишется и читается. Единственное, хочу заметить, что при встрече в запросе имен, без явного указания алиаса и совпадения имен полей и имен алиасов, имена алиасов будут иметь более высокий приоритет, чем имена полей.
• Расширения с помощью WITH
  Судя по всему WITH перекочевал в ASA из IBM DB2, однако с учетом того, что он появиться в новой версии MSSQL, этот оператор уже постепенно становиться стандартом SQL. Данный оператор расширяет возможности команды SELECT по работе с подзапросами по технологии Common Table Expression (CTE). Чтобы лучше понять его назначение рассмотрим пример:

  SELECT dept_id, n FROM (   SELECT dept_id, count(*) AS n   FROM employee GROUP BY dept_id ) AS a WHERE a.n = (         SELECT max(n)         FROM (           SELECT dept_id, count(*) AS n           FROM employee GROUP BY dept_id         ) AS b       )

  С точки зрения семантики запрос правильный, однако с точки зрения читабельности и легкости написания запрос нельзя назвать легким. Конечно, если подзапрос на получение кол-ва dept_id по employee используется во многих запросах, легче всего его оформить как представление (view) и дальше уже использовать его, вместо подзапроса. Однако создание представления может быть нежелательно по следующим причинам:

  • Запрос генерируется динамически и вызывается в динамическом SQL из хранимой процедуры. Создание или удаление представления вызывает блокирование системных таблиц, что не желательно делать во время выполнения хранимой процедуры.
  • У подзапроса есть фильтр по динамическим значениям параметров, переданным например в хранимую процедуру. Конечно можно использовать глобальные переменные в представлениях или оформить подзапрос в виде хранимой процедуры, однако в предыдущем выпуске рассылки я уже не рекомендовал без серьезного обоснования использовать эти решения. В данном случае такие решения не подходят.
  • Усложняется читабельность. Чтобы понять логику действия запроса, ссылающегося на представление, придется посмотреть на исходный скрипт представления.
  • Усложняется модифицируемость. Если подзапрос необходимо изменить, то придется изменять представления, что может гарантировать множество проблем, если на представление есть ссылки из других запросов.

  Думаю уже причин достаточно, чтобы показать, что использование представлений не всегда желательно. Для решения этих проблем как раз и существует оператор WITH, который дает возможность описать к запросу временное (локальное) представление:

  WITH CountEmployees(dept_id, n) AS (   SELECT dept_id, count(*)   FROM employee GROUP BY dept_id ) SELECT dept_id, n FROM CountEmployees WHERE n = ( SELECT max(n) FROM CountEmployees )

  В данном примере оператор WITH описывает локальное для данного запроса представление CountEmployees, на которое и ссылается сам запрос. Оператор WITH позволяет описать множество представлений и использовать их в одном запросе. Над такими представлениями можно проводить в запросе любые операции так, как если бы они были реальными представлениями.

  Оператор WITH позволяет реализовать еще одну полезную функцию в запросах - возможность написания иерархических запросов. Думаю всем приходилось сталкиваться с трудностями реализации таких запросов в ANSISQL. Решения получались трудоемкие и неэффективные. В данном случае WITH позволяет писать такие запросы простым и элегантным способом:

  WITH RECURSIVE   manager ( emp_id, manager_id,             emp_fname, emp_lname, mgmt_level ) AS     ( ( SELECT emp_id, manager_id, -- главный запрос                emp_fname, emp_lname, 0         FROM employee AS e         WHERE manager_id = emp_id )         UNION ALL       ( SELECT e.emp_id, e.manager_id, -- рекурсивный запрос                e.emp_fname, e.emp_lname, m.mgmt_level + 1         FROM employee AS e JOIN manager AS m               ON e.manager_id = m.emp_id AND                  e.manager_id <> e.emp_id AND                  m.mgmt_level < 20 ) ) SELECT * FROM manager ORDER BY mgmt_level, emp_lname, emp_fname