1234

Глава 16

Type created.

scott@TKYTE816> create or replace type nmArray as table of number 2 /

Type created.

Эти типы необходимы для эмуляции обработки массивов с помощью встроенного динамического SQL. Массивы именно этих типов мы и будем использовать (во встроенном динамическом SQL вообще нельзя использовать PL/SQL-таблицы). Теперь представим спецификацию пакета, который будет использоваться тестов:

scott@TKYTE816> create or replace package load data 2 as

4 procedure dbmssql array(p tname in

5 p arraysize in

6 p rows in

10 11 12 13 14

15 procedure native dynamic array(p tname in

16 p arraysize in

17 p rows in

18 end load data;

19 /

procedure

dbmssql array(p tname

p arraysize p rows

dbmssql noarray(p tname p rows

varchar2, number default number default

100, 500);

in varchar2, in number default

500);

procedure

native dynamic noarray(p tname

p rows

in varchar2, in number default

500);

varchar2, number default number default

500);

Package created.

Каждая из представленных выше процедур будет динамически вставлять строки в таблицу, заданную параметром PTNAME. Количество вставляемых строк определяет параметр PROWS; при использовании обработки массивов их размер задается параметром PARRAYSIZE. Теперь переходим к реализации:

scott@TKYTE816> create or replace package body load data

100 ,

500)

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

1235

19 20 21

22 23

27 28

29 30 31 32

34 35 36 37

41 42 43 44 45 46 47

l stmt

insert q1 (a,

into b, c)

p tname values (:a,

:]Э,

l theCursor := dbms sql.open cursor;

dbms sql.parse(l theCursor, l stmt, dbms sql.native); /*

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

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

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

for i in 1 .. p rows

loop

l cnt := l cnt+l;

l coll(l cnt) := i; l col2(l cnt) := sysdate+i; l col3(l cnt) := to char(i);

if (l cnt = p arraysize) then

dbms sql.bind array(l theCursor, dbms sql.bind array(l theCursor, dbms sql.bind array(l theCursor,

:a, :b ,

l coll, l col2, l col3.

1, 1, 1,

l cnt); l cnt); l cnt);

l status := dbms sql.execute(l theCursor); l cnt := 0; end if;

end loop;

if (l cnt > 0) then

dbms sql.bind array(l theCursor, :a, l coll,

dbms sql.bind array(l theCursor, :b, l col2,

dbms sql.bind array(l theCursor, :c, l col3, l status := dbms sql.execute(l theCursor);

end if;

dbms sql.close cursor(l theCursor);

1, 1, 1,

l cnt); l cnt); l cnt);

50 end;

Итак, в этой подпрограмме используются средства пакета DBMS SQL для вставки массива из N строк с помощью одной операции. Мы используем перегруженную подпрограмму BIND VARIABLE, позволяющую пересылать PL/SQL-таблицу соответствующего типа с загружаемыми данными. Мы также указываем границы массива, чтобы сервер Oracle знал , где начинается и заканчивается блок данных в переданной PL/SQL-таблице. В данном случае всегда следует начинать с индекса 1 и заканчивать индексом L CNT. Обратите внимание, что для имени таблицы в операторе INSERT задан псевдоним (корреляционное имя) Q1. Я сделал это для того, чтобы при анализе производительности с помощью утилиты TKPROF можно было определить, какие операторы INSERT использовались той или иной подпрограммой. Вообще, код получается довольно простым. Теперь представим реализацию на базе пакета DBMS SQL без обработки массивов:

1236

Глава 16

52 53

56 57 58 59 60 61

63 64

66 67 68

70 71

73 74 75 76 77 78

procedure dbmssql noarray(p tname

p rows

l stmt long;

l theCursor integer;

in varchar2,

in number default 500)

l status begin

l stmt :=

number; insert into

q2 (a, b, c)

p tname values (:a,

:]э.

:c);

l theCursor := dbms sql.open cursor;

dbms sql.parse(l theCursor, l stmt, dbms sql.native) ;

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

* Каждая строка вставляется отдельн оператором

* в цикле. */

for i in 1 .. p rows

loop

dbms sql.bind variable(l theCursor, :a, i); dbms sql.bind variable(l theCursor, :b, sysdate+i); dbms sql.bind variable(l theCursor, :с, to char(i)); l status := dbms sql.execute(l theCursor); end loop;

dbms sql.close cursor(l theCursor);

end;

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

79 80 81 82 83 84 85 86 87 88

89 90

92 93

procedure native dynamic noarray(p tname in varchar2,

p rows in number default 500)

begin

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

* Что может быть проще для написания и выполнения!

for i in 1 .. p rows loop

execute immediate

insert into p tname q3 (a, b, c) values (:ai, :b, :c) using i, sysdate+i, to char(i); end loop;

end;