Глава 9

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY , TRAILING NULLCOLS (DEPTNO,

DNAME upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED case when length(:last updated) <= 10

then to date(:last updated,dd/mm/уууу)

else to date(:last updated,dd/mm/yyyy hh24:mi:ss)

end

BEGINDATA

10,Sales,Virginia,1/5/2000 12:03:03 20,Acoounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 01:23:00 40,Finance,Virginia,15/3/2001

что дает в результате:

tkyte@TKYTE816> alter session

2 set nls date format

Session altered.

tkyte@TKYTE816> select * from dept;

dd-mon-yyyy hh24:mi:ss ;

DEPTNO DNAME

ENTIRE LINE

LAST UPDATED

10 SALES 20 ACCOUNTING 30 CONSULTING 40 FINANCE

VIRGINIA VIRGINIA VIRGINIA VIRGINIA

01-may-2000 12:03:03

21-jun-1999 00:00:00

05-jan-2000 01:23:00

15-mar-2001 00:00:00

Теперь к строкам загружаемтх данных будет применяться один из двух форматов даты (обратите внимание, что теперь мы загружаем не даты, а обычные строки). Оператор CASE выбирает одну из масок формата даты в зависимости от длины строки.

Можно создавать собственн1е функции для вызова при загрузке с помощью SQLLDR. Это прямое следствие того факта, что PL/SQL-функции можно вызывать в SQL. Допустим, даты во входном файле указаны в одном из следующих форматов (удивительно, как часто такое бывает - почему-то принято в файлах данных использовать несколько форматов дат):

dd-mon-yyyy

dd-month-yyyy

dd/mm/yyyy

dd/mm/yyyy hh24:mi:ss

количество секунд, прошедшее после 1 января 1970 года по Гринвичу (или UNIX-вре-мя )

Теперь использовать оператор CASE очень сложно, поскольку по длине строки нельзя однозначно определить ее формат. Вместо этого можно создать функцию, которая бу-

Загрузка данных

дет перебирать форматы даты, пока не найдет подходящий. Следующая функция в цикле перебирает возможные форматы даты, применяя их поочередно, пока не удастся успешно выполнить преобразование. Если по завершении цикла преобразовать строку в дату не удалось, мы предполагаем, что это дата в формате ОС UNIX, и выполняем соответствующее преобразование. Если и это не получится, информация об ошибке просто передается утилите SQLLDR, которая поместит соответствующую запись в BAD-файл. Функция выглядит следующим образом:

tkyte@TKYTE816> create or replace

2 3 4 5 6

8 9 10

12 13 14 15 16 17 18 19 20 21 22 23 24

26 27 28 29 30

function as

type

my to date(p string in varchar2) return date

fmtArray is l fmts fmtArray

table of varchar2(25);

: = fmtArray(dd-mon-yyyy , dd-month-yyyy , dd/nm/yyyy, dd/mm/yyyy hh24 :mi: ss);

l return date;

begin

for i in 1 loop

begin

l fmts.count

l return := exception

when others end;

EXIT when l retum loop;

to date(p string, l fmts(i));

then null;

is not null;

if (l return is null) then

l return := new time(to date(01011970,ddmmyyyy)

p string, GMT, EST);

end if;

return l return;

1/24/60/60

end;

Function created.

Теперь ее можно использовать в управляющем файле:

LOAD DATA INFILE * INTO TABLE DEFT REPLACE

FIELDS TERMINATED BY , (DEPTNO,

DNAME upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED my to date(:last updated)

Глава 9

BEGINDATA

10,Sales,Virginia,01-april-2001 20,Accounting,Virginia,13/04/2001 30,Consulting,Virginia,14/04/2001 12:02:02 40,Finance,Virginia,987268297 50,Finance,Virginia,02-apr-2001 60,Finance,Virginia,Not a date

После загрузки в журнальном файле окажется следующее сообщение об ошибке:

Record 6: Rejected - Error on table DEPT, column LAST UPDATED. ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at TKYTE.MY TO DATE , line 30

ORA-06512: at line 1

показывающее, что последнюю запись загрузить не удалось, но все остальные были загружены. Эта не загруженная запись окажется в BAD-файле. Ее можно исправить и загрузить повторно. Если проверить загруженные данные, получим:

tkyte@TKYTE816> alter session

2 set nls date format = dd-mon-yyyy hh24:mi:ss;

Session altered.

tkyte@TKYTE816> select deptno, dname, loc, last updated from dept;

Изменение существующих строк и вставка новых

Часто приходится добавлять записи из файла в таблицу. При этом данные используются для обновления существующих строк, задаваемых первичным ключом, или просто вставить их, если таких строк еще нет. За один шаг это сделать нельзя, но вполне можно за три, причем простых. Сначала я кратко опишу, что нужно сделать, а затем шаг за шагом продемонстрирую соответствующий код. Итак, необходимо.

1. Загрузить все данные с опцией APPEND, указав параметр ERRORS=99999999. Задание большого количества возможных ошибок позволит загрузить все хорошие (новые) записи. Записи, которые являются обновлениями существующих, будут отвергнуты из-за нарушения ограничения первичного ключа. Они будут записаны в ВАD-файл. Итак, все новые записи уже загружены.