Oracle позволяет вызывать пользовательские функции в коде SQL. Фактически это позволяет адаптировать язык SQL под требования конкретных приложений.
Каждый раз, когда исполнительное ядро SQL вызывает функцию PL/SQL, оно должно «переключаться» на исполнительное ядро PL/SQL. При многократном вызове функции затраты на переключение контекстов могут быть весьма значительными.
Требования к вызываемым функциям
Чтобы определяемую программистом функцию PL/SQL можно было вызывать из команд SQL, она должна отвечать следующим требованиям:
- Все параметры функции должны иметь режим использования
IN. РежимыIN OUTиOUTв функциях, встраиваемых в SQL-код, недопустимы. - Типы данных параметров функций и тип возвращаемого значения должны распознаваться сервером Oracle. PL/SQL дополняет основные типы Oracle, которые пока не поддерживаются базой данных. Речь идет о типах
BOOLEAN,BINARY_INTEGER, ассоциативных массивах, записях PL/SQL и определяемых программистом подтипах. - Функция должна храниться в базе данных. Функция, определенная на стороне клиента, не может вызываться в командах SQL, так как SQL не сможет разрешить ссылку на эту функцию.
По умолчанию пользовательские функции, вызываемые в
SQL, оперируют данными одной строки, а не столбца (как агрегатные функцииSUM,MINиAVG). Чтобы создать агрегатные функции, вызываемые вSQL, необходимо использовать интерфейсODCIAggregate, который является частью средыOracle ExtensibilityFramework. За подробной информацией по этой теме обращайтесь к документации Oracle.
Ограничения для пользовательских функций, вызываемых в SQL
С целью защиты от побочных эффектов и непредсказуемого поведения хранимых процедур Oracle не позволяет им выполнять следующие действия:
- Хранимые функции не могут модифицировать таблицы баз данных и выполнять команды
DDL(CREATE TABLE,DROP INDEXи т. д.),INSERT,DELETE,MERGEиUPDATE. Эти ограничения ослабляются, если функция определена как автономная транзакция. В таком случае любые вносимые ею изменения осуществляются независимо от внешней транзакции, в которой выполняется запрос. - Хранимые функции, которые вызываются удаленно или в параллельном режиме, не могут читать или изменять значения переменных пакета. Сервер Oracle не поддерживает побочные эффекты, действие которых выходит за рамки сеанса пользователя.
- Хранимая функция может изменять значения переменных пакета, только если она вызывается в списке выборки либо в предложении
VALUESилиSET. Если хранимая функция вызывается в предложенииWHEREилиGROUP BY, она не может изменять значения переменных пакета. - До выхода Oracle8 пользовательские функции не могли вызывать процедуру
RAISE_ APPLICATION_ERROR. - Хранимая функция не может вызывать другой модуль (хранимую процедуру или функцию), не соответствующий приведенным требованиям.
- Хранимая функция не может обращаться к представлению, которое нарушает любое из предшествующих правил. Представлением (view) называется хранимая команда
SELECT, в которой могут вызываться хранимые функции. - До выхода Oracle11g для передачи параметров функциям могла использоваться только позиционная запись. Начиная с Oracle11g, допускается передача параметров по имени и смешанная запись.
Непротиворечивость чтения и пользовательские функции
Модель непротиворечивости чтения в базе данных Oracle проста и понятна: после выполнения запрос «видит» данные в том состоянии, в котором они существовали (были зафиксированы в базе данных) на момент начала запроса, с учетом результатов изменений, вносимых командами DML текущей транзакции. Таким образом, если мой запрос был выполнен в 9:00 и продолжает работать в течение часа, даже если за это время другой пользователь внесет в данные изменения, они не отразятся в моем запросе.
Но если не принять специальных мер предосторожности с пользовательскими функциями в ваших запросах, может оказаться, что ваш запрос будет нарушать (по крайней мере на первый взгляд) модель непротиворечивости чтения базы данных Oracle. Чтобы понять этот аспект, рассмотрим следующую функцию и запрос, в котором она вызывается:
FUNCTION total_sales (id_in IN account.account_id%TYPE)
RETURN NUMBER
IS
CURSOR tot_cur
IS
SELECT SUM (sales) total
FROM orders
WHERE account_id = id_in
AND TO_CHAR (ordered_on, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY');
tot_rec tot_cur%ROWTYPE;
BEGIN
OPEN tot_cur;
FETCH tot_cur INTO tot_rec;
CLOSE tot_cur;
RETURN tot_rec.total;
END;
SELECT name, total_sales (account_id)
FROM account
WHERE status = 'ACTIVE';
Таблица account содержит 5 миллионов активных строк, а таблица orders — 20 миллионов. Я запускаю запрос в 10:00, на его завершение уходит около часа. В 10:45 приходит некто, обладающий необходимыми привилегиями, удаляет все строки из таблицы orders и закрепляет транзакцию. По правилам модели непротиворечивости чтения Oracle сеанс, в котором выполняется запрос, не должен рассматривать эти строки как удаленные до завершения запроса. Но при следующем вызове из запроса функция total_sales не найдет ни одной строки и вернет NULL — и так будет происходить до завершения запроса.
При выполнении запросов из функций, вызываемых в коде SQL, необходимо внимательно следить за непротиворечивостью чтения. Если эти функции вызываются в продолжительных запросах или транзакциях, вероятно, вам стоит выполнить следующую команду для обеспечения непротиворечивости чтения между командами SQL текущей транзакции: SET TRANSACTION READ ONLY
В этом случае необходимо позаботиться о наличии достаточного табличного пространства отмены.
Определение подпрограмм PL/SQL в командах SQL (12.1 и выше)
Разработчики уже давно могли вызывать свои функции PL/SQL из команд SQL. Допустим, я создал функцию с именем BETWNSTR, которая возвращает подстроку с заданной начальной и конечной позицией:
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
) продолжение #
RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (
string_in, start_in, end_in - start_in + 1 ));
END;
Функция может использоваться в запросах следующим образом:
SELECT betwnstr (last_name, 3, 5)
FROM employees
Эта возможность позволяет «расширить» язык SQL функциональностью, присущей конкретному приложению, и повторно использовать алгоритмы (вместо копирования). К недостаткам выполнения пользовательских функций в SQL следует отнести необходимость переключения контекста между исполнительными ядрами SQL и PL/SQL. Начиная с Oracle Database 12c вы можете определять функции и процедуры PL/SQL в секции WITH подзапроса, чтобы затем использовать их как любую встроенную или пользовательскую функцию. Эта возможность позволяет консолидировать функцию и запрос в одной команде:
WITH
FUNCTION betwnstr (
string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN (SUBSTR (
string_in,
start_in,
end_in - start_in + 1));
END;
SELECT betwnstr (last_name, 3, 5)
FROM employees
Главное преимущество такого решения — повышение производительности, так как при таком определении функций затраты на переключение контекста с ядра SQL на ядро PL/SQL существенно снижаются. С другой стороны, за него приходится расплачиваться возможностью повторного использования логики в других частях приложения.
Впрочем, для определения функций в секции WITH есть и другие причины. В SQL можно вызвать пакетную функцию, но нельзя сослаться на константу, объявленную в пакете (если только команда SQL не выполняется внутри блока PL/SQL), как показано в следующем примере:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 year_number CONSTANT INTEGER := 2013;
4 END;
5 /
Package created.
SQL> SELECT pkg.year_number FROM employees
2 WHERE employee_id = 138
3 /
SELECT pkg.year_number FROM employees
*
ERROR at line 1:
ORA-06553: PLS-221: 'YEAR_NUMBER' is not a procedure or is undefined
Классическое обходное решение основано на определении функции в пакете и ее последующем вызове:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 FUNCTION year_number
4 RETURN INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 c_year_number CONSTANT INTEGER := 2013;
4
5 FUNCTION year_number
6 RETURN INTEGER
7 IS
8 BEGIN
9 RETURN c_year_number;
10 END;
11 END;
12 /
Package body created.
SQL> SELECT pkg.year_number
2 FROM employees
3 WHERE employee_id = 138
4 /
YEAR NUMBER
------------
2013
Для простого обращения к значению константы в команде SQL потребуется слишком много кода и усилий. Начиная с версии 12.1 это стало излишним — достаточно создать функцию в секции WITH:
WITH
FUNCTION year_number
RETURN INTEGER
IS
BEGIN
RETURN pkg.year_number;
END;
SELECT year_number
FROM employees
WHERE employee_id = 138
Функции PL/SQL, определяемые в SQL, также пригодятся при работе с автономными базами данных, доступными только для чтения. Хотя в таких базах данных невозможно создавать «вспомогательные» функции PL/SQL, вы можете определять их прямо в запросах.
Механизм WITH FUNCTION стал чрезвычайно полезным усовершенствованием языка SQL. Тем не менее каждый раз, когда вы планируете его использование, стоит задать себе один вопрос: «Потребуется ли эта функциональность в нескольких местах приложения?»
Если вы ответите на него положительно, следует решить, компенсирует ли выигрыш по производительности от применения WITH FUNCTION потенциальные потери от копирования и вставки этой логики в нескольких командах SQL.
Учтите, что в версии 12.1 в блоках PL/SQL невозможно выполнить статическую команду select с секцией with function. Безусловно, это выглядит очень странно, и я уверен, что в 12.2 такая возможность появится, но пока при попытке выполнения следующего кода будет выдана ошибка:
SQL> BEGIN
2 WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2)
3 RETURN VARCHAR2
4 IS
5 BEGIN
6 RETURN fname_in || ' ' || lname_in;
7 END;
8
9 SELECT LENGTH (full_name (first_name, last_name))
10 INTO c
11 FROM employees;
12
13 DBMS_OUTPUT.put_line ('count = ' || c);
14 END;
15 /
WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2)
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PL/SQL: ORA-00905: missing keyword
Помимо конструкции WITH FUNCTION, в версии 12.1 также появилась директива UDF для улучшения быстродействия функций PL/SQL, выполняемых из SQL.
