Хранимые функции

Функция PL/SQL похожа на процедуру PL/SQL: она также имеет спецификацию и тело. Главное различие между процедурой и функцией в том, что функция предназначена для возврата значения, которое может использоваться в SQL-операторе.

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

calc_percent (value_1 NUMBER,

value_2 NUMBER) return NUMBER

Эта функция принимает в качестве входных параметров два числа, ссылаясь на них внутри себя как на VALUE_1 и VALUE_2. После написания тела функции ее можно вызывать в SQL-операторе следующим образом:

INSERT INTO employee VALUES (3000, CALC_PERCENT(300, 3000));

Триггеры

Триггер — это процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием (triggering event). Например, можно писать триггеры, срабатывающие при выполнении над таблицей операций INSERT, UPDATE или DELETE; при выдаче команд DDL; при входе пользователя в систему или его выходе из системы; при запуске или останове базы данных; при возникновении ошибок.

Между триггерами и процедурами PL/SQL есть три различия.

• Триггеры нельзя вызывать из кода программы. Oracle вызывает их автоматически в ответ на определенное событие.

• Триггеры не имеют списка параметров.

• Спецификация триггера немного отличается от спецификации процедуры.

Отличие хранимых процедур от sql-сценариев

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

5.3. Структура блока pl/sql

Программный код на языке PL/SQL, реализующий некоторый алгоритм, состоит из базовых блоков (basic blocks).

Базовый блок PL/SQL состоит из четырех секций, или разделов: секции заголовка (header section), необязательной секции объявлений (declaration section), выполняемой секции (execution section) и необязательной секции исключений (exception section).

Анонимный блок (anonymous block) — это блок PL/SQL без секции заголовка, иначе говоря, секции имени, поэтому он и называется анонимным. Анонимные блоки могут выполняться из SQL*Plus и использоваться в функциях, процедурах и триггерах PL/SQL. Сами процедуры, функции и триггеры также состоят из базовых блоков. Это значит, что базовый блок можно помещать в другой базовый блок.

Ниже приведен пример анонимного блока, в котором для вывода информации использована процедура dbms_output .put_line (слэш после блока заменяет команду run в среде SQL*Plus):

DECLARE

Num_a NUMBER := 6;

Num_b NUMBER;

BEGIN

Num_b := 0;

Num_a := Num_a / Num_b ;

Num_b := 7;

dbms_output .put_line ( ' Value of Num_b ' || Num_b) ;

EXCEPTION

WHEN ZERO_DIVIDE

THEN

dbms_output.put_line ( 'Trying to divide by zero');

dbms_output.put_line ( ' Value of Num_a ' I I Num_a) ;

dbms_output.put_line ( ' Value of Num_b ' || Num_b) ;

END;

/