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

30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001

В результате, таблица DEPT будет выглядеть так:

tkyte@TKYTE816> select * from dept;

DEPTNO DNAME LOC LAST UPDA

10 Sales Virginia 01-MAY-00

20 Accounting Virginia 21-JON-99

30 Consulting Virginia 05-JAN-00

40 Finance Virginia 15-MAR-01

Вот так просто. Достаточно указать формат даты в управляющем файле и утилита SQLLDR автоматически преобразует данные в даты. В некоторых случаях может потребоваться использовать более сложные функции SQL. Например, если входной файл содержит даты в различных форматах: иногда с компонентом времени, иногда - без, иногда в формате DD-MON-YYYY, иногда - в формате DD/MM/YYYY и т.д. В следующем разделе мы рассмотрим, как с помощью функций в SQLLDR решить эти проблемы.

Загрузка данных с использованием последовательностей и других функций

В этом разделе мы рассмотрим, как использовать последовательности и функции SQL при загрузке данных. Помните, однако, что для использования последовательностей и функций SQL необходимо обращаться к SQL-машине, поэтому при непосредственной загрузке использовать их нельзя.

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

LOAD DATA INFILE * INTO TABLE DEPT REPLACE

FIELDS TERMINATED BY , (DEPTNO,

DNAME upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED date dd/mm/yyyy

BEGINDATA

10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001

Глава 9

В результате, в базе данных окажется следующее:

tkyte@TKYTE816> select * from dept;

DEPTNO DNAME LOC ENTIRE LINE LAST UPDA

10 SALES VIRGINIA 01-MAY-00

20 ACCOUNTING VIRGINIA 21-JUN-99

30 CONSULTING VIRGINIA 05-JAN-00

40 FINANCE VIRGINIA 15-MAR-01

Обратите внимание, как просто перевести данные в верхний регистр - достаточно применить функцию UPPER к связываемой переменной. Учтите, что в SQL-функциях можно использовать любые столбцы, независимо от того, значение какого столбца определяет функция. Это означает, что значение столбца может задаваться как функция от нескольких других столбцов. Например, если надо загрузить данные в столбец ENTIRE LINE, можно было бы использовать оператор конкатенации SQL. В данном случае все несколько сложнее, чем кажется. Пока что в записях загружаемых данных - четыре поля. Если просто добавить ENTIRE LINE в управляющий файл следующим образом:

LOAD DATA INFILE * INTO TABLE DEPT REPLACE

FIELDS TERMINATED BY , (DEPTNO,

DUMB upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED date dd/mm/yyyy ,

ENTIRE LINE :deptno:dname:loc:last updated

BEGINDATA

10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001

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

Record 1: Rejected - Error on table DEPT, column ENTIRE LINE. Column not found before end of logical record (use TRAILING NULLCOLS)

В данном случае утилита SQLLDR сообщает, что данных в записи (полей) не хватило для всех столбцов. Решение этой проблемы - простое, и утилита SQLLDR даже подсказывает, что делать: USE TRAILING NULLCOLS. В результате SQLLDR будет связывать значение Null со столбцом данных, если в загружаемой записи данных для него нет. В данном случае добавление TRAILING NULLCOLS приведет к тому, что связываемая переменная :ENTIRE LINE будет получать значение Null. Поэтому мы попробуем еще раз, со следующим управляющим файлом:

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

LOAD DATA INFILE * INTO TABLE DEPT REPLACE

FIELDS TERMINATED BY ,

TRAILING NULLCOLS

(DEPTNO,

DNAME upper(:dname) ,

LOC upper(:loc) ,

LAST UPDATED date dd/mm/yyyy,

ENTIRE LINE :deptno:dname:loc:last updated

BEGINDATA

10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,Virginia,5/1/2000 40,Finance,Virginia,15/3/2001

Теперь в таблице будут такие данные:

tkyte@TKYTE816> select * from dept;

DEPTNO DNAME LOC ENTIRE LINE LAST UPDA

10 SALES VIRGINIA 10SalesVirginial/5/2000 01-MAY-00

20 ACCOUNTING VIRGINIA 20AccountingVirginia21/6/1999 21-JUN-99

30 CONSULTING VIRGINIA 30ConsultingVirginia5/l/2000 05-JAN-OO

40 FINANCE VIRGINIA 40FinanceVirginial5/3/2001 15-MAR-01

Понять, почему этот трюк удался, можно, если разобраться, как утилита SQLLDR строит операторы INSERT. Утилита SQLLDR просматривает заданный управляющий файл и находит в нем столбцы DEPTNO, DNAME, LOC, LAST UPDATED и ENTIRE LINE. Затем она создает пять связываемых переменных, имена которых совпадают с именами столбцов. Обычно, если функции не используются, она строит следующий простой оператор INSERT:

INSERT INTO DEPT (DEPTNO, DNAME, LOC, LAST UPDATED, ENTIRE LINE) VALUES (:DEPTNO, :DNAME, :LOC, :LAST UPDATED, :ENTIRE LINE) ;

Затем она разбирает входной поток, присваивает значения связываемым переменным и выполняет оператор. При использовании функций, SQLLDR включает их в оператор INSERT. В рассмотренном выше примере оператор INSERT, созданный утилитой SQLLDR, будет выглядеть так:

INSERT INTO Т (DEPTNO, DNAME, LOC, LAST UPDATED, ENTIRE LINE) VALUES (:DEPTNO, upper (:dname) , upper (:loc), :last updated, :deptno:dname:loc:last updated);

Поэтому практически все, что можно придумать и выполнить в операторе SQL, можно включить и в управляющий файл SQLLDR. С учетом наличия оператора CASE в SQL (он добавлен в Oracle 8i), можно создавать мощные и при этом простые схемы загрузки. Предположим, необходимо загрузить даты, которые иногда содержат компонент времени, а иногда - нет. Для этого можно использовать управляющий файл следующего вида: