Обработка ошибок, возникших при выполнении кода PL/SQL, страница 3

	Хранимая подпрограмма	Прикладная подпрограмма
Хранится	В базе данных	В приложении
Выполняется	Из любого приложения или инструментального средства базы данных	Только из данного приложения

Подпрограмма может содержать до четырех секций:

§  Обязательная секция заголовка – определяет имя, тип и аргументы подпрограммы

§  Декларативная необязательная секция – содержит объявление локальных переменных, констант, курсоров, пользовательских исключений

§  Обязательная секция выполняемого кода – содержит команды SQL для манипулирования данными в базе и команды PL/SQL для манипулирования данными в блоке

§  Необязательная секция обработки исключений – определяет действия, связанные с обработкой ошибок сервера или пользовательских исключений, возникших в секции выполняемого кода

Любое инструментальное средство Oracle позволяет создавать как хранимые, так и не хранимые программные единицы. Если Вы хотите создать не хранимую подпрограмму, действующую в пределах блока PL/SQL, в SQL*Plus, опишите ее в декларативной секции анонимного блока, в котором Вы будете ее использовать.

Процедура PL/SQL

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

Процедура состоит из двух частей:

·  спецификации, которая начинается ключевым словом PROCEDURE и заканчивается именем процедуры или списком параметров

·  тела, которое начинается ключевым словом IS и заканчивается оператором END процедуры.

PROCEDURE имя_процедуры

   [(имя_параметра [IN|OUT|IN OUT] тип_данных [:=|DEFAULT выражение], ...) ]

IS

   [список_переменных;]

BEGIN

   тело_блока;

END;

имя_процедуры – имя процедуры, в соответствии со стандартными правилами присвоения имен

имя_параметра – имя входного параметра процедуры

тип_данных – тип данных параметра, указывается без размера

выражение – начальное значение параметра

список_переменных – определения локальных переменных, используемых в процедуре

тело_блока – набор действий, выполняемых процедурой

Процедура вызывается как целая команда PL/SQL, аргументы указываются в скобках в том же порядке, в котором были объявлены формальные параметры.

SQL> DECLARE

  2     PROCEDURE emp_sal (v_job IN VARCHAR2)

  3     IS

  4        v_error_code NUMBER;

  5        v_error_text VARCHAR2(100);

  6        v_sal emp.sal%TYPE;

  7     BEGIN

  8        SELECT sal INTO v_sal FROM emp WHERE LOWER(job)=LOWER(v_job);

  9        DBMS_OUTPUT.put_line('Salary of ' || v_job || ' is ' || TO_CHAR(v_sal));

 10     EXCEPTION

 11        WHEN OTHERS THEN

 12           v_error_code := SQLCODE;

 13           v_error_text := SQLERRM;

 14           DBMS_OUTPUT.put_line(TO_CHAR(v_error_code) || ' - ' || v_error_text);

 15     END emp_sal;

 16  BEGIN

 17     emp_sal('President');

 18     emp_sal('Engineer');

 19     emp_sal('Clerk');

 20  END;

 21  /

Salary of President is 5000

100 - ORA-01403: no data found

-1422 - ORA-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed.

Для передачи значений в процедуру и из нее используются параметры. При объявлении процедуры указываются формальные параметры, которые служат для определения значений в исполняемой части блока. Фактические параметры или аргументы подставляются при вызове процедуры.

Виды параметров:

IN входной	OUT выходной	IN OUT входной/выходной
Аргумент по умолчанию	Должен быть задан	Должен быть задан
Передает значение из вызывающей среды в подпрограмму	Возвращает значение из подпрограммы в вызывающую среду	Передает значение из вызывающей среды в подпрограмму и возвращает значение из подпрограммы в вызывающую среду
Формальный параметр выступает в качестве константы, изменить ее значение нельзя	Формальный параметр выступает в качестве неинициализированной переменной	Формальный параметр выступает в качестве инициализированной переменной
Фактический параметр может быть выражением, константой, литералом или инициализированной переменной	Фактический параметр должен быть переменной, он не может быть константой или выражением	Фактический параметр должен быть переменной, он не может быть константой или выражением

Функция PL/SQL

Функции PL/SQL используются для возврата значений в вызывающую среду. Тип возвращаемого значения объявляется в секции заголовка функции с помощью ключевого слова RETURN. Само возвращаемое значение определяется в исполняемой секции блока (т.е. необходимо описать локальную переменную соответствующего типа для возвращаемого значения) и передается в вызывающую среду командой RETURN, которых может быть несколько в блоке. По крайней мере, одна команда RETURN в блоке должна быть.

Функция состоит из двух частей:

·  спецификации, которая начинается ключевым словом FUNCTION и заканчивается ключевым словом RETURN

·  тела, которое начинается ключевым словом IS и заканчивается оператором END функции.

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

   [(имя_параметра [IN|OUT|INOUT] тип_данных_параметра [:=|DEFAULT выражение], ...) ]

RETURN тип_данных

IS

   [список_переменных;]

BEGIN

   тело_блока;

END;

имя_функции – имя функции, в соответствии со стандартными правилами присвоения имен

имя_параметра – имя входного параметра процедуры

тип_данных_параметра – тип данных параметра, указывается без размера

тип_данных – тип данных возвращаемого значения, указывается без размера

выражение – начальное значение параметра

список_переменных – определения локальных переменных, используемых в процедуре

тело_блока – набор действий, выполняемых процедурой

Функция вызывается как часть предложения PL/SQL.

SQL> DECLARE

  2     v_sal emp.sal%TYPE;

  3     FUNCTION emp_sal (v_job IN VARCHAR2)

  4     RETURN NUMBER

  5     IS

  6        v_error_code NUMBER;

  7        v_error_text VARCHAR2(100);

  8        v_sal_inner emp.sal%TYPE;

  9     BEGIN

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

 11        RETURN v_sal_inner;

 12     END emp_sal;

 13  BEGIN

 14     DBMS_OUTPUT.put_line('Salary of President is ' || TO_CHAR(emp_sal('President')));

 15  END;

 16  /

Salary of President is 5000

PL/SQL procedure successfully completed.

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

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

Литература

1.  Вильям Дж. Пэйдж Использование Oracle8/8i: пер. с англ. – М.: Издательский дом "Вильямс", 1999.