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) В какой момент инициализируются атрибуты явного курсора?