数据加载与卸载：SQLLDR使用指南

### 数据加载与卸载：SQLLDR使用指南 #### 1. 通过SQLLDR加载LOB数据 使用SQLLDR加载大型对象（LOB）数据有多种方法，常见的有两种：一是数据与其他数据内联；二是数据存储在外部，输入数据包含要随行加载的文件名。 ##### 1.1 内联LOB数据加载 内联LOB数据通常包含换行符和其他特殊字符，一般会使用特定方法加载。以下是具体操作步骤： 1. 修改表结构：将`DEPT`表的`COMMENTS`列改为`CLOB`类型。 ```sql EODA@ORA12CR1> truncate table dept; Table truncated. EODA@ORA12CR1> alter table dept drop column comments; Table altered. EODA@ORA12CR1> alter table dept add comments clob; Table altered. ``` 2. 准备数据文件`demo.dat`： ```plaintext 10, Sales,Virginia,This is the Sales Office in Virginia| 20,Accounting,Virginia,This is the Accounting Office in Virginia| 30,Consulting,Virginia,This is the Consulting Office in Virginia| 40,Finance,Virginia,"This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: ""You will need to double up those quotes!"" to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->"| ``` 3. 创建控制文件： ```plaintext LOAD DATA INFILE demo.dat "str X'7C0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS char(1000000) ) ``` 注：此示例适用于UNIX/Linux，Windows系统的`STR`设置应为`'7C0D0A'`。加载数据时，需为`COMMENTS`列指定`CHAR(1000000)`，因为SQLLDR默认输入字段长度为`CHAR(255)`。 ##### 1.2 外部LOB数据加载 常见场景是数据文件包含要加载到LOB中的文件名，而非将LOB数据与结构化数据混合。SQLLDR将这种额外的数据文件称为`LOBFILE`。`LOBFILE`的数据格式有固定长度字段、分隔字段和长度/值对三种，最常见的是由文件结束符（EOF）终止的分隔字段。 以下是具体操作步骤： 1. 创建表： ```sql EODA@ORA12CR1> create table lob_demo 2 ( owner varchar2(255), 3 time_stamp date, 4 filename varchar2(255), 5 data blob 6 ) 7 / Table created. ``` 2. 生成输入文件并创建控制文件： ```plaintext LOAD DATA INFILE * REPLACE INTO TABLE LOB_DEMO ( owner position(14:19), time_stamp position(31:42) date "Mon DD HH24:MI", filename position(44:100), data LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rwxr-xr-x 1 oracle dba 14889 Jul 22 22:01 demo1.log_xt -rwxr-xr-x 1 oracle dba 123 Jul 22 20:07 demo2.ctl -rwxr-xr-x 1 oracle dba 712 Jul 23 12:11 demo.bad -rwxr-xr-x 1 oracle dba 8136 Mar 9 12:36 demo.control_files -rwxr-xr-x 1 oracle dba 825 Jul 23 12:26 demo.ctl -rwxr-xr-x 1 oracle dba 1681 Jul 23 12:26 demo.log -rw-r----- 1 oracle dba 118 Jul 23 12:52 dl.sql -rwxr-xr-x 1 oracle dba 127 Jul 23 12:05 lob_demo.sql -rwxr-xr-x 1 oracle dba 171 Mar 10 13:53 p.bsh -rwxr-xr-x 1 oracle dba 327 Mar 10 11:10 prime.bsh -rwxr-xr-x 1 oracle dba 24 Mar 6 12:09 run_df.sh ``` 运行SQLLDR后，可查看`LOB_DEMO`表内容： ```sql EODA@ORA12CR1> select owner, time_stamp, filename, dbms_lob.getlength(data) 2 from lob_demo 3 / ``` 结果如下： | OWNER | TIME_STAM | FILENAME | DBMS_LOB.GETLENGTH(DATA) | | ---- | ---- | ---- | ---- | | oracle | 22 - JUL - 14 | demo1.log_xt | 14889 | | oracle | 22 - JUL - 14 | demo2.ctl | 123 | | oracle | 23 - JUL - 14 | demo.bad | 712 | | oracle | 09 - MAR - 14 | demo.control_files | 8136 | | oracle | 23 - JUL - 14 | demo.ctl | 825 | | oracle | 23 - JUL - 14 | demo.log | 0 | | oracle | 23 - JUL - 14 | dl.sql | 118 | | oracle | 23 - JUL - 14 | lob_demo.sql | 127 | | oracle | 10 - MAR - 14 | p.bsh | 171 | | oracle | 10 - MAR - 14 | prime.bsh | 327 | | oracle | 06 - MAR - 14 | run_df.sh | 24 | 注：`demo.log`文件大小为0是因为在加载时文件被清空。这种方法同样适用于CLOB。 ##### 1.3 向对象列加载LOB数据 有时需要向包含LOB的复杂对象类型的表中加载数据，例如使用图像功能时。以`ORDIMAGE`类型列为例，以下是具体操作步骤： 1. 创建表： ```sql EODA@ORA12CR1> create table image_load( 2 id number, 3 name varchar2(255), 4 image ordsys.ordimage 5 ) 6 / Table created. ``` 2. 创建控制文件： ```plaintext LOAD DATA INFILE * INTO TABLE image_load REPLACE FIELDS TERMINATED BY ',' ( ID, NAME, file_name FILLER, IMAGE column object ( SOURCE column object ( LOCALDATA LOBFILE (file_name) TERMINATED BY EOF NULLIF file_name = 'NONE' ) ) ) BEGINDATA 1,icons,icons.gif ``` 这里引入了两个新结构： - `COLUMN OBJECT`：用于构建正确的对象列引用。 - `NULLIF FILE_NAME = 'NONE'`：当`FILE_NAME`字段包含`NONE`时，向对象列加载`NULL`。 加载完成后，通常需要使用PL/SQL对数据进行后处理： ```plsql begin for c in ( select * from image_load ) loop c.image.setproperties; end loop; end; / ``` #### 2. 从存储过程调用SQLLDR 不能直接从存储过程调用SQLLDR，因为它是一个命令行程序。不过，可以用Java或C编写外部过程来运行SQLLDR，但这与直接“调用”不同，加载过程在另一个会话中进行，不受事务控制，还需解析日志文件来确定加载是否成功。 过去，在Oracle 9i之前，可实现类似SQLLDR的过程，例如： - 用PL/SQL编写小型SQLLDR，使用`BFILES`读取二进制数据或`UTL_FILE`读取文本数据进行解析和加载。 - 用Java编写小型SQLLDR，可利用更多Java例程。 - 用C编写SQLLDR并作为外部过程调用。 #### 3. SQLLDR注意事项 - **TRUNCATE行为不同**：SQLLDR的`TRUNCATE`选项与SQL*Plus等工具的`TRUNCATE`不同，它使用扩展形式`truncate table t reuse storage`，不会释放已分配的区，只是将其标记为可用空间。若不需要此效果，可在执行SQLLDR前截断表。 - **默认长度为CHAR(255)**：SQLLDR输入字段默认长度为2