Oracle для профессионалов
Передача данных
В этом примере я собираюсь создать ряд процедур с параметром, передаваемым в режиме IN, и параметром, передаваемым в режиме OUT (или IN OUT). Мы напишем по процедуре для каждого из интересующих нас типов данных (наиболее часто используемых). При этом будет продемонстрирован правильный способ передачи входных данных и получения результатов каждого типа. Кроме того, я создам несколько функций и покажу, как возвращать данные некоторых из этих типов. Меня при работе с Java интересуют следующие типы:
Этот список несколько отличается от аналогичного списка для внешних процедур на языке C. В частности, в нем не указан тип данных BOOLEAN. Дело в том, что пока нет соответствия между типом данных PL/SQL BOOLEAN и типами данных языка Java. Нельзя передавать данные типа BOOLEAN как параметры внешним процедурам, написанным на языке Java.
С помощью объектно-реляционных расширений можно создавать типы данных любой сложности. Для создания таких типов данных я рекомендую использовать поставляемое корпорацией Oracle Java-средство JPublisher. Оно автоматически создает Java-классы, соответствующие объектным типам. Подробнее о JPublisher можно почитать в руководстве Oracle8i JPublisher User's Guide, которое входит в набор документации, предлагаемой корпорацией Oracle. Как и в случае внешних процедур на языке C, мы не будем углубляться в особенности использования объектных типов в хранимых процедурах на Java, ограничившись только простыми наборами данных скалярных типов.
Java-класс будет создан для тех же целей, что и представленная в предыдущей главе динамически подключаемая библиотека на языке C. Начнем с 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 as 5 language java 6 name 'demo_passing_pkg.pass(java.math.BigDecimal, 7 java.math.BigDecimal[])'
Даты Oracle сопоставляются типу данных Timestamp. И в этом случае можно было бы сопоставить датам множество других типов, например String, но во избежание потери информации при неявных преобразованиях я выбрал тип
Timestamp, который позволяет сохранить все данные, содержащиеся в объектах Oracle типа DATE.
8 9 procedure pass(p_in in date, p_out out date) 10 as 11 language java 12 name 'demo_passing_pkg.pass(java.sql.Timestamp, 13 java.sql.Timestamp[])';
Строки типа VARCHAR2 передаются очень просто — как данные типа java.lang.String.
14 15 procedure pass( p_in in varchar2, p_out out varchar2) 16 as 17 language java 18 name 'demo_passing_pkg.pass(java.lang.String, 19 java.lang.String[])';
Для данных типа CLOB мы используем предоставляемый Oracle Java-тип oracle.sql.CLOB. С помощью этого типа мы сможем получить входной и выходной потоки данных, используемые для чтения и записи данных типа CLOB.
20 21 procedure pass(p_in in CLOB, p_out in out CLOB) 22 as 23 language java 24 name 'demo_passing_pkg.pass(oracle.sql.CLOB, 25 oracle.sql.CLOB[])';
Теперь перейдем к наборам: вы видели, что, независимо от типа фактически передаваемого набора, используется один и тот же предоставляемый Oracle тип. Вот почему в данном случае Java-функции не являются перегруженными, как все предыдущие (пока что все вызываемые Java-функции назывались demo_passing_pkg.pass). Поскольку все типы наборов передаются как один и тот же тип Java, перегрузку имен использовать нельзя — необходимо называть функцию в соответствии с реально передаваемым типом данных:
26 27 procedure pass(p_in in numArray, p_out out numArray) 28 as 29 language java 30 name 'demo_passing_pkg.pass_num_array(oracle.sql.ARRAY, 31 oracle.sql.ARRAY[])'; 32 33 procedure pass(p_in in dateArray, p_out out dateArray) 34 as 35 language java 36 name 'demo_passing_pkg.pass_date_array(oracle.sql.ARRAY, 37 oracle.sql.ARRAY[])'; 38 39 procedure pass(p_in in strArray, p_out out strArray) 40 as 41 language java 42 name 'demo_passing_pkg.pass_str_array(oracle.sql.ARRAY, 43 oracle.sql.ARRAY[])';
Следующие две процедуры демонстрируют сопоставление для типов RAW
и INT. SQL-тип RAW будет сопоставляться встроенному типу byte
языка Java. Для целых чисел будет использоваться встроенный тип данных int языка Java:
44 45 procedure pass_raw(p_in in RAW, p_out out RAW) 46 as 47 language java 48 name 'demo_passing_pkg.pass(byte[], byte[][])'; 49 50 procedure pass_int(p_in in number, 51 p_out out number) 52 as 53 language java 54 name 'demo_passing_pkg.pass_int(int, int[])';
Наконец, для полноты изложения я продемонстрирую использование функций для возвращения данных простых скалярных типов:
55 56 function return_number return number 57 as 58 language java 59 name 'demo_passing_pkg.return_num() return java.math.BigDecimal'; 60 61 function return_date return date 62 as 63 language java 64 name 'demo_passing_pkg.return_date() return java.sql.Timestamp'; 65 66 function return_string return varchar2 67 as 68 language java 69 name 'demo_passing_pkg.return_string() return java.lang.String'; 70 71 end demo_passing_pkg; 72 /
Package created.
Эта спецификация пакета практически совпадает (за исключением процедур для данных типа BOOLEAN) с той, что использовалась для внешних процедур на языке C. В этом примере я поместил уровень связывания непосредственно в спецификацию, чтобы не пришлось писать избыточное тело пакета (все функции написаны на языке 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 class demo_passing_pkg extends Object 11 {
В первом из представленных далее методов демонстрируется единственно возможный способ передачи параметров в режиме OUT функции на Java; фактически мы передаем массив из одного элемента. При изменении значения в массиве изменяется параметр, переданный в режиме 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.Timestamp[] 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()+1); 39 else 40 { 41 p_out[0].setMonth(0); 42 p_out[0].setYear(p_out[0].getYear()+1); 43 } 44 System.out.println 45 ("Устанавливаем параметр out равным " + p_out[0].toString()); 46 } 47 }
Теперь переходим к самому простому из типов данных — String, который соответствует строковым типам SQL. Если вспомнить версию на языке C, с шестью формальными параметрами, индикаторными переменными, атрибутами strlen, функциями strcpy и т.п., то по сравнению с ней эта реализация тривиальна:
48 49 public static void pass(java.lang.String p_in, 50 java.lang.String[] p_out) 51 { 52 if (p_in != null) 53 { 54 System.out.println 55 ("Первый параметр " + p_in.toString()); 56 57 p_out[0] = p_in.toUpperCase(); 58 59 System.out.println 60 ("Устанавливаем параметр out равным " + p_out[0].toString()); 61 } 62 }
В методе для данных типа CLOB придется выполнить ряд дополнительных действий. Для того чтобы показать, как принимать и возвращать большие объекты, здесь выполняется копирование. Вы видите, что для изменения/чтения содержимого большого объекта используются стандартные потоки чтения/записи языка Java. В этом примере is — входной поток, а 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(1,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; 81 82 while((length=is.read(buffer,0,8192)) != -1) 83 os.write(buffer,0,length); 84 85 is.close(); 86 os.close(); 87 88 System.out.println 89 ("Устанавливаем параметр out равным " + 90 p_out[0].getSubString(1,80)); 91 } 92 }
Следующий метод — приватный (внутренний). Он выдает метаданные о переданном ему объекте типа oracle.sql.ARRAY. Для каждого из передаваемых Java трех типов массивов будет вызываться этот метод, информирующий о том, какого размера и типа массив передан:
93 94 private static void show_array_info(oracle.sql.ARRAY p_in) 95 throws SQLException 96 { 97 System.out.println("Тип массива " + 98 p_in.getSQLTypeName()); 99 System.out.println("Код типа массива " + 100 p_in.getBaseType()); 101 System.out.println("Длина массива " + 102 p_in.length()); 103 }
Теперь рассмотрим методы для обработки этих массивов. Использовать массивы несложно, если разобраться, как получать из них данные и изменять их. Получить данные очень просто; метод getArray() возвращает базовый массив данных. Приведя возвращаемое методом getArray() значение к нужному типу, мы получим Java-массив этого типа. Поместить данные в такой массив немного сложнее. Необходимо сначала получить дескриптор (метаданные) массива, а затем создать новый объект-массив с этим дескриптором и соответствующими значениями. Следующий набор методов продемонстрирует это для каждого из использованных типов массивов. Обратите внимание, что тексты методов практически совпадают, за исключением фактических обращений к массивам данных Java. Эти методы выдают метаданные для типа oracle.sql.ARRAY, выдают содержимое массива и копируют входной массив в выходной:
104 105 public static void pass_num_array(oracle.sql.ARRAY p_in, 106 oracle.sql.ARRAY[] p_out) 107 throws SQLException 108 { 109 show_array_info(p_in); 110 java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray(); 111 112 for(int i = 0; i < p_in.length(); i++) 113 System.out.println("p_in["+i+"] = " + values[i].toString()); 114 115 Connection conn = new OracleDriver().defaultConnection(); 116 ArrayDescriptor descriptor = 117 ArrayDescriptor.createDescriptor(p_in.getSQLTypeName(), 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_info(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 if (p_in != null) 162 p_out[0] = p_in; 163 }
Передача целых чисел — проблематична, я вообще не рекомендую их передавать. Нет способа передать значение 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 }
Наконец, перейдем к функциям. Если помните, на языке C написать их было непросто. Необходимо было выделять память, обрабатывать неопределенные значения, явно преобразовывать типы данных C в типы данных Oracle и т.д. Каждая C-функция при этом состояла как минимум из десятка строк кода. В случае же 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 created
tkyte@TKYTE816> set define on
Запрограммировать функцию на Java гораздо проще, чем на языке C, благодаря тому, что Java выполняет много действий автоматически, "за кадром". Для обеспечения аналогичной функциональности на языке C потребовалось около 1000 строк кода. Выделение памяти, которое требует столько внимания при программировании на C, в случае Java — не проблема. В случае ошибки возбуждается исключительная ситуация. Индикаторные переменные, с которыми надо было возиться в языке C, вообще не нужны в 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[1] = Элемент 2 p_in[2] = Элемент 3 p_in[3] = Элемент 4 p_in[4] = Элемент 5 l_out(1) = Элемент 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-метода мы скопировали входной массив в выходной после распечатки метаданных и значений элементов массива.
