Алгоритм выполнения операторов с помощью DBMS_SQL состоит из следующих шагов:
1. Преобразование SQL – оператора в строку символов;
2. Грамматический разбор строки символов с помощью DBMS_SQL.PARSE;
3. Привязка всех входных переменных с помощью DBMS_SQL.BIND_VARIABLE;
4. Если выполняемый оператор это оператор DML или DDL (UPDATE, DELETE, INSERT) – выполнение его с помощью DBMS_SQL.EXECUTE с последующим считыванием выходных переменных привязки с помощью DBMS_SQL.VARIABLE_VALUE (если нужно),
5. Если оператор является оператором извлечения (SELECT) – описание выходных переменных с помощью DBMS_SQL.DEFINE_COLUMN;
6. Выполнение запроса на выборку с помощью DBMS_SQL.EXECUTE и выборка результатов при помощи DBMS_SQL.FETCH_ROWS и DBMS_SQL.COLUMN_VALUE.
Рассмотрим в качестве примера процедуру, которая сначала удаляет, а затем повторно создает временную таблицу; описание таблицы передается процедуре как аргумент.
create or replace procedure RecreateTempTable (/* ОПИСАНИЕ ТАБЛИЦЫ ПЕРЕДАЕТСЯ В ПРОЦЕДУРУ С ПОМОЩЬЮ р_Description И ДОЛЖНО БЫТЬ СОДЕРЖИМЫМ ОПЕРАТОРА CREATE TABLE, РАСПОЛОЖЕННЫМ ПОСЛЕ ИМЕНИ ТАБЛИЦЫ. НАПРИМЕР, НИЖЕ ПРИВЕДЕН ПРАВИЛЬНЫЙ ВЫЗОВ ПРОЦЕДУРЫ: RecreateTempTable( ‘(num_col NUMBER, char_col VARCHAR2(2000))’) */
При анализе рассмотренного примера необходимо обратить внимание на следующее:
• Строка символов, грамматический разбор которой выполняется, может быть константой (здесь v_DropString), а также может создаваться программой динамически, с помощью символьных функций, например с помощью функции конкатенации (здесь v_CreateString).
• Обработка ошибок осуществляется так же, как и в статическом SQL, – ошибки устанавливаются и обрабатываются с помощью исключительных ситуаций. Отличие в том, что теперь во время выполнения блока, могут возвращаться ошибки компиляции (например ORA-942). В статическом РL/SQL такие ошибки обнаруживаются на этапе компиляции, еще до запуска блока на выполнение.
• Пользователю предоставляются большие возможности по управлению курсорами, в частности, определение моментов их открытия и закрытия. Все открытые курсоры должны быть закрыты даже при установлении исключительной ситуации. В данном случае закрытие курсора обеспечивается вызовом DBMS_SQL.CLOSE_CURSOR в обработчике исключительной ситуации.
На рисунке 37 приведена блок-схема, иллюстрирующая порядок, в котором обычно вызывается DBMS_SQL.
Рисунок 37 Блок-схема алгоритма обработки
данных в DBMS_SQL
С помощью DBMS_SQL можно обрабатывать операторы трех видов: операторы DML, DDL и ALTER SESSION, запросы и анонимные блоки PL/SQL, Каждый из видов обрабатывается различными процедурами. Ниже приведено краткое описание этих процедур, а более детальное объяснение каждой из них будет дано далее, в разделе “Использование DBMS_SQL”.
• OPEN_CURSOR. Как и в статическом SQL, в динамическом РL/SQL каждый SQL-оператор выполняется в границах курсора. Кроме того, в динамическом РL/SQL. можно управлять процессом обработки курсора. OPEN_CURSOR возвращает идентификационный номер курсора, используемый для обозначения контекстной области, в которой будет выполняться оператор. Этот номер будет указываться во всех последующих вызовах данного курсора.
• PARSE. Грамматический разбор оператора заключается в посылке его на сервер, где проверяются синтаксис и семантика данного оператора. Если он является запросом, то на этом этапе определяется план его выполнения. Если это оператор DDL, то он также и выполняется.
• BIND_VARIABLE. Привязка переменной к заполнителю аналогична процессу привязки, используемому в PL/SQL для статических SQL-операторов. Заполнитель (placeholder) – это специальный идентификатор в последовательности символов оператора. В процессе привязки устанавливается соответствие между заполнителем и фактической переменной, а модулю DBMS_SQL сообщается о типе и размере переменной. Привязка выполняется для входных переменных в SQL-операторах и для входных и выходных переменных в операторах PL/SQL.
• DEFINE_COLUMN. Описание элемента списка выбора аналогично привязке входной переменной. В данном случае переменными являются результаты запроса. Процедура DEFINE_COLUMN определяет тип и размер переменных PL/SQL, в которые будут записываться данные при считывании информации процедурой FETCH_ROWS. DEFINE_COLUMN используется только для элементов списка выбора (результатов запросов SELECT), а BIND_VARIABLE – для входных или выходных переменных (заполнителей, указанных в операторах).
• EXECUTE. Исполняет операторы, не являющиеся запросами (SELECT), и возвращает число обработанных строк. Для запросов же EXECUTE определяет активный набор (набор строк, удовлетворяющих условию WHERE запроса). После этого данные считываются процедурой FETCH_ROWS. Для любых операторов переменные привязки анализируются во время работы функции EXECUTE.
• FETCH_ROWS. С каждым вызовом процедуры FETCH_ROWS с сервера считывается все больший объем данных. Затем полученная информация преобразуется в типы данных, указанных процедурой DEFINE_COLUMN. EXECUTE_AND_FETCH позволяет объединить операции обработки и считывания данных в одном вызове.
• COLUMN_VALUE. После вызова FETCH_ROWS процедура COLUMN_VALUE возвращает фактическую информацию. В ней используются переменные того типа, который указан процедурой DEFINE_COLUMN. COLUMN_VALUE следует применять только для запросов.
• CLOSE_CURSOR. После окончания обработки данных курсор закрывается. При этом ресурсы, использовавшиеся курсором, освобождаются.
