Объектно-ориентированное программирование в PL/SQL

Пакеты языка PL/SQL

Создание пакета

Пакет - это объект схемы, который объединяет логически зависимые типы PL/SQL, данные и подпрограммы. Пакет состоит из двух частей: спецификации пакета и тела пакета .

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

В теле пакета содержится определение курсоров и реализация подпрограмм. Все элементы, объявляемые в теле пакета, невидимы для приложения, что позволяет скрывать от пользователя детали реализации подпрограмм.

Определение спецификации пакета выполняется оператором CREATE PACKAGE , который может иметь следующее формальное описание:

- Спецификация (видимая часть)
CREATE PACKAGE name AS 
- Объявление общедоступных типов
- и переменных
- Спецификация подпрограмм
END [name];

Определение тела пакета выполняется оператором CREATE PACKAGE BODY , который может иметь, с некоторыми сокращениями, следующее формальное описание:

- Тело пакета (скрытая часть)
CREATE PACKAGE BODY name AS
- Объявление локальных типов
- и переменных
- Тела подпрограмм
END [name];

Например:

- Спецификация пакета
CREATE PACKAGE tbl_rows AS
   TYPE RecTBL1 IS RECORD
	(tbl1f1 INTEGER, tbl1f2 REAL);
   PROCEDURE insert_tbl1
	(f1 INTEGER, f2 REAL);
END tbl_rows;
- Тело пакета
CREATE PACKAGE BODY tbl_rows AS
   PROCEDURE insert_tbl1
	(f1 INTEGER, f2 REAL) IS
   BEGIN
      INSERT INTO tbl1 
      VALUES (f1,f2);
   END insert_tbl;
END tbl_rows;

На общедоступные элементы пакета - типы, переменные и методы - можно ссылаться из триггеров, хранимых подпрограмм или OCI-приложений, используя следующий синтаксис: package_name.item_name.

Вызов пакетной процедуры из встроенного SQL может быть реализован в анонимном блоке PL/SQL, для выполнения которого используется SQL-оператор EXECUTE.

Например:

EXEC SQL EXECUTE
BEGIN 
	tbl_rows.insert_tbl1(1, 200); 
END;

Подпрограммы

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

Язык PL/SQL позволяет создавать подпрограммы и как процедуры, и как функции PL/SQL.

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

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

Для того чтобы самостоятельную подпрограмму можно было вызвать извне она должна храниться в базе данных. Такие подпрограммы называются хранимыми процедурами или хранимыми функциями. Для их создания применяются SQL-операторы CREATE PROCEDURE и CREATE FUNCTION .

Определение процедуры может иметь следующее формальное описание:

PROCEDURE name 
     [(parameter[, parameter, ...])]
IS
     [local declarations]
BEGIN
     executable statements
     [EXCEPTION exception handlers]
END [name];

Параметры в списке параметров определяются как:

parameter_name [IN | OUT | IN OUT]
	datatype [{:= | DEFAULT} expression]

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

Язык PL/SQL позволяет, чтобы количество фактических параметров было меньше, чем количество формальных параметров. В этом случае будут использованы значения по умолчанию, которые обязательно должны присутствовать для отсутствующих значений параметров.

Для определения соответствия между формальными и фактическими параметрами предусмотрены два типа нотаций:

• позиционная ;
• именованная.

При позиционной нотации порядок формальных и фактических параметров должен совпадать.

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

Процедура имеет две части:

• спецификацию, начинающуюся ключевым словом PROCEDURE и завершающуюся именем процедуры или списком параметров;
• тело процедуры, начинающееся ключевым словом IS и завершающееся ключевым словом END.

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

Определение функции может иметь следующее формальное описание:

FUNCTION name 
    [(parameter[, parameter, ...])]
RETURN datatype
IS
   [local declarations]
BEGIN
   executable statements
[EXCEPTION
   exception handlers]
END [name];

Параметры в списке параметров определяются как:

parameter_name [IN | OUT | IN OUT]
	datatype [{:= | DEFAULT} expression]

Например:

FUNCTION fun1 (f1 REAL, f2 REAL)
RETURN BOOLEAN IS
   min_f1 REAL :=0;
   max_f1 REAL :=1;
BEGIN
   SELECT ff1min, ff2max 
   INTO min_f1, max_f1
   FROM tbl1
   WHERE ff2 = f2;
   RETURN (f1 >= min_f1)
   AND (f2 <= max_f1);
EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
      INSERT INTO tbl2
      VALUES (min_f1, max_f1); 
      RETURN FALSE;
END fun1;

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

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

Рекурсивные и взаимно рекурсивные вызовы подпрограмм

Рекурсивным вызовом называется вызов подпрограммы из тела этой же подпрограммы .

При каждом рекурсивном вызове:

• создается новый экземпляр всех элементов, объявленных в подпрограмме, включая параметры, переменные, курсоры и исключения;
• создается свой экземпляр SQL-оператора.

Рассмотрим в качестве примера рекурсивных вызовов создание последовательности Фибоначчи ( 1, 1, 2, 3, 5, 8, 13, 21, ...), в которой каждый следующий элемент является суммой двух предыдущих.

Следующая функция fibonati1 реализует формирование последовательности Фибоначчи с применением рекурсии:

FUNCTION fibonati1 (n POSITIVE)
RETURN INTEGER IS
BEGIN
   IF (n = 1) OR (n = 2) THEN
    RETURN 1;
   ELSE
    RETURN fibonati1 (n-1) +
           fibonati1 (n-2);
   END IF;
END fibonati1;

Вместо рекурсивного способа можно использовать и итерационный способ, который менее нагляден, но требует и меньше памяти, и быстрее выполняется.

Так, следующая функция fibonati2 реализует формирование последовательности Фибоначчи итерационным способом:

FUNCTION fibonati2 (n POSITIVE)
RETURN INTEGER IS
   pos1 INTEGER := 1;
   pos2 INTEGER := 0;
   sum INTEGER;
BEGIN
   IF (n = 1) OR (n = 2) THEN
      RETURN 1;
   ELSE
      sum := pos1 + pos2;
      FOR i IN 3..n LOOP
         pos2 := pos1;
         pos1 := sum;
         sum := pos1 + pos2;
      END LOOP;
      RETURN sum;
   END IF;
END fibonati2;

Язык PL/SQL позволяет реализовывать взаимно рекурсивные вызовы, при которых подпрограммы прямо или опосредованно вызывают друг друга.