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

scott@TKYTE816> create or replace

2 function dump fixed width(p query in varchar2,

3 p dir in varchar2,

4 p filename in varchar2)

5 return number

6 is

7 l output utl file.file type;

8 l theCursor integer default dbms sql.open cursor;

9 l columnValue varchar2(40 00) ;

10 l status integer;

11 l colCnt number default 0;

12 l cnt number default 0;

13 l line long;

14 l descTbl dbms sql.desc tab;

15 l dateformat nls session parameters.value%type;

16 begin

17 select value into l dateformat

18 from nls session parameters

19 where parameter = NLS DATE FORMMAT;

21 /* Используем формат даты, включающий время. */

22 execute immediate

23 alter session set nls date format=dd-mon-yyyy hh24:mi:ss ;

24 l output := utl file.fopen(p dir, p filename, w, 32000); 25

2 6 /* Анализируем входной запрос, чтобы можно было получить его -> описание. */

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

29 /* Теперь получаем описание результатов запроса. */

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

32 /* Необходимо определить каждый столбец и указать серверу,

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

34 * будут выбираться в одну переменную типа varchar2(4000).

35 *

3 6 * Мы также определим максимальный размер каждого столбца. Это

37 * делается для того, чтобы при выдаче данных каждое поле

38 * начиналось и заканчивалось в одной и той же позиции в каждой -> записи.

39 */

40 for i in 1 .. l colCnt loop

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

43 if (l descTbl(i).col type = 2) /* тип number */

44 then

45 L descTbl(i).col max len := l descTbl(i) .col precision+2;

46 elsif (l descTbl(i).col type = 12) /* тип date */

47 then

48 /* длина заданного выше формата даты */

49 l descTbl(i).col max len := 20;

1232

Глава 16

50 end if;

51 end loop; 5 2

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

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

56 loop

57 /* Строим большую строку результата. Это более эффективно,

5 8 * чем вызывать процедуру UTL FILE.PUT в цикле.

59 */

60 l line := null;

61 for i in 1 .. l colCnt loop

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

63 l line := l line

6 4 rpad(nvl(l columnValue, ) ,

65 l descTbl(i).col max len);

66 end loop; 67

68 /* Теперь выдаем строку в файл и увеличиваем значение -> счетчика. */

69 utl file.put line(l output, l line);

7 0 l cnt := l cnt+l;

71 end loop; 72

73 /* Освобождаем ресурсы. */

74 dbms sql.close cursor(l theCursor);

75 utl file.fclose(l output);

77 /* Восстанавливаем формат даты ... и завершаем работу. */

78 execute immediate

79 alter session set nls date format= l dateformat ;

80 return l cnt;

81 exception

82 when others then

8 3 dbms sql.close cursor (l theCursor) ;

84 execute immediate

85 alter session set nls date format= t l dateformat ; 86

87 end dump fixed width;

88 /

Function created.

Итак, эта функция использует подпрограмму DBMS SQL.DESCRIBE COLUMNS

для поиска количества столбцов и их типов данных. Я изменил некоторые значения максимальных размеров, чтобы учесть используемый формат даты, а также десятичную запятую и знак в числах. Представленная выше подпрограмма не может выгрузить данные типа LONG, LONG RAW, CLOB и BLOB. Ее легко изменить для поддержки данных типа CLOB и даже LONG. Придется специальным образом выполнять связывание пе-ременн1х этих типов, а также использовать пакет DBMSCLOB для выборки даннгх

типа CLOB и подпрограмму DBMS SQL.COLUMN VALUE LONG - для данных типа

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

1233

LONG. Следует заметить, что добиться этого с помощью встроенного динамического SQL невозможно - его нельзя использовать, если список выбора в PL/SQL не известен.

Многократное выполнение одного и того же оператора

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

Подпрограмма Пазначение

DBMSSQLARRAY Использует обработку массивов в PL/SQL для множественной вставки строк

NATIVE DYNAMIC ARRAY Использует эмуляцию обработки массивов помощью таблиц объектного типа

DBMSSQL NOARRAY Выполняет построчную обработку при вставке строк

NAT1VE DYNAMIC NOARRAY Выполняет построчную обработку при вставке строк

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

Единственный определенный вывод, который можно сделать, - третий и четвертый методы намного медленнее первых двух. Следующие результаты были получены на платформе Solaris для одного пользователя, но результаты на платформе Windows были аналогичными. Выполните тесты на своей платформе, чтобы получить наиболее достоверные результаты.

scott@TKYTE816> create or replace type vcArray as table of varchar2(400) 2 /

Type created.

scott@TKYTE816> create or replace type dtArray as table of date 2 /