Хранимые программные единицы, страница 3

·  Если компиляция прошла успешно, исполняемый код (p-код) будет сохранен в словаре базы данных, и функция может быть вызвана

·  Если компиляция прошла успешно, и был сгенерирован p-код, хранимая функция может быть вызвана из любого инструмента, поддерживающего PL/SQL. Хранимая функция может быть вызвана, как часть выражения PL/SQL или из предложения SQL.

SQL> CREATE OR REPLACE FUNCTION emp_sal (v_job IN VARCHAR2)

  2  RETURN NUMBER

  3  IS

  4     v_sal_inner emp.sal%TYPE;

  5  BEGIN

  6     SELECT sal INTO v_sal_inner FROM emp WHERE LOWER(job)=LOWER(v_job);

  7     RETURN v_sal_inner;

  8  END emp_sal;

  9  /

Function created.

SQL> VARIABLE h_sal NUMBER

SQL> EXECUTE :h_sal := emp_sal('President')

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5000

Вызов хранимых функций в SQL

Хранимые функции могут быть вызваны, как часть выражения SQL, для выполнения сложных вычислений, увеличения производительности SQL запроса (ограничивая выборку строк с помощью хранимых функций, используемых в предложении WHERE, а не в приложении) или сложного манипулирования символьными данными (например, кодирование данных). Хранимые функции могут быть использованы везде, где могут быть использованы встроенные функции SQL:

·  В списке выбора команды SELECT

·  В предложениях WHERE, HAVING

·  В предложениях ORDER BY, GROUP BY

·  В предложении VALUES команды INSERT

·  В предложении SET команды UPDATE

SQL> CREATE OR REPLACE FUNCTION pres_sal (v_sal IN NUMBER)

  2  RETURN NUMBER

  3  IS

  4     v_sal_inner emp.sal%TYPE;

  5  BEGIN

  6     SELECT sal INTO v_sal_inner FROM emp WHERE LOWER(job)='president';

  7     RETURN v_sal_inner*0.1+v_sal;

  8  END pres_sal;

  9  /

Function created.

SQL> SELECT ename, sal, pres_sal(sal) FROM emp WHERE LOWER(job)='clerk';

ENAME             SAL PRES_SAL(SAL)

---------- ---------- -------------

SMITH             800          1300

ADAMS            1100          1600

JAMES             950          1450

MILLER           1300          1800

Однако хранимые функции, используемые в предложениях SQL, должны удовлетворять определенным условиям:

·  В SQL могут использоваться только хранимые функции, а не процедуры

·  Хранимая функция должна быть однострочной, а не групповой

·  Хранимая функция может иметь только параметры IN. Параметры OUT и IN OUT не допускаются

·  Типы данных параметров должны быть CHAR, DATE, NUMBER. Типы данных BOOLEAN, TABLE, RECORD не допускаются

·  Тип возвращаемого значения должен быть внутренним типом данных сервера Oracle

·  Способ передачи параметров может быть только позиционным

·  Хранимая функция не может изменять данные таблиц базы данных. Команды манипулирования данными INSERT, UPDATE, DELETE не допускаются

·  Вызов подпрограмм, нарушающих вышеизложенные условия, не допускается

Удаление хранимой функции

Для удаления хранимой функции используется команда DDL DROP FUNCTION.

DROP FUNCTION имя_функции;

SQL> DROP FUNCTION emp_sal;

Function dropped.

SQL> DROP FUNCTION pres_sal;

Function dropped.

Сравнение процедур и функций

Хранимые процедуры так же, как и хранимые функции могут возвращать вычисленные значения в вызывающую среду. Поэтому необходимо достаточно ясно представлять себе различия между процедурой и функцией, чтобы выбрать правильную стратегию при планировании программной логики. Создавайте процедуру, чтобы сохранить последовательность действий для последующего использования и создавайте функцию, чтобы вычислить значение, которое должно быть возвращено в вызывающую среду. Процедура, содержащая один параметр OUT, легко может быть переписана в виде функции

Процедура	Функция
Вызывается в качестве целой команды PL/SQL	Вызывается как часть выражения
Может, но не обязана возвращать значение в вызывающую среду	Обязана возвращать значение в вызывающую среду
Не могут быть вызваны из предложений SQL	Могут быть вызваны из предложений SQL

Свойства хранимых процедур и функций

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

Улучшение поддержки приложений. Одна и та же подпрограмма может использоваться в разных приложениях, поэтому достаточно модифицировать эту программную единицу вместо того, чтобы исправлять все приложения, в которых она используется.

Безопасность и целостность данных. Чтобы разрешить пользователю изменять данные посредством подпрограммы, необходимо предоставить только привилегию на выполнение этой программной единицы. Это позволяет предотвратить несанкционированный прямой доступ к данным, минуя программную логику приложения. Кроме того, подпрограмма позволяет автоматически выполнять связанные действия по обновлению данных в одной транзакции, что предохраняет данные от нарушения целостности.

Управление хранимыми подпрограммами

Для создания, выполнения, модификации и удаления хранимых программных единиц требуются соответствующие системные и объектные привилегии.

Чтобы пользователь мог создавать программные единицы в собственной схеме, необходима системная привилегия: Программные единицы в собственной схеме можно перекомпилировать, выполнить или удалить, для этого привилегии не нужны.

·  CREATE (ANY) PROCEDURE – право создавать хранимые программные единицы в собственной (или любой) схеме

·  ALTER ANY PROCEDURE – право перекомпилировать программные единицы в любой схеме

·  DROP ANY PROCEDURE – право удалять программные единицы в любой схеме

·  EXECUTE ANY PROCEDURE – право выполнять любые программные единицы

Пользователь, создавший подпрограмму, может предоставить кому-либо объектную привилегию на выполнение этой программной единицы.

·  EXECUTE имя_подпрограммы

По умолчанию хранимые подпрограммы и SQL предложения, вызываемые из хранимой программной единицы, выполняются с привилегиями пользователя, который ее создал. По этой причине объектные привилегии на объекты, к которым обращается подпрограмма, не нужны, если объекты находятся в той же схеме, что и сама подпрограмма (владелец объектов всегда имеет привилегии на них). С одной стороны это хорошо, т.к. для манипулирования объектами через программную единицу требуется единственная привилегия на выполнение этой подпрограммы. С другой стороны, такие подпрограммы оказываются жестко привязанными к схеме, в которой они хранятся, что может вызвать определенные неудобства. Например, одна и та же таблица dept находится одновременно в двух схемах: scott и blake, а хранимая процедура create_dept создана в схеме scott: