Вызов пользовательских функций в SQL и PL/SQL

Пользовательские функции в SQL и PL/SQLOracle позволяет вызывать пользовательские функции в коде 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 Extensibility Framework. За подробной информацией по этой теме обращайтесь к документации 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.

 

Вас заинтересует / Intresting for you:

Назначение языка SQL и необход...
Назначение языка SQL и необход... 1483 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3110 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 7008 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5746 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать