Книги, научные публикации
Pages: | 1 | ... | 15 | 16 | 17 | 18 | 19 | ... | 24 | Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва Х Санкт-Петербург Х Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с ...
-- [ Страница 17 ] --ORA-06521 "PL/SQL: ошибка отображения функции" $ oerr ora 6521 06521, 00000, "PL/SQL: Error mapping function" // * Причина: ошибка возникла при попытке динамического сопоставления с // указанной функцией в PL/SQL. // * Действие: другие сообщения об ошибках (если они есть) подскажут // причину выдачи этого сообщения. Эта ошибка обычно возникает по одной из следующих причин: Х в имени внешней процедуры в оболочке PL/SQL или в коде на языке С сделана ошибка;
Х разработчик забыл экспортировать функцию в Windows (_declspec( dllexport)). Для демонстрации этой ошибки я изменил исходный код в файле lobtofile.pc следующим образом: tifdef WIN_NT _dec1spec (dllexport) #endif i n t xlobToFile(OCIExtProcContext * ctx, char * filename, Я добавил х к имени файла. Теперь при попытке выполнения мы получаем: declare х clob;
у number;
begin у := l o b _ i o. w r i t e ( ' x ', x ) ;
end;
* ERROR at l i n e 1: ORA-06521: PL/SQL: Error mapping function Глава ORA-06522: ld.so.l: extprocPLSExtProc: fatal: lobToFile: can't find symbol OPA-06512: at "OPS$TKYTE.LOB_IO", line 0 ORA-06512: at line 1 Это показывает, что причина ошибки Ч can't find symbol, т.е. нет соответствия между именем, указанным в PL/SQL-коде, и именем функции во внешней библиотеке. Либо вкралась опечатка, либо имя функции не экспортировано (в среде Windows).
ORA-06523 "превышено максимальное количество аргументов" $ oerr ora 6523 06523, 00000, "Maximum number of arguments exceeded" // * Причина: имеется ограничение на количество аргументов, которые можно // передавать внешней функции. // * Действие: в документации для своей версии сервера и платформы // проверьте, как вычисляется максимальное количество // аргументов. Это сообщение об ошибке можно получить в случае слишком большого списка параметров. Во внешние процедуры обычно можно передавать до 128 параметров (меньше, если передаются числа двойной точности, double, поскольку они занимают 8 байт, а не 4). При получении этого сообщения об ошибке, если действительно необходимо передавать столько параметров, проще всего обойти это ограничение с помощью набора. Например, следующий фрагмент: 1 declare 2 l_input strArray := strArray();
3 l_output strArray := strArray();
4 begin 5 dbms_output.put_line('Pass strArray');
6 for i in 1.. 1000 loop 7 l_input.extend;
8 linput(i) := 'Element ' || i;
9 end loop;
10 demo_passing_pkg.pass(l_input, l_output);
11 dbms_output.put_line('l_input.count = ' || l_input.count || 12 ' l_output.count = ' || l_output.count);
13 for i in 1.. l_input.count loop 14 if (l_input(i) != l_output(i)) then 15 raise program_error;
16 end if;
17 end loop;
18* end;
SQL> / Pass strArray l_input.count = 1000 l_output.count = 1000 PL/SQL procedure successfully completed. показывает, что с помощью набора я могу передавать внешней процедуре 1 О строк ОО во много раз превысив ограничение на количество параметров.
Внешние процедуры на языке С ORA-06525 "неправильная длина для данных типа CHAR или RAW" 06525, 00000, "Length Mismatch for CHAR or RAW data" // * Причина: значение, указанное в переменной, задающей длину строки, // недопустимо. Эта ошибка может произойти, если в PL/SQL // переменная типа RAW указана в качестве параметра, // передаваемого в режиме INOUT, OUT или в качестве // возвращаемого значения, но соответствующая переменная, // задающая длину, не передана. Эта ошибка может также // возникать при несоответствии заданного в переменной // значения длины фактической длине данных типа orlvstr или // orlraw. // // * Действие: исправьте код внешней процедуры и правильно задайте // переменную, определяющую длину. Это сообщение об ошибке, если вы следуете моим принципам передачи и возврата параметров, может произойти только в случае возвращения из функции данных типа RAW и строки. Решение проблемы очень простое: надо правильно задать длину. Для пустого параметра типа RAW, переданного в режиме OUT, необходимо установить длину 0, как д е л а л о с ь в представленных выше примерах. Для непустого параметра типа RAW, переданного в режиме OUT, длина должна быть меньше или равна атрибуту MAXLEN. Аналогично, длина возвращаемой строки тоже должна устанавливаться правильно: меньше чем MAXLEN, но, поскольку память для строки выделяет внешняя процедура, значения MAXLEN она не получает, поэтому атрибут LENGTH должен быть меньше или равен 32760 (максимальное значение, которое может быть обработано в PL/SQL).
ORA-06526 "невозможно загрузить библиотеку PL/SQL" $ oerr ora 6526 06526, 00000, "Unable to load PL/SQL library" // * Причина: PL/SQL не смог загрузить библиотеку, на которую ссыпается // конструкция EXTERNAL. Это серьезная ошибка, которая обычно // не возникает. // // * Действие: сообщите о проблеме службе поддержки. Это внутренняя ошибка. Сообщение о ней выдаваться не должно, но если уж оно получено, возможны два варианта. Во-первых, это сообщение об ошибке может сопровождаться другим, более детальным сообщением. Например: ERROR at line 1: ORA-6526: Unable to load PL/SQL library ORA-4030: out of process memory when trying to allocate 65036 bytes (callheap,KQL tmpbuf) Второе сообщение самоочевидно: не хватает памяти. Необходимо сократить объем используемой сеансом памяти. Если же сопровождающее ошибку ORA-6526 сообщение не позволяет понять причину ошибки, обращайтесь в службу поддержки.
Глава ORA-06527 "ошибка SQLLIB внешней процедуры: %s" $ oerr ora 6527 06527, 00000, "External procedure SQLLIB error: %s" // * Причина: при выполнении внешней процедуры, написанной с помощью // Рго*С, возникла ошибка в библиотеке sqllib. // // * Действие: тест сообщения позволит понять, какая именно ошибка // произошла в библиотеке SQLLIB. Обратитесь к руководству // Oracle Error Messages and Codes, где можно найти полное // описание причин ошибки, и выполните соответствующие // действия.
С этой ошибкой все понятно. Более детальная информация о причинах будет представлена в сообщении.
Резюме В этой главе мы рассмотрели тонкости использования внешних процедур: Х поддержка информации о состоянии с помощью контекстов;
Х использование независимых от ОС функций для работы с файлами;
Х параметризация кода внешней процедуры с помощью внешних файлов параметров;
Х оснащение кода средствами отладки (с помощью макроса debugf), позволяющими найти причину проблем;
Х приемы написания безопасного кода (всегда передавать контекст, всегда передавать индикаторы значений NULL и т.д.);
Х как использовать универсальный шаблон для быстрой разработки внешних процедур с широкими функциональными возможностями;
Х различия между внешними процедурами, использующими исключительно библиотеку OCI, и процедурами, использующими средства прекомпилятора Рго*С;
Х как сопоставлять, принимать и передавать основные типы данных PL/SQL во внешние функции на языке С;
Х как передавать и принимать наборы данных. Имея представленные выше универсальный шаблон и файлы управления проектом, вы получили все необходимое для написания внешней процедуры от начала до конца за несколько минут. Самое сложное Ч сопоставление типов данных, но по представленным в этой главе таблицам это легко сделать. Они вам подскажут, какой тип использовать для передачи данных. Далее следуйте представленным в примерах принципам передачи параметров (всегда передавать контекст, всегда передавать атрибут MAXLEN для строк и данных типа RAW, всегда передавать индикаторные переменные и т.д.). Это обеспечит создание эффективных внешних процедур в кратчайшие строки.
Хранимые процедуры на языке Java В сервере Oracle 8.1.5 впервые появилась возможность использовать для реализации хранимых процедур язык Java. Для 99 процентов задач всегда хватало возможностей языка PL/SQL, и его по-прежнему можно использовать. В Oracle 8.0 ранее появилась возможность реализовать процедуры на языке С (см. главу 18). Хранимые процедуры на языке Java (еще один вид внешних процедур) Ч естественное расширение этой возможности, позволяющее использовать язык Java в тех случаях, когда раньше приходилось программировать на С или С++. Если необходимо разработать хранимую процедуру, теперь есть как минимум три возможности: использовать язык PL/SQL, Java или С. Я перечислил их в порядке предпочтения. Большую часть обработки в базе данных можно выполнить на PL/SQL. Если что-то нельзя сделать с помощью PL/SQL (в основном это касается интерфейсов с ОС), вступает в игру язык Java. Язык С используется при наличии уже созданного кода на С или в тех случаях, когда нельзя решить задачу средствами Java. Эта глава не раскрывает основы Java, интерфейса JDBC или программирования с помощью SQLJ. Предполагается, что читатель хоть немного знаком с языком Java и сможет разобраться в небольших фрагментах Java-кода. Предполагается также общее знание интерфейса JDBC и прекомпилятора SQLJ, хотя при наличии минимального опыта использования Java вы легко сможете понять фрагменты кода, связанные с JDBC и SQLJ.
Глава Когда используются хранимые процедуры на языке Java?
Внешние процедуры на языке Java отличаются от процедур на С тем, что, как и программные единицы PL/SQL, они выполняются встроенной виртуальной Java-машиной (JVM) сервера Oracle, непосредственно в адресном пространстве сервера. Чтобы использовать внешние процедуры на языке С, необходимо сконфигурировать процесс прослушивания, настроить файл TNSNAMES.ORA и запустить отдельный процесс. При использовании языка Java все это не нужно, поскольку как интерпретируемый язык он считается "безопасным" (как и PL/SQL). Нельзя создать Java-функцию, переписывающую часть области SGA. Это и хорошо, и плохо, как выяснится по ходу обсуждения. Тот факт, что работа происходит в одном адресном пространстве, обеспечивает более быстрое взаимодействие между кодом на Java и сервером, в частности происходит меньше переключений контекста между процессами на уровне ОС. С другой стороны, однако, Java-код всегда работает с правами "владельца ПО Oracle", поэтому хранимая процедура на Java (при наличии соответствующих привилегий) может переписать файл параметров инициализации сервера, INIT.ORA (или другие, еще более важные файлы, например файлы данных). Лично я постоянно использую небольшие фрагменты Java-кода для реализации того, что невозможно сделать с помощью PL/SQL. Например, в приложении А, посвященном основным стандартным пакетам, я демонстрирую, как я реализовал пакет для работы с сокетами TCP/IP при помощи Java. Я создавал его для версии Oracle 8.1.5, до появления пакета UTL_TCP (который тоже написан на языке Java), и предпочитаю использовать его до сих пор. Я также использую средства языка Java для передачи сообщений электронной почты с сервера. И для этих целей уже существует стандартный пакет, UTL_SMTP (тоже реализованный на языке Java), позволяющий отправлять простые сообщения, но непосредственное использование языка Java открывает множество других возможностей, включая передачу (и получение) сообщений электронной почты с вложениями. Я интенсивно использую пакет UTL_FILE для чтения и записи файлов в PL/SQL. Одна из возможностей, которых не хватает пакету UTL_FILE, Ч получение списка файлов в каталоге. С помощью языка PL/SQL его получить нельзя, а на Java Ч элементарно. Иногда необходимо выполнить команду ОС или программу из среды сервера. В этом случае язык PL/SQL тоже не поможет, а Java позволит легко решить задачу. Изредка мне необходимо узнать часовой пояс, установленный на сервере. В PL/SQL его получить нельзя, а вот с помощью Java Ч можно (эту возможность мы рассмотрим в приложении А при изучении стандартного пакета UTL_TCP). Надо измерять время с точностью до миллисекунд? В Oracle 8i с помощью Java это можно сделать. Если постоянно необходимо подключаться к СУБД DB2 для выполнения запросов, это можно сделать с помощью шлюза (Transparent Gateway) для СУБД DB2. Это позволит без ограничений выполнять соединения таблиц в разнородных базах данных, распределенные транзакции, прозрачную двухэтапную фиксацию и использовать много других возможностей. Но если необходимо выполнить запрос или изменение в базе дан Хранимые процедуры на языке Java ных DB2 и все перечисленные потрясающие возможности не нужны, достаточно загрузить в базу данных драйверы JDBC для DB2, написанные на языке Java, и воспользоваться ими (естественно, это применимо не только для СУБД DB2). По сути, любой из миллионов имеющихся не интерактивных (не обладающих пользовательским интерфейсом) фрагментов Java-кода можно загрузить в базу данных Oracle и использовать. Вот почему фрагменты Java-кода постоянно встречаются в приложениях. Я предпочитаю использовать язык Java, только когда это удобно и необходимо. Я по-прежнему считаю PL/SQL подходящим средством для создания подавляющего большинства хранимых процедур. Написав одну-две строки PL/SQL-кода, можно получить тот же результат, что и в случае многострочной программы на Java/JDBC. Препроцессор SQLJ уменьшает объем необходимого кода, но выдаваемый им код по производительности уступает сочетанию языков PL/SQL и SQL. Производительность кода на PL/ SQL при взаимодействии с SQL выше, чем для сочетания Java/JDBC, как и можно было предположить. Язык PL/SQL проектировался как расширение SQL, и они очень тесно интегрированы. Большинство типов данных языка PL/SQL Ч это стандартные типы данных SQL, а все типы данных SQL включены в PL/SQL. Между этими языками нет несоответствия типов. Доступ к SQL из кода на Java выполняется средствами функционального интерфейса, добавленного к языку. Каждый тип данных SQL необходимо преобразовать в некий тип данных Java, и, наоборот, все SQL-операторы выполняются процедурно, т.е. между этими языками нет тесной связи. Итак, если выполняется обработка данных в базе, надо использовать язык PL/SQL. Если надо на время выйти за пределы базы данных (например, чтобы отправить сообщение по электронной почте), лучшим средством для этого является язык Java. Если необходимо выполнить поиск в сообщениях электронной почты, хранящихся в базе данных, используйте язык PL/SQL. Если же необходимо загрузить сообщения электронной почты в базу данных, используйте Java.
Как работают внешние процедуры на языке Java Оказывается, внешние процедуры на языке Java (термин "внешняя процедура" в данном случае является синонимом "хранимой процедуры") создавать значительно проще, чем на языке С. Например, в предыдущей главе, посвященной созданию внешних процедур на языке С, пришлось решать следующие проблемы. Х Управление состоянием. Внешние процедуры могут потерять информацию о состоянии (текущие значения статических или глобальных переменных). Это связано с используемым механизмом кэширования динамически подключаемых библиотек. Поэтому необходим механизм определения и сохранения состояния в программах на языке С. Х Механизмы трассировки. Внешние процедуры выполняются на сервере как отдельный процесс. Хотя на некоторых платформах эти процедуры можно отлаживать с помощью обычного отладчика, это весьма сложно и, если ошибки возни Глава кают только при одновременном использовании внешней процедуры большим количеством пользователей, просто невозможно. Необходимо средство генерации трассировочных файлов по требованию, "начиная с этого момента". Х Использование параметров. Необходимо средство параметризации внешних процедур, чтобы можно было управлять их работой извне с помощью файла параметров, аналогично тому, как файл init.ora используется для управления сервером. Х Общая обработка ошибок. Необходимо простое средство выдачи пользователю вразумительных сообщений об ошибках. При использовании языка Java оказывается, что управление состоянием, трассировка и общая обработка ошибок уже не является проблемой. Для сохранения информации о состоянии достаточно объявить переменные в создаваемых Java-классах. Для обеспечении простейшей трассировки можно использовать вызовы System.out.println. Общую обработку ошибок можно выполнять с помощью функции RAISE_APPLICATION_ERROR языка PL/SQL. Все это продемонстрировано в следующем коде: tkyte@TKYTE816> create or replace and compile 2 java source named "demo" 3 as 4 import java.sql.SQLException;
5 6 public>
10 11 public static int IncrementCounter() throws SQLException 12 { 13 System.out.println("Входим в функцию IncrementCounter, -> counter = "+counter);
14 if (++counter >= 3) 15 { 16 System.out.println("Ошибка counter="+counter);
17 #sql { 18 begin raise_application_error(-20001, -> 'Слишком много вызовов');
end;
19 };
20 } 21 System.out.println("Выходим из функции IncrementCounter, -> counter = "+counter);
22 return counter;
23 } 24 } 25 / Java created. Состояние поддерживается с помощью статической переменной counter. Наша простая демонстрационная программа будет увеличивать счетчик при каждом вызове, а Хранимые процедуры на языке Java начиная с третьего и при последующих вызовах, будет автоматически выдавать сообщение об ошибке. Обратите внимание, что для создания небольших фрагментов кода вроде этого можно использовать утилиту SQL*Plus, непосредственно загружающую Java-код в базу данных, автоматически компилируя его в байт-код и запоминая в соответствующих структурах. Ни внешний компилятор, ни средства разработки JDK при этом не нужны Ч достаточно SQL-оператора CREATE OR REPLACE. Именно так я и предпочитаю создавать хранимые процедуры на языке Java. Это упрощает их установку на любой платформе. Не нужно запрашивать имя пользователя и пароль, как при использовании команды LOADJAVA (это утилита командной строки для загрузки исходного кода, классов Java или jar-файлов в базу данных). Не надо думать о каталогах для поиска классов (classpath) и других подобных нюансах. В приложении А мы рассмотрим утилиту LOADJAVA и пакет DBMS_JAVA, обеспечивающий интерфейс к программе LOADJAVA. Этот метод (с использованием оператора CREATE OR REPLACE) загрузки небольших Java-функций в базу данных особенно хорошо подходит для тех, кто только начинает осваивать технологии Java. Вместо установки JDBC-драйверов, среды разработки JDK, настройки списка каталогов для поиска классов можно просто компилировать код в базе данных, точно так же, как при создании программных единиц PL/SQL. Сообщения об ошибках компиляции выдаются точно так же, как и при использовании языка PL/SQL, например: tkyte@TKYTE816> create or replace and compile 2 java source named "demo2" 3 as 4 5 public>
11 12 return counter;
13 } 14 } 15 / Warning: Java created with compilation errors. tkyte@TKYTE816> show errors java source "demo2" Errors for JAVA SOURCE demo2: LINE/COL ERROR 0/0 0/0 demo2:8: Undefined variable: counter Info: 1 errors Это показывает, что функция my_routine, определенная в строке 8, обращается к необъявленной переменной. Не приходится выискивать ошибку в коде, поскольку получено информативное сообщение о ней. Я не раз убеждался, что многократных оши Глава бок при настройке JDBC/JDK/CLASSPATH можно легко избежать, загрузив за пару секунд код с помощью этого простого подхода. Вернемся теперь к работающему примеру. Хочу обратить ваше внимание на еще одну важную деталь в созданном выше классе. Метод, вызываемый из языка SQL, IncrementCounter, объявлен как статический. Он обязательно должен быть статическим. (Хотя не все должно быть статическим: при реализации статического метода можно использовать обычные методы). Для языка SQL необходим хотя бы один метод, который можно вызвать, не передавая неявно данные экземпляра с помощью скрытого параметра, вот почему нужен статический метод. Теперь, после загрузки небольшого Java-класса, необходимо создать для него спецификацию вызова в языке PL/SQL. Эта процедура очень похожа на ту, что была описана в главе 18 для внешних процедур на языке С, когда мы сопоставляли типы данных С типам данных SQL. Именно это мы и сделаем сейчас;
только на этот раз будут сопоставляться типы данных языка Java типам данных SQL: tkyte@TKYTE816> create or replace 2 function java_counter return number 3 as 4 language java 5 name 'demo.IncrementCounter() return integer';
6/ Function created. Теперь можно вызывать эту функцию: tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> exec dbms_output.put_line(java_counter);
1 PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dbms_output.put_line(java_counter);
2 PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dbms_output.put_line(java_counter);
BEGIN dbms_output.put_line(java_counter);
END;
* ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-20001: Слишком иного вызовов ORA-06512: at line 1 ORA-06512: at "TKYTE.JAVA_COUNTER", line 0 ORA-06512: at line 1 Как видите, информация о состоянии поддерживается автоматически, о чем свидетельствует увеличение счетчика с 1 до 2 и 3. Об ошибках сообщать тоже достаточно легко, но куда попадают результаты обращения к System.out.println? По умолчанию они попадают в файл трассировки. При наличии доступа к представлениям V$PROCESS, V$SESSION и V$PARAMETER можно определить имя файла трассировки в конфигура Хранимые процедуры на языке Java ции выделенного сервера следующим образом (этот пример предназначен для Windows для ОС UNIX он будет аналогичным, но полученное имя файла будет другим): tkyte@TKYTE816>select c.value||'\ORA'||to_char(a.spid,'fm00000')||Х.trc' 2 from v$process a, v$session b, v$parameter c 3 where a.addr = b.paddr 4 and b.audsid = userenv('sessionid1) 5 and c.name = 'user_dump_dest' 6/ C.VALUE||'\ORA'||TO_CHAR(A.SPID,'FM00000')||'.TRC' C:\oracle\admin\tkyte816\udump\ORA01236.trc tkyte@TKYTE816> edit C:\oracle\admin\tkyte816\udump\ORA01236.trc В этом файле можно обнаружить следующее:
Dump file C:\oracle\admin\tkyte816\udump\ORA01236.TRC Tue Mar 27 11:15:48 2001 ORACLE V8.1.6.0.0 - Production vsnsta=0 vsnsql=e vsnxtr=3 Windows 2000 Version 5.0, CPU type 586 Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 Ч Production Windows 2000 Version 5.0, CPU type 586 Instance name: tkyte816 Redo thread mounted by this instance: 1 Oracle process number: 12 Windows thread id: 1236, image: ORACLE.EXE *** 2001-03-27 11:15:48.820 *** SESSION ID:(8.11) 2001-03-27 11:15:48.810 Входим в функцию IncrementCounter, counter = 0 Выходим из функции IncrementCounter, counter = 1 Входим в функцию IncrementCounter, counter = 1 Выходим из функции IncrementCounter, counter = 2 Входим в функцию IncrementCounter, counter = 2 Ошибка! counter=3 oracle.jdbc.driver.OracleSQLException: ORA-20001: Слишком много вызовов ORA-06512: at line 1 Я также мог бы использовать средства пакета DBMS_JAVA для перенаправления этих результатов на экран утилиты SQL*Plus, чтобы избежать поиска соответствующего трассировочного файла при отладке функции. В этом разделе периодически упоминается пакет DBMS_JAVA, но полное его описание будет представлено в соответствующем разделе приложения А. Из этого небольшого примера понятно, что, по сравнению с созданием внешних процедур на языке С, создавать хранимые процедуры на Java Ч просто. Не нужно специально настраивать сервер Ч только инсталлировать Java в базу данных. Не нужен внешний компилятор. Многие средства, которые в случае языка С пришлось создавать самим, мы получаем от сервера автоматически. Это на самом деле просто.
Глава Я не описывал пока конфигурирование Java-кода с помощью файла параметров. Причина в том, что Java содержит встроенные средства для этого в виде класса java.util.Properties. Достаточно использовать метод load этого класса для загрузки ранее сохраненного набора свойств либо из большого объекта в таблице базы данных, либо из файла ОС, Ч что больше подходит. Далее я представлю несколько полезных примеров хранимых процедур на языке Java, в частности, упоминавшихся ранее в разделе "Когда используются хранимые процедуры на языке Java?". Но до этого я хочу переписать представленный в главе 18 пакет DEMO_PASSING_PKG на языке Java вместо С, чтобы продемонстрировать, как передавать и принимать основные типы данных SQL во внешних процедурах на языке Java.
Передача данных В этом примере я собираюсь создать ряд процедур с параметром, передаваемым в режиме IN, и параметром, передаваемым в режиме OUT (или IN OUT). Мы напишем по процедуре для каждого из интересующих нас типов данных (наиболее часто используемых). При этом будет продемонстрирован правильный способ передачи входных данных и получения результатов каждого типа. Кроме того, я создам несколько функций и покажу, как возвращать данные некоторых из этих типов. Меня при работе с Java интересуют следующие типы: Х строки (размером до 32 Кбайт);
Х числа (произвольного масштаба и точности);
Х даты;
Х целые числа (включая данные типа binary_integer);
Х данные типа RAW (размером до 32 Кбайт);
Х большие объекты (для любых данных размером более 32 Кбайт);
Х массивы строк;
Х массивы чисел;
Х массивы дат. Этот список несколько отличается от аналогичного списка для внешних процедур на языке С. В частности, в нем не указан тип данных BOOLEAN. Дело в том, что пока нет соответствия между типом данных PL/SQL BOOLEAN и типами данных языка Java. Нельзя передавать данные типа BOOLEAN как параметры внешним процедурам, написанным на языке Java. С помощью объектно-реляционных расширений можно создавать типы данных любой сложности. Для создания таких типов данных я рекомендую использовать поставляемое корпорацией Oracle Java-средство JPublisher. Оно автоматически создает Javaклассы, соответствующие объектным типам. Подробнее о JPublisher можно почитать в руководстве Oracle8i JPublisher User's Guide, которое входит в набор документации, предлагаемой корпорацией Oracle. Как и в случае внешних процедур на языке С, мы не будем углубляться в особенности использования объектных типов в хранимых процедурах на Java, ограничившись только простыми наборами данных скалярных типов.
Хранимые процедуры на языке Java Java-класс будет создан для тех же целей, что и представленная в предыдущей главе динамически подключаемая библиотека на языке С. Начнем с SQL-операторов для создания трех типов наборов Ч они совпадают с использовавшимися в примередля языка С в предыдущей главе: tkyte@TKYTE816> create or replace type numArray as table of number;
Type created. tkyte@TKYTE816> create or replace type dateArray as table of date;
Type created. tkyte@TKYTE816> create or replace type strArray as table of varchar2(255);
Type created. Теперь рассмотрим спецификацию PL/SQL-пакета для этого примера. Она будет состоять из набора перегруженных процедур и функций для проверки приема и передачи параметров в хранимых процедурах на языке Java. Каждая подпрограмма имеет параметр, предаваемый в режиме IN, и параметр, передаваемый в режиме OUT, что позволяет продемонстрировать передачу данных в Java-код и возвращение результатов. Первая процедура передает числовые данные. Данные Oracle типа NUMBER будут передаваться как Java-тип BigDecimal. Их можно принимать и как данные типа int, и как строки и как другие типы, но при этом возможна потеря точности. Данные типа BigDecimal могут без проблем принять любое значение типа NUMBER от сервера Oracle. Обратите внимание, что параметр, передаваемый в режиме OUT, на уровне Java принимается как массив данных типа BigDecimal. Так будет для всех параметров, передаваемых Java в режиме OUT. Для изменения параметра, переданного Java, нужно передавать "массив" параметров (в этом массиве будет только один элемент) и изменять соответствующий элемент массива. Далее, при описании кода на языке Java, вы увидите, как это сказывается на исходном коде. tkyte@TKYTE816> create or replace package demo_passing_pkg 2 as 3 procedure pass(p_in in number, p_out out number) 4 5 6 as language java name 'demo_passing_pkg.pass(java.math.BigDecimal, java.math.BigDecimal[])' Даты Oracle сопоставляются типу данных Timestamp. И в этом случае можно было бы сопоставить датам множество других типов, например String, но во избежание потери информации при неявных преобразованиях я выбрал тип Timestamp, который позволяет сохранить все данные, содержащиеся в объектах Oracle типа DATE. 8 9 10 11 12 13 procedure pass(p_in in date, p_out out date) as language java name 'demo_passing_pkg.pass(java.sql.Timestamp, java.sql.Timestamp[])';
Строки типа VARCHAR2 передаются очень просто Ч как данные типа java.lang.String.
14 15 16 17 18 Глава procedure pass(p_in in varchar2, p_out out varchar2) as language java name 'demo_passing_pkg.pass(java.lang.String, java.lang.String[])';
Для данных типа CLOB мы используем предоставляемый Oracle Java-тип oracle.sql.CLOB. С помощью этого типа мы сможем получить входной и выходной потоки данных, используемые для чтения и записи данных типа CLOB. 20 21 22 23 24 25 procedure pass(p_in in CLOB, p_out in out CLOB) as language java name 'demo_passing_pkg.pass(oracle.sql.CLOB, oracle.sql.CLOB[])';
Теперь перейдем к наборам: вы видели, что, независимо от типа фактически предаваемого набора, используется один и тот же предоставляемый Oracle тип. Вот почему в данном случае Java-функции не являются перегруженными, как все предыдущие (пока что все вызываемые Java-функции назывались demo_passing_pkg.pass). Поскольку все типы наборов передаются как один и тот же тип Java, перегрузку имен использовать нельзя Ч необходимо называть функцию в соответствии с реально передаваемым типом данных: 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 procedure pass(p_in in numArray, p_out out numArray) as language java name 'demo_passing_pkg.pass_num_array(oracle.sql.ARRAY, oracle.sql.ARRAY[])';
procedure pass(p_in in dateArray, p_out out dateArray) as language java name 'demo_passing_pkg.pass_date_array(oracle.sql.ARRAY, oracle.sql.ARRAY[])';
procedure pass(p_in in strArray, p_out out strArray) as language java name 'demo_passing_pkg.pass_str_array(oracle.sql.ARRAY, oracle.sql.ARRAY[])';
Следующие две процедуры демонстрируют сопоставление для типов RAW и INT. SQLтип RAW будет сопоставляться встроенному типу byte языка Java. Для целых чисел будет использоваться встроенный тип данных int языка Java: 44 45 46 47 procedure pass_raw(p_in in RAW, p_out out RAW> as language java Хранимые процедуры на языке Java 48 49 50 51 52 53 name 'demo_passing_pkg.pass(byte[], byte[][])';
procedure pass_int(p_in in number, p_out out number) as language j a v a name ' d e m o _ p a s s i n g _ p k g. p a s s _ i n t ( i n t, i n t [ ] ) ' ;
Наконец, для полноты изложения я продемонстрирую использование функций для возвращения данных простых скалярных типов: 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 end 72 / function return_number return number as language java name 'demo_passing_pkg.return_num() return java.math.BigDecimal';
function return_date return date as language java name 'demo_passing_pkg.return_date() return java.sql.Timestamp';
function return_string return varchar2 as language java name 'demo_passing_pkg.return_string() return java.lang.String';
demo_passing_pkg;
Package created. Эта спецификация пакета практически совпадает (за исключением процедур для данных типа BOOLEAN) с той, что использовалась для внешних процедур на языке С. В этом примере я поместил уровень связывания непосредственно в спецификацию, чтобы не пришлось писать избыточное тело пакета (все функции написаны на языке Java). Рассмотрим Java-код, реализующий использованные выше функции. Начнем с определения Java-класса demo_passing_pkg: tkyte@TKYTE816> set define off tkyte@TKYTE816> create or replace and compile 2 java source named "demo_passing_pkg" 3 as 4 import java.io.*;
5 import java.sql.*;
6 import java.math.*;
7 import oracle.sql.*;
8 import oracle.jdbc.driver.*;
9 10 public>
фактически мы передаем массив из одного элемента. При изменении значения в массиве изменяется параметр, переданный в режиме OUT. Вот почему все эти методы в качестве второго параметра принимают массив. Значение p_out[0] можно изменять, и оно будет передано методом в вызывающую среду. Изменения значения p_in в вызывающую среду не передаются. Интересная особенность данного метода Ч отсутствие индикаторной переменной. Язык Java поддерживает понятие пустого объекта (null) в объектных типах, как и языки SQL и PL/SQL. Он, однако, не поддерживает трехзначную логику, как SQL;
операции X IS NOT NULL нет Ч можно только непосредственно сравнивать объект с null. Не перепутайте и не пытайтесь писать условия вида p_in <> NULL в PL/SQL-коде, поскольку они не будут работать корректно. 12 public static void pass(java.math.BigDecimal p_in, 13 java.math.BigDecimal[] p_out) 14 { 15 if (p_in != null) 16 { 17 System.out.println 18 ("Первый параметр " + p_in.toString());
19 20 p_out[0] = p_in.negate();
21 22 System.out.println 23 ("Устанавливаем параметр out равным " + p_out[0].toString());
24 } 25 } Следующий метод работает с типом данных Oracle DATE. Он совпадает с представленным выше, за исключением того, что используются методы класса Timestamp для обработки даты. Наша задача Ч добавить к переданной дате один месяц: 26 27 public static void pass(java.sql.Timestamp p_in, 28 java.sql.Tiraestamp[] p_out) 29 { 30 if (p_in != null) 31 { 32 System.out.println 33 ("Первый параметр " + p_in.toString());
34 35 p_out[0] = p_in;
36 37 if (p_out[0].getMonth() < 11) 38 p_out[0].setMonth(p_out[0].getMonth()+l);
39 else 40 { 41 p_out[0].setMonth(0);
42 p_out[0].setYear(p_out[0].getYear()+l);
43 } Хранимые процедуры на языке Java 44 45 46 47 } System.out.println ("Устанавливаем параметр out равным " + p_out[0].toString());
} Теперь переходим к самому простому из типов данных Ч String, который соответствует строковым типам SQL. Если вспомнить версию на языке С, с шестью формальными параметрами, индикаторными переменными, атрибутами strlen, функциями strcpy и т.п., то по сравнению с ней эта реализация тривиальна:
49 public static void pass(java.lang.String p_in, 50 java.lang.String[] p_out) 51 { 52 if (p_in != null) 53 { 54 55 56 57 58 59 60 61 62 } System.out.println ("Первый параметр " + p_in.toString());
p_out[0] = p_in.toUpperCase();
System.out.println ("Устанавливаем параметр out равным " + p_out[0].toString());
} В методе для данных типа CLOB придется выполнить ряд дополнительных действий. Для того чтобы показать, как принимать и возвращать большие объекты, здесь выполняется копирование. Вы видите, что для изменения/чтения содержимого большого объекта используются стандартные потоки чтения/записи языка Java. В этом примере is Ч входной поток, a os Ч выходной. Метод копирует данные фрагментами по 8 Кбайт. Выполняется цикл чтения и записи, пока не закончатся считываемые данные: 63 64 public static void pass(oracle.sql.CLOB p_in, 65 oracle.sql.CLOB[] p_out) 66 throws SQLException, IOException 67 { 68 if (p_in != null && p_out[0] != null) 69 { 70 System.out.println 71 ("Первый параметр " + p_in.length());
72 System.out.println 73 ("Первый параметр '" + 74 p_in.getSubString(l,80) + " ' " ) ;
75 76 Reader is = p_in.getCharacterStream();
77 Writer os = p_out[0].getCharacterOutputStream();
78 79 char buffer!] = new char[8192];
80 int length;
1372 Глава 82 83 84 85 86 87 88 89 90 91 92 } while((length-is.read(buffer,0,8192)) != -1) os.write(buffer,0,length);
is.close();
os.close();
System.out.println ("Устанавливаем параметр out равным " + P_out[0].getSubString(1,80));
} Следующий метод Ч приватный (внутренний). Он выдает метаданные о переданном ему объекте типа oracle.sql.ARRAY. Для каждого из передаваемых Java трех типов массивов будет вызываться этот метод, информирующий о том, какого размера и типа массив передан: 93 94 95 96 97 98 99 100 101 102 103 private static void show_array_infо(oracle.sql.ARRAY p_in) throws SQLException { System.out.println("Тип массива "+ p_in.getSQLTypeName());
System.out.println("Код типа массива " + p_in.getBaseType()) ;
System.out.println("Длина массива "+ p_in.length());
} Теперь рассмотрим методы для обработки этих массивов. Использовать массивы несложно, если разобраться, как получать из них данные и изменять их. Получить данные очень просто;
метод getArray() возвращает базовый массив данных. Приведя возвращаемое методом getArray() значение к нужному типу, мы получим Java-массив этого типа. Поместить данные в такой массив немного сложнее. Необходимо сначала получить дескриптор (метаданные) массива, а затем создать новый объект-массив с этим дескриптором и соответствующими значениями. Следующий набор методов продемонстрирует это для каждого из использованных типов массивов. Обратите внимание, что тексты методов практически совпадают, за исключением фактических обращений к массивам данных Java. Эти методы выдают метаданные для типа oracle.sql.ARRAY, выдают содержимое массива и копируют входной массив в выходной: 104 105 106 107 108 109 110 111 112 113 public static void pass_num_array(oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out) throws SQLException { show_array_infо(p_in);
java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray();
for(int i = 0;
i < p_in.length();
i++) System.out.println("p_in["+i+"] = " + values[i].toString());
Хранимые процедуры на языке Java 114 115 Connection conn = new OracleDriver().defaultConnectionO;
116 ArrayDescriptor descriptor = 117 ArrayDescriptor.createDescriptor(p_in.getSQLTypeNatne(), -> conn);
118 119 p_out[0] = new ARRAY(descriptor, conn, values);
120 121 ) 122 123 public static void 124 pass_date_array(oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out) 125 throws SQLException 126 { 127 show_array_info(p_in);
128 java.sql.Timestamp[] values = (Timestamp[])p_in.getArray();
129 130 for(int i = 0;
i < p_in.length();
i++) 131 System.out.println("p_in["+i+"] = " + values[i].toString());
132 133 Connection conn = new OracleDriver().defaultConnection();
134 ArrayDescriptor descriptor = 135 ArrayDescriptor.createDescriptor (p_in.getSQLTypeName(), conn) ;
136 137 p_out[0] = new ARRAY(descriptor, conn, values);
138 139 ) 140 141 public static void 142 pass_str_array(oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out) 143 throws java.sql.SQLException,IOException 144 { 145 show_array_infо(p_in);
146 String[] values = (String[])p_in.getArray();
147 148 for(int i = 0;
i < p_in.length();
i++) 149 System.out.println("p_in["+i+"] = " + values[i]);
150 151 Connection conn = new OracleDriver().defaultConnection();
152 ArrayDescriptor descriptor = 153 ArrayDescriptor.createDescriptor(p_in.getSQLTypeName(), conn) ;
154 155 p_out[0] = new ARRAY(descriptor, conn, values);
156 157 ) Передача данных типа RAW ничем не отличается от передачи строк. С этим типом данных работать очень легко:
158 159 public static void pass(byte[] p_in, byte[][] p_out) 160 { Глава 161 162 163 } if (p_in != null) p_out[0] = p_in;
Передача целых чисел Ч проблематична, я вообще не рекомендую их передавать. Нет способа передать значение NULL Ч соответствующий тип данных int относится к базовым типам данных языка Java. Эти данные не являются объектами и поэтому не могут быть пустыми. Поскольку индикаторные переменные не поддерживаются, то при необходимости обработать пустые значения придется передавать отдельный параметр, а в PL/SQL-коде Ч проверять соответствующий флаг, чтобы определить, не возвращено ли пустое значение. Соответствующий метод представлен здесь для полноты, но лучше вообще не использовать данные целого типа, особенно как параметры, передаваемые в режиме IN, Ч Java-метод не сможет определить, что значение не нужно читать, поскольку пустые значения не поддерживаются. 164 165 public static void pass_int(int p_in, int[] p_out) 166 { 167 System.out.println 168 ("Входной параметр " + p_in);
169 170 p_out[0] = p_in;
171 172 System.out.println 173 ("Выходной параметр " + p_out[0]);
174 } Наконец, перейдем к функциям. Если помните, на языке С написать их было непросто. Необходимо было выделять память, обрабатывать пустые значения, явно преобразовывать типы данных С в типы данных Oracle и т.д. Каждая С-функция при этом состояла как минимум из десятка строк кода. В случае же Java достаточно добавить оператор return: 175 176 public static String return_string() 177 { 178 return "Hello World";
179 } 180 181 public static java.sql.Timestamp return_date() 182 { 183 return new java.sql.Timestamp(0);
184 } 185 186 public static java.math.BigDecimal return_num() 187 { 188 return new java.math.BigDecimal("44.3543");
189 } 190 191 } 192 / Хранимые процедуры на языке Java Java created tkyte@TKYTE816> s e t define on Запрограммировать функцию на Java гораздо проще, чем на языке С, благодаря тому, что Java выполняет много действий автоматически, "за кадром". Для обеспечения аналогичной функциональности на языке С потребовалось около 1000 строк кода. Выделение памяти, которое требует столько внимания при программировании на С, в случае Java Ч не проблема. В случае ошибки возбуждается исключительная ситуация. Индикаторные переменные, с которыми надо было возиться в языке С, вообще не нужны в Java. Проблема возникает при передаче типов данных, соответствующих не объектным типам Java, но, как я уже говорил, не следует их использовать, если может потребоваться передать пустые значения. Поскольку все компоненты созданы, можно вызывать подпрограммы. Например: tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output(1000000) tkyte@TKYTE816> declare 2 l_in strArray := StrArray();
3 l_out strArray := strArray();
4 begin 5 for i in 1.. 5 loop 6 l_in.extend;
7 l_in(i) := 'Элемент ' || i;
8 end loop;
9 10 demo_passing_pkg.pass(l_in, l_out);
11 for i in 1.. l_out.count loop 12 dbms_output.put_line('l_out(' || i || ') = ' || l_out{i));
13 end loop;
14 end;
15 / Тип массива SECOND.STRARRAY Код типа массива 12 Длина массива 5 p_in[0] = Элемент 1 p_in[l] = Элемент 2 p_in[2] - Элемент 3 p_in[3] = Элемент 4 p_in[4] = Элемент 5 l_out(l) = Элемент 1 l_out(2) = Элемент 2 l_out(3) = Элемент 3 l_out(4) = Элемент 4 l_out(5) = Элемент 5 PL/SQL procedure successfully completed. Первые восемь строк результата были сгенерированы Java-методом, а последние пять Ч PL/SQL-кодом. Значит, мы передали массив из PL/SQL в Java и получили его обратно. С помощью Java-метода мы скопировали входной массив в выходной после распечатки метаданных и значений элементов массива.
Глава Полезные примеры Я свято верю, что, если задачу можно решить с помощью одного SQL-оператора, это надо делать. Никогда не используйте, например, цикл FOR по курсору, если достаточно выполнить оператор UPDATE. Если задачу нельзя решить в SQL, попытайтесь решить ее в PL/SQL. Никогда не пишите внешнюю процедуру на языке Java или С, разве что задачу нельзя решить в PL/SQL или реализация на языке С существенно повышает производительность. Если по техническим причинам задачу нельзя решить с помощью PL/SQL, попробуйте решить ее на языке Java. Однако использование Java требует дополнительных ресурсов Ч памяти, процессорного времени и времени на первоначальный запуск виртуальной машины JVM. Использование PL/SQL также требует дополнительных ресурсов, но они уже выделены, ничего дополнительно запускать не надо. Теме не менее ряд задач нельзя решить с помощью языка PL/SQL, а при использовании Java они решаются элементарно. Ниже представлены полезные фрагменты Javaкода, используемые мной в повседневной практике. Это, конечно, не исчерпывающий список, а лишь вершина айсберга. В приложении А примеры использования языка Java в Oracle рассмотрены более широко.
Генерация списка файлов каталога Пакет UTL_FILE, который мы уже несколько раз использовали по ходу изложения, хорошо справляется с чтением и записью текстовых файлов. Очень часто, однако, необходимо обработать все файлы в указанном каталоге. Этого пакет не позволяет сделать. Для получения списков файлов каталога нет встроенных методов ни в SQL, ни в PL/SQL. На Java его очень легко получить. Вот как это делается: tkyte@TKYTE816> create global temporary table DIR_LIST 2 (filename varchar2(255)) 3 on commit delete rows 4/ Table created. В этой реализации я решил использовать для возвращения результатов из хранимой процедуры на Java временную таблицу. Я считаю этот метод наиболее удобным, потому что он позволяет в дальнейшем легко сортировать список и выбирать файлы с нужными именами. Необходим следующий фрагмент Java-кода: tkyte@TKYTE816> create or replace 2 and compile java source named "DirList" 3 as 4 import java.io.*;
5 import java.sql.*;
6 7 public>
13 String[] list = path.list();
14 String element;
15 16 for(int i = 0;
i < list.length;
i++) 17 { 18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME) 20 VALUES (:element) };
21 ) 22 } 23 24 ) 25 / Java created. Я решил использовать SQLJ, чтобы сократить программу. Подключение к базе данных уже выполнено, поэтому реализация с помощью интерфейса JDBC потребовала лишь нескольких дополнительных строк кода. Но с помощью препроцессора SQLJ выполнять SQL-операторы в Java так же просто, как и в PL/SQL. Теперь, конечно же, необходимо создать спецификацию вызова: tkyte@TKYTE816> create or replace 2 procedure get_dir_list(p_directory in varchar2) 3 as language java 4 name 'DirList.getList(java.lang.String)';
5/ Procedure created. Прежде чем запускать эту процедуру, следует учесть еще один нюанс. Необходимо предоставить процедуре право делать то, что она должна Ч читать список файлов каталога. В данном случае я обладаю правами администратора базы данных, поэтому могу предоставить соответствующие привилегии сам себе, но обычно приходится обращаться с соответствующим запросом к администратору. Если помните, во введении к этой главе я писал: "... Java-код всегда работает с правами владельца ПО Oracle, поэтому хранимая процедура на Java при предоставлении соответствующих привилегий может переписать файл параметров инициализации сервера, INIT.ORA (или другие, еще более важные файлы, например файлы данных)." Сервер Oracle защищается от этого следующим образом: для выполнения небезопасных действий необходимо явно получить соответствующую привилегию. Попытавшись использовать эту процедуру до получения необходимых привилегий, мы получим следующее сообщение об ошибке: tkyte@TKYTE816> exec get_dir_list('с:\temp');
BEGIN get_dir_list<'c:\temp');
END;
* ERROR at line 1:
Глава ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission c:\temp read) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE)) ORA-06512: at "TKYTE.GET_DIR_LIST", line 0 ORA-06512: at line 1 Поэтому предоставим себе право получать список файлов в соответствующем каталоге: tkyte@TKYTE816> begin 2 dbms_java.grant_permission 3 (USER, 4 'java.io.FilePermission', 5 'c:\temp', 6 'read');
7 end;
8/ PL/SQL procedure successfully completed. И можно выполнять процедуру: tkyte@TKYTE816> exec get_dir_list('c:\temp');
PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from dir_list where rownum < 5;
FILENAME a.sql abc.dat activation activation8i.zip Соответствующие права доступа определяются спецификацией Java2 Standard Edition (J2SE) и подробно описаны на странице security/Permission.html. В приложении А мы подробно рассмотрим пакет DBMS_JAVA и его использование. Есть еще один нюанс, который необходимо учитывать. Oracle 8.1.6 Ч первая версия СУБД Oracle, поддерживающая систему прав доступа, задаваемую спецификацией J2SE. В Oracle 8.1.5 для этого приходилось использовать роли. К сожалению, роль была ровно одна: JAVASYSPRIV. Ее использование будет подобно предоставлению роли администратора базы данных каждому пользователю только потому, что ему необходимо создать представление, Ч это слишком мощная роль для выполнения такого простого действия. При наличии роли JAVASYSPRIV можно делать все, что угодно. Будьте осторожны при использовании этой роли в версии 8.1.5 и постарайтесь перейти на следующие версии, где принята более избирательная модель привилегий.
Выполнение команды ОС Если бы я получал десятицентовую монету всякий раз, отвечая на вопрос о том, как выполнить команду ОС! До появления поддержки языка Java в СУБД, это действитель Хранимые процедуры на языке Java но было сложно. Теперь же это почти тривиально. Есть, вероятно, сотни способов сделать это, но следующий фрагмент кода работает вполне удовлетворительно: tkyte@TKYTE816> create or replace and compile 2 java source named "Util" 3 as 4 import java.io.*;
5 import java.lang.*;
6 7 public>
13 int rc = -1;
14 15 try 16 { 17 Process p = rt.exec(args[0]);
18 19 int bufSize = 4096;
20 BufferedInputStream bis = 21 new BufferedInputStream(p.getInputStream(), bufSize);
22 int len;
23 byte buffer[] = new byte[bufSize];
24 25 // Выдаем то, что получено программой 26 while ((len = bis.read(buffer, 0, bufSize)) != -1) 27 System.out.write(buffer, 0, len);
28 29 rc = p.waitFor();
30 } 31 catch (Exception e) 32 { 33 e.printStackTrace();
34 rc = -1;
35 } 36 finally 37 { 38 return rc;
39 } 40 } 41 } 42 / Java created. Он позволяет выполнить любую программу и получить ее результаты либо в файле трассировки на сервере, либо, при использовании средств пакета DBMS_JAVA, в буфере пакета DBMS_OUTPUT. Это, однако, весьма мощное средство Ч при наличии соответствующих привилегий с его помощью можно выполнять любую команду от име 1380 Глава ни пользователя Ч владельца ПО Oracle. В данном случае я хочу иметь возможность получить список процессов с помощью утилиты /usr/bin/ps в ОС UNIX и \bin\tlist.exe в Windows. Для этого мне необходимы две привилегии: tkyte@TKYTE816> BEGIN 2 dbms_java.grant_permission 3 (USER, 4 'java.io.FilePermission', 5 Ч '/usr/bin/ps', -- для UNIX 6 c:\bin\tlist.exe', -- для WINDOWS 7 'execute');
8 9 dbmsiava.grant_permission 10 (USER;
11 'java.lang.RuntimePermission', 12 '*', 13 'writeFileDescriptor');
14 end;
15 / PL/SQL procedure successfully completed. В вашей системе может отсутствовать утилита tlist.exe. Она входит в состав набора инструментальных средств Windows Resource Toolkit и доступна не во всех Windomсистемах. Этот пример просто показывает, что можно сделать, Ч отсутствие доступа к tlist.exe не помешает демонстрации. Этот метод можно использовать дм выполнения любой программы. Учтите, однако, что нужно быть внимательным, предоставляя права на выполнение программ с помощью пакета DBMS_JAVA. Например, предоставление права на выполнение программы c:\winnt\system32\cmd.exe фактически означает разрешение выполнять ВСЕ программы, что очень опасно. Первый вызов dbms_java.grant_permission позволяет запускать одну конкретную программу. При желании можно рискнуть и указать вместо имени программы символ *. Это позволит выполнять любые программы. Я не думаю, однако, что это разумно;
явно перечисляйте полные имена программ, в надежности которых вы уверены. Вторая привилегия позволяет генерировать результаты во время выполнения. Здесь придется использовать метасимвол *, поскольку я не знаю точно, куда именно будут выдаваться результаты (в стандартный выходной поток, stdout, например, или куда-нибудь еще). Теперь необходимо создать уровень связывания: tkyte@TKYTE816> create or replace 2 function RUN_CMD(p_cmd in varchar2) return number 3 as 4 language java 5 name 'Util.RunThis(java.lang.String[]) return integer';
6/ Function created. tkyte@TKYTE816> create or replace procedure rc( 2 as Хранимые процедуры на языке Java 3 x number;
4 begin 5 x := run_cmd(p_cmd);
6 if (x о 0) 7 then 8 raise program_error;
9 end if;
10 end;
11 / Procedure created. Здесь я создал еще один уровень абстракции выше функции связывания, чтобы можно было выполнять программу как процедуру. Давайте посмотрим, как это работает: tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output(1000000) PL/SQL procedure successfully completed. tkyte@TKYTE816> exec rc('C:\WINNT\system32\cmd.exe /c dir') Volume in drive С has no label. Volume Serial Number is F455-B3C3 Directory of C:\oracle\DATABASE 05/07/2001 10:13a
Получение времени с точностью до миллисекунд Примеры становятся все меньше, короче и выполняются быстрее. Это я и хочу подчеркнуть. С помощью небольших фрагментов Java-кода, примененных в соответствующих местах, можно существенно расширить функциональные возможности. В Oracle 9i эта функция станет избыточной, поскольку эта версия поддерживает временные отметки с точностью менее секунды. Но при необходимости такая точность измерения времени достижима и в предыдущих версиях: tkyte@TKYTE816> c r e a t e or replace java source 2 named "MyTimestamp" 3 as 4 5 6 7 8 9 10 11 12 13 14 Глава import java.lang.String;
import java.sql.Timestamp;
public>
} };
/ Java created. tkyte@TKYTE816> create or replace function my_timestamp return varchar2 2 as language java 3 name 'MyTimestamp.getTimestamp() return java.lang.String';
4/ Function created. tkyte@TKYTE816> select my_timestamp, 2 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual 3/ MY)TIMESTAMP 2001-03-27 19:15:59.688 TO_CHAR(SYSDATE,'YY 2001-03-27 19:15: Возможные ошибки Большинство сообщений об ошибках, которые вы получите при использовании хранимых процедур на Java, связаны с компиляцией кода и несоответствием типов параметров. Некоторые из наиболее типичных сообщений об ошибках рассмотрены ниже.
ORA-29549 Java Session State Cleared По ходу разработки можно столкнуться с сообщениями следующего вида: s e l e c t my_timestamp, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual * ERROR at l i n e 1: ORA-29549: c l a s s TKYTE.MyTimestamp has changed, Java session s t a t e cleared Это означает, что использованный в сеансе класс был перекомпилирован (скорм всего Ч вами же). Вся связанная с этим классом информация о состоянии потеряна. Достаточно повторно выполнить оператор, при выполнении которого было выдано это сообщение, и информация о состоянии обновится. По этой причине следует избегать повторной загрузки Java-классов в действующе* производственной системе. После этого использующий Java-класс сеанс при обращении к нему получит такое сообщение об ошибке.
Хранимые процедуры на языке Java Ошибки прав доступа Мы уже знакомы с таким сообщением: E R R at l i n e 1: RO ORA-29532: Java c a l l terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission c:\temp read) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE IPolicyTableProxy(TKYTE)) ORA-06512: at "TKYTE.GET_DIR_LIST", l i n e 0 ORA-06512: at l i n e 1 К счастью, в тексте сообщения об ошибке явно указано, какие привилегии необходимо получить, чтобы вызов был успешным. Обладающий соответствующими привилегиями пользователь должен предоставить вам эти привилегии с помощью процедуры GRANT_PERMISSION пакета DBMS_JAVA.
ORA-29531 no method X in> create or replace 2 function RUN_CMD(p_cmd in varchar2) return number 3 as 4 language java 5 name 'Util.RunThis(String[]) return integer';
7/ Function created. будет выдано сообщение об ошибке ORA-29531. Обратите внимание, что в списке параметров функции Util.RunThis, я указал тип данных String, а не java.lang.String. tkyte@TKYTE816> exec rc('c:\winnt\system32\cmd.exe /c d i r ' ) java.lang.NullPointerException at oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions. java) at oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions.java) BEGIN RC('c:\winnt\system32\cmd.exe /c d i r ' ) ;
END;
* E R R at l i n e 1: RO ORA-29531: no method RunThis in>
Резюме В этой главе вы узнали, как реализовать хранимые процедуры на языке Java. Это не означает, что весь существующий код на языке PL/SQL необходимо переписать в виде хранимых процедур на Java. Но если, программируя на PL/SQL, вы столкнетесь с неразрешимой проблемой, что обычно происходит при необходимости выйти за пределы базы данных и обеспечить взаимодействие с операционной системой, попробуйте решить задачу с помощью языка Java. Благодаря полученным в этой главе сведениям вы сможете передать основные типы данных SQL, в том числе массивы, с уровня PL/SQL на уровень Java-методов и получить результаты. Я представил несколько полезных фрагментов Java-кода, которые можно использовать непосредственно;
обратившись к документации по языку Java, вы обнаружите десятки других фрагментов, незаменимых при разработке приложений. При осторожном использовании, программирование на Java может существенно расширить возможности разработки приложений.
Использование объектнореляционных средств Начиная с версии Oracle 8, в базах данных сервера Oracle могут использоваться объектно-реляционные средства. Выходя за пределы стандартных скалярных типов NUMBER, DATE и строк символов, объектно-реляционные средства Oracle позволяют расширить набор поддерживаемых типов данных. Можно создавать собственные типы данных, включающие: Х атрибуты, каждый из которых может быть скалярной величиной или набором (массивом) других объектных/скалярных типов;
Х методы для работы с данными этого типа;
Х статические методы;
Х необязательный метод сравнения, используемый для сортировки и сравнения данных. Затем этот новый тип можно использовать для создания таблиц, столбцов таблиц, представлений или для расширения возможностей языков SQL и PL/SQL. Вновь созданный пользовательский тип данных можно использовать точно так же, как и базовый тип данных DATE. В этой главе я продемонстрирую, как использовать объектно-реляционные средства сервера Oracle. Будет также показано, как их не следует использовать. Я буду описывать компоненты этой технологии последовательно. Однако эту главу нельзя считать полным обзором всех возможностей объектно-реляционных средств Oracle. Этому посвящено 200-страничное руководство Oracle Application Developer's Guide Ч Object-Relational Features. Цель данной главы Ч показать, когда и как использовать эти возможности.
Глава Объектно-реляционные средства Oracle можно использовать во многих языках. При программировании на Java Ч через интерфейс JDBC, на Visual Basic Ч с помощью компонентов 0 0 4 0 (Oracle Objects for Ole). При программировании с помощью библиотеки OCI (Oracle Call interface), языка PL/SQL и прекомпилятора Pro*C очень легко использовать соответствующие функциональные возможности. Корпорация Oracle предоставляет различные инструментальные средства, упрощающие использование объектно-реляционных возможностей сервера в этих языках. Например, при программировании на Java/JDBC можно использовать Oracle JPublisher Ч утилиту, автоматически генерирующую Java-классы, представляющие объектные типы базы данных, наборы и PL/SQL-пакеты (это генератор кода, который позволяет сопоставлять сложные типы SQL типам языка Java). Библиотека OCI поддерживает на стороне клиента встроенный кэш объектов, используемый для эффективного управления и обработки объектов. Прекомпилятор Pro*C включает средство OTT (Object Type Translator Ч транслятор объектных типов) для генерации структур (struct) языка С/С++, соответствующих объектным типам. Я не буду касаться использования этих языков и средств Ч все это детально описано в документации сервера Oracle. Все внимание будет сосредоточено на создании объектных типов в базе данных.
В каких случаях используются объектнореляционные средства Я использую объектно-реляционные средства сервера Oracle преимущественно для естественного расширения возможностей языка PL/SQL. Объектный тип Ч прекрасный способ добавить в PL/SQL новые функциональные возможности аналогично тому, как классы позволяют сделать это в С++ или Java. В следующем разделе мы рассмотрим соответствующий пример. Объектные типы можно также использовать для стандартизации. Я могу создать новый тип, скажем, ADDRESS_TYPE, который инкапсулирует определение адреса или отдельных компонентов, из которых состоит адрес. Можно даже добавить служебные функции (методы) для этого типа, которые, например, возвращают адрес в формате, подходящем для распечатки на почтовых наклейках. Теперь при создании таблицы, в которой должны содержаться данные об адресе, можно просто указать столбец типа ADDRESS_TYPE. Атрибуты адреса при этом будут добавлены в таблицу автоматически. Пример такого использования тоже будет рассмотрен. Объектные типы можно использовать для объектно-реляционного представления чисто реляционных, по сути, данных. Т.е. можно взять пару таблиц EMP/DEPT и построить объектно-реляционное их представление, в котором каждая строка таблицы DEPT будет содержать набор объектов ЕМР. Не соединяя таблицы ЕМР и DEPT, я смогу обратиться к объектному представлению DEPT и получить информацию из таблиц DEPT и ЕМР в одной строке. В следующем разделе мы рассмотрим и этот пример. Объектные типы можно также использовать для создания объектных таблиц. Преимущества и недостатки объектных таблиц рассматривались в главе 6. Объектные таблицы содержат множество скрытых столбцов;
при использовании этих таблиц возникают побочные эффекты, и происходят различные "чудеса". Кроме того, обычно (для Использование объектно-реляционных средств множества различных целей) необходимо строго реляционное представление данных (в частности, для утилит и средств создания отчетов, которые "не понимают" объектные типы). Именно поэтому объектные таблицы я стараюсь не использовать. Я использую объектные представления реляционных данных, что в конечном итоге дает те же возможности, что и объектные таблицы. Однако при этом я контролирую все аспекты физического хранения данных. Поэтому тему объектных таблиц я здесь подробно рассматривать не буду.
Как работают объектно-реляционные средства В этом разделе мы рассмотрим использование объектно-реляционных средств Oracle для решения следующих задач: Х расширение набора стандартных типов данных в системе;
Х естественное расширение возможностей языка PL/SQL;
Х создание объектно-реляционных представлений реляционных, по сути, данных.
Добавление новых типов данных в систему Начнем с простого: типа данных ADDRESS_TYPE. Рассмотрим синтаксис соответствующих конструкций, их возможности, побочные эффекты, с которыми можно столкнуться, и т.п. Для начала создадим простой тип: tkyte@TKYTE816> create or replace type Address_Type 2 as object 3 (street_addrl varchar2(25), 4 street_addr2 varchar2(25), 5 city varchar2(30), 6 state varchar2(2), 7 zip_code number 8) 9/ Type created. Это простейшая разновидность оператора CREATE TYPE. По ходу работы над примером мы добавим в него дополнительные конструкции. Этот тип состоит только из заданных скалярных типов, не имеет методов, специфических функций сравнения Ч ничего "выдающегося". Зато его можно сразу же использовать в таблицах и в PL/SQLкоде: tkyte@TKYTE816> create table people 2 (name varchar2(10), 3 home_address address_type, 4 work_address address_type 5) Глава / Таblе created. tkyte@TKYTE816> declare 2 l_home_address address_type;
3 l_work_address address_type;
4 begin 5 l_home_address := Address_Type('123 Main S t r e e t ', n u l l, 6 'Reston', 'VA', 45678);
7 l_work_address :=Address_Type('l OracleWay', n u l l, 8 'Redwood', 'CA', 23456);
9 10 insert into people 11 (name, home_address, work_address) 12 values 13 ('TomKyte', l_home_address, l_work_address);
14 end;
15 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from people;
NAME HOME_ADDRESS(STREET_WORK_ADDRESS(STREET_ Tom Kyte ADDFESS_TYPE('123 Ma ADDRESS_TYPE('1 Orac in Street', NULL, 'R le Way', NULL, 'Redw eston', 'VA', 45678) ood', 'CA', 23456) Как видите, использовать этот новый тип в операторе CREATE TABLE так же легко, как, например, тип NUMBER. Кроме того, объявлять переменные типа ADDRESS_TYPE в PL/SQL тоже просто: в языке PL/SQL новые типы данных можно использовать сразу же. Новые функциональные возможности использованы в приведенном PL/SQL-коде в строках с 5 по 8. Здесь вызывается конструктор объекта нового типа. Стандартный конструктор типа позволяет задать значения для всех атрибутов объектного типа. По умолчанию создается только один стандартный конструктор, при вызове которого надо указать значения для всех атрибутов типа. В разделе "Использование типов для расширения возможностей PL/SQL" мы рассмотрим, как создавать специфические конструкторы с помощью статических функций-членов. Созданные переменные типа ADDRESS_TYPE после инициализации можно использовать в качестве связываемых переменных в SQL-операторах, как было показано выше. Достаточно просто вставить значения столбцов NAME, HOME_ADDRESS и WORK_ADDRESS. Несложный SQL-запрос позволяет получить эти данные. С помощью SQL можно обращаться не только к столбцу HOME_ADDRESS, но и к каждому из компонентов HOME_ADDRESS. Например: tkyte@TKYTE816> select name, home_address.state, work_address.state 2 from people 3/ select name, home_address.state, work_address.state ERROR at line 1: ORA-00904: invalid column name Использование объектно-реляционных средств tkyte@TKYTE816> select name, P.home_address.state, P.work_address.state 2 from people P 3/ NAME Tom Kyte HOME_ADDRESS.STATE VA WORK_ADDRESS.STATE CA Я продемонстрировал неправильный и правильный способ. Первый пример Ч это то, что обычно пишут разработчики. Запрос, конечно, не работает. Чтобы обратиться к компонентам объектного типа, необходимо использовать корреляционное имя, как сделано во втором запросе. В нем я задал для таблицы PEOPLE псевдоним P (можно использовать любой допустимый идентификатор, включая слово PEOPLE). Если возникает необходимость сослаться на отдельные компоненты адреса, я использую псевдоним. Как же выглядит в действительности таблица PEOPLE? То, что показывает сервер Oracle, весьма отличается от того, что используется на самом деле, как можно догадаться, прочитав главу 6 и изучив примеры с вложенной или объектной таблицей: tkyte@TKYTE816> desc people Name Null? Type NAME VARCHAR2(10) HOME_ADDRESS ADDRESS_TYPE WORK_ADDRESS ADDRESS_TYPE tkyte@TKYTE816> select name, length 2 from sys.col$ 3 where obj# = (select object_id 4 from user_objects 5 where object_name = 'PEOPLE') 6/ NAME NAME HOME_ADDRESS SYS_NC00003$ SYS_NC00004$ SYS_NC00005$ SYS_NC00006$ SYS_NC00007$ WORK_ADDRESS SYS_NC00009$ SYS_NC00010$ SYS_NC00011$ SYS_NC00012$ SYS_NC00013$ 13 rows selected. Сервер Oracle сообщает, что в таблице Ч три столбца, но в реальном словаре данных их, однако, Ч тринадцать. В нем можно обнаружить скрытые скалярные столбцы. Хотя все не так очевидно и используются скрытые столбцы, применять скалярные объектные типы (без вложенных таблиц) подобным образом несложно. С такого рода неочеLENGTH 10 1 25 25 30 2 22 1 25 25 30 2 Глава видностью вполне можно смириться. Если использовать опцию SET DESCRIBE утилиты SQL*Plus, можно заставить эту утилиту показывать всю структуру объектного типа: tkyte@TKYTE816> set describe depth all tkyte@TKYTE816> desc people Name Null? NAME HOME_ADDRESS STREET_ADDR1 STREET_ADDR2 CITY STATE ZIP_CODE WORK_ADDRESS STREET_ADDR1 STREET_ADDR2 CITY STATE ZIP_CODE Type VARCHAR2(10) ADDRESS_TYPE VARCHAR2(25) VARCHAR2(25) VARCHAR2(30) VARCHAR2(2) NUMBER ADDRESS_TYPE VARCHAR2(25) VARCHAR2(25) VARCHAR2(30) VARCHAR2(2) NUMBER Это очень удобно для определения поддерживаемых атрибутов. Теперь давайте немного усложним тип ADDRESS_TYPE: добавим функцию выдачи адреса в удобном формате в виде одного поля. Для этого можно добавить в тело типа соответствующую функцию-член: tkyte@TKYTE816> alter type Address_Type 2 BEPLACE 3 as object 4 (street_addrl varchar2(25), 5 street_addr2 varchar2(25), 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2 10 ) 11 / Type altered. tkyte@TKYTE816> create or replace type body Address_Type 2 as 3 member function toString return varchar2 4 is 5 begin 6 if (street_addr2 is not NULL) 7 then 8 return street_addrl || chr(10) || 9 street_addr2 || chr(10) || 10 city || ', ' || state || ' ' || zip_code;
11 else 12 return street_addrl || chr(10) || 13 city || ', ' || state || ' ' || zip_code;
Использование объектно-реляционных средств 14 15 16 end if;
end;
end;
/ Type body created. tkyte@TKYTE816> s e l e c t name, p.home_address.toString() 2 from people P 3/ NAME P.HOME_ADDRESS.TOSTRING() Tom Kyte 123 Main Street Reston, VA 45678 Вот и первый пример метода объекта. Каждый метод вызывается с неявным параметром SELF. Можно добавить этот префикс к атрибутам STREET_ADDR1, STREET_ADDR2 и т.д.: SELF.street_addrl || chr(10) || SELF.street_addr2... но он и так добавляется неявно. Тут вы вполне резонно можете заметить: "Ведь все это можно сделать с помощью реляционной таблицы и PL/SQL-пакета". Это действительно так. Однако использование объектного типа с методами, как показано выше, дает определенные преимущества. Х Обеспечивается более совершенный механизм инкапсуляции. Тип ADDRESS_TYPE инкапсулирует и поддерживает адрес, со всеми его атрибутами и функциональными возможностями. Х Методы более тесно привязываются к специфическим данным. Это очень важный момент. Если используются скалярные столбцы и PL/SQL-функция, форматирующая их для вывода адреса на печать, эту функцию можно вызвать с любыми данными. Можно передать значение столбца EMPLOYEE_NUMBER в качестве почтового индекса, фамилию Ч вместо названия улицы и т.д. Привязывая метод к атрибутам, мы гарантируем, что метод TOSTRING может работать только с данными адреса. Пользователи, вызывающие этот метод, не должны задумываться о передаче соответствующих данных Ч они "уже здесь". Однако объектный тип имеет один недостаток: в Oracle 8i он мало поддается изменениям. С помощью оператора ALTER можно добавлять новые методы, но нельзя ни удалить существующие, ни добавить дополнительные атрибуты после создания таблицы, использующей этот тип (да и удалить добавленные методы невозможно). Единственное, что можно делать, Ч это добавлять методы или изменять их (тело типа). Другими словами, развитие схемы при использовании объектных типов затруднено. Если со временем окажется, что для объекта ADDRESS_TYPE необходим еще один атрибут, придется пересоздавать объекты, в которые этот тип встроен. Это не относится к объектным типам, которые не используются для столбцов таблиц базы данных или в операторах Глава CREATE TABLE OF TYPE. Другими словами, если объектные типы используются исключительно в объектных представлениях или для расширения возможностей PL/SQL (как описано в следующих разделах), этой проблемой можно пренебречь. С объектными типами также связаны специфические методы MAP и ORDER. Они используются при сортировке, сравнении или группировке экземпляров объектных типов. Если у объектного типа нет функции MAP или ORDER, при попытке выполнения этих операций вы получите следующее сообщение об ошибке: tkyte@TKYTE816> select * from people order by home_address;
select * from people order by home_address * ERROR at line 1: ORA-22950: cannot ORDER objects without MAP or ORDER method tkyte@TKYTE816> select * from people where home_address > work_address;
select * from people where home_address > work_address ERROR at line 1: ORA-22950: cannot ORDER objects without MAP or ORDER method tkyte@TKYTE816> select * from people where home_address = work_address;
no rows selected Упорядочивать данные объектного типа, использовать их при поиске "больших" или "меньших" значений будет невозможно. Значения можно будет сравнивать только на равенство. При этом сервер Oracle выполняет сравнение по всем атрибутам, чтобы узнать, не совпадают ли они. Чтобы можно было выполнять все остальные операции, необходимо добавить метод MAP или метод ORDER (объектный тип может иметь либо метод MAP, либо метод ORDER, но не оба одновременно). Метод MAP Ч это функция, работающая с одним экземпляром объекта и возвращающая значение одного из скалярных типов, которое сервер Oracle будет использовать для сравнения с другими однотипными объектами. Например, если объектный тип представляет точку на плоскости с координатами X и Y, функция MAP может возвращать квадратный корень из (X*X+Y*Y) Ч расстояние от начала координат. Метод ORDER принимает два экземпляра объекта Ч SELF и объект для сравнения с SELF. Метод ORDER возвращает 1, если SELF больше этого объекта, -1, если SELF меньше другого объекта или 0, если объекты равны. Метод MAP предпочтительнее, поскольку работает намного быстрее и даже может вызываться в параллельных запросах (метод ORDER нельзя использовать при распараллеливании). Метод MAP достаточно вызвать для экземпляра объекта один раз, и после этого сервер Oracle может использовать это значение при сортировке. Метод ORDER при сортировке большого множества, возможно, придется вызывать сотни или тысячи раз с одними и теми же данными. Для созданного ранее типа ADDRESS_TYPE я продемонстрирую оба метода. Сначала Ч метод ORDER tkyte@TKYTE816> alter type Address_Type 2 REPLACE 3 as object 4 (street_addrl varchar2(25), 5 street_addr2 varchar2(25), Использование объектно-реляционных средств 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2, 10 order member function order_function(compare2 in Address_type) 11 return number 12 ) 13 / Type a l t e r e d. tkyte@TKYTE816> c r e a t e or replace type body Address_Type 2 as 3 member function toString return varchar2 4 is 5 begin 6 if (street_addr2 is not NULL) 7 then 8 return street_addrl || chr(10) || 9 street_addr2 || chr(10) || 10 city || ', ' || state || ' ' || zip_code;
11 else 12 return street_addrl || chr(10) || 13 city || ', ' || state || ' ' || zip_code;
14 end if;
15 end;
16 17 order member function order_function(compare2 in Address_type) 18 return number 19 is 20 begin 21 if (nvl(self.zip_code,-99999) <> nvl(compare2.zip_code,-99999)) 22 then 23 return sign(nvl(self.zip_code,-99999) 24 - nvl(compare2.zip_code,-99999));
25 end if;
26 if (nvl(self.city,chr(0)) > nvl(compare2.city,chr(0))) 27 then 28 return 1;
29 elsif (nvl(self.city,chr(0)) 32 end if; 33 if (nvl(self.street_addrl,chr(0)) > 34 nvl(compare2.street_addr1,chr(0))) 35 then 36 return 1; 37 elsif (nvl(self.street_addrl,chr(O)) < 38 nvl(compare2.street_addr1,chr(0))) 39 then 40 return -1; 41 end if; 42 if (nvl(self.street addr2,chr(0)) > Глава 43 nvl (compare2. street_addr2, chr (0))) 44 then 45 return 1; 46 elsif (nvl(self.street_addr2,chr(0)) < 47 nvl(compare2.street_addr2,chr(0))) 48 then 49 return -1; 50 end if; 51 return 0; 52 end; 53 end; 54 / Type body created. Этот метод сравнивает два адреса по следующему алгоритму. 1. Если значение почтового индекса (ZIP_CODE) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше Ч 1. 2. Если значение города (CITY) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше Ч 1. 3. Если значение первого компонента адреса (STREET_ADDR1) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше Ч 1. 4. Если значение второго компонента адреса (STREET_ADDR2) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше Ч 1. 5. Иначе вернуть 0 (адреса совпадают). Как видите, при сравнении приходится постоянно проверять, не переданы ли значения NULL, и т.п. В результате метод получился достаточно большим и сложным. Он, несомненно, неэффективен. Задумав написать метод ORDER, попробуйте использовать вместо него метод MAP. Представленное выше сравнение лучше переписать в виде метода MAP. Учтите, что если вы уже изменили тип, добавив в него представленный выше метод ORDER, придется удалить таблицу, зависящую от этого типа, удалить сам тип и создать все заново. Методы нельзя удалять Ч их можно только добавлять с помощью оператора ALTER TYPE, а нам надо избавиться от существующего метода ORDER. Полный пример должен был бы включать операторы DROP TABLE PEOPLE, DROP TYPE ADDRESS_TYPE и CREATE TYPE и лишь затем Ч следующий оператор ALTER TYPE: tkyte@TKYTE816> alter type Address_Type 2 REPLACE 3 as object 4 (street_addrl varchar2(25), 5 street_addr2 varchar2(25), 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2, 10 шар member function mapping_function return varchar Использование объектно-реляционных средств 11 ) 12 / Type altered. tkyte@TKYTE816> create or replace type body Address_Type 2 as 3 member function toString return varchar2 4 is 5 begin 6 if (street_addr2 is not NULL) 7 then 8 return street_addrl || chr(10) || 9 street_addr2 || chr(10) || 10 city || ', ' || state || ' ' || zip_code; 11 else 12 returnstreet_addr1 || chr(10) || 13 city || ', ' || state || ' ' || zip_code; 14 end if; 15 end; 16 17 map member function mapping_function return varchar2 18 is 19 begin 20 returnto_char(nvl(zip_code,0), 'fm00000') || 21 lpad(nvl(city,' ' ), 30) || 22 lpad(nvl(street_addrl,' ' ), 25) || 23 lpad(nvl(street_addr2,' ' ), 25); 24 end; 25 end; 26 / Type body created. Возвращая строку фиксированной длины, содержащую значение ZIP_CODE, затем Ч CITY и поля STREET_ADDR, можно переложить задачу сравнений и сортировки на сервер Oracle. Прежде чем переходить к другим вариантам использования объектных типов (я больше всего люблю использовать их для расширения возможностей языка PL/SQL), хочу представить еще один тип наборов Ч VARRAY. В главе 6 мы рассматривали вложенные таблицы и их реализацию. Было показано, что они реализуются в виде пары родительской и дочерней таблиц, со скрытым суррогатным ключом в родительской таблице и столбцом NESTED_TABLE_ID в порожденной. Массив VARRAY во многом похож на вложенную таблицу, но реализуется абсолютно иначе. Массив VARRAY (или вложенная таблица) используется для хранения массива данных, связанных с одной строкой. Например, если необходимо хранить в таблице PEOPLE дополнительные таблицы (скажем, массив прежних адресов проживания, начиная с самого старого), можно сделать следующее: tkyte@TKYTE816> create or replace type Address_Array_Type 2 as varray(25) of Address_Type 3/ Глава Type created. tkyte@TKYTE816> alter table people add previous_addresses Address_Array_Type 2/ Table altered. tkyte@TKYTE816> set describe depth all tkyte@TKYTE816> desc people Name Null? NAME HOME_ADDRESS STREET_ADDR1 STREET_ADDR2 CITY STATE ZIP_CODE METHOD MEMBER FUNCTION TOSTRING RETURNS VARCHAR2 METHOD MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2 WORK_ADDRESS ADDRESS_TYPE STREET_ADDR1 VARCHAR2(25) STREET_ADDR2 VARCHAR2(25) CITY VARCHAR2(30) STATE VARCHAR2(2) ZIP_CODE NUMBER METHOD MEMBER FUNCTION TOSTRING RETURNS VARCHAR2 METHOD MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2 EREVIOUS_ADDRESSES ADDRESS_ARRAY_TYPE STREET_ADDR1 VARCHAR2(25) STREET_ADDR2 VARCHAR2(25) CITY VARCHAR2(30) STATE VARCHAR2(2) ZIP_CODE NUMBER METHOD MEMBER FUNCTION TOSTRING RETURNS VARCHAR2 METHOD MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR Type VARCHAR2(10) ADDRESS_TYPE VARCHAR2(25) VARCHAR2(25) VARCHAR2(30) VARCHAR2(2) NUMBER Использование объектно-реляционных средств Итак, теперь в нашей таблице можно хранить до 25 предыдущих адресов. Вопрос в том, что при этом происходит "за кадром"? Обратившись к словарю данных, можно увидеть следующее: tkyte@TKYTE816> select name, length 2 from sys.col$ 3 where obj# = (select object_id 4 from user_objects 5 where object_name = 'PEOPLE') 6/ NAME NAME HOME_ADDRESS SYS_NC00003$ SYS_NC00004$ SYS_NC00005$ SYS_NC00006$ SYS_NC00007$ WORK_ADDRESS SYS_NC00009$ SYS_NC00010$ SYS_NC00011$ SYS_NC00012$ SYS_NC00013$ PREVIOUS_ADDRESSES 14 rows selected. Сервер Oracle добавил столбец размером 2940 байт для реализации массива VARRAY. Данные массива VARRAY будут храниться тут же, в самой строке. При этом возникает интересный вопрос: что произойдет, если размер массива станет больше 4000 байт (это максимальный размер структурированного столбца, поддерживаемый сервером Oracle)? Если удалить столбец и пересоздать его как VARRAY(50), произойдет следующее: tkyte@TKYTE816> alter table people drop column previous_addresses 2/ Table altered. tkyte@TKYTE816> create or replace type Address_Array_Type 2 as varray(50) of Address_Type 3/ Type created. tkyte@TKYTE816> alter table people add previous_addresses Address_Array_Type 2/ Table altered. tkyte@TKYTE816> select object_type, object_name, 2 decode(status,'INVALID','*','') 3 tablespace_name 4 from user_objects a, user_segments b status, LENGTH 10 1 25 25 30 2 22 1 25 25 30 2 22 1400 Глава 5 where a.object_name - b.segment_name (+) 6 order by object_type, object_name 7/ OBJECT_TYPE LOB TABLE TYPE TYPE BODY OBJECT_NAME SYS_LOB0000026301C00014$$ PEOPLE ADDRESS_ARRAY_TYPE ADDRESS_TYPE ADDRESS_TYPE S TABLESPACE_NAME DATA DATA tkyte@TKYTE816> select name, length 2 from sys.col$ 3 where obj# - (select object_id 4 from user_objects 5 where object_name = 'PEOPLE') 6/ NAME NAME HOME_ADDRESS SYS_NC00003$ SYS_NC00004$ SYS_NC00005$ SYS_NC00006$ SYS_NC00007$ WORK_ADDRESS SYS_NC00009$ SYS_NC00010$ SYS_NC00011$ SYS_NC00012$ SYS_NC00013$ PREVIOUS_ADDRESSES 14 rows selected. Как видите, теперь сервер Oracle автоматически создал большой объект. Если объем данных в массиве VARRAY не превышает примерно 4000 байтов, данные хранятся вместе со строкой (inline). Если же объем данных больше, массив VARRAY выносится из строки в сегмент большого объекта (как и любой большой объект). Массивы VARRAY либо хранятся в строке как столбец типа RAW, либо (при достаточно большом объеме) как большой объект. Дополнительных ресурсов для поддержки данных типа VARRAY (по сравнению с вложенной таблицей) надо очень мало, что делает массив VARRAY привлекательным методом хранения повторяющихся данных. Поиск по массиву VARRAY можно реализовать, преобразовав его данные в таблицу, что сделает его не менее гибким, чем вложенные таблицы: tkyte@TKYTE816> update people 2 set previous_addresses = Address_Array_Type( 3 Address_Type('312 Johnston Dr', null, LENGTH 10 1 25 25 30 2 22 1 25 25 30 2 22 Использование объектно-реляционных средств 4 5 6 7 8 1 row updated. 'Bethlehem', 'PA', 18017), Address_Type('513 Zulema S t ', 'Apartment # 3 ", 'Pittsburg', 'PA', 18123), Address_Type('840 South Frederick S t ', n u l l, 'Alexandria', 'YA', 20654)); tkyte@TKYTE816> select name, prev.city, prev.state, prev.zip_code 2 from people p, table(p.previous_addresses) prev 3 where prev.state = 'PA'; NAME Tom Kyte Tom Kyte CITY Bethlehem Pittsburg ST PA PA ZIP_CODE 18017 Существенное различие состоит в том, что при реализации с помощью вложенной таблицы можно создать индекс по столбцу STATE вложенной таблицы, и оптимизатор этот индекс использовал бы. В данном случае столбец STATE проиндексировать нельзя. Итак, основные отличия вложенных таблиц от массивов переменной длины (VARRAY) представлены в следующей таблице. Вложенная таблица Элементы "массива" не упорядочены. Данные из набора могут возвращаться совсем не в том порядке, в каком они туда вставлялись. VARRAY VARRAY Ч настоящие массивы. Данные после вставки остаются упорядоченными. В рассмотренном ранее примере данные добавлялись в конец массива. Это означает, что самый старый адрес идет в массиве первым, а последний по времени адрес находится в конце массива. При использовании внешней таблицы для упорядочения адресов по давности потребуется дополнительный атрибут. Массивы VARRAY хранятся как столбец типа RAW или как большой объект. При этом для обеспечения их работы требуются минимальные дополнительные ресурсы. Для массивов VARRAY при создании типа задается ограничение на количество хранящихся элементов. Массивы VARRAY необходимо изменять процедурно. Нельзя выполнять операторы вида: INSERT INTO TABLE (SELECT P.PREVIOUS ADDRESSES FROM PEOPLE P) VALUES... как для вложенной таблицы. Для добавления адреса придется использовать процедурный код (см. пример ниже). Вложенные таблицы физически хранятся в виде пары родительской и дочерней таблиц с суррогатными ключами. Вложенные таблицы не имеют ограничения на количество хранящихся элементов. Вложенные таблицы можно изменять (добавлять/изменять/удалять в них данные) с помощью языка SQL. 1402 Глава Вложенная таблица Для получения данных строки при использовании вложенных таблиц необходимо выполнять реляционное соединение. В случае небольших наборов это повлечет чрезмерное использование ресурсов. VARRAY Для получения данных массива VARRAY соединение выполнять не нужно. В случае небольших наборов данные хранятся в самой строке; если же наборы большие в сегменте большого объекта. При обращении к элементам массива VARRAY требуется меньше ресурсов, чем при обращении к вложенной таблице. При изменении же данных массива VARRAY ресурсов требуется больше, чем при изменении вложенной таблицы, поскольку заменять приходится весь массив, а не один элемент. В представленной выше таблице указано, что массив VARRAY нельзя изменять с помощью SQL-операторов с конструкцией TABLE; его надо обрабатывать процедурно. Для изменения столбцов типа VARRAY лучше всего написать хранимую процедуру. Ее код может быть примерно таким: tkyte@TKYTE816> declare 2 l_prev_addresses address_Array_Type; 3 begin 4 select p.previous_addresses into l_prev_addresses 5 from people p 6 where p.name = 'Tom Kyte'; 7 8 l_prev_addresses.extend; 9 l_prev_addresses(l_prev_addresses.count) := 10 Address_Type('123 Main Street', null, 11 'Reston', 'VA', 45678); 12 13 update people 14 set previous_addresses = l_prev_addresses 15 where name = 'Tom Kyte'; 16 end; 17 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select name, prev.city, prev.state, prev.zip_code 2 from people p, table(p.previous_addresses) prev 3/ NAME Tom K y t e Tom Kyte Tom Kyte Tom K y t e CITY Bethlehem Pittsburg Alexandria Reston ST PA PA VA VA ZIP_CODE 18017 18123 20654 Мы рассмотрели преимущества и недостатки использования расширенных типов данных Oracle в таблицах базы данных. Вы должны решить, стоит ли ради возможности Использование объектно-реляционных средств создавать новые типы данных с однозначно реализованными методами обработки и использовать их в определениях столбцов, жертвовать возможностью развивать эти типы со временем (добавлять или удалять атрибуты).* Мы также сравнили использование массивов VARRAY и вложенных таблиц как способов физического хранения данных. Было показано, что массивы VARRAY больше подходят для хранения ограниченного набора упорядоченных элементов, чем вложенные таблицы. Использовать массивы VARRAY очень удобно для хранения списка элементов, которому не требуется отдельная таблица. Избирательное использование новых типов существенно улучшает систему и ее структуру. Использование объектных типов Oracle в качестве типов столбцов таблиц (но не создание объектных таблиц, как было продемонстрировано в главе 6) позволяет обеспечить стандартизацию и вызов процедур (методов) с семантически правильными параметрами. Огорчает только невозможность существенно развивать тип данных схемы после того, как создана хотя бы одна таблица, в которой этот тип используется. Использование типов для расширения возможностей языка PL/SQL Именно в этом объектно-реляционные средства Oracle максимально преуспели. Язык PL/SQL Ч очень гибкий и мощный, как доказывает уже то, что механизм расширенной репликации (Advanced Replication) был написан полностью на PL/SQL еще в версии Oracle 7.1.6. Приложения из набора Oracle Applications (Human Resources Ч управление персоналом, Financial Applications Ч бухгалтерский учет, CRM applications Ч управление ресурсами и т.д.) разработаны в основном на PL/SQL. Хотя это и замечательный язык программирования, встречаются ситуации, когда его базовые возможности требуется расширить (как и в случае языков Java, С, С++ или любых других языков программирования). Это можно сделать с помощью объектных типов. Они добавляют новые функциональные возможности в PL/SQL, как классы Ч в языках Java или С++. В этом разделе я продемонстрирую, как использовать объектные типы для упрощения программирования на PL/SQL. Будет создан тип данных File на основе средств пакета UTL_FILE. UTL_FILE Ч это стандартный пакет, поставляемый в составе сервера Oracle и позволяющий выполнять в PL/SQL операции ввода-вывода (чтение и запись) текстовых данных в файлы на сервере. Он обеспечивает функциональный интерфейс, аналогичный семейству f-функций языка С (fopen, fclose, fread, fwrite и т.д.). Функциональные возможности пакета UTL_FILE будут инкапсулированы в простой в использовании объектный тип. Создание нового типа данных PL/SQL Пакет UTL_FILE возвращает записи PL/SQL (данные типа RECORD). Это несколько усложняет работу, но проблему можно решить. Усложнение связано с тем, что объектный тип SQL может содержать только SQL-типы, но не типы данных PL/SQL. Поэтому 'Хочу отметить, что в версии Oracle 9i ситуация принципиально меняется, поскольку появляется возможность развивать систему типов за счет наследования. - Прим. научн. ред. Глава нельзя создать объектный тип, содержащий атрибут типа записи PL/SQL, но нам это необходимо, чтобы инкапсулировать функциональные возможности существующего пакета. Чтобы решить эту проблему, придется создать вместе с типом небольшой PL/SQLпакет. Начнем со спецификации типа Ч прототипа того, что мы планируем создать: tkyte@TKYTE816> create or replace type FileType 2 as object 3 (g_file_name varchar2(255), 4 g_path varchar2(255), 5 g_file_hcLL number, 6 7 static function open(p_path in varchar2, 8 p_file_name in varchar2, 9 p_mode in varchar2 default 'r', 10 p_maxlinesize in number default 32765) 11 return FileType, 12 13 member function isOpen return boolean, 14 member procedure close, 15 member function get_line return varchar2, 16 member procedure put(p_text in varchar2), 17 member procedure new_line(p_lines in number default 1), 18 member procedure put_line(p_text in varchar2), 19 member procedure putf(p_fmt in varchar2, 20 p_argl in varchar2 default null, 21 p_arg2 in varchar2 default null, 22 p_arg3 in varchar2 default null, 23 p_arg4 in varchar2 default null, 24 p_arg5 in varchar2 default null), 25 member procedure flush, 26 27 static procedure write_io(p_file in number, 28 p_operation in varchar2, 29 p_parml in varchar2 default null, 30 p_parm2 in varchar2 default null, 31 p_parm3 in varchar2 default null, 32 p_parm4 in varchar2 default null, 33 p_parm5 in varchar2 default null, 34 p_parm6 in varchar2 default null) 35 ) 36 / Type created. Эта спецификация очень похожа на спецификацию пакета UTL_FILE (если вы не знакомы с пакетом UTL_FILE, можете прочитать о нем в приложении А). Он обеспечивает практически те же функциональные возможности, что и пакет UTL_FILE, просто в более удобном (как мне кажется) виде. Помните, при рассмотрении создания типа ADDRESS_TYPE я говорил, что каждый объектный тип имеет один стандартный конструктор и в этом конструкторе надо задать значения для всех атрибутов типа. Пользо Использование объектно-реляционных средств вательский код этот стандартный конструктор не выполняет. Другими словами, он может использоваться только для установки атрибутов объектного типа. Это не слишком удобно. Статическая функция OPEN в представленном выше типе будет использоваться для демонстрации создания собственных, куда более полезных (и сложных), конструкторов для типов. Обратите внимание, что функция OPEN Ч часть объектного типа FILETYPE сама возвращает данные типа FILETYPE. Она выполняет необходимую настройку и возвращает полностью инициализированный объект. Именно для этого в основном используются статические функции-члены в объектных типах: с их помощью создают сложные конструкторы объектов. Статические функции и процедуры в объектном типе отличаются от остальных процедур и функций тем, что не получают неявного параметра SELF. Эти функции похожи на функции или процедуры пакета. Они пригодятсядля реализации общих утилит, вызываемых другими методами, но не требующих доступа к данным экземпляра (атрибутам объекта). Процедура WRITE_IO в представленном выше объектном типе Ч пример такого рода утилиты. Я использую ее для обращения к пакету UTL_FILE, связанного с записью в файл, так что не приходится каждый раз повторять 14-строчный блок обработки исключительных ситуаций. Обратите внимание, что в этом объектном типе нет ссылок на тип данных UTL_FILE.FILE_TYPE, поскольку атрибуты объектного типа могут быть только SQLтипов. Эту запись необходимо сохранить в другом месте. Для этого я собираюсь использовать PL/SQL-пакет следующего вида: tkyte@TKYTE816> create or replace package FileType_pkg 2 as 3 type utl_fileArrayType is table of utl_file.file_type 4 index by binary_integer; 5 6 g_files utl_fileArrayType; 7 8 g_invalid_path_msg constant varchar2(131) default 9 'INVALID_PATH: Недопустимое местонахождение или имя файла.'; 10 11 g_invalid_mode_msg constant varchar2(131) default 12 'INVALID_MODE: Недопустимый параметр open_mode %s в вызове FOPEN.'; 13 14 g_invalid_filehandle_msg constant varchar2(131) default 15 'INVALID_FILEHANDLE: Недопустимый дескриптор файла.'; 16 17 g_invalid_operation_msg constant varchar2(131) default 18 'INVALID_OPERATION: Файл нельзя открыть или обработать так, '|| 19 'как запрошено.'; 20 21 g_read_error_msg constant varchar2(131) default 22 'READ_ERROR: В ходе операции чтения произошла ошибка '|| 23 'операционной системы.'; 24 25 g_write_error_msg constant varchar2(131) default 26 'WRITE_ERROR: В ходе операции записи произошла ошибка '|| 27 'операционной системы.'; Глава 29 g_internal_error_msg constant varchar2(131) default 30 'INTERNAL_ERROR: Неопределенная ошибка в PL/SQL.'; 31 32 g_invalid_maxlinesize_msg c o n s t a n t v a r c h a r 2 ( 1 3 1 ) d e f a u l t 33 'INVALID_MAXLINESIZE: Указанный максимальный размер строки %d - 'II 34 'слишком велик или слишком м а л ' ; 35 end; 36 / Package created. Этот пакет будет использоваться для хранения записей типа UTL_FILE.FILE_TYPE в процессе выполнения. Каждый экземпляр объектного типа (переменная) FILE_TYPE будет выделять себе пустой "слот" в представленном выше массиве G_FILES. Это показывает, как создавать "приватные" данные в объектных типах Oracle. Реальные данные времени выполнения будут храниться в переменной пакета G_FILES, а в объектном типе Ч только дескриптор (индекс в массиве). В текущей реализации объектов в Oracle все данные объектного типа Ч общедоступны. Невозможно создать скрытый атрибут типа, недоступный для пользователей. Например, в случае представленного выше типа FILE_TYPE вполне можно обратиться к переменной экземпляра G_FILE_NAME непосредственно. Если это нежелательно, необходимо скрыть эту переменную в PL/SQLпакете так же, как мы скрыли там тип PL/SQL-записи. Никто не сможет обратиться данным в PL/SQL-пакете, не получив привилегию EXECUTE для этого пакета, поэтому данные защищены. Этот пакет также используется для хранения ряда констант. Объектные типы не поддерживают неизменяемые данные, поэтому пакет представляет собой удачное место для их хранения. Я предпочитаю называть пакет, поддерживающий тип подобным образом, так, чтобы в его имя входило имя типа. Поскольку мы создали тип FILETYPE, для его поддержки создан пакет FILETYPE_PKG. Теперь можно переходить к телу типа FILETYPE. Оно будет содержать все представленные ранее методы, статические функции и процедуры. Ниже приведен код с комментариями. tkyte@TKYTE816> create or replace type body FileType 2 as 3 4 static function open(p_path in varchar2, 5 p_file_name in varchar2, 6 p_mode in varchar2 default 'r', 7 p_maxlinesize in number default 32765) 8 return FileType 9 is 10 l_file_hdl number; 11 l_utl_file_dir varchar2(1024); 12 begin 13 l_file_hdl := nvl(fileType_pkg.g_files.last, 0)+l; 14 15 filetype_pkg.g_files(l_file_hdl) := 16 utl_file.fopen(p_path, p_file_name, p_mode, p_maxlinesize); Использование объектно-реляционных средств 17 return fileType(p_file_name, p_path, l_file_hdl); Представленная выше часть статической функции OPEN отвечает за поиск свободного слота в приватных данных (скрытых в пакете fileype_pkg). Для этого она добавляет единицу к значению атрибута LAST PL/SQL-таблицы. Если таблица пуста, LAST имеет значение NULL, поэтому его значение передается функции NVL, и первое выделяемое значение будет иметь индекс ]. Следующее Ч 2 и так далее. Функция CLOSE будет удалять записи при закрытии файла, так что место в массиве при открытии и закрытии файлов будет использоваться повторно. Остальная часть функции очень проста; она открывает указанный файл и возвращает готовый к использованию, полностью проинициализированный экземпляр объекта FILETYPE. Далее в функции FILETYPE.OPEN идет блок обработки исключительных ситуаций, позволяющий перехватить и обработать все ошибки, которые могут возникнуть при выполнении функции UTL_FILE.FOPEN: 19 exception 20 when utl_file.invalid_path then 21 begin 22 execute immediate 'select value 23 from v$parameter 24 where name = ''utl_file_dir''' 25 into l_utl_file_dir; 26 exception 27 when others then 28 l_utl_file_dir := p_path; 29 end; 30 if (instr( l_utl_file_dir||',', p_path || ',') = 0 ) 31 then 32 raise_application_error 33 (-20001,'Каталог ' || p_path || 34 ' не входит в список каталогов utl_file_dir "' || 35 l_utl_file_dir || ' " ' ) ; 36 else 37 raise_application_error 38 (-20001,fileType_pkg.g_invalid_path_msg); 39 end if; 40 when utl_file.invalid_mode then 41 raise_application_error 42 (-20002,replace(fileType_pkg.g_invalid_mode_msg,'%s',p_mode)); 43 when utl_file.invalid_operation then 44 raise_application_error 45 (-20003,fileType_pkg.g_invalid_operation_msg); 46 when utl_file.internal_error then 47 raise_application_error 48 (-20006,fileType_pkg.g_internal_error_msg); 49 when utl_file.invalid_maxlinesize then 50 raise_application_error 51 (-20007, replace(fileType_pkg.g_invalid_maxlinesize_msg, 52 '%d',p_maxlinesize)); 53 end; Глава Блок обработки исключительных ситуаций создан для перехвата и повторного возбуждения исключительных ситуаций UTL_FILE более удобным образом, чем это делает пакет UTL_FILE. Вместо получения в вызывающей подпрограмме обычного сообщения об ошибке (SQLERRM) USER DEFINED EXCEPTION, мы получим нечто более осмысленное, вроде: Недопустимый режим открытия файла. Кроме того, для исключительной ситуации INVALID_PATH, которая возбуждается в том случае, когда файл нельзя открыть из-за неверного имени файла или каталога, выполняются дополнительные проверки, и причина ошибки устанавливается более точно. Если владелец этого типа имеет привилегию SELECT на представление SYS.V_$PARAMETER, мы выбираем из него значение параметра инициализации UTL_FILE_DIR и проверяем, можно ли использовать тот каталог, который мы пытаемся использовать. Если нельзя, выдается соответствующее сообщение. Из всех ошибок, происходящих в процессе работы с пакетом UTL_FILE, эта, несомненно, самая "популярная". Выдавая такое точное сообщение об ошибке, мы сэкономим многие часы отладки для начинающих пользователей пакета UTL_FILE. Продолжая рассмотрение, переходим к методу Open: 55 56 57 58 59 member function isOpen return boolean is begin return utl_file.is_open(filetype_pkg.g_files(g_file_hdl)); end; Это просто оболочка для существующей функции UTL_FILE.IS_OPEN. Поскольку эта функция пакета UTL_FILE никогда не возбуждает исключительных ситуаций, ее реализация очень проста. Далее идет более сложный метод GET_LINE: 61 member function get_line return varchar2 62 is 63 l_buffervarchar2(32765); 64 begin 65 utl_file.get_line(filetype_pkg.g_files(g_file_hdl), l_buffer); 66 return l_buffer; 67 exception 68 when utl_file.invalid_filehandle then 69 raise_application_error 70 (-20002,fileType_pkg.g_invalid_filehandle_msg); 71 when utl_file.invalid_operation then 72 raise_application_error 73 (-20003,fileType_pkg.g_invalid_operation_msg); 74 when utl_file.read_error then 75 raise_application_error 76 (-20004,fileType_pkg.g_read_error_msg); 77 when utl_file.internal_error then 78 raise_application_error 79 (-20006,fileType_pkg.g_internal_error_msg); 80 end; В нем используется локальная переменная типа VARCHAR2(32765), Ч переменная максимально возможного в PL/SQL размера и одновременно самая длинная строка, Использование объектно-реляционных средств которую фактически позволяет прочитать пакет UTL_FILE. Как и в представленном ранее методе OPEN, мы перехватываем и обрабатываем исключительные ситуации, которые возбуждаются подпрограммой UTL_FILE.GET_LINE, и преобразуем их в вызовы RAISE_APPLICATION_ERROR. Это позволяет выдавать информативные сообщения об ошибках в функции GET_LINE (для удобства использования GET_LINE реализована как функция, а не как процедура). Теперь переходим к другой статической процедуре Ч WRITE_IO. Процедура WRITE_IO используется единственно для того, чтобы избежать написания одних и тех же обработчиков исключительных ситуаций шесть раз, для каждой из подпрограмм, связанных с записью, поскольку все они возбуждают одни и те же исключительные ситуации. Эта функция, добавленная исключительно для удобства программирования, просто вызывает одну из шести функций пакета UTL_FILE и обрабатывает все возможные ошибки: 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 static procedure write_io(p_file in number, p_operation in varchar2, p_parml in varchar2 default null, p_parm2 in varchar2 default null, p_parm3 in varchar2 default null, p_parm4 in varchar2 default null, p_parm5 in varchar2 default null, p_parm6 in varchar2 default null) is l_file utl_file.file_type default filetype_pkg.g_files(p_file); begin if (p_operation='close') then utl_file.fclose(l_file); elsif (p_operation='put') then utl_file.put(l_file,p_parml) ; elsif (p_operation='new_line') then utl_file.new_line(l_file,p_parm1); elsif (p_operation='put_line') then utl_file.put_line(l_file, p_parml); elsif (p_operation='flush') then utl_file.fflush(l_file); elsif (p_operation='putf') then utl_file.putf(l_file,p_parml, p_parm2, p_parm3, p_parm4, p_parm5, p_parm6); else raise program_error; end if; exception when utl_file.invalid_filehandle then raise_application_error (-20002,fileType_pkg.g_invalid_filehandle_msg); when utl_file.invalid_operation then raise_application_error (-20003,fileType_pkg.g_invalid_operation_msg); when utl_file.write error then Глава 117 raise_application_error 118 (-20005,fileType_pkg.g_write_error_msg); 119 when utl_file.internal_error then 120 raise_application_error 121 (-20006,fileType_pkg.g_internal_error_msg); 122 end; Остальные методы вызывают метод WRITE_IO с соответствующими параметрами: 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 member procedure close is begin fileType.write_io(g_file_hdl, 'close'); filetype_pkg.g_files.delete(g_file_hdl); end; member procedure put(p_text in varchar2) is begin fileType.write_io(g_file_hdL, 'put',p_text); end; member procedure new_line(p_lines in number default 1) is begin fileType.write_io(g_file_hdl, 'new_line',p_lines); end; member procedure put_line(p_text in varchar2) is begin fileType.write_io(g_file_hd1, 'put_line', p_text); end; member procedure putf (p_fmt in varchar2, p_argl in varchar2 default null, p_arg2 in varchar2 default null, p_arg3 in varchar2 default null, p_arg4 in varchar2 default null, p_arg5 in varchar2 default null) is begin fileType.write_io (g_file_hd1, 'putf', p_fmt, p_argl, p_arg2, p_arg3, p_arg4, p_arg5); end; member procedure flush is begin fileType.write_io(g_file_hdl, 'flush'); end; end; Использование объектно-реляционных средств / Type body created. В представленном коде перехватываются все исключительные ситуации пакета UTL_FILE и возбуждаются другие исключительные ситуации с помошью процедуры RAISE_APPLICATION_ERROR. Это основная причина инкапсуляции средств пакета UTL_FILE в объектный тип. Пакет UTL_FILE возбуждает исключительные ситуации с описанием USER-DEFINED EXCEPTION. Эти исключительные ситуации определены разработчиками пакета UTL_FILE, и при их возбуждении сервер Oracle выдает сообщение: "USER-DEFINED EXCEPTION". Оно не слишком информативно и не помогает разобраться в причинах ошибки. Я предпочитаю использовать процедуру RAISE_APPLICATION_ERROR, которая позволяет задать значения встроенных функций SQLCODE и SQLERRM, возвращаемые клиенту. Чтобы увидеть, как это может повлиять на отладку, достаточно рассмотреть следующий небольшой пример, демонстрирующий, какого рода сообщения об ошибках можно получить от пакета UTL_FILE и объектного типа FILETYPE: tkyte@TKYTE816> d e c l a r e 2 futl_file.file_type :=utl_file.fopen('c:\temp\bogus', 3 'foo.txt', 'w'); 4 begin 5 utl_file.fclose(f); 6 end; 7/ declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.UTL_FILE", line 98 ORA-06512: at "SYS.UTL_FILE", line 157 ORA-06512: at line 2 tkyte@TKYTE816> declare 2 f fileType := fileType.open('c:\temp\bogus', 3 'foo.txt', 'w'); 4 begin 5 f.close; 6 end; 7/ declare * ERROR at line 1: ORA-20001: The path c:\temp\bogus is not in the utl_file_dir path "c:\temp, c:\oracle" ORA-06512: at "TKYTE.FILETYPE", line 54 ORA-06512: at line 2 Нетрудно понять, с помощью какого сообщения проще определить причину ошибки. Второе сообщение об ошибке (при наличии у владельца типа доступа к представлению V$PARAMETER) очень точно объясняет причину ошибки: использован недопус Глава тимый каталог, не указанный в параметре инициализации UTL_FILE_DIR. Даже при отсутствии доступа к представлению V$PARAMETER будет выдано следующее сообщение: * ERROR at line 1: ORA-20001: INVALID_PATH: File location or filename was invalid. ORA-06512: at "TKYTE.FILETYPE", line 59 ORA-06512: at line 2 которое все равно лучше, чем краткое user-defined exception. В этом типе также следует обратить внимание на возможность установки предпочтительных стандартных значений параметров подпрограмм. Например, до версии Oracle 8.0.5 пакет UTL_FILE имел ограничение на максимальную длину строки Ч 1023 байт. Если попытаться выдать более длинную строку, пакет UTL_FILE возбуждает исключительную ситуацию. По умолчанию точно так же происходит и в Oracle 8i. Если явно не указать максимальный размер строки при вызове UTL_FILE.FOPEN, ограничение 1023 байт остается. Я лично предпочитаю по умолчанию устанавливать максимальную длину строки равной 32 Кбайт. В начале кода я также задал стандартный режим открытия файла Ч на чтение ('R'). Поскольку в 90 процентах случаев я использую пакет UTL_FILE для чтения файла, такое стандартное значение для меня имеет смысл. Теперь давайте проверим работу объектного типа и рассмотрим использование всех функций и процедур. Сначала создадим файл (предполагается, что мы работаем в Windows NT, существует каталог c:\temp и параметр инициализации UTL_FILE_DIR содержит c:\temp) и запишем в него определенные данные. Затем мы закроем этот файл, сохранив данные. Это продемонстрирует возможности записи типа FILETYPE: tkyte@TKYTE816> declare 2 f fileType := fileType.open('c:\terrp\ ' f o o. t x t ', ' w ' ) ; 3 begin 4 if (f.isOpen) 5 then 6 dbms_output.put_line('Файл открыт'); 7 end if; 8 9 for i in 1.. 10 loop 10 f.put(i || ', ' ) ; 11 end loop; 12 f.put_line(ll); 13 14 f.new_line(5); 15 for i in 1.. 5 16 loop 17 f.put_line('cтpoкa ' || i ) ; 18 end loop; 19 20 f.putf('%s %s', 'Hello', 'World'); 21 22 f.flush; Использование объектно-реляционных средств 24 f.close; 25 end; 26 / Файл открыт PL/SQL procedure successfully completed. Далее продемонстрировано чтение файла с помощью объекта типа FILETYPE. Откроем только что записанный файл и убедимся, что прочитаны именно те данные, которые в нем содержатся: tkyte@TKYTE816> declare 2 ffileType :=fileType.open('c:\temp', ' f o o. t x t ' ) ; 3 begin 4 if (f.isOpen) 5 then 6 dbms_output.put_line('Файл открыт'); 7 end if; 8 9 dbms_output.put_line 10 ('строка 1: (должна быть l,2,...,11)' || f.get_line); 11 12 for i in 2.. 6 13 loop 14 dbms_output.put_line 15 ('строка ' || i || ': (должна быть пустой)' || f.get_line); 16 end loop; 17 18 for i in 7.. 11 19 loop 20 dbms_output.put_line 21 ('строка ' || to_char(i+l) || 22 ': (должна быть строка N ) ' || f.get_line); 23 end loop; 24 25 dbms_output.put_line 26 ('строка 12: (должна быть Hello World)' || f.get_line); 27 28 begin 29 dbms_output.put_line(f.get_line); 30 dbms_output.put_line('B предыдущей операторе должна -> произойти ошибка') ; 31 exception 32 when NO_DATA_FOUND then 33 dbms_output.put_line('получили no data found, как и -> ожидалось'); 34 end; 35 f.close; 36 end; 37 / Файл открыт строка 1: (должна быть l,2,...,ll)l,2,3,4,5,6,7,8,9,10,ll Глава строка 2: (должна быть пустой) строка 3: (должна быть пустой) строка 4: (должна быть пустой) строка 5: (должна быть пустой) строка 6: (должна быть пустой) строка 8: (должна быть строка N)строка 1 строка 9: (должна быть строка N)строка 2 строка 10: (должна быть строка N)строка 3 строка 11: (должна быть строка N)строка 4 строка 12: (должна быть строка N)строка 5 строка 12: (должна быть Hello World)Hello World получили no data found, как и ожидалось PL/SQL procedure successfully completed. Мы инкапсулировали пакет UTL_FILE в объектный тип Oracle. Получился замечательный интерфейс к стандартному пакету, работающий именно так, как нам хотелось. В терминах объектного программирования, мы создали класс UTL_FILE, включив в него методы, которые работают так, как нужно нам, а не так, как придумали разработчики Oracle. Мы не переписывали пакет UTL_FILE, а только создали для него другой интерфейс. Это хороший прием программирования, позволяющий легко решать проблемы, связанные с изменением реализации пакета UTL_FILE или появлением ошибки в новой версии. Все эти проблемы можно решить, изменив тело типа, а не сотни или тысячи непосредственно зависящих от пакета приложений. Например, в одной из версий пакета UTL_FILE нельзя было открыть несуществующий файл в режиме А (на добавление); при этом файл не создавался, хотя и должен был. Для решения проблемы достаточно было написать следующий код: begin file_stat := utl_file.fopen(file_dir,filename,'a'); exception Ч если файл не существует, fopen не сработает Ч из-за ошибки режима 'a': 371510 when utl_file.invalid_operation then Ч все остальные исключительные ситуации распространяются Ч во внешний блок, как обычно file_stat := utl_file.fopen(file_dir,file_name,'w'); end; Если файлы открываются на добавление в 100 подпрограммах, придется делать много исправлений. Если же использован дополнительный уровень интерфейса, достаточно внести изменение только в одном месте. Уникальные приемы использования наборов Еще один вариант применения объектных типов в PL/SQL связан с использованием наборов и их возможностей взаимодействия с языками SQL и PL/SQL. Наборы позволяют сделать три вещи в SQL и PL/SQL, о реализации которых часто спрашивают разработчики. Использование объектно-реляционных средств Х Как выбрать данные (SELECT *) из PL/SQL-функции?* Можно написать PL/SQLфункцию и обращаться с запросами к ней, а не к таблице базы данных. Х Как выбрать данные в массив записей? PL/SQL изначально позволяет выполнять выборку данных BULK COLLECT (выбирать по нескольку строк за раз) в PL/SQLтаблицу. К сожалению, это можно делать только в PL/SQL-таблицы с элементами скалярных типов. Я не могу сделать следующее: s e l e c t c l, c2 BULK COLLECT INTO record_type from T поэтому приходится писать: s e l e c t c l, c2 BULK COLLECT INTO t a b l e l, table2 from T С помощью наборов можно обеспечить выборку данных в записи. Х Как вставить запись? Вместо вставки по столбцам, я могу вставить в таблицу одну запись. SELECT * из PL/SQL-функции Чтобы продемонстрировать эту возможность, давайте вернемся к проблеме использования связываемых переменных (это моя любимая тема). Часто мне приходится слышать утверждения, что необходимо выполнить запрос вида: select * from t where c in (:bind_variable) где BIND_VARIABLE представляет собой список значений. Другими словами, переменная BIND_VARIABLE получает, допустим, значение ' 1, 2, 3' и необходимо, чтобы представленный выше запрос выполнялся как: select * from t where с in (1, 2, 3) и возвращал строки, в которых с = 1, 2 или 3; но при таком значении связываемой переменной фактически выполняется запрос: select * from t where с in ( ' 1, 2, 3 ' ) Он возвращает строки, в которых с = '1,2,3' Ч значение столбца с равно одной такой строке. Такие запросы часто порождаются пользовательскими интерфейсами, в которых пользователь может отметить флажками одно или несколько (любое количество) возможных значений из списка. Чтобы не создавать уникальные запросы для каждого сочетания флажков (мы знаем, насколько это плохо), необходим метод связывания произвольного количества элементов в списке. Ну, поскольку можно выполнять SELECT * из PL/SQL-функиии, решение есть. Сейчас я его продемонстрирую: tkyte@TKYTE816> c r e a t e or replace type myTableType 2 as t a b l e of number; 3/ Type created. Созданный таким образом тип данных и будет возвращать PL/SQL-функция. Этот тип обязательно должен быть задан на уровне SQL с помощью оператора CREATE TYPE. * Другая формулировка той же проблемы: как возвращать результирующие множества из хранимых функций? Прим. научн. ред. Глава Нельзя использовать тип, заданный в PL/SQL-пакете; конечная цель Ч выбирать данные с помощью SQL-операторов, поэтому необходим тип данных SQL. Этот пример также показывает, что я не являюсь принципиальным противником использования вложенных таблиц. Именно этот тип набора оптимален при программировании на PL/SQL, если приходится решать подобного рода задачи. При использовании массива VARRAY придется искусственно ограничить количество элементов. Размер же вложенной таблицы ограничен только объемом памяти, доступной в системе. tkyte@TKYTE816> create or replace 2 function str2tbl(p_str in varchar2) return myTableType 3 as 4 l_str long default p_str || ','; 5 l_n number; 6 l_data myTableType := myTabletype(); 7 begin 8 loop 9 l_n := instr(l_str, ', ' ) ; 10 exit when (nvl(l_n,0) = 0); 11 l_data.extend; 12 l_data(l_data.count) := 13 ltrim(rtrim(substr(l_str,l,l_n-l))); 14 l_str := substr(l_str, l_n+l); 15 end loop; 16 return l_data; 17 end; 18 / Function created. Итак, создана PL/SQL-функция, принимающая строку со списком значений через запятую и преобразующая ее в SQL-тип MYTABLETYPE. Осталось только найти способ выбрать эти данные с помощью SQL-оператора. Это легко сделать с помощью оператора TABLE и преобразования типа, CAST: tkyte@TKYTE816> variable bind_variable varchar2(30) tkyte@TKYTE816> exec :bind_variable := '1,3,5,7,991 PL/SQL procedure successfully completed. BIND_VARIABLE 1,3,5,7,99 tkyte@TKYTE816> select * 2 from TABLE (cast(str2tbl(:bind_variable) asmyTableType)) 3/ COLUMN_VALUE 1 3 5 7 Использование объектно-реляционных средств Теперь легко использовать эту конструкцию в подзапросе IN: tkyte@TKYTE816> select * 2 from all_users 3 where user_id in 4 (select * 5 from TABLE ( c a s t ( s t r 2 t b l ( : b i n d _ v a r i a b l e ) as myTableType)) 6 ) 7/ USERNAME SYSTEM USER_ID CREATED 5 04-NOV- Этот прием можно использовать во многих случаях. Можно применить к значению PL/SQL-переменной типа набора операцию ORDER BY, можно возвращать клиенту наборы данных, сгенерированные PL/SQL-функцией, можно задавать конструкции WHERE для выбора определенных значений PL/SQL-переменных и т.д. Если пойти чуть дальше, таким способом можно возвращать полные результирующие множества с несколькими столбцами. Например: tkyte@TKYTE816> c r e a t e type myRecordType as object 2 (seq int, 3 a int, 4 b varchar2(10), 5 c date 6) 7/ Type created. tkyte@TKYTE816> create table t (x int, y varchar2(10), z date); Table created. tkyte@TKYTE816> create or replace type myTableType 2 as table of myRecordType 3/ Type created. tkyte@TKYTE816> create or replace function my_function return myTableType 2 is 3 l_data myTableType; 4 begin 5 l_data := myTableType(); 6 7 for i in 1..5 8 loop 9 l_data.extend; 10 l_data(i) :=myRecordType(i, i, 'row ' || i, sysdate+i); 11 end loop; 12 return l_data; 13 end; 14 / Function created. Глава tkyte@TKYTE816> select * 2 from TABLE (cast(my_function() as mytableType)) 3 where c > sysdate+1 4 order by seq desc 5/ SEQ 5 4 3 2 AB 5 4 3 2 row row row row 5 4 3 2 C 29-MAR-01 28-MAR-01 27-MAR-01 26-MAR- Множественная выборка данных в записи Итак, мы рассмотрели, как использовать наборы для выборки данных из PL/SQLфункции. Теперь разберемся, как с их помощью обеспечить множественную выборку данных в аналог PL/SQL-записей. Выполнить множественную выборку в массив реальных PL/SQL-записей нельзя, но можно выбрать данные во вложенную таблицу SQL. Для этого потребуется два объектных типа: скалярный тип, представляющий запись, и вложенная таблица с записями этого типа. Например: tkyte@TKYTE816> create type myScalarType 2 as object 3 (username varchar2(30), 4 user_id number, 5 created date 6) 7/ Type created. tkyte@TKYTE816> create type myTableType as table of myScalarType 2/ Type created. Теперь все готово для выборки данных в переменную типа MYTABLETYPE следующим образом: tkyte@TKYTE816> declare 2 l_users myTableType; 3 begin 4 select cast(multiset(select username, user_id, created 5 from all_users 6 order by username) 7 as myTableType) 8 into l_users 9 from dual; 10 11 dbms_output.put_line('Retrieved '|| l_users.count || ' rows'); 12 end; 13 / Retrieved 25 rows PL/SQL procedure successfully completed. Использование объектно-реляционных средств Запрос к представлению ALL_USERS можно заменить любым запросом, выбирающим строку типа VARCHAR2(30), число и дату. Запрос может быть сколь угодно сложным, включать соединения и т.п. Фокус в том, что результаты этого подзапроса преобразуются в объектный тип. Затем можно выбрать все результирующее множество в локальную переменную с помощью стандартного оператора SELECT... INTO. Вставка записей Зная, что можно выполнять операторы SELECT * FROM НАБОР, где НАБОР Ч это либо локальная переменная, либо PL/SQL-функция, возвращающая вложенную таблицу, нетрудно придумать, как выполнить аналогичным образом оператор INSERT. Необходимо задать переменную типа вложенной таблицы и заполнить ее записями, которые требуется вставить. Следующий пример демонстрирует, как будет выглядеть вставка одной строки: tkyte@TKYTE816> create table t as select * from all_users where 1=0; Table created. tkyte@TKYTE816> declare 2 l_users myTableType := 3 myTableType(myScalarType('tom', 1, sysdate)); 4 begin 5 insert into t 6 select * from TABLE (cast(l_users as myTableType)); 7 end; 8/ tkyte@TKYTE816> select * from t; USERNAME USER_ID CREATED tom 1 24-MAR-01 При работе с много-столбцовой таблицей этот прием может пригодиться. Итак, в этом разделе мы рассмотрели использование объектных типов Oracle для расширения возможностей языка PL/SQL аналогично тому, как для этих целей используются классы в языках Java или С++. Описаны также интересные варианты использования вложенных таблиц. Возможность выполнять оператор SELECT * из PL/SQL-функции открывает заманчивые перспективы. Списки значений произвольной длины для конструкции IN Ч только начало. Возможности поистине безграничны. Можно написать небольшую функцию, использующую средства пакета UTL_FILE для чтения файла ОС, разбиения прочитанных строк на поля по запятым и возвращения результирующего множества, построенного по содержимому обычного файла, для вставки в другую таблицу или соединения с существующей таблицей. Такое использование объектных типов дает новую жизнь хорошо зарекомендовавшему себя языку программирования. Создав самостоятельно один-два типа, вы найдете применение этой методике во множестве приложений. Это логический способ объединения данных и функций для работы с ними, что является одной из основных целей объектно-ориентированного программирования. Предвидя протесты, я не называю это Глава чисто объектно-ориентированным программированием на PL/SQL, но это, определенно, очень близкая к нему методика. Объектно-реляционные представления Это весьма мощное средство для тех, кто хочет работать с объектно-реляционными расширениями, но должен обеспечивать для множества приложений реляционное представление данных. Можно использовать стандартный механизм VIEW для создания объектов на основе реляционных таблиц. Не нужно создавать объектные таблицы, со всеми их мистическими столбцами Ч можно создать объектное представление стандартных таблиц (скорее всего уже существующих). Подобные представления обеспечат возможности, аналогичные объектной таблице того же типа, но без дополнительных затрат ресурсов на поддержку скрытых ключей, суррогатных ключей и пр. В этом разделе мы используем таблицы ЕМР и DEPT для создания представления данных по отделам. Это очень похоже на пример создания вложенной таблицы в главе 6, где был создан тип EMP_TAB_TYPE как вложенная таблица записей типа EMP_TYPE, а таблица DEPT содержала столбец Ч вложенную таблицу этого типа. Здесь мы еще раз смоделируем типы EMP_TYPE и EMP_TAB_TYPE, но создадим еще и объектный тип DEPT_TYPE, а также представление этого типа. Интересно отметить, что использование объектных представлений позволяет взять лучшее из двух миров (реляционного и объектно-реляционного). Например, можно создать приложение, в котором должно использоваться представление данных по отделам. В представлении указаны данные по отделам, а информация о сотрудниках отдела естественным образом представляется как набор, один из атрибутов отдела. Другому приложению необходимо другое представление тех же данных. Например, службе охраны на проходной необходим доступ к данным по сотрудникам. Отдел в данном случае лишь атрибут записи о сотруднике, в то время как для другого приложения список сотрудников является атрибутом отдела. В этом Ч сила реляционной модели: она позволяет одновременно поддерживать несколько представлений данных. Объектная модель не обеспечивает поддержку нескольких различных представлений одних и тех же данных так же просто (если вообще обеспечивает) или эффективно. Используя несколько различных объектных представлений реляционных данных, можно обеспечить потребности всех приложений. Необходимые типы Использованные в этом примере типы позаимствованы из главы 6, с добавлением типа DEPT_TYPE. Вот как они создаются: scott@TKYTE816> create or replace type emp_type 2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), Использование обьектно-реляционных средств 9 comm 10 ); 11 / Type created. number(7, 2) scott@TKYTE816> create or replace type emp_tab_type 2 as table of emp_type 3/ Type created. scott@TKYTE816> create or replace type dept_type 2 as object 3 (deptno number(2), 4 dname varchar2(14), 5 loc varchar2(13), 6 emps emp_tab_type 7) 8/ Type created. Отдел моделируется как объект с такими атрибутами: номер отдела (deptno), название (dname), местонахождение (loc) и список сотрудников (emps). Объектно-реляционное представление По представленным выше определениям типов легко получить данные для этого представления по существующим реляционным данным. Вот как создается представление: scott@TKYTE816> create or replace view dept_or 2 of dept_type 3 with object identifier(deptno) 4 as 5 select deptno, dname, loc, 6 cast (multiset ( 7 select empno, ename, job, mgr, hiredate, sal, comm 8 from emp 9 where emp.deptno = dept.deptno) 10 as emp_tab_type ) 11 from dept 12 / View created. Назначение конструкций CAST и MULTISET вам уже знакомо Ч с их помощью коррелированный подзапрос преобразуется в набор типа вложенной таблицы. Для каждой строки в таблице DEPT мы запрашиваем всех сотрудников соответствующего отдела. С помощью конструкции WITH OBJECT IDENTIFIER серверу Oracle можно сообщить, какой столбец (или столбцы) однозначно идентифицируют строку в представлении. Это позволяет серверу автоматически создать ссылку на объект (object reference), что и обеспечивает возможности работы с этим представлением как с объектной таблицей. Книги, научные публикации