1544 Глава 23

8 end loop;

9 end; 10 /

PL/SQL procedure successfully completed.

и в отчете TKPROF увидим:

OVERALL TOTALS FOR ALL REGURSIVE STATEMENTS

Misses in library cache during parse: 22

504 user SQL statements in session.

114 internal SQL statements in session.

618 SQL statements in session.

0 statements EXPLAINed in this session.

Разница огромна. Для анализа 500 уникальных операторов (эмулирующих поведение процедуры с правами вызывающего, которая обращается при каждом вызове к другой таблице) требуется 17,95 секунд процессорного времени. Для анализа же 500 одинаковых операторов (эмулирующих использование стандартной процедуры с правами создателя) понадобилось 0,74 секунды процессорного времени. В 24 раза меньше!

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

Однако это не повод отказываться от использования процедур с правами вызывающего. Используйте их, но помните о последствиях.

Более надежный код для обработки ошибок

При создании хранимой процедуры со следующим кодом:

begin

for x in (select pk from t) loop

update y set c - c+0.5 where d = x.pk;

end loop;

end;

вполне можно быть уверенным, что при отсутствии синтаксических и семантических ошибок (компилируется успешно) она будет работать. При использовании процедур с

Права вызывающего и создателя 545

правами создателя это верно. Я точно знаю, что объекты (таблицы или представления) T и Y существуют, что T доступен для чтения, a Y можно изменять.

При использовании процедуры с правами вызывающего, ни в чем нельзя быть уверенным. Существует ли объект T, и если - да, то имеется ли в нем столбец с именем PK? И имею ли я для него привилегию SELECT? А если имею, то не через роль ли она получена? Ведь тогда при вызове процедуры из подпрограммы с правами создателя, она не сработает, хотя при непосредственном вызове будет работать прекрасно. Существует ли объект Y? И так далее. Другими словами, все условия, которые раньше можно было считать гарантированно выполненными, вызывают сомнению в процедурах с правами вызывающего. Так что, хотя процедуры с правами вызывающего и открывают новые возможности программирования, в некотором отношении они его усложняют.

При использовании представленного выше кода надо готовиться к обработке множества вполне вероятных случаев:

объекта T нет;

объект T есть, но нет необходимых для доступа к нему привилегий;

объект T есть, но в нем нет столбца PK;

объект T существует и имеет столбец PK, но тип данных столбца отличается от использованного при компиляции;

все то же в отношении объекта Y.

Поскольку изменение объекта Y происходит только при получении определенных данных из T, мы можем многократно успешно выполнить эту процедуру, но однажды, когда в T будут помещены данные, процедура не сработает. Мы никогда не могли обратиться к объекту Y, но процедура не сработала потому, что мы впервые попытались . Ошибка во фрагменте кода произойдет только тогда, когда он выполнится.

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

create or replace procedure P

authid current user

no such table exception;

pragma exception init(no such table,-942); insufficient privs exception;

pragma exception init(insufficient privs,-1031); invalid column name exception;

pragma exception init(invalid column name,-904); inconsistent datatypes exception;

pragma exception init(inconsistent datatypes,-932); begin

for x in (select pk from t) loop

update y set c = c+0.5 where d = x.pk; end loop; exception

when NO SUCH TABLE then

dbms output.put line(Перехвачена ошибка: sqlerrm);

1546

Глава 23

when INSUFFIGIENT PRIVS then

dbms output.put line(Перехвaченa ошибка: sqlerrm);

When INVALID GOLUMN NAME then

dbms output.put line(Перехвaченa обка: sqlerrm);

when INGONSISTENT DATATYPES then

dbms output.put line(Перехвaченa ошибка: sqlerrm);

(дальше идет множество других обработчиков ошибок).. .

end;

Побочные эффекты использования SELECT *

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

tkyte@TKYTE816> create table t (msg varchar2(25), c1 int, c2 int); Table created.

tkyte@TKYTE816> insert into t

1 row created.

values (c1=l, c2=2, 1, 2);

tkyte@TKYTE816> create or replace procedure P

2 authid current user

3 as

4 begin

5 for x in (select * from t) loop

6 dbms output.put line(msg=

7 dbms output.put line(G1 =

8 dbms output.put line(G2 =

9 end loop;

10 end;

11 /

Procedure created.

tkyte@TKYTE816> exec p

msg= cl=l, c2=2 G1 = 1 G2 = 2

PL/SQL procedure successfully completed.

tkyte@TKYTE816> grant execute on P to u1;

Grant succeeded.

x.msg);

x.cl);

x.c2);