Встроенные функции

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

Функции для работы с контекстными переменными

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

Контекстные переменные принадлежат различным пространствам имен.

Функция RDB$GET_CONTEXT возвращает значение типа VARCHAR контекстной переменной одного из пространств имен:

• SYSTEM — предоставляет доступ к системным контекстным переменным. Эти переменные доступны только для чтения;
• USER_SESSION — предоставляет доступ к пользовательским контекстным переменным, заданным через функцию RDB$SET_CONTEXT. Переменные существуют в течение подключения;
• USER_TRANSACTION — предоставляет доступ к пользовательским контекстным переменным, заданным через функцию RDB$SET_CONTEXT. Переменные существуют в течение транзакции;
• DDL_TRIGGER — предоставляет доступ к системным контекстным переменным, доступным только во время выполнения DDL триггера. Эти переменные доступны только для чтения;
• AUTHDATA — предоставляет доступ к информации об аутентификации и ФИО пользователя.

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

RDB$GET_CONTEXT('пространство имен', '<имя переменной>')

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

Пространства имен USER_SESSION и USER_TRANSACTION изначально пусты. Пользователь может создать и установить значение переменных в них функцией RDB$SET_CONTEXT и получить их значения из функции RDB$GET_CONTEXT.

Пространство имен SYSTEM доступно только для чтения. Оно содержит много предопределенных переменных. Полный список можно найти в документации. Рассмотрим некоторые из них.

Обращение к несуществующему пространству имён или несуществующей переменной в пространстве имен SYSTEM приведёт к ошибке, а в пространстве имен USER_SESSION и USER_TRANSACTION, функция вернёт NULL.

Использование пространства имён DDL_TRIGGER допустимо, только во время работы DDL триггера. Его использование также допустимо в хранимых процедурах и функциях, вызванных триггерами DDL.

Контекст DDL_TRIGGER работает как стек. Перед вызовом DDL триггера, значения, относящиеся к выполняемой команде, помещаются в этот стек. После завершения работы триггера значения выталкиваются. Таким образом, значения переменных в пространстве имён DDL_TRIGGER будут соответствовать команде, которая вызвала последний DDL триггер в стеке вызовов.

Функция RDB$SET_CONTEXT создает переменную, устанавливает ее значение или обнуляет в одном из используемых пользователями для записи пространстве имён: USER_SESSION, USER_TRANSACTION.

В рамках одного соединения может быть максимум 1000 переменных.

RDB$SET_CONTEXT ('USER_SESSION' | 'USER_TRANSACTION', '<имя переменной>', '<значение переменной>' | NULL)

Параметр <имя переменной> — это строка, чувствительная к регистру. Параметр <значение переменной> — значение любого типа, приводимое к типу VARCHAR.

Функция возвращает только два значения типа INTEGER: 1 — если переменная уже существовала и 0 — если не существовала.

Для удаления переменной надо установить её значение в NULL. Если данное пространство имен не существует, то функция вернёт ошибку.

Математические функции

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

Функции для работы со строками

ASCII_CHAR

Возвращает символ ASCII по его коду.

ASCII_CHAR(<числовое значение>)

Входной параметр может принимать значения в диапазоне от 0 до 255, иначе выдается сообщение об арифметическом переполнении. Если входной параметр имеет значение NULL, то возвращается пустое значение NULL.

ASCII_VAL

Возвращает число, соответствующее коду ASCII символа заданного строкового параметра.

ASCII_VAL(<строка>)

Возвращается NULL, если входной параметр имеет значение NULL. Возвращается 0, если входной параметр является строкой, не содержащей ни одного символа.

OCTET_LENGTH

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

OCTET_LENGTH(<строка>)

Следует помнить, что не во всех наборах символов количество байт, занимаемых строкой, равно количеству символов.

CHARACTER_LENGTH

Функция CHARACTER_LENGTH (сокращенное название CHAR_LENGTH) возвращает количество символов, занимаемых входным параметром функции.

CHARACTER_LENGTH_LENGTH(<строка>)

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

LOWER/UPPER

Переводит все буквы строки в нижний/верхний регистр.

LOWER (<строка>)
UPPER(<строка>)

Если исходное значение не содержит букв, то будет возвращаться само исходное значение.

Точный результат зависит от набора символов входной строки. Например, для наборов символов NONE и ASCII только ASCII символы переводятся в нижний регистр; для OCTETS — вся входная строка возвращается без изменений.

POSITION

Отыскивает позицию подстроки в исходной строке. Существует два варианта синтаксиса:

POSITION(<подстрока> IN <строка>)
POSITION(<подстрока>, <строка> [, <начальная позиция> [, <номер вхождения подстроки>]])

Функция возвращает целое число — позицию подстроки в исходной строке. Если подстрока отсутствует в исходной строке, то функция возвращает 0.

Третий аргумент (во втором варианте синтаксиса) задаёт позицию в строке, с которой начинается поиск подстроки, тем самым игнорируются любые вхождения подстроки в исходную строку до этой позиции.

Четвёртый аргумент определяет, какое по счету вхождение подстроки нужно искать. Если аргумент не задан, то по умолчанию равен 1, то есть результатом будет позиция первого вхождения подстроки в строку.

REPLACE

Отыскивает подстроку в исходной строке и заменяет на другую.

REPLACE(<исходная строка>, <подстрока>, <строка замены>)

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

Если любой из аргументов равен NULL, то результатом всегда будет NULL, даже если не было произведено ни одной замены

SUBSTRING

Функция SUBSTRING возвращает подстроку исходной строки.

SUBSTRING( <строка> FROM <начальная позиция> [FOR <длина подстроки>])

Начальная позиция — номер позиции в строке, начиная с которой выделяется подстрока. Нумерация символов в строке начинается с единицы. Если начальная позиция подстроки превышает количество символов в строке, то будет выделена пустая подстрока, т.е. строка, содержащая ноль символов.

Длина подстроки — количество символов, которые выбираются в результирующую строку. Должно быть положительным числом. Если задать количество символов, которое выходит за границы исходной строки, то результат будет усечен до размера, соответствующего положению последнего символа исходной строки. При этом не будет выдано никаких диагностических сообщений. Если ключевое слово FOR не указано, то в подстроку помещаются все оставшиеся до конца исходной строки символы.

TRIM

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

TRIM ([LEADING | TRAILING | BOTH] [<удаляемые символы>] FROM <строка>)
TRIM (<строка>)

Внутри функции указывается спецификатор удаления и он может иметь следующие значения:

• LEADING — символы удаляются из начальной части строки.
• TRAILING — удаляются конечные символы строки.
• BOTH (значение по умолчанию) — символы одновременно удаляются как из начальной, так и из конечной части стоки.

Удаляемые символы — строка, содержащая произвольное количество символов. Эта строка заключается в апострофы. Если параметр опущен, предполагаются пробелы.

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

Например,

select TRIM ('  Руководство ' || 'по SQL  ') from rdb$database;

вернет Руководство по SQL. Здесь по умолчанию убираются символы пробелов.

TRIM (LEADING '*' FROM '***********Руководство ' || 'по SQL*******')

вернет Руководство по SQL*******. В результате выполнения функции будут удалены только начальные символы “*”.

TRIM (TRAILING '*' FROM '***********Руководство ' || 'по SQL*******')

вернет ***********Руководство по SQL. В результате выполнения функции будут удалены только конечные символы “*”.

TRIM (BOTH '*' FROM '***********Руководство ' || 'по SQL*******')

вернет Руководство по SQL. В результате выполнения функции будут удалены все символы “*”.

Ключевое слово BOTH можно не задавать. В этом случае удаляются указанные символы как с начала, так и с конца строки.

Функции для работы с датой и временем

DATEADD

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

DATEADD(<количество> <элемент даты/времени> TO <входной параметр>)
DATEADD(<элемент даты/времени>, <количество>, <входной параметр>)

Элемент даты/времени - это YEAR, MONTH, WEEK, DAY, WEEKDAY, YEARDAY, HOUR, MINUTE, SECOND, MILLISECOND.

С типом данных, содержащим только время, не могут использоваться элементы, относящиеся к дате, с типом данных DATE не могут использоваться элементы времени. Для типа данных TIMESTAMP допустимы любые варианты.

При задании элементов, указывающих месяц, день, час, минуту, секунду или миллисекунду, происходит естественное изменение значений всех вышележащих элементов, составляющих дату и время. Например, вызов следующей функции вернет дату и время на 10 лет ранее текущей даты.

DATEADD(SECOND, -60*60*24*365*10, CURRENT_TIMESTAMP)

DATEDIFF

Возвращает целое число, задающее интервал в соответствии с указанным выделяемым элементом между двумя значениями типа данных DATE, TIME или TIMESTAMP. У функции есть два формата.

DATEDIFF(<элемент даты/времени> FROM <параметр 1> TO <параметр 2>)
DATEDIFF(<элемент даты/времени>, <параметр 1>, <параметр 2>)

Например, чтобы определить, сколько лет осталось до 2050 года, нужно выполнить функцию:

DATEDIFF (YEAR, CURRENT_DATE, CAST('01.01.2050' AS DATE))

EXTRACT

Функция позволяет выделять различные элементы даты и времени.

EXTRACT (<выделяемый элемент> FROM <дата>)

В следующем операторе из переменной DATE_C типа DATE выделяются день, месяц и год. Полученные данные при помощи операции конкатенации приводятся к виду, принятому в нашей стране:

EXTRACT (DAY FROM DATE_C) || '.' ||
EXTRACT (MONTH FROM DATE_C) || '.' ||
EXTRACT (YEAR FROM DATE_C)

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

CAST

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

CAST ({<значение> | NULL} AS <тип данных>)

Преобразование NULL в любой тип данных всегда дает тот же NULL.

В целочисленные типы данных (SMALLINT, INTEGER, BIGINT) можно выполнять преобразование числовых данных и констант с фиксированной точкой (DECIMAL, NUMERIC), с плавающей точкой (FLOAT, DOUBLE PRECISION), данных текстового BLOB и строковых данных (CHAR, VARCHAR, NCHAR и NCHAR VARYING), содержащих только цифры и десятичную точку.

В дробные числа с фиксированной точкой (DECIMAL, NUMERIC) можно преобразовывать все целочисленные данные и данные с фиксированной или плавающей точкой, данные типа BLOB подтипа TEXT, а также строки, содержащие данные, по форме соответствующие числам.

В строковые типы данных (CHAR, VARCHAR, NCHAR и NCHAR VARYING) можно преобразовывать любой тип данных. Необходимо лишь указать размер строкового типа, достаточный для того, чтобы в него поместился результат преобразования.

В типы данных DATE, TIME и TIMESTAMP можно преобразовать любую строку, содержащую дату в одном из допустимых форматов.

Функции побитовых операций

Выходные и выходные параметры побитовых операций - целые числа. Если любой из входных параметров имеет значение NULL, то возвращается также NULL.

Функции побитовых операций представлены в таблице.

Функции для работы с UUID

CHAR_TO_UUID

Данная функция преобразует переданное в качестве параметра 32-х символьное ASCII представление UUID (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX) в восьмеричное представление, оптимизированное для хранения.

CHAR_TO_UUID(<string>)

Например, запрос

select CHAR_TO_UUID('93519227-8D50-4E47-81AA-8F6678C096A1') from rdb$database

вернет 935192278D504E4781AA8F6678C096A1

GEN_UUID

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

GEN_UUID()

Возвращаемое значение содержит 16 символов.

UUID_TO_CHAR

Данная функция преобразует переданное в качестве параметра восьмеричное представление UUID CHAR(16) в 32-х символьное ASCII представление (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX).

UUID_TO_CHAR(<uuid>)

Условные функции

CASE

Условное выражение. Дает возможность выбрать результирующее значение из множества различных выражений.

CASE <исходное выражение>
    WHEN <выражение 1> THEN {<результат 1> | NULL}
    [WHEN <выражение 2> THEN {<результат 2> | NULL}] 
    ...
    [ELSE {<значение по умолчанию> | NULL}]
END

Исходное выражение возвращает значение, с которым сравниваются выражения N в последующих предложениях WHEN. При совпадении функция возвращает результат N или NULL, если пустое значение указано в этом предложении.

Если ни одно выражение N в списке предложений WHEN не равно исходному выражению, то, в случае присутствия предложения ELSE, возвращается значение по умолчанию (или NULL, если именно оно указано в этом предложении). Если же в этом случае отсутствует предложение ELSE, то функция возвращает значение NULL.

Если исходное выражение имеет значение NULL, то оно не будет соответствовать ни одному из выражений N, даже тем, которые имеют значение NULL.

Есть еще один синтаксический вариант функции CASE - поисковый CASE:

CASE
    WHEN <логическое выражение> THEN {<результат> | NULL}
    [WHEN <логическое выражение> THEN {<результат> | NULL}] 
    ...
    [ELSE {<выражение по умолчанию> | NULL}]
END

Здесь <логическое выражение> даёт тройной логический результат: TRUE, FALSE или NULL. Первое выражение, которое вернет TRUE, определяет результат. Если нет выражений, возвращающих TRUE, то в качестве результата берётся <выражение по умолчанию> из ветви ELSE. Если нет выражений, возвращающих TRUE, и ветвь ELSE отсутствует, результатом будет NULL.

COALESCE

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

COALESCE (<выражение 1>, <выражение 2> [, <выражение 3>]...)

Выполняется просмотр выражений в списке слева направо. Функция возвращает первое встретившееся непустое значение (NOT NULL). Если все выражения в списке имеют пустое значение, то функция возвращает NULL.

IIF

Проверяет условие, если оно истинно, то возвращает первое значение, иначе - второе.

IIF (<условие>, <значение 1> <значение 2>)

Контрольные вопросы

1. Как можно узнать адрес подключившегося клиента внутри хранимой процедуры?
2. Как сохранить какое-либо значение для использования в последующих запросах?
3. Сколько времени будет храниться такое значение?
4. В чем разница функций CEIL и ROUND?
5. В чем разница функций OCTET_LENGTH и CHAR_LENGTH?
6. Как из строки выделить подстроку?
7. Как удалить заданные символы из начала или конца строки?
8. Как вычислить дату через 3 года от текущей?
9. Как вычислить какой день недели будет через год?
10. Как преобразовать значение одного типа данных, в значение другого?
11. Как осуществить побитовый сдвиг числа вправо?
12. Как получить глобальный уникальный идентификатор?
13. Как обеспечить, чтобы переменная никогда не была NULL?
14. Как преобразовать символы строки к верхнему регистру?