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

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])
{<тело подпрограммы>} — // Собственно подпрограмма

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

Параметр Режим Описание
instr In Входной параметр для SQL-функции
outstr Out Возвращаемое значение SQL-функции
ind_instr In Индикатор для параметра Instr (<0, если instr не определено)
ind_outstr Out Индикатор для параметра outstr
sqlstr In/out Значение SQLSTATE, характеризующее результат исполнения
name_prog In Имя подпрограммы
spesh_name In Особое имя для подпрограммы
msgstr In/out Текст сообщения, помещаемый в диагностическую область

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

FROUND 0 0
FROUND 1 1
FROUND 2 1

SELECT CNum.SumAmt FROM AllCustAmt

2001 767,1900024441406
2002 1713,22998046875
2003 5160,4501953125
2004 1385,69995117188
2006 14614,8798828125
2007 1900,099975558594
2008 1116,84997558594

SELECT CNum, FRound(SumAmt, 2) FROM AllCustAmt

2001 767,19
2002 1713,23
2003 5160,45
2004 1385,7
2006 14614,88
2007 1900,1
2008 1116,85

SELECT CNum, FRound(SumAmt, 1) FROM AllCustAmt

2001 767,2
2002 1713,2
2003 5160,45
2004 1385,7
2006 14614,9
2007 1900,1
2008 1116,8

Внешние функции в 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 – Среда исполнения общего языка.


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





Статистика

Рейтинг@Mail.ru