7 Процедурные расширения SQL. Триггеры и хранимые процедуры

7.1 Процедурные расширения

В приложениях баз данных наряду с чтением данных всегда есть и задачи их обработки - как выполнения каких-либо вычислений на основании выбранных данных, так и изменения данных по результатам принятых решений. Как мы показали выше, SQL является чрезвычайно мощным языком, почти любые вычисления на основании выбранных данных могут быть заложены в сам запрос. В языке SQL имеется и ряд возможностей (например, выражение CASE), которые позволяют вставить элементы алгоритмов в выполнение запроса. Однако в основе своей SQL является непроцедурным языком, в нем нет тех средств управления потоком вычислений, которые имеются в обычных языках программирования. Использование SQL совместно с языками программирования общего назначения является прекрасным решением, когда речь идет о создании приложений, но не слишком удобно в тех случаях, когда процедурная логика должна быть встроена в саму базы данных. В последнем случае получается, что проектирование базы данных требует привлечения дополнительного инструментария (системы программирования).

В промышленности баз данных наметились два пути решения этой проблемы. С одной стороны, большинство производителей корпоративных СУБД разработали для своих продуктов собственные процедурные языки (PL/SQL в Oracle, Transact-SQL в Microsoft SQL Server), совместимые с SQL. Если продукт СУБД имеет такой процедурный язык, то и разработка базы данных, и разработка приложений в значительной степени ориентируются на этот язык, что существенно снижает их переносимость. С другой стороны, процесс стандартизации SQL ввел процедурные операторы в сам язык SQL (что реализуется, например, в DB2, начиная с версии 7.1). Реализации стандартных расширений SQL в промышленных продуктах имеют, однако, собственные диалекты.

Следует указать на принципиальную разницу в реализации первого и второго подходов. Процедурный язык типа PL/SQL не является расширением SQL. Это - самостоятельный язык. Если в СУБД имеется "машина SQL" - некий модуль, "понимающий" язык SQL, то применение процедурного языка требует, например, "машины PL/SQL", которая обеспечивает выполнение программы на процедурном языке, а для выполнения встречающихся в ней операторов SQL обращается к "машине SQL". Реализация же процедурных расширений SQL обеспечивает выполнение процедурных операторов непосредственно самой "машиной SQL".

Как уже было сказано, в Oracle имеется собственный процедурный язык, называемый PL/SQL. PL/SQL не является только процедурной добавкой к SQL. Это самостоятельный язык программирования с объектно-ориентированными свойствами, пригодный для создания полноценных приложений. Мы, однако, ограничиваемся рассмотрением только его основных процедурных свойств.

В DB2 процедурное расширение появилось сравнительно поздно. Ранее рекомендовалось использовать для этих целей языки общего назначения (C, REXX, в последнее время - Java). В DB2 версии 7.1 появилась возможность использовать процедурный SQL в хранимых процедурах, а в версии 7.2 эта возможность была распространена на другие конструкции (триггеры, функции и т.п.). Процедурный SQL DB2 (он называется здесь "составной SQL") является именно расширением языка, обрабатываемым основной "машиной SQL".

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

Признаком конца оператора в процедурном SQL является символ ';' (точка с запятой). Последовательность операторов, составляющая процедурный блок ("составной оператор" в DB2, "блок PL/SQL" в Oracle), заключается в операторные скобки BEGIN ... END, мы будем называть такую последовательность блоком.

7.1.1 При выполнении процедурных действий в блоке, как правило, необходимы переменные для хранения некоторых промежуточных значений. В Oracle объявление переменных предшествует блоку и образует секцию объявления. Секция объявления начинается ключевым словом DECLARE, после которого следуют объявления переменных - до ключевого слова BEGIN, начинающего процедурную часть блока. В DB2 объявления переменных находятся внутри блока и производятся операторами DECLARE. Например:

DECLARE
  var1 REAL;
  var2 DATE;
  var3 VARCHAR2(5);
BEGIN
  . . .
END

BEGIN
DECLARE var1 REAL;
DECLARE var2 DATE;
DECLARE var3 VARCHAR2(5);
  . . .
END

7.1.2 В обеих СУБД имеется возможность как присваивания переменной значения вычисляемого выражения, так и присваивания переменной значения, выбираемого из базы данных. Например:

var1 := var2;
SELECT a INTO var1
   FROM table
   WHERE b = yyyy;

SET var1 = var2;
SET var1 = (SELECT a 
   FROM table
   WHERE b = yyyy);

7.1.3 Одной из проблем, возникающих при связывании языка SQL с процедурной логикой, является то, что результатом выборки является множество строк, а процедурная логика приспособлена для обработки последовательностей. Для преодоления этого противоречия в стандарт SQL введен механизм курсора, который реализован и в SQL DB2, и в PL/SQL Oracle. Курсор представляет собой результат выборки из базы данных, сделанной однократно и сохраняемой на сервере. После того, как выборка сделана, приложение может последовательно выбирать результат выборки строку за строкой.

Курсор, прежде всего, должен быть объявлен. В объявлении курсора определяется его имя и запрос, составляющий курсор. Например:

    DECLARE CURSOR mycur IS
      SELECT a,b FROM table WHERE a>b

Объявление курсора не является выполнимым оператором. Выборка, заданная в объявлении курсора, выполняется только при его открытии. Например:

    OPEN CURSOR mycur

После открытия приложение может последовательно выбирать строки, используя оператор FETCH. Например:

    FETCH FROM mycur INTO var1,var2

Каждое следующее выполнение FETCH выбирает значение столбцов из следующей строки выборки в переменные заданного списка. Оператор FETCH, как правило, применяется в цикле (см. также п.7.1.8).

После того, как выбраны все нужные строки, курсор должен быть закрыт. Например:

    CLOSE mycur

7.1.4 Синтаксис условного оператора в Oracle и DB2 различается только написанием одного ключевого слова ELSIF/ELSEIF. Например:

IF a>b
   THEN
   оператор1; оператор2; 
   ...
ELSIF a>c   
   THEN
   оператор3; оператор4; 
   ...
ELSE
   оператор5; оператор6; 
   ...
END IF;

IF a>b
   THEN
   оператор1; оператор2; 
   ...
ELSEIF a>c   
   THEN
   оператор3; оператор4; 
   ...
ELSE
   оператор5; оператор6; 
   ...
END IF;

Cоставляющие ELSIF/ELSEIF и ELSE являются необязательными.

Следует отметить, однако, что условие в операторе IF (и в операторе WHILE - см. ниже) в DB2 является условием SQL, как оно описано в п.3.1.5, а в Oracle - условием PL/SQL. Хотя предикаты, употребляемые в SQL и PL/SQL, в основном одинаковы, в PL/SQL не предусматриваются запросы в условии.

7.1.5 В обеих СУБД имеется операторы цикла. Оператор бесконечного цикла LOOP очень похож в DB2 и в Oracle, но в DB2 цикл может иметь метку и есть возможность в случае вложенных циклов выполнить выход на желаемый уровень вложения. Операторы выхода из цикла - разные: EXIT в Oracle и LEAVE в DB2. Например:

LOOP
  оператор1; оператор2; 
  ...
  EXIT WHEN a>b;
END LOOP;

label: LOOP
  оператор1; оператор2;
  ...
  IF a>b
    THEN LEAVE label;
END LOOP label;

7.1.6 Циклы с предусловием почти одинаковы. Например:

WHILE a>b
LOOP
  оператор1; оператор2; 
  ...
END LOOP;

WHILE a>b
DO
  оператор1; оператор2;
  ...
END LOOP;

7.1.7 Для цикла со счетчиком в Oracle имеется специальный оператор, в DB2 такой цикл реализуется через цикл WHILE. Например:

FOR index IN ind1 .. ind2
LOOP
  оператор1; оператор2; 
  ...
END LOOP;

SET index=ind1;
WHILE index<=ind2 DO
  оператор1; оператор2;
  ...
  SET index=index+1;
END LOOP;

7.1.8 В обеих СУБД выборка множества строк выполняется при помощи курсора, для этого существует оператор FOR. В Oracle курсор для выборки в цикле объявляется явно, в DB2 применяется специальный оператор FOR, включающий в себя скрытый курсор:

DECLARE CURSOR mycur IS
  SELECT a,b FROM table
    WHERE b = yyyy;
BEGIN
  FOR table_rec IN mycur 
    LOOP      
    total:=
      total+table_rec.a;
  END LOOP;

FOR cycle AS
  SELECT a,b FROM table
    WHERE b = yyyy;
  DO
    SET total:=total+a;
END FOR;

7.1.9 В обеих СУБД имеется также оператор безусловного перехода. Например:

    GOTO label;
    ...
    label: оператор;

7.1.10 Для процедур/функций в обеих СУБД предусмотрен оператор возврата. В DB2 оператор может возвращать код - признак завершения процедуры. Например:

RETURN;
RETURN выражение; (только функции)

RETURN признак; (только процедуры)
RETURN выражение; (только функции)

В Oracle существует большое число предустановленных имен для исключительных ситуаций. В DB2 имеются только три предустановленные ситуации - NOT FOUND (не найдено), SQLEXCEPTION (ошибка) и SQLWARNING (предупреждение). Всем исключительным ситуациям соответствуют предопределенные значения SQSTATE и SQLCODE, таким образом, точную диагностику ситуаций SQLEXCEPTION и SQLWARNING можно выполнить, анализируя значение SQSTATE или SQLCODE (для NOT FOUND значение SQSTATE - '02000' и SQLCODE - +100).

В PL/SQL Oracle пользовательская исключительная ситуация вводится объявлением переменной типа EXCEPTION, в DB2 - специальным оператором DECLARE CONDITION. Исключение в DB2 обязательно связывается с некоторым значением SQSTATE, это может быть одно из значений, зарезервированных для пользовательских исключений. Генерация исключений из программы выполняется в PL/SQL Oracle оператором RAISE, а в DB2 - оператором SIGNAL.

Обработчик исключений в PL/SQL Oracle, общий для всех исключений, составляет отдельную часть блока PL/SQL, начинающуюся со слова EXCEPTION и содержащую набор операторов WHEN, распознающих типы исключений и задающих действия, выполняемые по каждому типу (возможны любые действия, которые можно запрограммировать средствами PL/SQL). В DB2 обработчики исключительных ситуаций назначаются для каждой ситуации отдельно оператором DECLARE тип_обработчика HANDLER и задают действия по обработке ситуации. Типов обработчика предусмотрено только три - CONTINUE (продолжить), EXIT (завершить) и UNDO (отменить). Имеется также возможность установить текст сообщения об ошибке, выдающегося при индикации ситуации. Например:

DECLARE
   myerror EXCEPTION; 
BEGIN
  SELECT ...
  IF ... THEN
    RAISE myerror;
  ...
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     ...;
  WHEN myerror THEN
     ...;
  WHEN OTHERS THEN
     ...;
END;

BEGIN ATOMIC
DECLARE myerror CONDITION 
    FOR SQLSTATE '99999';
DECLARE UNDO HANDLER FOR NOT FOUND
  SET MESSAGE_TEXT=
    'Not Found: UNDO Performed';
DECLARE EXIT HANDLER 
  FOR SQLEXCEPTION
  SET MESSAGE_TEXT=
    'Exception: UNDO Performed';
END;
SELECT ...
IF ... THEN
  SIGNAL myerror 
    SET MESSAGE_TEXT='Error';
END IF;
...