1216

Глава 16

scott@TKYTE816> create or replace

10 11 12

function get row cnts(p tname as

l cnt number; begin

execute immediate select count(*) from into l cnt;

in varchar2) return number

p tname

return l cnt;

end;

Function created.

scott@TKYTE816> set serveroutput on

scott@TKYTE816> exec dbms output.put line(get row cnts(emp)); 14

PL/SQL procedure successfully completed.

Использовав оператор SELECT...INTO... в качестве аргумента для EXECUTE IMMEDIATE, мы существенно уменьшили объем кода. Девять процедурных шагов, обходимых при использовании пакета DBMS SQL, превратились в один шаг в учае встроенного динамического SQL. Не всегда удается свести все к одному шагу - иногда необходимо три, как будет показано ниже, - но общая идея понятна. Встроенный динамический SQL в данном случае обеспечивает более высокую производительность при написании кода (последствия его использования с точки зрения производительности мы рассмотрим чуть позже). Также бросается в глаза отсутствие раздела EXCEPTION -обработка исключительных ситуаций не нужна, поскольку все происходит неявно. курсора, который необходимо закрывать, ничего не нужно освобождать. Сервер Oracle все делает сам.

Теперь реализуем с помощью встроенного динамического SQL функцию UPDATE ROW:

scott@TKYTE816> create or replace

10 11 12 13 14 15 16 17

function update row(p owner

p newDname p newLoc

p deptno

p rowid

in in in in

varchar2, varchar2, varchar2, varchar2,

out varchar2)

return number is

begin

execute immediate

update p owner .dept set dname = :bvl, loc = :bv2 where deptno = to number(:pk) returning rowid into :out using p newDname, p newLoc, p deptno returning into p rowid;

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

18 return sql%rowcount;

19 end;

20 /

Function created.

scott@TKYTE816> set serveroutput on

scott@TKYTE816> declare

2 l rowid varchar(50);

3 l rows number;

4 begin

5 l rows := update row(SCOTT, CONSULTING,

6 SHINGTON, 10, l rowid);

8 dbms output.put line(Updated l ros rows);

9 dbms output.put line(its rowid was M l rowid);

10 end;

11 / Updated 1 rows

its rowid was AAAGnuAAFAAAAESAAA

PL/SQL procedure successfully completed.

Снова код существенно сократился - один шаг вместо шести; такой код проще читать и сопровождать. В этих двух случаях встроенный динамический SQL, несомненно, превосходит средства пакета DBMS SQL.

Помимо оператора EXECUTE IMMEDIATE встроенный динамический SQL поддерживает динамическую обработку курсорных переменных, REF CURSOR. Курсорные переменные достаточно давно поддерживаются сервером Oracle (с версии 7.2). Первоначально они позволяли открыть (OPEN) запрос (результирующее множество) в хранимой процедуре и передать ссылку на него клиенту. С их помощью хранимые процедуры возвращают результирующие множества клиентам при использовании VB, протоколов JDBC и ODBC или библиотеки OCI. Позднее, в версии 7.3, поддержка курсорных переменных была расширена, так что в PL/SQL появилась возможность использовать их не только в операторе OPEN, но и в операторе FETCH (в качестве клиента могла использоваться другая подпрограмма на PL/SQL). Это позволило подпрограмме на PL/SQL принимать результирующее множество в качестве входных данных и обрабатывать его. Таким образом, впервые стало возможно централизовать общую обработку результатов запросов: одна подпрограмма может выбирать данные из нескольких различных запросов (результирующих множеств). До появления версии Oracle 8i, однако, курсорные переменные по сути были исключительно статические. На этапе компиляции (при создании хранимой процедуры) надо бтло точно знать, какой SQL-запрос будет выполняться. Это было весьма существенное ограничение, поскольку не позволяло динамически изменять условия запроса, запрашивать другую таблицу и т.п. Начиная с Oracle 8i встроенный динамический SQL позволяет динамически открывать для запроса курсорную переменную. При этом используется следующий синтаксис:

OPEN курсорная переменная FOR select ...

USING связываемая переменная1, связываемая переменная2,

1218

Глава 16

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

scott@TKYTE816> create or replace package my pkg 2 as

type refcursor Type is ref cursor;

procedure get emps(p ename in varchar2 default NULL, p deptno in varchar2 default NULL, p cursor in out refcursor type);

create or replace package body my pkg

procedure

get emps(p ename in varchar2 default NULL, p deptno in varchar2 default NULL, p cursor in out refcursor type)

l query long; l bind varchar2(30); begin

l query := select deptno,

8 end;

Package created. scott@TKYTE816>

2 as

10 11 12 13 14 15 16 17 18 19

22 23

25 end;

26 end;

27 /

Package body created. scott@TKYTE816> variable С refcursor

scott@TKYTE816> set autoprint on scott@TKYTE816> exec my pkg.get emps(p ename

ename, job from emp;

if (p ename is not NULL) then

l query := l query where ename l bind := % M upper(p ename) elsif (p deptno is not NULL) then

l query := l query where deptno l bind := p deptno;

else

raise application error(-20001,Missing end if;

open p cursor for l query using l bind;

like %;

to number(:x);

search criteria);

=> a, p cursor => :C)

PL/SQL procedure successfully completed.