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

2. Загрузить BAD-файл в рабочую таблицу с опцией TRUNCATE. Структура этой таблицы совпадает со структурой реальной - она должна иметь тот же набор требований и т.д. В результате в нее будут загружены только уже имеющиеся в основной таблице записи. Записи, отвергнутые по другим причинам (кроме нарушения требования первичного ключа) несоответствия данных, в эту таблицу тоже не попадут.

3. Обновить данные, получающиеся при соединении реальной и рабочей таблицы.

Используя таблицу DEPT из предыдущих примеров и данные, полученные в результате последней загрузки (отделы 10, 20, 30, 40 и 50), загрузим следующие данные:

10,Sales,New York,14-april-2001 60,Finance,Virginia,14-april-2 001

В результате одна запись должна быть изменена, а одна - вставлена. Предполагая, что эти данные находятся в файле new.dat, создадим управляющий файл load.ctl следующего вида:

LOAD DATA

INTO TABLE DEPT

APPEND

FIELDS TERMINATED BY , (DEPTNO,

DNAME upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED my to date(:last updated)

Этот управляющий файл очень похож на предыдущий, но конструкции INFILE * и BEGINDATA удалены, а конструкция REPLACE заменена конструкцией APPEND. Файл, из которого надо загружать данные, мы будем задавать в командной строке, поэтому конструкция INFILE не нужна, а поскольку данные находятся во внешнем файле, то и BEGINDATA указывать не нужно. Так как мы хотим вставлять новые записи и изменять существующие, используется конструкция APPEND, а не REPLACE, как ранее. Итак, теперь можно загрузить данные с помощью команды:

C:\>sqlldr userid=tkyte/tkyte control=load.ctl data=new.dat errors=9999999

При выполнении этой команды будет сгенерирован BAD-файл с одной записью. Запись для отдела 10 окажется в файле new. bad, поскольку нарушает ограничение первичного ключа. Это можно проверить, обратившись к журнальному файлу, load.log:

Record 1: Rejected - Error on table DEPT. ORA-00001: unique constraint (TKYTE.EMP PK) violated

Теперь загрузим BAD-файл с помощью почти такого же управляющего файла. Надо изменить имя таблицы, в которую будут загружаться данные: DEPT на DEPT WORKING, а также использовать конструкцию REPLACE вместо APPEND. Таблица, в которую будут загружаться данные, создается следующим образом:

tkyte@TKYTE816> create table dept working 2 as

Глава 9

3 select * from dept

4 where 1=0

Table created.

tkyte@TKYTE816> alter table dept working

2 add constraint dept working pk

3 primary key(deptno)

Table altered.

При загрузке данных не забудьте указать параметр ВAD=<имя файла> в командной строке, чтобы не произошло чтения и записи одного и того же файла!

C:\sqlldr>sqlldr userid=tkyte/tkyte control=load working.etl bad=w}orking.bad data=new.bad

После загрузки в таблице DEPT WORKING окажется одна строка. Если в файле WORKING.BAD окажутся записи, значит, они действительно плохие, нарушают одно из требований целостности и требуют отдельного рассмотрения. Теперь, когда и эта загрузка выполнена, можно обновить существующие строки в таблице DEPT с помощью оператора UPDATE:

tkyte@TKYTE816> set autotrace on explain

tkyte@TKYTE816> update (select

2 3 4 5 6

8 9 10 11 12

dept.dname dept.loc

dept.last updated w.dname w.loc

w.last updated from dept working where dept.deptno = dept dname = w dname, dept loc = w loc, dept last updated = w last updated

/*+ ORDERED USE NL(dept) dept dname, dept loc,

dept last updated, w dname, w loc,

w last updated W, dept w.deptno)

13 /

1 row updated.

Execution Plan

UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=67 Bytes=5226) UPDATE OF DEPT

NESTED LOOPS (Cost=83 Card=67 Byte3=5226)

TABLE ACCESS (FULL) OF DEPT WORKING (Cost=l Card=82 TABLE ACCESS (BY INDEX ROWID) OF DEPT (Cost=l Card=8

INDEX (UNIQUE SCAN) OF EMP PK (UNIQUE)

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

LOC LAST UPDA

14-APR-01

DEPTNO DNAME

10 SALES

NEW YORK

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

20 ACCOUNTING VIRGINIA 13-APR-01

30 CONSULTING VIRGINIA 14-APR-01

40 FINANCE VIRGINIA 14-APR-01

50 FINANCE VIRGINIA 02-APR-01

60 FINANCE VIRGINIA 14-APR-01

6 rows selected.

Поскольку рабочая таблица обычно не анализируется, мы используем подсказку оптимизатору, предлагающую использовать таблицу DEPT WORKING как ведущую при соединении. Необходимо, чтобы таблица DEPT WORKING просматривалась полностью. Мы будем изменять строку, соответствующую каждой строке в этой таблице, а выбирать ее из таблицы DEPT для изменения будем по индексу (это указано в строках плана, следующих за NESTED LOOPS). В большинстве случаев такой подход наиболее эффективен.

Загрузка данных из отчетов

Иногда необходимо загрузить данные из текстового отчета. Эти данные представлены в определенном формате, но разбросаны по всему отчету. Например, мне пришлось загружать данные из отчета примерно такого вида:

3205679761 - Detailed Report

July 01, 2000 21:24

Location : location data 1 Status : status 1

July 01, 2000 22:18

Location : location data 2 Status : status 2

3205679783 - Detailed Report

July 01, 2000 21:24

Location : location data 3 Status : status data 3

в следующую таблицу:

tkyte@TKYTE816> create table t

2 (serial no varchar2(20) ,

3 date time varchar2(50) ,

4 location varchar2(100) ,

5 status varchar2(100)

Table created.

Весьма вероятно, что с помощью ряда хитроумных триггеров, пакета PL/SQL для поддержки состояния и нескольких трюков SQLLDR этот отчет можно загрузить не-