Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

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

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

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

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

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

Функция 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) для формирования значения другого столбца:

UPDATE MEMBERSHIP

SET MEMBER_CODE = MEMBER_GROUP || CAST(MEMBER_ID AS CHAR(8))

WHERE MEMBER_CODE IS NULL;

См. главу 8, где более подробно осуждается функция CAST(), а также последующие главы, в которых индивидуально рассматриваются типы данных.

Эта функция выделяет часть полей типа данных DATE, TIME и TIMESTAMP в виде числа.

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

EXTRACT(часть FROM поле)

YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | WEEKDAY | YEARDAY

часть является одним значением из указанного списка необязательных ключевых слов, WEEKDAY выделяет день недели (воскресенье = 1, понедельник = 2 и т.д.), YEARDAY выделяет день в году (от 1 января = 1 до 366).

поле- поле типа данных DATE, TIME или TIMESTAMP (столбец, переменная или выражение).

Все части возвращают SMALLINT за исключением SECOND, которое возвращает

DECIMAL(6,4).

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

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

SELECT

FIRST_NAME,

LAST_NAME,

EXTRACT(DAY FROM DATE_OF_BIRTH) AS BIRTHDAY

FROM MEMBERSHIP

WHERE DATE_OF_BIRTH IS NOT NULL

AND EXTRACT (MONTH FROM DATE_OF_BIRTH) = EXTRACT (MONTH FROM

CURRENT_DATE)

ORDER BY 3;

Firebird имеет только две внутренние строковые функции. Большое количество строковых функций доступно во внешних функциях (см. следующий раздел этой главы).

SUBSTRING() является внутренней функцией, реализующей функцию ANSI SQL SUBSTRING(). Она возвращает поток, состоящий из байта с номером начальная-позиция и всех последующих байтов до конца строки значение. Если указано необязательное предложение FOR длина, она вернет меньшее из длина байт и количество байт до конца входного потока.

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

SUBSTRING(значение FROM начальная-позиция [FOR длина])

Необязательное предложение FOR задает длину возвращаемой подстроки.

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

начальная-позиция должна преобразовываться в целое >= 1. Не может быть заменяемым параметром.

длина должна преобразовываться в целое >= 1. Не может быть заменяемым параметром.

Возвращаемое значение является строкой.

Значения начальная-позиция и длина являются позициями байтов, что имеет значение для многобайтовых наборов символов.

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

Для аргументов столбцов BLOB указанный столбец должен быть двоичным BLOB (SUB_TYPE 0) или текстовым BLOB (SUB_TYPE 1) с набором символов один байт на символ. В настоящий момент функция не обрабатывает текстовые BLOB с наборами символов Chinese (максимум два байта на символ) и Unicode (максимум три байта на символ).

Следующий оператор будет изменять значение столбца COLUMNB, присваивая ему строку до 99 символов, начиная с четвертой позиции оригинальной строки:

UPDATE ATABLE

SET COLUMNB = SUBSTRING (COLUMNB FROM 4 FOR 99)

WHERE ...

См. также внешние функции SUBSTR(), SUBSTRLEN() и RTRIM().

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

DSQL, PSQL, ISQL, ESQL; обеспечивает работу со строками в тех наборах символов и последовательностях сортировки, которые поддерживают преобразование нижний/верхний регистры. Любая платформа.

UPPER(значение)

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

Если набор символов и последовательность сортировки поддерживают преобразование в верхний регистр, функция возвращает строку, в которой все символы преобразованы в верхний регистр. Строка имеет ту же длину, что и входное значение. Для наборов символов, не поддерживающих преобразование в верхний регистр, функция возвращает неизмененное входное значение,[76]

Входное значение не может иметь тип данных BLOB.

Следующее ограничение CHECK проверяет входную строку, выясняя, содержит ли она все символы в верхнем регистре:

ALTER TABLE MEMBERSHIP

ADD CONSTRAINT CHECK_LOCALITY_CASE

CHECK(LOCALITY = UPPER(LOCALITY));

См. также внешние функции LOWER() и F_PROPERCASE().

Функция GEN_ID() является механизмом, с помощью которого модули PSQL и приложения получают числа от генераторов. Генераторы подробно обсуждаются в главе 9. См. также разд. "Реализация автоинкрементных ключей" главы 31.

GEN_IDO вычисляет и возвращает значение генератора.

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

GEN_ID(значение1, значение2)

значение1 является идентификатором существующего генератора.

значение2 является значением шага - целый тип или выражение, которое преобразуется в целый тип.

Возвращается значение типа BIGINT.

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

GEN_IDO всегда выполняется вне контекста какой-либо транзакции. Эта операция в Firebird доступна только пользователю. Как только число будет получено от генератора, оно никогда не будет создано тем же генератором, за исключением случаев, когда пользователь изменит последовательность, используя отрицательную величину шага, или с помощью оператора SET GENERATOR.

Следующий оператор возвращает новое значение генератора GEN_SERIAL:

SELECT GEN_ID(GEN_SERIAL, 1) FROM RDB$DATABASE;

В приведенном далее примере генератор используется в триггере BEFORE INSERT для получения значения первичного ключа:

CREATE TRIGGER BI_AUTHORS FOR AUTHORS

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.AUTHOR_ID IS NULL) THEN

NEW.AUTHOR_ID = GEN_ID(GEN_AUTHOR_ID, 1);

END ^

Агрегатные (обобщенные) функции выполняют вычисления над значениями столбца, например, значениями, выбранными из числового столбца запрашиваемого набора.

Firebird имеет группу агрегатных функций, которые чаще всего используются в комбинации с условиями группирования для вычисления итогов или статистики на уровне группы. Агрегатными функциями являются: SUM(), вычисляющая итог, MAX() и MIN(), возвращающие наибольшее и наименьшее значение соответственно, и AVG(), вычисляющая среднее значение. Функция COUNT() также ведет себя как агрегатная функция в сгруппированных запросах.

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

В небольшом количестве случаев агрегатные функции могут оперировать с наборами, которые не являются субъектами предложения GROUP BY и возвращающими не более одной строки. Логически результат подобного запроса не может выводить значения любого столбца базы данных или значения, полученные от неагрегатных функций. Для иллюстрации рассмотрим следующий запрос, который объединяет бюджеты одного проекта за один фискальный год. Таблица имеет одну запись бюджета для каждого из пяти отделов:

SELECT

'MKTPR-1994' AS PROJECT,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE PROJ_ID = 'MKTPR' AND FISCAL_YEAR = 1994;

Выходом будет одна строка: строковое поле времени выполнения и вычисленный итог.

Оглавление книги