Руководство По Языку SQL СУБД Firebird 5.0 | Специальные Типы Данных

Тип данных `SQL_NULL`

Данный тип данных содержит не данные, а только состояние: NULL или NOT NULL.Также, этот тип данных не может быть использован при объявлении полей таблицы, переменных PSQL, использован в описании параметров.Этот тип данных добавлен для улучшения поддержки нетипизированных параметров в предикате IS NULL.Такая проблема возникает при использовании “отключаемых фильтров” при написании запросов следующего типа:

WHERE col1 = :param1 OR :param1 IS NULL

после обработки, на уровне API запрос будет выглядеть как

WHERE col1 = ? OR ? IS NULL

В данном случае получается ситуация, когда разработчик при написании SQL запрос рассматривает :param1 как одну переменную, которую использует два раза, а на уровне API запрос содержит два отдельных и независимых параметра.Вдобавок к этому, сервер не может определить тип второго параметра, поскольку он идёт в паре с IS NULL.

Именно для решения проблемы “? IS NULL” и был добавлен этот специальный тип данных SQL_NULL.

После введения данного специального типа данных при передаче запроса и его параметров на сервер будет работать такая схема: приложение передаёт параметризованные запросы на сервер в виде “?”.Это делает невозможным слияние пары “одинаковых” параметров в один.Так, например, для двух фильтров (двух именованных параметров) необходимо передать четыре позиционных параметра (далее предполагается, что читатель имеет некоторое знакомство с Firebird API):

SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)

После выполнения isc_dsql_describe_bind() sqltype 2-го и 4-го параметров устанавливается в SQL_NULL.Как уже говорилось выше, сервер Firebird не имеет никакой информации об их связи с 1-м и 3-м параметрами — это полностью прерогатива программиста.Как только значения для 1-го и 3-го параметров были установлены (или заданы как NULL) и запрос подготовлен, каждая пара XSQLVARs должна быть заполнена следующим образом:

Пользователь задал параметры

Первый параметр (сравнение значений): установка *sqldata в переданное значение и *sqlind в 0 (для NOT NULL);
Второй параметр (проверка на NULL): установка *sqldata в null (указатель null, а не SQL NULL) и *sqlind в 0 (для NOT NULL).

Пользователь оставил поле пустым

Оба параметра (проверка на NULL): установка *sqldata в null (указатель null, а не SQL NULL) и *sqlind в -1 (индикация NULL).

Другими словами: значение параметра сравнения всегда устанавливается как обычно.SQL_NULL параметр устанавливается также, за исключением случая, когда sqldata передаётся как null.

Преобразование типов данных

При написании выражения или при задании, например, условий сравнения, нужно стараться использовать совместимые типы данных.В случае необходимости использования смешанных данных различных типов, желательно первоначально выполнить преобразования типов, а уже потом выполнять операции.

При рассмотрении вопроса преобразования типов в Firebird большое внимание стоит уделить тому, в каком диалекте база данных.

Явное преобразование типов данных

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

Синтаксис

CAST (<expression> | NULL AS <data_type>)

<data_type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::=  См. <<fblangref-datatypes-syntax-scalar,Синтаксис скалярных типов данных>>

<blob_datatype> ::= См. <<fblangref-datatypes-syntax-blob,Синтаксис типа данных BLOB>>

<array_datatype> ::= См. <<fblangref-datatypes-syntax-array,Синтаксис массивов>>

Преобразование к домену

При преобразовании к домену учитываются объявленные для него ограничения, например, NOT NULL или описанные в CHECK и если <expression> не пройдёт проверку, то преобразование не удастся.В случае если дополнительно указывается TYPE OF (преобразование к базовому типу), при преобразовании игнорируются любые ограничения домена.При использовании TYPE OF с типом [VAR]CHAR набор символов и сортировка сохраняются.

Преобразование к типу столбца

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

CREATE TABLE TTT (
  S VARCHAR (40)
  CHARACTER SET UTF8 COLLATE UNICODE_CI_AI);
COMMIT;

/* У меня много друзей (шведский)*/
SELECT
  CAST ('Jag har manga vanner' AS TYPE OF COLUMN TTT.S)
FROM RDB$DATABASE;

Допустимые преобразования для функции `CAST`

Table 1. Допустимые преобразования для функции CAST
Из типа	В тип
Числовые типы	Числовые типы, `[VAR]CHAR`, `BLOB`
`[VAR]CHAR`, `BLOB`	`[VAR]CHAR`, `BLOB`, `BOOLEAN`, Числовые типы, `DATE`, `TIME`, `TIMESTAMP`
`DATE`, `TIME`	`[VAR]CHAR`, `BLOB`, `TIMESTAMP`
`TIMESTAMP`	`[VAR]CHAR`, `BLOB`, `TIME`, `DATE`
`BOOLEAN`	`[VAR]CHAR`, `BLOB`

Для преобразования строковых типов данных в тип BOOLEAN необходимо чтобы строковый аргумент был одним из предопределённых литералов логического типа ('true' или 'false').

Important

При преобразовании типов следует помнить о возможной частичной потери данных, например, при преобразовании типа данных TIMESTAMP в DATE.

Преобразование строк в дату и время

Для преобразования строковых типов данных в типы DATE, TIME или TIMESTAMP необходимо чтобы строковый аргумент был либо одним из предопределённых литералов даты и времени, либо строковое представление даты в одном из разрешённых форматов.

<date_literal> ::=
  [YYYY<p>]MM<p>DD |
  MM<p>DD[<p>YYYY] |
  DD<p>MM[<p>YYYY] |
  MM<p>DD[<p>YY] |
  DD<p>MM[<p>YY]


<time_literal> := HH[:mm[:SS[.NNNN]]]

<datetime_literal> ::= <date_literal> <time_literal>

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

<p> ::= whitespace | . | , | - | /

Table 1. Описание формата даты и времени
Аргумент	Описание
datetime_literal	Литерал даты-времени.
date_literal	Литерал даты.
time_literal	Литерал времени.
YYYY	Год из четырёх цифр.
YY	Последние две цифры года (00-99).
MM	Месяц.Может содержать 1 или 2 цифры (1-12 или 01-12). В качестве месяца допустимо также указывать трёх буквенное сокращение или полное наименование месяца на английском языке, регистр не имеет значение.
DD	День.Может содержать 1 или 2 цифры (1-31 или 01-31).
HH	Час.Может содержать 1 или 2 цифры (0-23 или 00-23).
mm	Минуты.Может содержать 1 или 2 цифры (0-59 или 00-59).
SS	Секунды.Может содержать 1 или 2 цифры (0-59 или 00-59).
NNNN	Десятитысячные доли секунды.Может содержать от 1 до 4 цифр (0-9999).
p	Разделитель, любой из разрешённых символов, лидирующие и завершающие пробелы игнорируются
time zone region	Один из часовых поясов связанных с регионом
hour displacement	Смещение времени для часов относительно GMT
minute displacement	Смещение времени для минут относительно GMT

Table 2. Литералы с предопределёнными значениями даты и времени
Литерал	Значение	Тип данных для диалекта 1	Тип данных для диалекта 3
`'NOW'`	Текущая дата и время	TIMESTAMP	TIMESTAMP
`'TODAY'`	Текущая дата	TIMESTAMP (c нулевым временем)	DATE (только дата)
`'TOMORROW'`	Завтрашняя дата	TIMESTAMP (c нулевым временем)	DATE (только дата)
`'YESTERDAY'`	Вчерашняя дата	TIMESTAMP (c нулевым временем)	DATE (только дата)

Правила:

В формате Год-Месяц-День, год обязательно должен содержать 4 цифры;
Для дат в формате с завершающим годом, если в качестве разделителя дат используется точка “.”, то дата интерпретируется в форме День-Месяц-Год, для остальных разделителей она интерпретируется в форме Месяц-День-Год;
Если год не указан, то в качестве года берётся текущий год;
Если указаны только две цифры года, то для получения столетия Firebird использует алгоритм скользящего окна. Задача заключается в интерпретации двухсимвольного значения года как ближайшего к текущему году в интервале предшествующих и последующих 50 лет;
Если не указан один из элементов времени, то оно принимается равным 0.

При использовании преобразования строковых литералов в тип даты/времени с помощью функции CAST() вычисление значения всегда происходит в момент выполнения.

При преобразовании строковых литералов с предопределёнными значениями даты и времени в тип TIMESTAMP точность составляет 3 знака после запятой (миллисекунды).

Tip	Настоятельно рекомендуем в литералах дат использовать только формы с полным указанием года в виде 4 цифр во избежание путаницы.

Example 1. Преобразование строк в дату и время:

SELECT
  CAST('04.12.2014' AS DATE) AS d1, -- DD.MM.YYYY
  CAST('12-04-2014' AS DATE) AS d2, -- MM-DD-YYYY
  CAST('12/04/2014' AS DATE) AS d3, -- MM/DD/YYYY
  CAST('04.12.14' AS DATE) AS d4,   -- DD.MM.YY
  -- DD.MM в качестве года берётся текущий
  CAST('04.12' AS DATE) AS d5,
  -- MM/DD в качестве года берётся текущий
  CAST('12/4' AS DATE) AS d6,
  CAST('2014/12/04' AS DATE) AS d7, -- YYYY/MM/DD
  CAST('2014.12.04' AS DATE) AS d8, -- YYYY.MM.DD
  CAST('2014-12-04' AS DATE) AS d9, -- YYYY-MM-DD
  CAST('11:37' AS TIME) AS t1, -- HH:mm
  CAST('11:37:12' AS TIME) AS t2, -- HH:mm:ss
  CAST('11:31:12.1234' AS TIME) AS t3, -- HH:mm:ss.nnnn
  -- HH:mm:ss.nnnn +hh
  CAST('11:31:12.1234 +03' AS TIME WITH TIME ZONE) AS t4,
  -- HH:mm:ss.nnnn +hh:mm
  CAST('11:31:12.1234 +03:30' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm:ss.nnnn tz
  CAST('11:31:12.1234 Europe/Moscow' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm tz
  CAST('11:31 Europe/Moscow' AS TIME WITH TIME ZONE) AS t6,
  -- DD.MM.YYYY HH:mm
  CAST('04.12.2014 11:37' AS TIMESTAMP) AS dt1,
  -- MM/DD/YYYY HH:mm:ss
  CAST('12/04/2014 11:37:12' AS TIMESTAMP) AS dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn
  CAST('04.12.2014 11:31:12.1234' AS TIMESTAMP) AS dt3,
  -- YYYY-MM-DD HH:mm:ss.nnnn +hh:mm
  CAST('2014-12-04 11:31:12.1234 +03:00' AS TIMESTAMP WITH TIME ZONE) AS dt4,
  -- DD.MM.YYYY HH:mm:ss.nnnn tz
  CAST('04.12.2014 11:31:12.1234 Europe/Moscow' AS TIMESTAMP WITH TIME ZONE) AS dt5,
  CAST('now' AS DATE) AS d_now,
  CAST('now' AS TIMESTAMP) AS ts_now,
  CAST('now' AS TIMESTAMP WITH TIME ZONE) AS ts_now_tz,
  CAST('today' AS DATE) AS d_today,
  CAST('today' AS TIMESTAMP) AS ts_today,
  CAST('today' AS TIMESTAMP WITH TIME ZONE) AS ts_today_tz,
  CAST('tomorrow' AS DATE) AS d_tomorrow,
  CAST('tomorrow' AS TIMESTAMP) AS ts_tomorrow,
  CAST('tomorrow' AS TIMESTAMP WITH TIME ZONE) AS ts_tomorrow_tz,
  CAST('yesterday' AS DATE) AS d_yesterday,
  CAST('yesterday' AS TIMESTAMP) AS ts_yesterday,
  CAST('yesterday' AS TIMESTAMP WITH TIME ZONE) AS ts_yesterday_tz
FROM rdb$database

Note	Поскольку `CAST('NOW' AS TIMESTAMP)` всегда возвращает актуальные значения даты и времени, то она может использоваться для измерения временных интервалов и скорости выполнения кода в процедурах, триггерах и блоках кода PSQL.

Example 2. Использование CAST('NOW' AS TIMESTAMP) измерения длительности выполнения кода

EXECUTE BLOCK
RETURNS (ms BIGINT)
AS
DECLARE VARIABLE t1 TIME;
DECLARE VARIABLE n BIGINT;
BEGIN
  t1 = CAST('now' AS TIMESTAMP);
  /* Долгая операция  */
  SELECT COUNT(*) FROM rdb$types, rdb$types, rdb$types INTO n;
  /*======*/
  ms = DATEDIFF(MILLISECOND FROM t1 TO CAST('now' AS TIMESTAMP));
  SUSPEND;
END

См. также:

Литералы даты и времени, [fblangref-scalarfuncs-cast].

Неявное преобразование типов данных

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

При использовании 1-го диалекта во многих выражениях выполняется неявное преобразование одних типов в другой без применения функции CAST.Например, в выражении отбора в диалекте 1 можно записать:

WHERE DOC_DATE < '31.08.2014'

и преобразование строки в дату произойдёт неявно.

В 1-м диалекте можно смешивать целые данные и числовые строки, строки неявно преобразуются в целое, если это будет возможно, например:

2 + '1'

корректно выполнится.

В 3-м диалекте подобное выражение вызовет ошибку, в нем потребуется запись следующего вида:

2 + CAST('1' AS SMALLINT)

Неявное преобразование типов при конкатенации

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

Example 1. Неявное преобразование типов при конкатенации

SELECT 30||' days hath September, April, June and November' CONCAT$
FROM RDB$DATABASE

CONCAT$
------------------------------------------------
30 days hath September, April, June and November

Тип Данных SQL_NULL

Тип данных `SQL_NULL`

Преобразование типов данных

docnext count = 7

Явное преобразование типов данных

Преобразование к домену

Преобразование к типу столбца

Допустимые преобразования для функции `CAST`

Преобразование строк в дату и время

Неявное преобразование типов данных

Неявное преобразование типов при конкатенации

Тип Данных SQL_NULL

Тип данных SQL_NULL

Преобразование типов данных

docnext count = 7

Явное преобразование типов данных

Преобразование к домену

Преобразование к типу столбца

Допустимые преобразования для функции CAST

Преобразование строк в дату и время

Неявное преобразование типов данных

Неявное преобразование типов при конкатенации

Тип данных `SQL_NULL`

Допустимые преобразования для функции `CAST`