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

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

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

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

С большинством из этих функций мы уже сталкивались, разберем их синтаксис подробней, и опробуем на примерах. Для этого откроем утилиту 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:

Под типом данных 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;

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

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

GEN_ID

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

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

Работу этой функции мы достаточно подробно рассмотрели в лекции № 20.

UDF

В практике программирования нередко встречаются ситуации, когда программисту недостаточно того набора функций, который предоставлен сервером InterBase. К счастью, InterBase дает возможность создавать и подключать к базе данных собственные функции, которые называются UDF ( User Defined Functions - Функции, определенные пользователем). Прелесть этого механизма в том, что такие функции можно написать на любом языке программирования, включая Delphi, который позволяет делать библиотеки DLL ( Dynamic Link Library - Динамически подключаемые библиотеки). Программист может реализовать в одном или нескольких DLL -файлах множество необходимых ему функций практически любой сложности, затем поместить этот файл (файлы) там, где установлен InterBase. Останется только подключить описанные функции к рабочей базе данных, после чего любую из этих функций можно использовать в запросах на любых клиентских ПК.

Для демонстрации этой возможности как нельзя лучше подойдет функция Upper_Rus, описанная В.В.Фароновым в книге "Программирование баз данных в Delphi7. Учебный курс". Функция преобразует все символы строки в верхний регистр, но в отличие от стандартной Upper, она корректно сработает и с другими наборами символов и порядком сортировки.

Для начала нам нужно создать DLL -файл. Откройте Delphi. В нашем случае нам нужно будет создать отдельный DLL -файл, поэтому создавать его нужно как отдельный проект. Выберите команду меню < File -> Close All >, чтобы закрыть новое приложение, которое Delphi запускает автоматически. Затем выберем команду < File -> New -> Other >. Откроется окно, в котором на вкладке New нам нужно выбрать DLL Wizard:

При этом откроется окно модуля без всяких форм, которое содержит лишь следующий код (комментарии опущены):

library Project1;

uses
  SysUtils,
  Classes;

{$R *.res}

begin
end.

Выберем команду < File -> Save All >, где нам предложат сохранить проект без всяких модулей. Создайте для проекта отдельную папку, а проект назовите Udf_Dll. Далее приводится весь код библиотеки Udf_Dll (без комментариев):

library Udf_Dll;

uses
  SysUtils, Classes;

{$R *.res}

function Upper_Rus(InpString: PChar): PChar; cdecl;
//Функция преобразует буквы входной строки в заглавные
begin
  Result := PChar(ANSIUpperCase(String(InpString)));
end;

exports Upper_Rus;

begin
end.

Поскольку это DLL -проект, который не может работать самостоятельно, выбирать команду Run не нужно. Вместо этого нажмите < Ctrl+F9 >, либо выберите команду < Project -> Compile Udf_Dll >. В результате в указанной вами папке появится файл Udf_Dll.dll.

В приведенном выше коде мы создали функцию Upper_Rus, которая имеет входной и выходной строковые параметры типа PChar (строковый тип Windows ). Кроме того, для правильной работы с InterBase, эта экспортируемая функция задекларирована как cdecl (соглашение о передаче входных параметров). В теле функции входная строка преобразуется в верхний регистр функцией WinAPI ANSIUpperCase, благодаря чему ЛЮБОЙ набор символов (не обязательно русский) будет корректно преобразован в верхний регистр.

В конце мы указываем, что описанная функция Upper_Rus предназначена для экспорта.

Delphi можно закрыть. Полученный файл динамической библиотеки Udf_Dll.dll скопируйте в каталог UDF сервера InterBase (по умолчанию - C:\Program Files\Borland\InterBase\UDF). Если скопировать файл в другой каталог, InterBase его не найдет.

Теперь эту функцию нужно зарегистрировать в базе данных First (сервер InterBase должен быть запущен, утилита IBConsole открыта, база данных First выделена и запущена утилита Interactive SQL ). В окне запросов Interactive SQL укажите следующий запрос:

DECLARE EXTERNAL FUNCTION UPPER_RUS
CSTRING(256)
RETURNS CSTRING(256)
ENTRY_POINT 'Upper_Rus'
MODULE_NAME 'UDF_DLL';
COMMIT;

Здесь указан тип строк InterBase CSTRING, что соответствует типу PChar в Delphi, и установлено ограничение в 256 символов. Теперь, если вы посмотрите в дереве серверов IBConsole в разделе " External Functions " нашей БД First, вы увидите зарегистрированную функцию UPPER _RUS.

В Interactive SQL мы можем ввести запрос, показывающий разницу между стандартной функцией UPPER и нашей функцией UPPER _RUS (может потребоваться перезагрузка IBConsole, или хотя бы закрытие ( Disconnect ) и открытие ( Connect ) базы данных First ):

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

Как видно из рисунка, там, где стандартная функция UPPER не смогла преобразовать текст в верхний регистр, функция UPPER _RUS с этой задачей справилась. Далее эту функцию можно использовать в пределах базы данных First на любом пользовательском ПК, который подключен к InterBase.