Функции, определенные пользователем

Функции, определенные пользователем (UDF – User Defined Functions) расширяют возможности, предоставляемые встроенными функциями SQL, и их можно использовать везде, где могут использоваться встроенные функции. Функцию, определенную пользователем, можно создать как функцию с источником, реализация которой наследуется от какой-либо другой существующей функции, или как внешнюю функцию, которая написана на одном из языков программирования.

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

Как указывалось выше, при определении особых типов данных система автоматически генерирует только функции преобразования исходного типа данных в новый тип и наоборот, а также функции сравнения. Никакие другие функции, в том числе арифметические или агрегатные для особых типов, создаваемых на основе числовых, не генерируются. Если возникает необходимость использовать подобные функции с операндами созданного особого типа, такие функции должны быть созданы пользователем и зарегистрированы на основе существующих аналогичных функций – функций-источников. Такая регистрация выполняется с помощью предложения CREATE FUNCTION, имеющего следующий синтаксис:

CREATE FUNCTION имя_функции ( список_параметров )

RETURNS тип_данных_результата

SOURCE имя_функции_источника ( тип_операнда, … )

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

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

Имя функции вместе с количеством и типами данных параметров должно быть уникальным. Длина, точность и масштаб, указываемые для соответствующих типов данных, во внимание не принимаются. Так, например, типы параметров VARCHAR(20) и VARCHAR(35) считаются одинаковыми. Поэтому при указании соответствующих типов данных значения длины, точности и масштаба можно не указывать, кодируя только пустые скобки. Так, вместо VARCHAR(20) можно указать VARCHAR(), а вместо DECIMAL(9,3) – DECIMAL().

RETURNS – определяет тип данных результата функции.

SOURCE – указывает, что создаваемая функция реализуется с помощью другой функции (функции-источника), уже известной менеджеру базы данных. Функцией-источником может быть любая встроенная функция, за некоторыми исключениями (например, COALESCE, NULLIF, VALUE), или ранее определенная пользователем скалярная функция. Конструкция SOURCE может быть указана только для скалярной или агрегатной функции; ее нельзя указывать для табличной функции. Конструкция SOURCE указывает на идентичность создаваемой функции с функцией-источником, т.е. там, где требуется выполнить создаваемую функцию, на самом деле, будет выполнена функция-источник.

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

В качестве имени функции (создаваемой и функции-источника) может быть указан знак операции, заключенный в двойные кавычки, например: "+". Это позволяет использовать знаки операций с операндами соответствующих типов.

Примеры:

Пусть был определен особый тип данных MPH на основе встроенного типа INTEGER:

CREATE DISTINCT TYPE MPH AS INTEGER WITH COMPARISON

Для созданного типа данных MPH сгенерированы необходимые функции преобразования INTEGER(MPH) и MPH(INTEGER).

Пример 1. Необходимо предоставить возможность использовать для особого типа данных MPH операцию сложения ("+"):

CREATE FUNCTION "+" (MPH, MPH)

RETURNS MPH

SOURCE "+" (INTEGER, INTEGER)

При использовании операции сложения для операндов типа MPH функция преобразования INTEGER(MPH) преобразует операнды к типу INTEGER, затем выполняется встроенная функция (операция) сложения, результат которой (типа INTEGER) с помощью функции преобразования MPH(INTEGER) преобразуется к типу MPH.

Пример 2. Необходимо предоставить возможность использовать для колонки типа MPH агрегатную функцию AVG(колонка):

CREATE FUNCTION AVG_MPH (MPH)

RETURNS MPH

SOURCE AVG (INTEGER)

Здесь также выполняются преобразования INTEGER(MPH) для операнда функции AVG и MPH(INTEGER) для результата.

В данном примере для функции использовано собственное имя – AVG_MPH. Можно, вместо этого, использовать и существующее имя функции – AVG:

CREATE FUNCTION AVG (MPH)

RETURNS MPH

SOURCE AVG (INTEGER)

Внешние функции, определенные пользователем

Внешние функции, определенные пользователем, могут быть скалярными или табличными и могут быть написаны с использованием разных языков программирования (Java, C, …). Мы ограничимся рассмотрением функций, написанных на языке SQL PL.

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

Табличная функция возвращает таблицу и может быть вызвана только из конструкции FROM предложения SELECT; при этом вызов табличной функции должен начинаться с ключевого слова TABLE с обязательным переименованием: TABLE (имя_функции (списак_аргументов)) AS корреляционное_имя

Внешние функции, определенные пользователем, регистрируются в системе также с помощью предложения CREATE FUNCTION, имеющего следующий синтаксис:

CREATE FUNCTION имя_функции ( список_параметров )

RETURNS тип_возвращаемого_результата