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

1237

В этой подпрограмме массивы не обрабатываются. Простенькая программа; ее легко создать, но вот производительность будет очень низкой из-за постоянно выполняемых разборов оператора. Наконец, пример эмуляции вставки массивов с помощью встроенного динамического SQL:

99 is

100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

130 131 132 133 134 135 136 137 138 139 140

procedure native dynamic array(p tname in varchar2,

p arraysize in number default 100, p rows in number default 500)

l stmt long;

l theCursor integer;

l status

l coll l col2 l col3 l cnt

begin

number; nmArray dtArray

nmArray(); dtArray();

vcArray := vcArray(); number := 0;

* Здесь мы будем формировать данные. После формирования

* ARRAYSIZE строк мы вставляем их все сразу. В конце цикла,

* если еще остались строки, мы их тоже вставляем.

l coll.extend(p arraysize); l соl2.extend(p arraysize); l соl3.extend(p arraysize); for i in 1 .. p rows

loop

l cnt := l cnt+l; l coll(l cnt) := i;

l col2(l cnt) l col3(l cnt)

if (l cnt then

:= sysdate+i; := to char(i);

p arraysize)

execute immediate begin forall i in 1 . . :n insert into

p tname

q4 (a, b, c) values (:a(i), :b(i), :c(i)); end;

USING l cnt, l cnt := 0;

l coll, l col2, l col3;

end if;

end loop;

if (l cnt > 0)

then

execute immediate begin forall i in 1 . . :n insert into q4 (a, b, c)

p tname values (:a(i).

:b(i), :c(i));

1238

Глава 16

142 end;

143 USING l cnt, l coll, l col2, l col3;

144 end if;

145 end;

147 end load data;

148 /

Package body created.

Как видите, тут все немного запутано. Наша подпрограмма создает код, который будет динамически выполняться. В этом динамически формируемом коде используется оператор FORALL для множественной вставки строк из массивов. Поскольку в операторе EXECUTE IMMEDIATE можно использовать только типы данных SQL, пришлось заранее создать в базе данных соответствующие типы. После этого необходимо динамически выполнять оператор:

begin

forall i in 1 .. :n

insert into t (a,b,c) values (:a(I) , :b(I) , :c(I)) ;

end;

подставляя в него количество вставляемых строк и три массива значений. Как будет показано ниже, обработка массивов ускоряет вставку во много раз. Необходимо решить, стоит ли это ускорение того, чтобы отказаться от простоты написания подпрограммы с помощью встроенного динамического SQL при отсутствии массивов. Конечно, трудно что-то противопоставить одной строке кода! Если речь идет о программе одноразового использования, для которой производительность несущественна, я бы выбрал самый простой способ. Если речь идет о многократно используемой подпрограмме, которую будут использовать достаточно долго, я бы выбрал пакет DBMS SQL, если скорость работы имеет значение и количество связываемых переменных заранее не известно, и - встроенный динамический SQL, если производительность приемлема, а количество связываемых переменных хорошо известно.

Наконец, не стоит забывать о результатах, представленных в главе 10, - там бхло показано, что желательно сокращать количество мягких разборов. Пакет DBMSSQL позволяет это сделать, а встроенный динамический SQL - нет. Необходимо хорошо представлять себе, что именно надо сделать, и выбирать соответствующий подход. Если пишется программа загрузки данных, которую запускают раз в день, и при этом запросы анализируются всего несколько сотен раз, встроенный динамический SQL прекрасно подходит. С другой стороны, если пишется подпрограмма, использующая один и тот же динамический SQL-оператор десятки раз и выполняемая одновременно десятками пользователей, имеет смысл использовать средства пакета DBMS SQL, чтобы можно было проанализировать запрос один раз, а затем только выполнять.

Я выполнил представленные ранее подпрограммы с помощью следующего тестового кода (помните, мы работаем в однопользовательской системе!):

create table t (a int, b date, с varchar2(15));

alter session set sql trace=true; truncate table t;

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

1239

exec load data.dbmssqlarray(t, 50, 10000); truncate table t;

exec loaddata.nativedynamicarray(t, 50, 10000); truncate table t;

exec load data.dbmssql noarray(t, 10000) truncate table t;

exec load data.native dynamic noarray(t, 10000) В отчете TKPROF можно обнаружить следующее: IN load data.dbms sql array(t, 50, 10000); чЮ;

Общие профили выполнения очень близки: 2,59 и 2,30 секунд процессорного времени. Различие - в деталях. Если вы обратили внимание, в представленном ранее коде я сделал каждый оператор вставки уникальным, добавив псевдонимы таблиц Ql, Q2, Q3 и Q4. Благодаря этому можно определить, сколько раз анализировался каждый оператор. В подпрограмме на основе пакета DBMS SQL и массивов использовался псевдоним Q1, а в подпрограмме со встроенным динамическим SQL - псевдоним Q4. Получена: следующие результаты:

insert into t q1 (a, b, c) values (:a, :b, :c)

call

Parse и:

count cpu elapsed disk query current

1 0.00 0.01 0 0 0

begin

forall i in 1 . . :n

insert into t q4 (a, b, c) values (:a(i), :b(i), :c(i));

end;

call count cpu

Parse 200 0.10

elapsed disk query current

0.07 0 0 0

rows

rows