Аруп Нанда, Oracle ACE, представляет свой список наиболее важных новых возможностей для администаторов баз данных.
Часть 1: Особенности SQL и PL/SQL
Источник: сайт корпорации Oracle, раздел "Технологические статьи" (Technical Articles), 22 июня 2005
Прозрачное шифрование данных и поддержка XQuery - две главные новые связанные с SQL особенности в Oracle Database 10g, выпуск 2, но список новых возможностей ими не исчерпывается. Содержание части 1:
- *Прозрачное шифрование данных;
- *XML-запрос;
- *Расширенный COMMIT (фиксация);
- *Предложение Error Logging (регистрации ошибок);
- *Пакет WRAP;
- *Условная компиляция;
- *Неограниченный вывод по DBMS_OUTPUT
Шифрование (encryption) - это тема, которая вызывает смешанную реакцию у многих пользователях, а именно: интерес вместе с чувством осторожности, возникающей из осознаваемой сложности управления ключами, что может привести к неэффективной установке, если она сделана не правильно. Также имеют место издержки по производительности, связанные с шифрованием и дешифровкой значений, что для большинства прикладных разработчиков (букв. architects - архитекторов) делает процесс несколько менее приемлемым. В результате многие системы разрабатываются вообще без шифрования, а вместо него снабжаются сильной внешней защитой, типа усиленных паролей и надлежащих схем авторизации.
Однако, представим себе ситуацию, когда похищен или собственно сервер, или хотя бы только диски, коль скоро они могут быть установлены на сервере с той же самой ОС, а затем с них сняты данные. Или рассмотрим случай, когда администратор базы данных - жулик, который проходит сквозь внешнюю защиту ежедневного ведения бизнеса и затем выгружает конфиденциальную клиентскую информацию. В обоих случаях пострадавшие предприниматели, если подобное имело бы место в штате Калифорния (а, возможно, вскоре так будет и в других штатах U.S.), юридически обязаны уведомить всех клиентов, затронутых нарушением защиты.
В редких (но, конечно, реальных) случаях схема аутентификации (the authentication scheme) может быть взломана (moot). Именно поэтому прозрачное шифрование данных (TDE - transparent data encryption [ transparent - прозрачный (не заметный для пользователя или программы) - словарь Lingvo ]) так важно для организаций, для которых установка защиты является высшим приоритетом. Это средство обеспечивает шифрование, передавая сложность управления ключами в ведение механизма базы данных. В то же самое время оно позволяет АБД управлять таблицами базы данных, фактически лишая его возможности видеть данные.
Используя TDE в Oracle Database 10 g Выпуск 2, вы можете зашифровать один или более столбцов таблицы. Все, что вы должны сделать, это определить столбец, как шифруемый, при этом не пишется ни одной строки кода. Помните, шифрование требует ключа и алгоритма, чтобы зашифровать входные значения. TDE генерирует отдельный ключ для каждой таблицы. Поскольку такой подход делает управление ключами более простым, но и более подверженным хищению, то применяется еще один ключ - мастер-ключ, который может быть установлен на уровне базы данных. Ключ таблицы шифруется мастер-ключом, который обязан быть применен к ключу таблицы. Следовательно, чтобы расшифровывать столбец обязательно применение мастер-ключа, так же как и ключа таблицы. (Более детальные сведения о шифровании вообще и использовании предоставляемых Oracle пакетов можно получить, в частности, в моей статье в Magazine Oracle " Encrypt Your Data Assets " ). [ От редакции OM/RE : перевод этой статьи опубликован ранее в нашем журнале в февральском выпуске http://www.oracle.com/global/ru/oramag/feb2005/gen_encrypt.html. ]
Мастер-ключ хранится вне базы данных в месте, известном как "wallet" ("бумажник"), по умолчанию в $ORACLE_BASE/admin/$ORACLE_SID/wallet. Концептуально, это показано на рисунке ниже.
После того, как сконфигурировано TDE или, более конкретно, сконфигурированы бумажник и мастер-ключ, вы можете использовать этот механизм, чтобы защитить значения данных. Для того, чтобы зашифровать некий столбец таблицы, можно воспользоваться следующим SQL-предложением:
create table accounts ( acc_no number not null, first_name varchar2(30) not null, last_name varchar2(30) not null, SSN varchar2(9) ENCRYPT USING 'AES128', acc_type varchar2(1) not null, folio_id number ENCRYPT USING 'AES128', sub_acc_type varchar2(30), acc_open_dt date not null, acc_mod_dt date, acc_mgr_id number )
Здесь было применено TDE на столбцах SSN и FOLIO_ID, данные в которых теперь хранятся зашифрованным способом непосредственно в таблице. Однако, когда пользователь выбирает данные из таблицы, он видит их обычным образом, поскольку расшифровка выполняется в процессе восстановления данных. Если же похищаются диски, информация, содержавшаяся в табличных сегментах, остается зашифрованной. Вор нуждается в ключе таблицы, чтобы увидеть зашифрованные значения, но чтобы получить его, ему нужен мастер-ключ, которая хранится внешним способом и, следовательно, недоступен.
Обратите внимание на фразы в определениях столбцов SSN и FOLIO_ID, которые задают ENCRYPT с использованием 128-битового механизма Advanced Encryption Standard.
База данных приходит с заранее сконфигурированным бумажником. Чтобы установить пароль бумажника, используйте команду:
alter system set encryption key authenticated BY "topSecret";
Эта команда создает бумажник, если он еще не создан, и затем установит пароль "topSecret" (имеется зависимость от регистра). Затем вы можете начать использовать шифрование в определениях столбцов в процессах, как создания таблицы, так и ее модификации.
Шифрование Внешних Таблиц
В вышеупомянутом примере, чтобы зашифровать столбцы, я использовал обычную таблицу. Вы можете также использовать TDE для внешних таблиц. Например, если вы хотите сгенерировать дамп-файл, содержащий данные из таблицы ACCOUNTS, для ее переноса в другое место, можно использовать простую фразу ENCRYPT.
create table account_ext
organization external
(
type oracle_datapump
default directory dump_dir
location ('accounts_1_ext.dmp',
'accounts_2_ext.dmp',
'accounts_3_ext.dmp',
'accounts_4_ext.dmp')
)
parallel 4
as
select
ACC_NO,
FIRST_NAME,
LAST_NAME,
SSN ENCRYPT IDENTIFIED BY "topSecret",
ACC_TYPE,
FOLIO_ID ENCRYPT IDENTIFIED BY "topSecret",
SUB_ACC_TYPE,
ACC_OPEN_DT,
ACC_MOD_DT
from accounts;
В файлах accounts_*_ext.dmp значения столбцов SSN и FOLIO_ID не будут идти открытым текстом, а будут зашифрованы. Если вы захотите использовать эти файлы, как внешние таблицы, то нужно Вы предоставить пароль topSecret, чтобы прочитать эти файлы.
Как видно из предыдущего, TDE - это очень полезное дополнение к управлению доступом (но не замена).
Для многих приложений XML долго был стандартом де-факто для опеделения типа данных (datatype), включающего большой символьный контент. Недавно он также стал использоваться для распределения памяти в других приложениях, неограничиваясь только большим контентом.
Oracle обеспечивает интеграцию XML с базой данных, начиная с Oracle9 i Database. В том выпуске можно было сделать запрос XML-контента, используя много различных способов. В Oracle Database 10 g Выпуск 2 новые функции XQUERY И XMLTABLE выполняют запрос XML-контента даже еще проще. (Примечание: более подробное обсуждение спецификации Xquery не входит в план этой статьи; интересующимся могу рекомендовать прочитать статью в журнале Oracle Magazine "XQuery: A New Way to Search" http://www.oracle.com/technology/oramag/oracle/05-jan/o15industry.html )
XQuery
Сначала давайте посмотрим на более простую из этих двух функций: XQuery. Приведем пример:
SQL> xquery 2 for $var1 in (1,2,3,4,5,6,7,8,9) 3 let $var2 := $var1 + 1 4 where $var2 < 6 5 order by $var2 descending 6 return $var2 7 / Result Sequence ------------------ 5 4 3 2
Новая SQL-команда xquery указывает на команду XQuery. Внимательно посмотрите на эту команду: представлен новый синтаксис FOR ... IN ... встроенного представления, введенного в Oracle9 i Database.
Общая структура XQuery описывается акронимом FLOWR (произносится как "flower"), который поддерживает FOR, LET, ORDER BY, WHERE и RETURN. В вышеупомянутом примере мы видим, что строка 2 определяет источник данных, который является рядом чисел от 1 до 9. Это может быть любой источник: связка скалярных значений или элементов XML-данных, указанных фразой FOR. Строка также указывает переменную, которая содержит эти значения (var1). В строке 3, специфицированной фразой LET, другая переменная var2 получает значение var1, к которому добавляется 1.
Среди всех этих возвращаемых значений мы интересуемся только теми, что меньше 6, что определено фразой WHERE. Затем мы сортируем по убыванию результирующий набор по значению var2, что показано фразой ORDER BY в строке 6. Наконец, эти значения предоставляются пользователю фразой RETURN.
Если сравнить этот синтаксис с правильным SQL-синтаксисом, то фразы RETURN, FOR, WHERE и ORDER BY походят на SELECT, FROM, WHERE и ORDER. Фраза LET не имеет никакой аналогии в SQL, но ее содедержимое представлено в других фразах.
Давайте рассмотрим практический пример этого нового мощного инструмента в действии. Сначала создадим таблицу, содержащую данные о коммуникациях владельца банковского счета.
create table acc_comm_log ( acc_no number, comm_details xmltype ;
)
Теперь вставим в нее несколько записей.
insert into acc_comm_log
values
(
1,
xmltype(
'<CommRecord>
<CommType>EMAIL</CommType>
<CommDate>3/11/2005</CommDate>
<CommText>Dear Mr Smith</CommText>
</CommRecord>')
)
/
insert into acc_comm_log
values
(
2,
xmltype(
'<CommRecord>
<CommType>LETTER</CommType>
<CommDate>3/12/2005</CommDate>
<CommText>Dear Mr Jackson</CommText>
</CommRecord>')
);
insert into acc_comm_log
values
(
3,
xmltype(
'<CommRecord>
<CommType>PHONE</CommType>
<CommDate>3/10/2005</CommDate>
<CommText>Dear Ms Potter</CommText>
</CommRecord>')
;
)
А сейчас посмотрим, какие записи находятся в этой таблице:
SQL> l
1 select acc_no,
2 XMLQuery(
3 'for $i in /CommRecord
4 where $i/CommType != "EMAIL"
5 order by $i/CommType
6 return $i/CommDate'
7 passing by value COMM_DETAILS
8 returning content) XDetails
9 from acc_comm_log
10 /
ACC_NO XDETAILS
---------- ------------------------------
1
2 <CommDate>3/12/2005</CommDate>
3 <CommDate>3/10/2005</CommDate>
XMLTable
Другая функция, XMLTable, имеет подобную же цель, но возвращает столбцы, как в случае обычныго SQL-запроса. Вот как она действует.
1 select t.column_value 2 from acc_comm_log a, 3 xmltable ( 4 'for $root in $date 5 where $root/CommRecord/CommType!="EMAIL" 6 return $root/CommRecord/CommDate/text()' 7 passing a.comm_details as "date" 8* ) t SQL> / COLUMN_VALUE --------------------- 3/12/2005 3/10/2005
Этот пример иллюстрирует, как можно использовать обычные SQL-предложения с XML-таблицей, возвращаемой посредством XML-запроса. Запросы для определенных команд руководствовуются сильно структурованной FLOWR-моделью.
XQuery против XMLTable
Теперь, когда были представлены оба способа, которые позволяют использовать XML в обычном SQL- запросе, давайте посмотрим, где следует применять каждый из них и при каких обстоятельствах.
Первый метод - Xquery - позволяет получать данные в XMLType, с которым моно манипулировать как XML в любой программе или приложении, которое поддерживает эту функциональность. В нашем примере вы видели, что результирующий вывод данных банковского счета находится в XML-формате, и поэтому можно использовать любой инструмент, не обязательно реляционный, чтобы отображать и манипулировать этими данными. Второй метод - XMLTable - объединяет функциональные возможности обычного SQL и XML. Результирующий вывод данных банковского счета - не XML, но реляционный.
Обратите внимание, что в обоих случаях источник - XML, но XQuery представляет данные в формате XML, используя XMLType, тогда как XMLTable представляет данные как реляционная таблица, с которой можно манипулировать как с обычной таблицей. Лучше всего эти функциональные возможности будут работать в существующих программах, в которых предполагается обеспечение мощности XML при соединении таблиц.
XML весьма полезен там, где заранее не известна точная структура данных. В приведеном выше примере записи о коммуникациях различны по режимам связи. Если связь осуществляется по электронной почте, то атрибутами могут быть email-адрес получателя, адреса возврата, любые точные копии (cc:, bcc:, и так далее), текст сообщения и так далее. Если связь - обращение по телефону, то атрибуты - вызванный телефонный номер, тип номера (домашний, рабочий, сотовый и так далее), ответивий человек, оставленна голосовая почта ( voicemail) и так далее. Если нужно было бы спроектировать таблицу, которая держит все подобные возможные типы атрибутов, это составило бы очень много столбцов и, возможно, было бы утомительно для чтения. Однако, если имеется только один XMLType-столбец, то туда можно погрузить (cram - запихнуть) все и все же сохранить уникальные атрибуты для типа коммуникации.
Запрос же может использовать простой SQL-интерфейс, делая разработку приложения легкой как ветерок (a breeze).
Для получения подробной информации о реализации Oracle XQuery посетите на OTN страницу Oracle XQuery http://www.oracle.com/technology/tech/xml/xquery/index.html .
Когда сесия [ скорее всего, автор имел в виду транзакцию - прим. А.Бачина ] завершается (commit - фиксация), буфер журнала (redo log) сбрасывается на диск в оперативный файл журнала. Этот процесс гарантирует, что транзакции могут быть в случае необходимости переиграны, когда выполненяется восстановление базы данных.
Иногда, однако, для обеспечения лучшей производительности может быть желателен компромис в отношении гарантируемуей способности восстановления. В Oracle Database 10 g Выпуск 2 теперь имеется управление, в каком режиме поток redo-данных записывается в оперативные журнальные файлы. Этим поведением можно управлять при помощи как собственно предложения commit, так и просто назначить изменение заданного по умолчанию поведения базы данных.
Давайте посмотрим, как работает предложение commit. При выходе из транзакции, когда выдается COMMIT, можно задать дополнительную фразу:
COMMIT WRITE <option>
где <option> - это тип воздействия на поток redo-данных. По умолчанию применяется опция WAIT. Например, вы ввели:
COMMIT WRITE WAIT;
Эта команда имеет тот же самый эффект, что и собствено COMMIT. Фиксация не вернет управление пользователю, пока поток redo-данных не будет записан в оперативные журнальные файлы. Если же вы не хотите ждать, то можно ввести :
COMMIT WRITE NOWAIT;
В этом случае управление немедленно возвращается в сессию, даже прежде, чем поток redo-данных не будет записан.
Когда выдается команда на фиксацию, процесс Log Writer пишет поток redo-данных в оперативные журнальные файлы. Если вы выполняете серию транзакций, скажем, в среде пакетной обработки, то вы можете не захотеть, чтобы фиксация совершалась так часто. Конечно, лучший способ действий - изменить приложение, чтобы сократить число фиксаций; но это, вероятнее всего, легче сказать, чем сделать. В подобном же случае можно просто выдать следующее предложение фиксации:
COMMIT WRITE BATCH;
Эта команда сделает запись фиксации в журнальный файл потока redo-данных в пакете, а не по каждой commit. Эту методику следует использовать для сокращения числа сбросов на диск буфера журнала в среде с частыми фиксациями. Если же надо записать буфер журнала немедленно, то выдайте команду:
COMMIT WRITE IMMEDIATE;
Если нужно установить определенное поведение при фиксации по умолчанию для базы данных, то следует выполнить следующее предложение:
ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
Эта команда сделать такое поведение режимом по умолчанию для всей базы данных. Это же можно сделать на уровне сессии:
ALTER SESSION SET COMMIT_WORK = NOWAIT;
Как в случае с любым параметром, этот параметр, если задан, производит установку на системном уровне. Если имеет место установка на уровне сессии, то она имеет приоритет [по сравнению с установкой на системном уровне], и, наконец, [явно] заданная фраза в предложении COMMIT имеет [самый высокий] приоритет.
Эта опция не доступна для распределенных транзакций.
Поймайте ошибку и идти дальше: предложение Error Logging (регистрация ошибок)
Предположим, вы пробуете вставить записи из таблицы ACCOUNTS_NY в таблицу ACCOUNTS. Таблица ACCOUNTS имеет первичный ключ на столбце ACC_NO. Возможно, что некоторые строки из ACCOUNTS_NY могут нарушить условие по первичному ключу. Пробуем использовать обычное предложение вставки:
SQL> insert into accounts 2 select * from accounts_ny; insert into accounts * ERROR at line 1: ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated
Ни одна из записей таблицы ACCOUNTS_NY не была загружена. Теперь пробуем сделать то же самое со включенной регистрацией ошибок (error logging). Сначала нужно создать таблицу для сохранения записей, отклоненных DML-предложением. Назовем эту таблицу ERR_ACCOUNTS.
exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')
Затем выполним прежнее предложение с фразой регистрации ошибок.
SQL> insert into accounts
2 select * from accounts_ny
3 log errors into err_accounts
4 reject limit 200
5 /
6 rows created.
Обратите внимание, что таблица ACCOUNTS_NY содержит 10 строк, но только шесть строк были вставлены; остальные четыре строки были отклонены из-за какой-то ошибки. Чтобы узнать, что это было, выдадим запрос к таблице ERR_ACCOUNTS.
SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
2 from err_accounts;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ACC_NO
--------------- -------------------------------------------------- ------
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9997
olated
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS)vi 9998
olated
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9999
olated
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 10000
olated
Обратите внимание на столбцы ORA_ERR_NUMBER$, который показывает номер ошибки, с которым сталкивается Oracle в течение выполнения DML-предложения, и ORA_ERR_MESG$, который показывает сообщение об ошибке. В нашем случае видно, что четыре записи были отклонены, поскольку они нарушили ограничение первичного ключа PK_ACCOUNTS.
Эта таблица также включает в себя все столбцы таблицы ACCOUNTS, включая столбец ACC_NO. Посмотрев на отклоненные записи, обратим внимание, что их учетные номера (account numbers) уже существуют в таблице; поэтому записи были отклонены по ошибке со ORA-00001. Без фразы регистрации ошибок предложение терпел неудачу вцелом, без отклоненных записей. А использовав эту фразу, только были отклонены только недопустимые записи; все другие были успешно загружены.
Защитите исходный код: Пакет WRAP
Модули PL/SQL-программ часто содержат весьма конъюктурную и конфиденциальную информацию о деятельности компании, о ее торговых секретах, что заставляет защищать эту группу объектов, подобно таблицам. Для того, чтобы предотвратить неправомочное чтение исходного текста, программы часто сворачиваются, используя wrap-утилиту командной строки.
Wrap можно вызвать только после того, как создан PL/SQL-скрипт; эта утилита из понятного вводного текста создает свернутый (wrapped) файл. Однако в некоторых случаях может понадобиться генерировать свертку динамически из PL/SQL-кода. В этом случае wrap-утилита не может быть вызвана, потому что еще не существует никакого исходного файла.
К счастью, Oracle Database 10 g Выпуск 2 обеспечивает поставляемый пакет, который можно использовать, чтобы создать код в свернутом формате. Эти пакетные дополнения не являются заменой wrap-утилите. Она применяется в тех случаях, когда требуется быстро свернуть большое количество исходных файлов, используюя опцию командной строки.
Представим себе, например, что нам надо создать простую процедуру p1 в свернутом формате.
create or replace procedure p1 as begin null; end;
В PL/SQL-модуле можно это же создать динамически, но в свернутом формате:
begin dbms_ddl.create_wrapped ('create or replace procedure p1 as begin null; end;') end; /
Теперь надо проверить, что процедура свернута. Выберем ее исходный текст из словаря данных:
SQL> select text from user_source where name = 'P1';
TEXT ----------------------------------------------------------------- procedure p1 wrapped a000000 369 abcd abcd ...и так далее ...
Первая строка свернутой процедуры p1 подтверждает, что процедура была создана со свертыванием. Если даже посмотреть DDL этой процедуры посредством функции DBMS_METADATA.GET_DDL(), то и в этом случае исходный код виден как свернутый.
Иногда может иметь место несколько другое требование. Например, надо сгенерировать PL/SQL-код, но не создавать процедуру. В том случае следует сохранить его в файле или в таблице, чтобы выполнить позже. Но поскольку вышеупомянутый подход создает именно процедуру, это не будет работать в данном случае. Поэтому надо вызвать другую функцию из этого пакета:
SQL> select dbms_ddl.wrap
2 ('create or replace procedure p1 as begin null; end;')
3 from dual
4 /
DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREP1ASBEGINNULL;END;')
----------------------------------------------------------------------
create or replace procedure p1 wrapped
a000000
369
abcd
abcd
... и так далее ...
Выход WRAP-функции - свернутый PL/SQL-код, переданный как параметр. Этот параметр может быть сохранен в плоском файле или в таблице и выполнен позже. Это удобно в ситуациях, когда вы генерируете код, который будет развернут в другом месте, а защита кода никогда не может поставить его под угрозу.
Показанное выше решение прекрасно работает до тех пор, пока можнр передать полный текст сохраненного кода как тип данных varchar2s, который ограничен объемом в 32К. Если размер PL/SQL-кода превышает 32К, следует использовать немного другой прием: применить коллекционную переменную (collection variable) в качестве ввода.
В этом случае можно использовать поставляемый тип данных: varchar2s в пакете DBMS_SQL. Это - коллекционный (collection datatype) тип данных (TABLE OF VARCHAR2), с каждым элементом таблицы принимается до 32К текста. Это можно расширить по мере необходимости на сколько угодно элементов. Предположим, например, что нужно свернуть очень длинную процедуру, называемую myproc, которая определена следующим образом:
create or replace procedure myproc as l_key VARCHAR2(200); begin l_key := 'ARUPNANDA'; end;
Конечно, myproc - не самая длинная процедура из всех возможных, но для целей демонстрации это можно предположить. Для того, чтобы создать ее как свернутую, можно выполнить следующий PL/SQL-блок:
1 declare 2 l_input_code dbms_sql.varchar2s; 3 begin 4 l_input_code (1) := 'Array to hold the MYPROC'; 5 l_input_code (2) := 'create or replace procedure myproc as '; 6 l_input_code (3) := ' l_key VARCHAR2(200);'; 7 l_input_code (4) := 'begin '; 8 l_input_code (5) := ' l_key := ''ARUPNANDA'';'; 9 l_input_code (6) := 'end;'; 10 l_input_code (7) := 'the end'; 11 sys.dbms_ddl.create_wrapped ( 12 ddl => l_input_code, 13 lb => 2, 14 ub => 6 15 ); 16* end;
Здесь мы определили переменную, l_input_code, для удержания входного открытого текстовый кода. В строках 4 - 10 мы заполнили строки кодом, который мы собираемся переносить сворачивать. В этом примере для той же самой простоты я использовал очень маленькие строки. В действительности, вы можете быть вынуждены использовать весьма долго строки до 32К размером. Точно так же я использовал только 7 элементов в массиве; в действительности можно использовать сколько угодно, лишь бы соответствовать полному коду.
Строки 11 - 15 показывают, как я вызвал процедуру создания свернутой процедуры. Я в строке 12 передал коллекцию как DDL-параметр. Но сделаем здесь паузу. Я использовал комментарий как первый элемент массива, возможно, для документации. Однако, это не допустимый синтаксис. Точно так же я использовал другой комментарий в последнем элементе (7) массива, снова недопустимый синтаксис для того, чтобы создать процедуру. Чтобы пдопустить свертку только допустимых строк, я определил в строках 13 и 14 самый низкий (2) и самые высокий (6) элементы из совокупности, которая хранит наш код. Параметр LB показывает нижнюю границу массива, которая в нашем примере является (2), а HB - верхнюю границу (6).
Видно, что используя этот подход, теперь можно создать процедуру любого размера в свернутом формате изнутри вашего PL/SQL-кода.
Условная компиляция PL/SQL: пишем единажды, выполняем мнократно
Многие из вас работали с языком C, который поддерживает концепцию директив компилятора. В C- программах, в зависимости от версии применяемого компилятора, значение некоторых переменных может отличаться.
В Oracle Database 10 g Выпуск 2 PL/SQL имеет подобную возможность: теперь можно предусмотреть директивы препроцессора, которые срабатывают в течение компиляции, а не во время выполнения. Для примера давайте создадим очень простую функцию, которая возвращает строку.
1 create or replace function myfunc 2 return varchar2 3 as 4 begin 5 $if $$ppval $then 6 return 'PPVAL was TRUE'; 7 $else 8 return 'PPVAL was FALSE'; 9 $end 10* end;
Обратим внимание на строку 5, где были использованы директивы препроцессора, оценивающие переменную ppval. Поскольку ppval - переменная препроцессора, не обычного PL/SQL, это определяется использованием $$-нотации. Далее, чтобы дать компилятору знать, что он должен обработать строки только в течение трансляции, надо определить действия специальной $-нотацией, то есть $if. Давайте скомпилируем эту функцию с различными значениями переменной ppval.
SQL> alter session set plsql_ccflags = 'PPVAL:TRUE'; Session altered.
Компилируем и выполняем функцию.
SQL> alter function myfunc compile; Function altered. SQL> select myfunc from dual; MYFUNC ------------------------------------- PPVAL was TRUE
Значение ppval в течение компиляции было установлено на false (ложь). Теперь изменим значение переменной и снова выполним функцию.
SQL> alter session set plsql_ccflags = 'PPVAL:FALSE'; Session altered. SQL> select myfunc from dual; MYFUNC --------------------------------------------------------- PPVAL was TRUE rather it takes the value set during the compilation.
Здесь, хотя в сессии значение ppval является FALSE (ЛОЖЬ), функция не воспринимает это значение; ей требуется установка значения в процессе трансляции. Снова перекомпилируем и выполним функцию.
SQL> alter function myfunc compile; Function altered. SQL> select myfunc from dual; MYFUNC --------------------------------------------------- PPVAL was FALSE
В процессе компиляции значение ppval было FALSE (ЛОЖЬ), и именно это значение было возвращено.
Итак, когда следует использовать эту особенность? Есть несколько возможных ситуаций, например, можно использовать ее как флажок отладки, чтобы отображать дополнительные сообщения, или можно написать программу, которая компилируется по разному в зависимости от платформы. Поскольку оценка заложена в процессе компиляции, а не во время выполнения, эффективность выполнения сильно возрастает.
Упомянутый выше пример работает прекрасно, пока вы используеме тот же самый флажок препроцессора, на который ссылаются все компилируемые функции. Но что будет, если для каждого кода имеется свой различный флажок? Например, функция calculate_interest может иметь флажок ACTIVE_STATUS_ONLY, установленный на TRUE (ИСТИНА), в то время как функция apply_interest может иметь флажок FOREIGN_ACCOUNTS, установленный на FALSE (ЛОЖЬ). Для того, чтобы компилировать их с соответствующими флажками, можете ввести:
alter function calculate_interest compile plsql_ccflags = 'ACTIVE_STATUS_ONLY:TRUE' reuse settings; alter function apply_interest compile plsql_ccflags = FOREIGN_ACCOUNTS:TRUE' reuse settings;
Обратите внимание, что нет никакой установки уровня сессии. Фраза reuse (повторное использование) гарантируют, что будут использоватся те же самые директивы компилятора, когда функции перетранслируются позже.
Давайте рассмотрим другую разновидность этой новой особенности. В дополнение к определению условной переменной можно также проверить статическую константу пакета в условной компиляции. Например, предположим, что вы хотите управлять выводом отладки PL/SQL-процедуры, основанной на применении Boolean (булевой) пакетной константы. Сначала создадим пакет, как:
create or replace package debug_pkg is debug_flag constant boolean := FALSE; end;
Константа debug_flag определяет в коде условную логику. Теперь можно внедрить код в пакет следующим образом:
create or replace procedure myproc as begin $if debug_pkg.debug_flag $then dbms_output.put_line ('Debug=T'); $else dbms_output.put_line ('Debug=F'); $end end;
Обратим внимание, что на пакетную константу ссылаются непосредственно без какого-либо $-признака. В этом случае нет никакой необходимости установить какие-либо параметры условной компиляции - выражения сессионого или системного уровня. Коль скоро функция откомпилирована, вы не нужно передать какое-либо дополнительное предложение. Чтобы увидеть, как это работает, выполним:
SQL> exec myproc Debug=F
Поскольку значение debug_pkg.debug_flag сейчас является FALSE (ЛОЖЬ), выполненная процедура возвратила "F", как и ожидалось. Теперь изменим значение константы:
create or replace package debug_pkg is debug_flag constant boolean := TRUE; end; Then, execute the procedure again: SQL> exec myproc Debug=T
Процедура закрепила значение константы, чтобы показать "T", как и ожидалось. Обратим внимание на имеющееся здесь очень важное различие: перекомпиляция процедуры не потребовалась; изменение константы было закреплено автоматически!
А помните ли вы страшную ошибку, о которой напоминает следующие строки?
ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ORA-06512: at "SYS.DBMS_OUTPUT", line 97 ORA-06512: at "SYS.DBMS_OUTPUT", line 112 ORA-06512: at line 2
Это происходило вследствие того, что максимально возможный символьный текст, обрабатываемый встроенным пакетом dbms_output, не мог превышать 1 миллион байтов. В Oracle Database 10 g Выпуск 2 это ограничение снято: теперь максимальный вывод может быть такой, сколько требуется. Вы можете установить размер вывода в "unlimited" ("неограничено"), просто задав:
set serveroutput on Это предложение продуцирует следующий результат:
SQL> show serveroutput serveroutput ON size 2000 format WORD_WRAPPED
Обратим внимание, что значение по умолчанию максимального размера вывода имело обыкновение быть 2 000. В Oracle Database 10 g Выпуск 2 команда показывает следующий результат:
SQL> show serveroutput serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED Значение по умолчанию - UNLIMITED.
Другое неудобство было связано с максимальным размером строки, отображаемой dbms_output. Ниже показано типичное сообщение об ошибке для строк, длиннее 255 байтов.
ERROR at line 1: ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line ORA-06512: at "SYS.DBMS_OUTPUT", line 35 ORA-06512: at "SYS.DBMS_OUTPUT", line 115 ORA-06512: at line 2
В Oracle Database 10 g Выпуск 2 строки могут иметь произвольную длину