Думаю, многие из вас помнят или, по крайней мере, работали с такой конструкцией:

		
.
.
SELECT TO_CHAR(FIELD1), SUBSTR(FIELD2,4,5) FROM
.
.

Здесь хорошо видно применение встроенных функций и процедур в операторе SQL. Вызовы этих функций по своей сути процедурны, по этому ранее такие вольности не допускались. Но в PL/SQL версии 2.1 и выше, именно для хранимых функций такие ограничения отменены. Что собственно очень удобно само по себе. Если обычная или модульная функция отвечает определенным требованиям, то ее можно вызывать во время выполнения SQL-оператора. Если функция создана вами, ее тоже можно вызвать и как встроенную функцию из SQL-оператора. Но при этом она должна отвечать определенным требованиям. Эти требования определяются в терминах, так называемых уровней строгости. Существует четыре различных уровня строгости. Уровень строгости (purity level), определяет структуры данных, которые может считывать или модифицировать функция. Они имеют следующие определения:

1. Любая функция, вызываемая из SQL-оператора, не может модифицировать таблицы базы данных (WNDS).
2. Для того, чтобы функция могла быть выполнена удаленно (через связь базы данных) или параллельно, она не должна читать или записывать значения модульных переменных (RNPS или WNPS).
3. Функция, вызываемая из команды SELECT, VALUES или SET, могут записывать модульные переменные. Во всех других командах, функции должны иметь уровень строгости WNPS.
4. Функция строга настолько, насколько строги вызываемые ею подпрограммы. Если функция вызывает хранимую процедуру, которая выполняет к примеру обновление информации (оператор UPDATE), то функция не имеет уровня строгости WNDS и следовательно не может быть использована в SQL-операторе.
5. Независимо от уровня строгости, хранимые функции PL/SQL - нельзя использовать в ограничении CHECK команды CREATE TABLE или ALTER TABLE. А так же использовать для указания значения по умолчанию, для столбца, так как в этих ситуациях требуется, чтобы описания не изменялись.

То есть, если сказать более просто, не применяйте вызовы тех функций, которые модифицируют таблицы! Давайте опишем уровни строгости функций вот такой табличкой:

Уровень строгости	Значение	Описание
WNDS	Write no database state (не записывать состояния базы данных)	Функция не модифицирует таблицы базы данных. (При помощи операторов DML)
RNDS	Read no database state (не читать состояния базы данных)	Функция не читает таблицы базы данных. (При помощи оператора SELECT)
WNPS	Write no package state (не записывать состояния модуля)	Функция не модифицирует модульные переменные (Модульные переменные не используются в левой части операции присваивания и в операторе FETCH)
RNPS	Read no package state (не читать состояние модуля)	Функция не анализирует модульные переменные (Модульные переменные не используются в правой части операции присваивания и в процедурных или SQL - выражении)

Кроме тех ограничений, которые мы с вами рассмотрели, функция созданная пользователем, то есть вами, должна отвечать так же дополнительным требованиям, чтобы ее можно было вызвать из SQL-операторов. Кстати все встроенные функции тоже отвечают этим требованиям. А вот собственно и требования:

1. Функция должна храниться в БД или отдельно или быть частью модуля. Она не должна быть локальной по отношению к другому блоку.
2. Функция должна иметь входные параметры только с типом IN! Но не IN OUT или OUT!
3. Для формальных параметров должны использоваться только те типы, которые применяются в БД, но не типы PL/SQL, такие как BOOLEAN или RECORD! Типы БД это - NUMBER, CHAR, VARCHAR2, ROWID, LONG, LONG ROW и DATE.
4. Тип возвращаемый функцией так же должен быть типом БД!

Рассмотрим пример для всего вышеизложенного. Напишем простую функцию, которая по ключевому полю конкатенирует строки из таблицы SALESREPS. А затем выполним SQL-оператор, применив нашу функцию.

Записываем:

CREATE OR REPLACE FUNCTION FullData(EMPL IN SALESREPS.EMPL_NUM%TYPE) RETURN VARCHAR2
IS

i_REZ VARCHAR2(120);

BEGIN

	SELECT NAME || ' ' || TITLE INTO i_REZ
	FROM SALESREPS WHERE EMPL_NUM = EMPL;
	
	RETURN i_REZ;

END FullData;
/

Получаем после компиляции:

SQL> CREATE OR REPLACE FUNCTION FullData(EMPL IN SALESREPS.EMPL_NUM%TYPE) RETURN VARCHAR2
  2  IS
  3  
  4  i_REZ VARCHAR2(120);
  5  
  6  BEGIN
  7  
  8   SELECT NAME || ' ' || TITLE INTO i_REZ
  9   FROM SALESREPS WHERE EMPL_NUM = EMPL;
 10  
 11   RETURN i_REZ;
 12  
 13  END FullData;
 14  /

Функция создана.

А теперь SQL - оператор:

SELECT EMPL_NUM, FullData(EMPL_NUM) "Full Data" FROM SALESREPS 
/

SQL> SELECT EMPL_NUM, FullData(EMPL_NUM) "Full Data" FROM SALESREPS
  2  /


     EMPL_NUM Full Data
------------- ----------------------------------
          105 Вася Пупкин Рапорт продажа
          109 Маша Распутина Рапорт продажа
          102 Филип Киркоров Рапорт продажа
          106 Света Разина Круто втюхал
          104 Наташа Королева Рапорт пусто
          101 Игорь Николаев Рапорт продажа
          110 Крис Кельми Рапорт продажа
          108 Игорь Петров Рапорт продажа
          103 Дима Маликов Рапорт продано
          107 Маша Сидорова Продано все
          120 Максим Галкин Продано все

11 строк выбрано.

Как видите наш SQL- оператор сработал без замечаний! Так как все, что мы делали соответствует purity level! А вот для модульных функций дело обстоит несколько иначе. Для того, чтобы в модулях определять уровни строгости необходима прагма - RESTRICT_REFERENCES (ограничить ссылки). Эта прагма устанавливает уровень строгости для конкретной функции. Записывается она следующим образом:

----- PRAGMA RESTRICT_REFERENCES ( имя_функции, WNDS, [WNPS], [RNDS], [RNPS] ) ------

Например заголовок нашего модуля из шага 97 мог быть записан вот так:

CREATE OR REPLACE PACKAGE test_pkg IS

	PROCEDURE Out_Screen(TOSC IN VARCHAR2);
	
	FUNCTION Add_Two_Num(A IN NUMBER, B IN NUMBER) RETURN NUMBER;
	PRAGMA RESTRICT_REFERENCES (Add_Two_Num, WNDS, WNPS, RNPS);
	
	FUNCTION Min_Two_Num(A IN NUMBER, B IN NUMBER) RETURN NUMBER;

	FUNCTION FACTORIAL(NUM IN NUMBER) RETURN NUMBER;
	PRAGMA RESTRICT_REFERENCES (FACTORIAL, WNDS, WNPS, RNPS);
	
END test_pkg;
/

Здесь мы применили ее дважды, так как задали прагму для двух функций. В чем собственно необходимость использования прагмы RESTRICT_REFERENCES? Почему прагма применяется в модульной функции и не обязательна для автономной? Ответить на этот вопрос можно проанализировав взаимосвязи, между заголовком и телом модуля. Вспомним, что блоки PL/SQL вызывающие модульную функцию зависят только от заголовка модуля, но не от его тела. Более того при создании вызывающего блока тело модуля вообще может отсутствовать. Поэтому компилятору PL/SQL необходимо указание помогающее определить уровни строгости модульной функции, чтобы проверить корректность использования функции в вызывающем блоке. При последующих модификациях, тела модуля код функции проверяется на соответствие заданной прагме. Те же правила справедливы и для переопределенных функций. Модули, которые встроены в сам PL/SQL не являются строгими. По этому не могут быть включены в SQL - оператор! Например DBMS_OUTPUT. И на последок, при вызове функций в SQL - операторах, формальные параметры могут иметь значение по умолчанию. Но применять позиционное представление запрещается! Вот собственно, этот раздел и замыкает тематику работы с пакетами.