Функции, определенные пользователем (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 тип_возвращаемого_результата