Объектно-ориентированное программирование в 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 позволяет, чтобы количество фактических параметров было меньше, чем количество формальных параметров. В этом случае будут использованы значения по умолчанию, которые обязательно должны присутствовать для отсутствующих значений параметров.

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

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

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

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

Тело процедуры, как и любой блок 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 позволяет создавать перегружаемые подпрограммы, имеющие одинаковое имя, но различный список формальных параметров. Параметры перегружаемых функций должны различаться хотя бы по одному из следующих признаков: по типу, по количеству, по порядку следования параметров.

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

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

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

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

Рассмотрим в качестве примера рекурсивных вызовов создание последовательности Фибоначчи ( 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 позволяет реализовывать взаимно рекурсивные вызовы, при которых подпрограммы прямо или опосредованно вызывают друг друга.

Внешние процедуры

Использование внешних процедур значительно расширяет функциональность сервера, предоставляя интерфейс для вызова программ, разработанных на других языках программирования, таких как C++ или Pascal, и созданных как DLL-библиотеки.

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

Вызовы внешних процедур можно выполнять из:

Для применения внешней процедуры следует сначала определить используемую DLL-библиотеку, выполнив оператор CREATE LIBRARY library_name {IS | AS} 'file_path';. Затем необходимо зарегистрировать внешнюю процедуру, указав в процедуре PL/SQL, используемой для опосредованного вызова внешней процедуры, место расположения процедуры, способ ее вызова и список передаваемых параметров.

Если DLL-библиотека c_lib.dll содержит функцию f1_ext, которая определена в С как int c_ext_f1(int x_val, int y_val);, то подпрограмма PL/SQL reg_ext_f1, выполняющая регистрацию этой внешней процедуры, может быть записана следующим образом:

CREATE FUNCTION reg_ext_f1(
   x BINARY_INTEGER, y BINARY_INTEGER)
   RETURN BINARY_INTEGER AS EXTERNAL
   LIBRARY c_lib
   - Имя внешней функции
   NAME "c_ext_f1"
   LANGUAGE C;

Для вызова внешней функции DLL-библиотеки из анонимного блока PL/SQL достаточно выполнить вызов функции PL/SQL, зарегистрированной как внешняя функция.

Например:

DECLARE
   var1 BINARY_INTEGER;
   x BINARY_INTEGER;
   y BINARY_INTEGER;
BEGIN
x:=1; y:=2;
- Вызов внешней функции f1_ext:
   var1 := reg_ext_f1(x, y);
END;