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

1213

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

scott@TKYTE816> create or replace

2 3 4

7 8 9

10 11 12

function update row(p owner in varchar2, p newDname in varchar2, p newLoc in varchar2, p deptno in varchar2, p rowid out varchar2)

return number is

l theCursor

integer;

l columnValue number default NULL;

l status l update

integer; long;

13 begin

14 15 16 17

18 19 20 21

l update :=

update p owner И .dept

set dname = :bvl, loc - :bv2 where deptno = to number(:pk) returning rowid into :out;

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

l theCursor := dbms sql.open cursor;

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

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

begin

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

dbms sql.parse(c =:

statement =:

language flag =>

Шаг 3, связать все входные

dbms sql.bind variable(c

name

- l theCursor,

- l update, dbms sql.native);

и выходные переменные. => l theCursor, => :bv1.

value => pnewDname);

dbms sql.bind variable(c

name

value

dbms sql.bind variable(c

name

=> ltheCursor, => :bv2,

=> p newLoc); => l theCursor, => :pk.

1214

Глава 16

37 38 39 40 41 42

value => p deptno); dbms sql.bind variable(c => l theCursor,

name => :out, value => p rowid, out value size => 4000);

Учтите, что, хотя возвращаем1е переменные передаются как параметры в режиме OUT, необходимо связать их перед выполнением. Необходимо также указать наибольший размер ожидаемого результата (OUT VALUE SIZE), чтобы сервер Oracle в1делил под него соответствующее пространство.

43 44 45 46 47 48 49 50 51 52

53 54 55 56 57

60 61

Шаг 4: выполнить оператор. Поскольку это оператор ЯМД, в переменной L STATUS окажется количество измененных строк. Именно это значение мы и возвращаем.

l status

dbms sql.execute(l theCursor);

-- Шаг 5: выбрать OUT-переменные из результатов выполнения. dbms sql.variable value(c => l theCursor,

name => :out,

value => p rowid) ;

-- Шаг 6: закрыть курсор.

dbms sql.close cursor(с =>

return l columnValue; exception

when dup val on index then dbms output.put line(==> dbms sql.close cursor(с => RAISE;

end ;

l theCursor) ;

sqlerrm); l theCursor);

63 end;

64 /

Function created.

scott@TKYTE816> scott@TKYTE816>

lrowid l rows begin

l rows

set serveroutput on declare

varchar(50);

number;

:= update row(SCOTT,

CONSULTING, WASHINGTON, 10, l rowid);

9 10

dbms output.put line(Updated

l rows

dbms output.put line(its end;

Updated 1 rows

its rowid was AAAGnuAAFAAAAESAAA

rowid was

rows) ;

l rowid);

PL/SQL procedure successfully completed.

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

Итак, я продемонстрировал особенности использования пакета DBMS SQL для выполнения блока кода с передачей входных данных и выборкой результатов. Повторю еще раз: представленный выше блок кода лучше реализовать с помощью встроенного динамического SQL (чуть ниже мы так и сделаем). Подпрограммы пакета DBMS SQL в нем применялись в основном для демонстрации использования соответствующего функционального интерфейса. В других главах книги, в частности в главе 9, посвященной загрузке данных, продемонстрировано, почему пакет DBMS SQL по-прежнему широко используется. В главе 9 рассмотрен код программ загрузки и выгрузки данных на PL/SQL. В них средства DBMS SQL используются в полном объеме, позволяя обрабатывать неизвестное количество столбцов любого типа как во входных данных (для операторов INSERT), так и в результатах (для операторов SELECT).

Мы рассмотрели примерно 75 процентов функциональных возможностей пакета DBMS SQL. Чуть позже, многократно выполняя один и тот же динамически сформированный оператор, мы рассмотрим взаимодействие с помощью массивов и сравним использование пакета DBMS SQL и встроенного динамического SQL. Пока, однако, мы завершим обзор пакета DBMS SQL. Полный список входящих в него подпрограмм и описание их входнтх/выходнтх параметров можно найти в руководстве Oracle8i Supplied PL/SQL Packages Reference, где отдельно рассмотрена каждая подпрограмма.

Встроенный динамический SQL

Встроенный динамический SQL впервые появился в Oracle 8i. Он позволяет декларативно выполнять динамический SQL в среде PL/SQL. Большинство действий можно втполнить с помощью одного оператора, EXECUTE IMMEDIATE, а остальные - с помощью оператора OPEN FOR. Оператор EXECUTE IMMEDIATE имеет следующий синтаксис:

EXECUTE IMMEDIATE оператор

[INTO {переменная1., переменная2, ... переменнаяN запись}] [USING [IN ООТ IN OUT] связываемая переменная1, ... связываемая переменнаяN]

[{RETURNING RETURN} INTO результат1 [, результатN]...];

где:

оператор - любой оператор SQL или PL/SQL-блок;

переменная1, переменная2,... переменнаяN или запись - переменные PL/SQL, в

которые необходимо выбрать данные (столбцы одной строки результатов оператора SELECT);

связ1ваемая переменная1,... связ1ваемая переменнаяN - набор переменных PL/

SQL, используемых для передачи входных данных/результатов;

результат1, ... результатN - набор PL/SQL-переменных, используемых для размещения результатов, возвращаемых конструкцией RETURN оператора ЯМД.

В следующем примере код для функций GET ROW CNTS и UPDATE ROW, которые мы ранее реализовали с помощью средств пакета DBMS SQL, переписан с использованием оператора EXECUTE IMMEDIATE. Начнем с функции GET ROW CNTS: