1210 Глава 16

Связать i-ую переменную с оператором

4) Выполнить оператор

5) Выполнить цикл по i по выходным связываем1м переменн

Получить значение i-й выходной переменной с помощью variable value

6) Закрыть курсор

Наконец, при выполнении операторов ЯОД (в которгх нельзя использовать связываемые переменные), PL/SQL-блоков или операторов ЯМД, в которых нет связываемых переменных, представленный выше алгоритм упрощается (хотя, для этого типа операторов я всегда предпочитаю использовать не пакет DBMS SQL, а встроенный динамический SQL):

1) Открыть курсор

2) Проанализировать оператор

3) Выполнить оператор

4) Закрыть курсор

Рассмотрим пример использования пакета DBMSSQL для выполнения запроса, подсчитывающего количество строк в таблице базы данных, к которой пользователь имеет доступ:

scott@TKYTE816> create or replace

2 function get row cnts(p tname in varchar2 ) return number

3 as

4 l theCursor integer;

5 l columnValue number default NULL;

6 l status integer;

7 begin

9 -- Шаг 1, открыть курсор.

10 1 theCursor := dbms sql.open cursor;

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

11 begin

13 -- Шаг 2, проанализовать запрос.

14 dbms sql.parse(c => l theCursor,

15 statement => select count(*) from

->p tname,

16 language flag => dbms sql.native); 17

Обратите внимание, что параметр language flag получает значение одной из констант пакета DBMS SQL.NATIVE. Это вызывает анализ запроса по правилам сервера, выполняющего код. Можно также задать значения DBMS SQL.V6 или DBMS SQL.V7. Я всегда использую значение NATIVE.

Динамический SQL 1211

Шаги 3 и 4 из представленного ранее псевдокода нам не нужны, поскольку результате! известно! и никаких связываемых переменных в этом примере нет.

18 -- Шаг 5, убедиться, что запрос возвращает данн1е типа NUM

19 dbms sql.define column (с => ltheCursor,

20 position => 1,

21 column => lcolumnValue);

Процедура DEFINE COLUMN - перегруженная, так что компилятор сам определяет, когда надо вызывать версию для типа NUMBER, а когда - для DATE или VARCHAR.

23 -- Шаг 6, выполнить оператор.

24 l status := dbms sql.execute(l theCursor);

Если бы выполнялся оператор ЯМД, переменная L STATUS получила бы значение, равное количеству возвращенных строк. Для оператора SELECT возвращаемое значение несущественно.

2 6 -- Шаг 7, выбрать строки.

27 if (dbms sql.fetch rows(c => l theCursor) > 0)

28 then

2 9 -- Шаг 8, получить значения из очередной строки.

3 0 dbms sql.column value(c => l theCursor,

31 position => 1,

32 value => l columnValue);

33 end if ; 34

35 -- Шаг 9, закрыть курсор.

36 dbms sql.close cursor(c => l theCursor);

37 return l columnValue;

38 exception

39 when others then

40 dbms output.put line ( = = > sqlerrm) ;

41 dbms sql.close cursor(с => l theCursor);

42 RAISE;

43 end;

44 end;

45 /

Function created.

scott@TKYTE816> set serveroutput on scott@TKYTE816> begin

2 dbms output.put line(Emp has this many rows

3 get row cnts(emp));

4 end;

Emp has this many rows 14

PL/SQL procedure successfully completed.

scott@TKYTE816> begin 2 dbms output.put line(Not a table has this many rows

1212

Глава 16

3 get row cnts(NOT A TABLE)>;

4 end;

= = > ORA-00942: table or view does not exist begin

ERROR at line 1:

ORA-00942: table or view does not exist ORA-06512: at SCOTT.GET ROW CNTS , line 60 ORA-0 6512: at line 2

Рассмотренный пример начинается созданием курсора с помощью вызова DBMS SQL.OPEN CURSOR. Следует отметить, что это специфический курсор DBMS SQL - его нельзя передать для выборки данных в приложение на Visual Basic или использовать в качестве PL/SQL-курсора. Для выборки данных с помощью этого курсора необходимо использовать подпрограммы пакета DBMS SQL. Затем мы проанализировали запрос SELECT COUNT(*) FROM TABLE, где значение TABLE передается при вызове во время выполнения - оно просто конкатенируется со строкой запроса. Приходится вклеивать имя таблицы в запрос, поскольку связываемые переменные нельзя использовать в качестве идентификатора (имени таблицы или имени столбца, например). После анализа запроса мы вызвали DBMS SQL.DEFINE COLUMN, чтобы указать, что первый (и единственный в данном случае) столбец в списке SELECT должен интерпретироваться при выборке как тип NUMBER. To, что мы хотим выбирать данные именно этого типа, явно не указано - процедура DBMS SQL.DEFINE COLUMN перегружена и имеет несколько версий для данных типа VARCHAR, NUMBER, DATE, BLOB, CLOB и так далее. Тип возвращаемого значения определяется по типу переменной, в которую он помещается. Поскольку переменная L COLUMNVALUE в рассмотренном примере имеет тип NUMBER, вызывается версия процедуры DEFINE COLUMN для чисел. Затем мы вызываем DBMS SQL.EXECUTE. Если бы выполнялся оператор INSERT, UPDATE или DEL, функция EXECUTE вернула бы количество затронутгх строк. В случае запроса возвращаемое значение функции не определено, и его можно проигнорировать. После выполнения оператора вызывается функция DBMS SQL.FETCH ROWS. Функция FETCH ROWS возвращает количество фактически выбранных строк. В нашем случае, поскольку связывались скалярные переменные (не массивы), функция FETCH ROWS будет возвращать 1 до тех пор, пока не исчерпаются данные, - тогда она вернет 0. При извлечении каждой строки мы вызываем DBMS SQL.COLUMN VALUE для каждого столбца в списке выбора, чтобы получить его значение. Наконец, мы завершаем вгпол-нение функции, закрывая курсор с помощью вызова DBMS SQL.CLOSE CURSOR.

Теперь рассмотрим, как использовать пакет DBMS SQL для обработки динамически формируемых параметризованных PL/SQL-блоков или операторов ЯМД. Я часто использую такое динамическое формирование, например, при загрузке данных из файлов операционной системы с помощью пакета UTL FILE (он позволяет читать текстовые файлы в PL/SQL). Пример подобного рода утилиты б1л представлен в главе 9. Там мы использовали пакет DBMS SQL для динамического построения операторов INSERT, в которых количество столбцов становится известным только при выполнении и меня-