Сюрпризы PL/SQL

Быстрый тест ваших знаний об исключениях, откатах и коллекциях.

Источник статьи в оригинале: Oracle Magazine, март-апрель 2016
http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26plsql-2925919.html

Стивен Ферштейн
Oracle ACE Director,
архитектор Oracle,
специалист по PL/SQL

PL/SQL — это мощный и во многих случаях очень прямолинейный язык программирования баз данных. Без преувеличения, PL/SQL — это в точности то, что вам требуется, и ничего больше.

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

Вопрос 1

Исключения и откаты

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

Выполним следующую операцию:

CREATE TABLE plch_plants
(
   plant_id     INTEGER PRIMARY KEY,
   plant_name   VARCHAR2 (4) 
)
/

Какой из следующих вариантов после выполнения выводит Count=1?

А.

BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Rose');

   BEGIN
      INSERT INTO plch_plants
           VALUES (2, 'Kudzu');
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
   END;

   SELECT COUNT (*) INTO l_count FROM plch_plants;

   DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

Б.

BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Rose');

   INSERT INTO plch_plants
        VALUES (2, 'Kudzu');
EXCEPTION
   WHEN OTHERS
   THEN
      SELECT COUNT (*) INTO l_count FROM plch_plants;

      DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

В.

BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Kudzu');

   INSERT INTO plch_plants
        VALUES (2, 'Rose');
EXCEPTION
   WHEN OTHERS
   THEN
      SELECT COUNT (*) INTO l_count FROM plch_plants;

      DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

Выводы

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

Подробнее об обработке и управлении транзакциями.

Вопрос 2

Чем управляет секция исключений

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

Какой из следующих вариантов после выполнения выводит “Пришла весна!”?

А.

BEGIN
   RAISE VALUE_ERROR;
   DBMS_OUTPUT.put_line ('Пришла весна!');
END;
/

Б.

DECLARE
   l_number   NUMBER (3, 0) := 2016;
BEGIN
   DBMS_OUTPUT.put_line ('Пришла весна!');
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Пришла весна!');
END;
/

В.

DECLARE
   l_number NUMBER (3, 0);
BEGIN
   l_number := 2016;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Пришла весна!');
END;
/

Выводы

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

Подробнее о распространении исключений.

Вопрос 3

Неожиданные вариации в коллекциях

Коллекции в PL/SQL — это структуры, подобные массивам, и существует три — сосчитайте их: три! — разных типа коллекций, каждый со своими характеристиками и способами использования.

Выполним такие операции:

CREATE TYPE plch_numbers_nt IS TABLE OF NUMBER
/

CREATE TYPE plch_numbers_va IS VARRAY (3) OF NUMBER
/

Какой из следующих вариантов после выполнения выводит Count=0?

А.

DECLARE
   TYPE plch_numbers_aa IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   l_numbers   plch_numbers_aa;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/

Б.

DECLARE
   l_numbers   plch_numbers_nt;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/

В.

DECLARE
   l_numbers   plch_numbers_va;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/

Выводы

Работая с вложенными таблицами и коллекциями типа varray, вы должны (в большинстве случаев) сначала инициализировать эти коллекции вызовом их функции-конструктора, имя которой совпадает с названием типа коллекции. Однако это не является необходимым при работе с ассоциативными массивами.

Подробнее о коллекциях и записях в PL/SQL.