Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

Например, множество предикатов в форме

<выражение-или-константа> = COLUMNX

делают работу по исправлению ошибок и пересмотру кода каторжным трудом по сравнению с

COLUMNX = <выражение-или-константа>

Важно принимать дополнительные "усилия", чтобы сделать все выражения совместимыми.

Массивы, BLOB и строки

Массивы вовсе не могут использоваться в предикатах поиска, поскольку SQL не имеет средств доступа к хранящимся в массивах данным. Выражения для столбцов BLOB являются весьма ограниченными, BLOB не может сравниваться на равенство ни с другим BLOB, ни с любым другим типом данных. Текстовый BLOB может использоваться в STARTING WITH и с некоторыми ограничениями в предикатах LIKE и CONTAINING. Некоторые внешние функции могут работать с типами BLOB - см. функции BLOB В приложении 1.

Строки могут проверяться любыми операторами сравнения, хотя такие операторы, как <, >, >= и <=, не часто являются полезными.

Условия упорядочения и группирования

Когда выходное поле создается в выражении, оно может быть использовано для установления условий упорядочения или группирования набора. При этом синтаксис правил для выражений в ORDER BY и GROUP BY различается, также существуют различия между версиями 1.0.x и 1.5.

Выражения для ORDER BY

На поле, созданное во время выполнения с использованием выражения, не может быть ссылок как на условие в предложении ORDER BY по его алиасу. Оно может быть использовано с помощью ссылки на его номер в наборе - то есть на его позицию в строке, считая единицей позицию самого левого выходного поля. Например, следующее

SELECT

MEMBER_ID,

LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME,

JOIN_DATE

FROM MEMBERSHIP

ORDER BY 2;

создает список, упорядоченный по полю, полученному в результате конкатенации.

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

SELECT DOCUMENTED, TITLE, DESCRIPTION FROM DOCUMENT

ORDER BY STRLEN(DESCRIPTION) DESC;

Выражения для GROUP BY

В SQL оператор GROUP BY используется для собирания наборов данных и иерархической их организации или суммирования.

Во всех версиях Firebird вы можете выполнять иерархическое группирование, основанное на вызове внешней функции (UDF), однако в версии 1.0.x вы не можете использовать порядковый номер выходного столбца в качестве условия группирования. Firebird 1.5 добавляет возможность использования в GROUP BY порядкового номера, а также несколько других типов выражений, включая выражения подзапросов.

Другие изменения в Firebird 1.5 ужесточают отдельные правила группирования, убирая поддержку некоторых неправильных вариантов синтаксиса группирования, которые были допустимы в Firebird 1.0.x.

Правила и взаимодействия выражений для группирования и упорядочения иногда являются сложными в реализации. Эти темы подробно обсуждаются в главе 23.

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

SELECT

MEMBER_TYPE,

EXTRACT(MONTH FROM J0IN_DATE) AS MONTH_NUMBER,

/* 1, 2, и т.д. */

F_CMONTHLONG(JOIN_DATE) AS SMONTH,

/* UDF, возвращающая месяц в виде строки */

COUNT (*) AS MEMBERS_JOINED

FROM MEMBERSHIP

GROUP BY

MEMBER_TYPE, EXTRACT(MONTH FROM JOIN_DATE);

Выражения CHECK в DDL

Применение выражений не ограничивается только DML; мы уже видели их использование в определении таблицы для описания вычисляемых столбцов. Каждый раз, когда вы определяете ограничение CHECK для таблицы, столбца или домена, вы применяете выражения. По своей природе ограничение CHECK выполняет проверку на одно или более значений - тестирует предикат. Вот пример, где проверяется номер сотового телефона участника, который должен начинаться с нуля, если номер вообще присутствует:

ALTER TABLE MEMBERSHIP

ADD CONSTRAINT CHECK_CELLPHONE _NO

CHECK (CELLPHONE_NO IS NULL OR CELLPHONE_NO STARTING WITH '0');

Выражения в PSQL

Процедурный язык для триггеров и хранимых процедур PSQL широко использует выражения для управления потоком выполнения. PSQL предоставляет структуры IF(<предикат>) THEN и WHILE(<предикат>) DO. Любой предикат, который может быть использован в условии поиска, также может быть предикатом для условия управления потоком в программе.

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

CREATE TRIGGER BI_MEMBERSHIP FOR MEMBERSHIP

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.MEMBER_ID IS NULL) THEN

NEW. MEMBER_I D - GEN_ID(GEN_MEMBER_ID, 1);

END

Подробную информацию о написании триггеров и хранимых процедур см. в части VII.

Вызовы функций

После установки Firebird содержит минимальный набор внутренних функций SQL. Хотя новые функции появляются время от времени, тем не менее сохраняется одно из основных достоинств Firebird: малый объем памяти, занимаемый сервером.

Функциональные возможности сервера могут быть легко расширены за счет его возможности получать доступ к функциям из внешних библиотек. Традиционно такие функции называются функциями, определенными пользователями (User-Defined Functions, UDF). Более корректно называть их внешними библиотеками функций. В реальности большинство администраторов баз данных используют хорошо протестированные библиотеки, находящиеся в общем пользовании и свободно распространяемые.

Внутренние функции SQL

В табл. 21.7 представлены внутренние функции SQL, доступные в Firebird.

Таблица 21.7. Внутренне реализованные функции SQL

Функция

Тип

Назначение

CAST()

Преобразование

Преобразует столбец из одного типа данных в другой

EXTRACT()

Преобразование

Выделяет части даты и времени (год, месяц, день и т.д.) из значений DATE, TIME и TIMESTAMP

SUBSTRING()

Строка

Отыскивает последовательность символов в строке

UPPER()

Строка

Преобразует символы в строке в верхний регистр

GEN_ID()

Генерация

Возвращает значение генератора

AVGO

Агрегат

Вычисляет среднее значение набора значений

COUNT()

Агрегат

Возвращает количество строк, которые удовлетворяют условию поиска запроса

MAX()

Агрегат

Отыскивает максимальное значение в наборе значений

MIN()

Агрегат

Отыскивает минимальное значение в наборе значений

SUM()

Агрегат

Суммирует значения в наборе числовых значений

Функции преобразования

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

CAST()

Функция CAST() широко используется. Она позволяет преобразовывать элемент данных одного типа данных в другой тип или трактовать его как другой тип данных.

Доступность

DSQL, PSQL, ISQL, ESQL, Firebird 1.5 и выше. Любая платформа.

Синтаксис

CAST(значение AS <тип-данных>)

Дополнительные ключевые слова

Фраза AS <тип-данных> с аргументом является обязательной.

Аргументы

значение является столбцом или выражением, которое преобразуется к типу данных, допустимому для преобразования в тип данных, названный в ключевом слове AS.

<тип-данных> должен быть родным типом данных Firebird. Нельзя указывать имя домена. Рис. 8.1 показывает все допустимые преобразования из одного типа в другой.

Возвращаемое значение

Функция возвращает вычисляемое поле заданного типа данных.

Пример

В следующем фрагменте PSQL поле LOG_DATE типа данных TIMESTAMP преобразуется в тип данных DATE, потому что для вычисления нужно получить целые дни:

. . .

IF (CURRENT_DATE - CAST (LOG_DATE AS DATE) = 30) THEN

STATUS = '30 DAYS';

Следующий оператор выбирает значение из столбца типа INTEGER, преобразует его в строку и соединяет со столбцом типа CHAR(3) для формирования значения другого столбца: