Иван Задворьев - Язык PL/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 с.