Язык PL/SQL

PL/SQL procedure successfully completed.

SQL> EXECUTE print_id_list('tab2',20);

30

PL/SQL procedure successfully completed.

Как видно, использование NDS позволяет писать очень компактный код.

Пакет DBMS_SQL

Использование встроенного пакета DBMS_SQL для выполнения динамического SQL предусматривает в общем случае последовательность из 8 этапов.

Таблица 12. Этапы выполнения динамического SQL с помощью DBMS_SQL.

Программа

Описание этапа

OPEN_CURSOR

открывается курсор DBMS_SQL

PARSE

производится синтаксический разбор предложения SQL в курсоре (DDL-команды сразу и выполняются на этом этапе)

BIND_VARIABLE

со всеми связываемыми переменными предложения SQL в курсоре связываются значения

DEFINE_COLUMN

для SQL-запросов указывается, значения каких столбцов выборки в какие переменные PL/SQL будут считываться

EXECUTE

для открытого курсора выполняется предложение SQL

FETCH_ROWS

для SQL-запросов считывается строка выборки (обычно считывание осуществляется в цикле по всей выборке)

COLUMN_VALUE

переменным PL/SQL присваиваются значения столбцов текущей считанной строки из курсора

CLOSE_CURSOR

закрывается курсор DBMS_SQL

Перепишем процедуру print_id_list с использованием вместо NDS встроенного пакета DBMS_SQL.

SQL> CREATE PROCEDURE print_id_list_dbms_sql(p_table_name IN VARCHAR2,

2 p_id IN INTEGER) IS

3 c_cursor INTEGER;

4 ignore INTEGER;

5 l_id INTEGER;

6 BEGIN

7 c_cursor := DBMS_SQL.open_cursor;

8 DBMS_SQL.parse(c_cursor,

9 'SELECT ID FROM '||p_table_name||' WHERE id>:p_id',

10 DBMS_SQL.NATIVE);

11 DBMS_SQL.define_column(c_cursor, 1, l_id);

12 DBMS_SQL.bind_variable(c_cursor, 'p_id', p_id);

13 ignore := DBMS_SQL.execute(c_cursor);

14 LOOP

15 IF DBMS_SQL.fetch_rows(c_cursor)>0 THEN

16 DBMS_SQL.column_value(c_cursor, 1, l_id);

17 DBMS_OUTPUT.PUT_LINE(l_id);

18 ELSE

19 EXIT;

20 END IF;

21 END LOOP;

22 DBMS_SQL.close_cursor(c_cursor);

23 END;

24 /

Procedure created.

SQL> EXECUTE print_id_list_dbms_sql('tab1',0);

1

2

PL/SQL procedure successfully completed.

SQL> EXECUTE print_id_list_dbms_sql('tab2',20);

30

PL/SQL procedure successfully completed.

Код новой версии процедуры print_id_list выглядит более громоздким. Этим и объясняется то, что пакет DBMS_SQL, как правило, используют только тогда, когда использовать NDS нельзя. В остальных случаях обходятся одной строчкой кода с командой EXECUTE IMMEDIATE.

Выполнение динамического SQL четвертой категории

Читатель, вероятно, уже заметил в синтаксисе команды EXECUTE IMMEDIATE ограничение, мешающее использовать встроенный динамический SQL во всех случаях – в EXECUTE IMMEDIATE после конструкций INTO и USING необходимо указывать жестко заданные перечни переменных и констант PL/SQL. Они фиксируются на этапе написания программы и изменяться не могут. Поэтому NDS не подходит для выполнения четвертой категории динамического SQL, когда до стадии выполнения неизвестно количество столбцов результирующей выборки или количество параметров.

Пакет DBMS_SQL позволяет выполнять динамический SQL четвертой категории, так как на стадии выполнения его процедуры и функции можно вызывать любое количество раз. То есть надо просто вызывать процедуру DBMS_SQL.DEFINE_COLUMN и функцию DBMS_SQL.COLUMN_VALUE по числу возвращаемых SQL-запросом столбцов, а процедуру DBMS_SQL.BIND_VARIABLE – по числу имен связываемых переменных.

Задание для самостоятельной разработки

Пусть список значений параметров, которые указывают пользователи на web-странице при подборе моделей телефонов, формируется frontend-приложением в виде символьной строки из пар «параметр=значение», разделенных символом «;». Для цены передается значение вида from/to с указанными пользователем границами диапазона. Название параметра соответствует названию столбца таблицы.

Примеры поисковых запросов:

1) LTE=1;dual_sim=1

2) price=10000/12000;color=black;LTE=1

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

Задача облегчается тем, что список столбцов динамического SQL-запроса фиксирован (считаем, что всегда необходимо выводить два столбца – наименование модели и цену).

Как писать процедуру поиска моделей телефонов, в целом понятно. Строку параметров необходимо разобрать на пары «параметр=значение» по разделителям, потом в цикле дополнить шаблон SQL-запроса ограничениями со связываемыми переменными через AND по числу выделенных из строки пар, открыть курсор DBMS_SQL, разобрать в нем сформированное предложение SQL, связать значения переменных и так далее.

Рекомендуется использовать объявленные с помощью атрибута %ROWTYPE записи PL/SQL и считывание из курсора всех строк с помощью конструкции BULK COLLECT.

После проверки работоспособности процедуры можно попробовать добавить в таблицу phone_models новый столбец, например, manufacturer. Если код PL/SQL написан правильно и в хорошем стиле, то для того, чтобы процедура смогла обрабатывать запросы с новым параметром вида

3) LTE=1;dual_sim=1,manufacturer=Samsung

изменять в коде ничего не придется.

Рекомендации по дальнейшему изучению

Нет сомнений, что программист обычно ставит перед собой цель написать хорошую программу. Для понимания, достигнута ли эта цель, необходимо определиться с тем, какая программа является хорошей.

Авторы книги являются сторонниками следующего утверждения: «Хорошая программа – это программа, которая работает, соответствует предъявляемым к ней требованиям и которую легко сопровождать и развивать».

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

в программе есть развитые механизмы протоколирования (логирования) своих действий;

в программе используется ясная и продуманная схема обработки исключений;

программа имеет код, который хорошо структурирован, понятен, легко изменяется и расширяется.

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

Для современных языков программирования C++, Java, C# есть готовые библиотеки ведения логов – log4cpp, log4J, log4net. Для PL/SQL тоже есть библиотека логирования LOG4PLSQL, которая имеет открытый исходный код и построена по образцу log4J – распространенного механизма логирования для программ на Java. Для обработки исключений в PL/SQL также есть библиотеки с открытым исходным кодом, например, Quest Error Manager. Настоятельно рекомендуется освоить и использовать эти механизмы при программировании на PL/SQL.

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

Что же касается приобретения умения написания кода PL/SQL, который хорошо структурирован, понятен, легко изменяется и расширяется, то рекомендуется прочитать книгу Стивена Фейерштейна «Oracle PL/SQL для профессионалов». В 2015 году на русском языке вышло уже шестое издание этой книги. С книгами по PL/SQL вообще довольно интересная ситуация – по языку SQL, по основам технологий Oracle, по администрированию баз данных десятками авторов написаны сотни книг, а вот по языку PL/SQL содержательные книги можно пересчитать по пальцам двух рук, и почти все они написаны Стивеном Фейрштейном.

После получения некоторой практики программирования на PL/SQL, для проверки знаний рекомендуется пройти онлайн-тестирование на сайте PL/SQL Challenge, основанном на концепции «активного изучения». Тем, кто уже сделал PL/SQL своим основным рабочим инструментом и по восемь часов в день программирует на этом языке, рекомендуется прочитать книгу Коннора МакДональда «Oracle PL/SQL для профессионалов. Практические решения». Это книга экспертного уровня по PL/SQL.

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

В соответствии с правилами Томаса Кайта, задачи, не решаемые средствами SQL и PL/SQL, следует решать путем разработки хранимых программ на Java и внешних библиотек на C++. Мы рекомендуем читателю попробовать самому создать в базе данных Oracle программы на Java и написать для них обертки (wrappers) на PL/SQL. Этой теме посвящены отдельные главы в книгах Фейерштейна, есть и статьи в интернете.

Вопросы для самопроверки

1) Перечислите достоинства и недостатки программ на PL/SQL.

2) В чем заключаются преимущества объявлений с привязкой?

3) Чем вложенные таблицы отличаются от массивов?

4) Почему OTHERS-обработчик указывается последним в списке обработчиков в разделе обработки исключений?

5) Почему для обработки пользовательских исключений неэффективно использование OTHERS–обработчика?

6) Для чего предназначена процедура RAISE_APPLICATION_ERROR ?

7) Для чего используется привязка ошибок сервера к пользовательским исключениям?

8) Почему неявные курсоры так названы?

9) Какие требования предъявляются к SQL-запросу неявного курсора?

10) В какой момент инициализируются атрибуты явного курсора?

11) В какой момент атрибут %FOUND курсора примет значение FALSE?

12) За счет чего повышается производительность при использовании команды FORALL?

13) Когда сервер Oracle устанавливает неявные точки сохранения?

14) Для чего используются автономные транзакции?

15) Чем режим передачи значений параметров OUT отличается от IN OUT?

16) Для какого режима передачи значений параметров всегда используется передача по ссылке?

17) Какие переменные называются локальными переменными пакета?

18) В каком месте кода следует объявлять магические числа?

19) Для решения каких задач используются триггеры?

20) Для таблицы имеется триггер на выполнение предложения UPDATE. В каких случаях он не сработает ни разу?

21) С какой целью производится временное отключение триггеров?

22) В чем заключается причина ошибок доступа к мутирующей таблице?

23) Для чего предназначены встроенные пакеты?

24) Чем статический SQL отличается от динамического?

25) Какие виды предложений SQL не могут быть выполнены с помощью NDS (Native Dynamic SQL) и почему?

Рекомендуемая литература

Андон Ф., Резниченко В. Язык запросов SQL. – СПб.: Питер, 2006 г. – 416 с.

Гупта С. Oracle PL/SQL: руководство для разработчиков. – М.: Лори, 2014 г. – 464 с.

Зудилова Т. В., Иванов С. Е., Хоружников С. Э. SQL и PL/SQL для разработчиков СУБД Oracle. – СПб:НИУ ИТМО, 2012 г. – 74 с.

Кайт Т., Кун Д. Oracle для профессионалов. Архитектура и методики программирования. 3-е изд. – М.: Вильямс, 2015 г. – 960 с.

Карвин Б. Программирование баз данных SQL. Типичные ошибки и их устранение. – М.: Рид Груп, 2012 г. – 336 с.

Коннор МакДональд. Oracle PL/SQL для профессионалов. Практические решения. – СПб: ДиаСофт, 2005 г., – 560 с.

Лаврентьев В.С. Освоение SQL и PL/SQL Oracle. Лабораторные работы. – М.: Национальный исследовательский ядерный университет «МИФИ», 2009 г. – 105 с.

Мак-Локлин М. Oracle Database 11g. Программирование на языке PL/SQL. – М.: Лори, 2014 г. – 879 с.

Пржиялковский В.В. Введение в Oracle SQL. – М.: Бином, 2012 г. – 320 с.

Смирнов С.Н., Задворьев И.С. Работаем с Oracle: Учебное пособие / 2-е изд., испр. и доп. – М.: Гелиос АРВ, 2002 г.– 496 с.

Смирнов С.Н., Киселев А.В. Практикум по работе с базами данных. – М.: Гелиос АРВ, 2012 г. – 160 с.

Урман С., Хардман Р., МакЛафлин М. Oracle Database 10g. Программирование на языке PL/SQL. – М.: Лори, 2010 г. – 816 с.

Фейерштейн С., Нанда А. Oracle PL/SQL для администраторов баз данных. – СПб.: Символ-Плюс, 2008 г. – 496 с.

Фейерштейн С., Прибыл Б. Oracle PL/SQL для профессионалов. 6-е изд. – СПб.: Питер, 2015 г. – 1024 с.