Опубликован: 07.05.2010 | Уровень: специалист | Доступ: платный
Лекция 23:

Стандартные функции InterBase. UDF

< Лекция 22 || Лекция 23: 12 || Лекция 24 >
Аннотация: На этой лекции мы разберем подробный синтаксис встроенных функций InterBase, а также опробуем их на примерах. Кроме того, мы познакомимся с механизмом UDF (внешние пользовательские функции), создадим DLL-файл с такой функцией и подключим его к базе данных.

InterBase имеет в своем арсенале весьма незначительный набор стандартных функций, которые можно использовать в запросах. Это связано с тем, что, во-первых, основным достоинством InterBase является малый объем сервера, и низкие требования к аппаратному обеспечению, что позволяет использовать InterBase практически на любом компьютере. А во-вторых, InterBase предоставляет очень привлекательную возможность для программиста создавать собственные функции ( UDF ) и подключать их к серверу, к конкретной базе данных. В рамках лекции мы рассмотрим и эту тему.

Стандартные функции InterBase

Стандартные функции InterBase представлены в таблице 23.1:

Таблица 23.1.
Функция Тип Назначение
AVG () Агрегатная Вычисляет и возвращает среднее значение из набора записей.
COUNT () Агрегатная Подсчитывает и возвращает количество записей, удовлетворяющих условию поиска запроса.
MAX () Агрегатная Находит и возвращает максимальное значение из набора записей.
MIN () Агрегатная Находит и возвращает минимальное значение из набора записей.
SUM () Агрегатная Суммирует значения всех записей и возвращает результат.
CAST () Преобразование Преобразует значение столбца из одного типа данных в другой.
UPPER () Преобразование Преобразует все символы строки в верхний регистр.
GEN_ID () Числовая Возвращает (и увеличивает) значение генератора.

С большинством из этих функций мы уже сталкивались, разберем их синтаксис подробней, и опробуем на примерах. Для этого откроем утилиту IBConsole (сервер InterBase должен быть запущен), откроем в нем нашу базу First.gdb и запустим Interactive SQL. Примеры из запросов будем делать в этом окне.

AVG

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

Если число строк, возвращенных запросом SELECT равно 0, то AVG вернет NULL. Синтаксис:

AVG([ALL] <столбец|выражение> | DISTINCT  <столбец|выражение>);

Если указан необязательный параметр ALL (по умолчанию), то среднее арифметическое значение вычисляется из всех столбцов или выражения. Если же указан параметр DISTINCT, то при вычислении будут исключены повторяющиеся значения. Пример:

SELECT AVG(Stoimost) FROM Tovar

COUNT

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

COUNT ([DISTINCT] <имя_поля>);

Если указан необязательный параметр DISTINCT, из вычисления будут исключены повторяющиеся значения. Примеры (выполняйте их по очереди, а не разом, иначе в окне Interactive SQL вы получите результат только последнего примера - каждая новая выборка будет перекрывать результат работы предыдущей выборки):

/*Количество всех записей:*/
SELECT COUNT(Nazvanie) FROM Tovar;

/*То же самое, но исключив повторяющиеся значения:*/
SELECT COUNT(DISTINCT Stoimost) FROM Tovar;

/*Количество всех записей, удовлетворяющих условию:*/
SELECT COUNT(Nazvanie) FROM Tovar WHERE Stoimost = 10;

MAX / MIN

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

Если MAX / MIN применяются для строковых столбцов CHAR / VARCHAR, то максимум или минимум определяется в зависимости от символьного набора ( CHARACTER SET ) и порядка сортировки ( COLLATION ). Другими словами, функции возвращают максимальный или минимальный текст из всех строк, учитывая, что 'А' меньше, чем 'Я'.

Синтаксис:

MAX([ALL] <столбец|выражение> | DISTINCT  <столбец|выражение>);
MIN([ALL] <столбец|выражение> | DISTINCT  <столбец|выражение>);

Примеры:

/*Максимальное и минимальное значения из числового столбца стоимости товаров:*/
SELECT MAX(Stoimost), MIN(Stoimost) FROM Tovar;

/*Максимальное и минимальное значения из строкового столбца с названием товаров:*/
SELECT MAX(Nazvanie), MIN(Nazvanie) FROM Tovar;

SUM

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

Синтаксис:

SUMM([ALL] <столбец|выражение> | DISTINCT  <столбец|выражение>);

Пример:

/*Сумма всех значений из числового столбца стоимости товаров:*/
SELECT SUM(Stoimost) FROM Tovar;

CAST

Функция позволяет преобразовывать один тип данных в другой, или трактовать его, как другой тип данных. Функцию удобно использовать в запросах, которые смешивают данные разных типов в одном поле. Также CAST может использоваться в условиях поиска. Следует помнить, что типы данных должны соответствовать преобразованию. То есть, любое число можно превратить в строку, однако не любую строку можно превратить в число. Если строка содержит значение '123', она корректно преобразуется, и функция вернет правильный результат. Если строка содержит значение 'АБВ', то ее невозможно будет преобразовать в числовой тип, и функция вернет ошибку.

Типы данных, преобразуемые функцией CAST, представлены в таблице 23.2:

Таблица 23.2.
Исходный тип данных Возможный для преобразования тип данных
NUMERIC CHAR, VARCHAR, DATE
CHAR, VARCHAR NUMERIC, DATE
DATE CHAR, VARCHAR, DATE

Под типом данных NUMERIC подразумеваются целые и вещественные числовые типы.

Синтаксис:

CAST(<поле | значение> AS <тип_данных>)

Пример:

/*Вывод в одном поле объединенных значений строкового столбца Nazvanie */
/*и числового поля Stoimost, преобразованного в строку:*/
SELECT Nazvanie || ' - ' || CAST(Stoimost AS VARCHAR(25)) FROM Tovar;

В примере использован символ конкатенации (объединения) строк "||", вторая часть строки преобразуется функцией CAST из типа DOUBLE PRECISION.

UPPER

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

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

Синтаксис:

UPPER(<значение>);

Поскольку у нас в базе данных все символьные столбцы создавались с набором WIN1251 и порядком сортировки PXW_CYRL, то функция сработает правильно. Для наглядности в примере ниже мы выведем один и тот же столбец дважды, в первом случае не изменяя порядок сортировки, чтобы функция корректно перевела символы в верхний регистр. А во втором поле порядок сортировки изменим на WIN1251, чтобы функция не смогла сделать преобразование:

SELECT UPPER(Nazvanie), UPPER(Nazvanie COLLATE WIN1251)
FROM Tovar;

В результате мы получим примерно такой набор данных:

Преобразование функцией UPPER строк с различным порядком сортировки

Рис. 23.1 . Преобразование функцией UPPER строк с различным порядком сортировки

Как видно из примера, текст с набором символов WIN1251 и порядком сортировки WIN1251 возвращается функцией UPPER без изменений.

GEN_ID

Функция является механизмом, увеличивающим значение указанного генератора на указанный шаг, и возвращающим текущее значение этого генератора. Если шаг равен 0, увеличения значения не происходит. Синтаксис:

CEN_ID(<генератор>, <шаг>);

Работу этой функции мы достаточно подробно рассмотрели в "Генераторы и триггеры. Реализация автоинкрементного поля" .

< Лекция 22 || Лекция 23: 12 || Лекция 24 >
Евгений Медведев
Евгений Медведев

В лекции №2 вставляю модуль данных. При попытке заменить name на  fDM выдает ошибку: "The project already contains a form or module named fDM!". Что делать? 

Анна Зеленина
Анна Зеленина

При вводе типов успешно сохраняется только 1я строчка. При попытке ввести второй тип вылезает сообщение об ошибке "project mymenu.exe raised exception class EOleException with message 'Microsoft Драйвер ODBC Paradox В операции должен использоваться обновляемый запрос'. 

Денис Попов
Денис Попов
Россия, Оренбург, Оренбургский государственный университет, 2015
Рустам Кадыров
Рустам Кадыров
Россия, Тирлян, Тирлянская школа №5, 2003