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

30 l sep := and ;

31 dbms session.set context(bv context,

32 p cnames(i),

33 p values(i));

34 end loop; 35

36 open l cursor for l query;

37 loop

38 fetch l cursor into l ename, l empno, l job;

39 exit when l cursor%notfound;

40 dbms output.put line(l ename M , l empno M M l job);

41 end loop;

42 close l cursor;

43 end;

45 end dyn demo;

46 /

Package body created.

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

2 dyn demo.do query( dyn demo.array(ename, job),

3 dyn demo.array(like, =),

4 dyn demo.arrayC%A%, CLERK));

5 end;

ADAMS,7 87 6,CLERK JAMES,7900,CLERK

PL/SQL procedure successfully completed.

Так что, с точки зрения использования связываемых переменных, все гораздо сложнее, чем в случае пакета DBMS SQL, - необходим хитр1й прием. После того, как вы поймете суть этого приема, вполне можно использовать встроенный динамический SQL вместо средств пакета DBMS SQL, если только запрос в1дает фиксированное количество результатов и используется контекст приложения. Чтобы эффективно решать с помощью встроенного динамического SQL подобного рода задачи, необходимо создать и использовать контекст приложения. В конечном итоге оказывается, что представленный выше пример с курсорными переменными при реализации с помощью встроенного динамического SQL работает быстрее. В случае простых запросов, когда временем обработки самого запроса можно пренебречь, встроенный динамический SQL обеспечивает скорость выборки данных почти вдвое выше, чем пакет DBMS SQL.

Количество столбцов выходных данных на этапе компиляции не известно

Здесь все понятно: если клиент, выбирающий и обрабатывающий данные, создается на PL/SQL, необходимо использовать пакет DBMS SQL. Если клиент, выбирающий и обрабатывающий данные, - приложение на процедурном языке программирования, ис-

1226

Глава 16

пользующее интерфейсы ODBC, JDBC, OCI и т.п., необходимо использовать встроенный динамический SQL.

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

FETCH курсор INTO переменная!, переменная2, переменная3,

но сделать этого нельзя, потому что до момента выполнения не известно, сколько переменных надо в него поместить. Это один из случаев, когда придется использовать средства пакета DBMS SQL, поскольку он позволяет применять следующие конструкции:

41 42 43 44 45 46 47 48 49 50 51 52

54 55

while loop

(dbms sql.fetch rows(l theCursor) > 0)

Стро длинную строку результатов, - это эффективнее, * вызывать DBMS OUTPUT.PUT LINE в цикле. */

l cnt := l cnt+l; l line := l cnt;

/* Шаг 8 - получить и обработать данные столбцов. */ for i in 1 .. l colCnt loop

dbms sql.column value(l theCursor, i, l columnValue);

l line := l line , l columnValue; end loop;

/* Теперь выдаем строку. */ dbms output.put line(l line); end loop;

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

scott@TKYTE816> create or replace

10 11 12 13 14 15 16 17 18 19 20

procedure dump query(p query in varchar2) is

l columnValue

l status

l colCnt

l cnt

l line

varchar2(4000);

integer;

number default 0; number default 0; long;

/* Мы будем использовать эту таблиц, чтобы узнать,

* сколько столбцов придется выбирать, чтобы определить их,

* а затем выбрать их значения.

l descTbl dbms sql.desc tab;

/* Шаг 1: открыть курсор. */

l theCursor integer default dbms sql.open cursor; begin

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

1227

21 /* Шаг 2: проанализировать запрос, чтобы можно было получить -> описание его результатов. */

22 dbms sql.parse(l theCursor, p query, dbms sql.native);

24 /* Шаг З: получаем описание результатов запроса. */

25 dbms sql.describe columns(l theCursor, l colCnt, l descTbl); 26

27 /* Шаг 4 в этом примере не используется, потому что связывать -> ничего не нужно.

28 * Шаг 5: необходимо определить каждый столбец, сообщить серверу,

2 9 * что и куда мы будем выбирать. В данном случае все данные

3 0 * будут выбираться в одну локальную переменную типа -> varchar2(4ООО).

31 */

32 for i in 1 .. l colCnt

33 loop

34 dbms sql.define column(l theCursor, i, l columnValue, 4000);

35 end loop; 36

37 /* Шаг 6: выполнить оператор. */

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

40 /* Шаг 7: выбрать все строки. */

41 while (dbms sql.fetch rows(l theCursor) > 0)

42 loop

43 /* Строим длинную строку результатов - это эффективнее, чем

44 * вызывать DBMS OUTPOT.PUT LINE в цикле.

45 */

46 l cnt l cnt+l;

47 l line := l cnt;

48 /* Шаг 8: получаем и обрабатываем данные столбцов. */

49 for i in 1 .. l colCnt loop

50 dbms sql.column value(l theCursor, i, l columnValue);

51 l line := l line M , M l columnValue;

52 end loop;

54 /* Теперь выдаем строку. */

55 dbms output.put line(l line);

56 end loop; 57

58 /* Step 9: закрываем курсор, чтобы освободить ресурсы. */

59 dbms sql.close cursor(l theCursor);

60 exception

61 when others then

62 dbms sql.close cursor(l theCursor);

63 raise;

64 end dump query;

65 /

Procedure created.

Из этого следует, что пакет DBMS SQL позволяет с помощью процедуры DBMS SQL.DESCRIBE COLUMNS получить количество, имена и типы данных стол-