1) Понятие внешней функции
SQL/PSM разрешает для определения тела подпрограммы использовать код, написанный на широко известных HOST-языках программирования. Под внешней функцией понимается функция, написанная на одном из HOST-языков программирования и специальным образом подключенная к БД.

2) Особенности внешних функций
• Внешние функции позволяют, частности, обеспечить доступ к функциям операционной системы и приложению.
• Коды реализации могут располагаться где угодно в файловой структуре операционной системы, но они должны иметь “обертки” на SQL, хранящиеся в БД.
• Как и хранимая, внешняя функция имеет заголовок, который определяет ее для вызова SQL-операторами.
• Необходимо представить также и сведения о том, как будут отображаться параметры функции.
• После “регистрации” в БД внешняя функция может быть вызвана таким же образом, как и хранимая.
• Типы данных SQL должны отображаться в типы данных HOST-языка. Значение в SQL, которое может иметь неопределенное значение, также должно отображаться в HOST-языке.

3) Объявление внешней функции

CREATE FUNCTION <имя функции> ([<имя параметра> <тип данных> [(<размер>)] [AS LOCATOR], … ]) [RETURNS <тип данных>[(<размер>)]
[AS LOCATOR]][CAST FROM <тип данных>[(<размер>)] [AS LOCATOR]] [LANGUAGE {ADA|C|FORTRAN|NUMPS|PASCAL|PL1}] [SPECIFIC <специальное имя>]
[NO SQL] [[NOT DETERMINISTIC]
[RETURN NULL ON INPUT|CALL ON NULL INPUT] [STATIC DISPATCH]
EXTERNAL [NAME <имя внешней функции>] [PARAMETER STYLE {SQL|GENERAL}] [TRANSFORM GROUP <имя группы>]

4) Ключевые слова
• <имя параметра> — не является обязательным, поскольку параметры сопоставляются ссылками в программе в порядке появления. Первый параметр в списке приписывается первому доступному адресату, второй – второму и т.д.
• AS LOCATOR – используется при объявлении функции, обрабатывающей поля типа BLOB, CLOB, ARRAY, например, фильтры.
• CAST FROM <тип данных>[(<размер>)] [AS LOCATOR] – позволяет изменить тип данных “на ходу”.
• [PARAMETER STYLE {SQL|GENERAL}] – определяет способ работы с NULL-значениями. Если SQL, то параметры, например индикаторы, передаются автоматически. Иначе нет.
• NO SQL – показывает, что функция не содержит SQL-операторов.
• SPECIFIC <специальное имя> – используется с пользовательскими типами данных.
• STATIC DISPATCH – возвращает статические значения определенного пользователем типа данных или ARRAY.
• Если входной параметр допускает значение NULL (базовый язык не может обрабатывать NULL-значения), то можно указывать RETURN NULL ON INPUT и функция даже не вызывается, а ответ выдается немедленно. CALL ON NULL INPUT, используемый по умолчанию, предполагает получение благоприятно результата.
• TRANSFORM GROUP <имя группы> – обеспечивает преобразование значений пользовательских типов данных в HOST-переменные и обратно.

5) Стили параметров
• Стиль параметров GENERAL
Этот стиль отображения предназначен для подпрограмм на HOST-языке, которые уже существуют. Подпрограмма на HOST-языке будет иметь по одному параметру для каждого описания в SQL. В этом случае необходимо очень аккуратно работать с NULL-значениями.

Пример функции на стиль параметров GENERAL:

CREATE FUNCTION MaxRating (VarChar(30) RETURNS Char(10) LANGUAGE C EXTERNAL NAME maxrat PARAMETER STYLE GENERAL
void maxrat (char instr[31])
{<тело подпрограммы>} — // Собственно подпрограмма

Использовать функцию можно следующим образом:

SELECT * FROM SalesPeople WHERE MaxRating(‘Rating’)=200

• Стиль параметров SQL
Этот стиль отображения параметров рекомендуется для функций, предназначенных для лучшей поддержки SQL. Возвращаемое значение функции становится параметром кода на HOST-языке, следующим за всеми параметрами этого кода, которые отображают параметры SQL. Как SQL-параметры, так параметр возвращаемого значения, снабжаются параметрами индикаторами на HOST-языке, которые могут представлять NULL-значения.

Пример функции на стиль параметров SQL:

CREATE FUNCTION MaxRating(Varchar(30)) RETURNS Char(10) LANGUAGE C EXTERNAL NAME maxrat PARAMETER STYLE SQL;
Void maxrat(char instr[30], char outstr[10], long *ind_instr, long *ind_outstr, char sqlstr[6], char name_prog[100], char spesh_name[100], char msgstr[80])
{<тело подпрограммы>} — // Собственно подпрограмма

Таблица с описанием параметров:

6) Внешние функции промышленных серверов

→ Внешние функции в InterBase/Firebird
• Создание функции на HOST-языке
Первоначально на HOST-языке, например Pascal или C, следует создать библиотеку динамически компоновки (DLL), включающую необходимые функции.

Library MATH;
Uses SysUtils, Math;
Function FRound(Var Dbl: Double; Var Digits: Integer):Double; cdecl; export;
Begin
If Dbl>0 then Result:=Trunc(Dbl)+Round(IntPower(10,Digits)*Frac(Dbl))/IntPower(10,Digits)
Else Result:=0.0;
End;
Exports FRound;
End.

• Размещение и регистрация в БД
Необходимо расположить эту библиотеку в каталоге InterBase\Bin (Firebird\UDF). Для регистрации подпрограммы в соответствующей БД следует выполнить SQL-оператор следующего вида для каждой внешней функции:

DECLARE EXTERNAL FUNCTION fround
DOUBLE PRECISION, INTEGER //два входных параметра
RETURNS DOUBLE PRECISION BY VALUE
EXTRY_POINT “FRound” //<имя функции в Dll-модуле>
MODULE_NAME “Math”; //<имя модуля>

SELECT * FROM RDB$FUNCTIONS

Результат: FROUND

SELECT * FROM RDB$FUNCTION_ARGUMENTS

SELECT CNum.SumAmt FROM AllCustAmt

SELECT CNum, FRound(SumAmt, 2) FROM AllCustAmt

SELECT CNum, FRound(SumAmt, 1) FROM AllCustAmt

→ Внешние функции в Oracle
• Создание библиотеки
Первоначально в БД необходимо создать объект LIBRARY, связывающий псевдоним библиотеки с файлом:

CREATE LIBRARY <псевдоним> {IS|AS} <путь до файла>;

Пример: CREATE LIBRARY MyLib AS “MyLib.Dll”;

• Объявление внешней библиотеки

EXTERNAL LIBRARY <псевдоним> [NAME <имя внешней подпрограммы>] [LANGUAGE <язык>] [CALLING STANDARD {C|PASCAL}] [WITH CONTEXT] [PARAMETERS(<внешний параметр>,…)]

• Ключевые слова
Параметр подразумевает следующую конструкцию:

{{<имя параметра> [<свойство>]|RETURN <свойство>} [BY REF] [<тип данных>]}

Свойство подразумевает следующую конструкцию:

{INDICATOR|LENGTH|MAXLEN|CHARSETID|CHARSETFORM}

Примечания: WITH CONTEXT позволяет передать внешней программе указатель контекста, например для доступа к служебным подпрограммам. Свойства задают дополнительные атрибуты параметров и необходимы для согласования внешнего языка и языка PL/SQL.

• Регистрация подпрограмм в БД

CREATE PROCEDURE|FUNCTION <имя подпрограммы> ([<имя параметра> {IN|OUT|IN OUT} <тип данных> [(<размер>)],…]) [RETURN <тип данных> [(<размер>)]] {IS|AS} <внешнее тело>;

Пример:

CREATE FUNCTION factorial(p_num IN BINARY_INTEGER) RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY MyLib NAME “factorial” LANGUAGE C;
int factorial (int p_num); объявление на языке C

• Особенности использования
Для обработки NULL-значений можно использовать в подпрограммах предопределенные константы OCI_IND_NULL и OCI_IND_NOTNULL, которые внешняя подпрограмма может сравнивать с индикаторами.
Пример объявления функции и прототипа на С:

…LANGUAGE C PARAMETERS (p_num, p_num INDICATOR, RETURN INDICATOR, RETURN);
int factorial(int p_num, short p_num_ind, short *retind);

• Внешние функции в SQL Server

CREATE FUNCTION <имя функции CLR> ([@<имя параметра> [AS] <тип данных> [(<размер>)] [=<значение по умолчанию>],…]) RETURNS <скалярное значение> [AS] EXTERNAL NAME <имя метода>

Примечание: CLR – Common Language Runtime – Среда исполнения общего языка.

Комментарии запрещены.