Oracle SQL Loader的详细语法

### Oracle SQL Loader 的详细语法与应用 #### 一、概述 SQL*Loader 是 Oracle 提供的一款强大而灵活的数据加载工具，主要用于将操作系统中的文件数据批量导入到 Oracle 数据库中。相较于其他数据库系统的加载工具，SQL*Loader 提供了更多的自定义选项和加载模式，能够满足各种复杂的加载需求。 #### 二、SQL*Loader 的基本概念 - **控制文件**：SQL*Loader 使用一种特殊的文本文件来控制加载过程，这个文件被称为控制文件。它包含了所有关于数据文件的格式、目标表结构以及加载规则的信息。 - **数据文件**：需要导入的数据存储在文本文件中，称为数据文件。这些文件通常由逗号或其他字符分隔字段。 - **加载模式**：SQL*Loader 支持多种加载模式，包括追加（append）、替换（replace）等。 #### 三、SQL*Loader 的基本使用方法 1. **使用命令行调用 SQL*Loader** ```bash sqlldr 用户名/密码 control=控制文件路径 ``` 2. **控制文件示例** 下面是一个简单的控制文件示例，用于从 CSV 文件中加载数据到 EMP 表： ```plaintext LOAD DATA INFILE 'c:\data\mydata.csv' INTO TABLE emp FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (empno, empname, sal, deptno) ``` 其中 `c:\data\mydata.csv` 是数据文件路径，该文件包含如下数据： ``` 10001,"Scott Tiger",1000,40 10002,"Frank Naude",500,20 ``` 3. **指定记录长度的示例控制文件** 当数据文件中的记录具有固定的长度时，可以通过以下方式指定： ```plaintext LOAD DATA INFILE * REPLACE INTO TABLE departments (dept_position POSITION(02:05) CHAR(4), dept_name POSITION(08:27) CHAR(20) ) BEGIN DATA COSC COMPUTER SCIENCE ENG ENGLISH LITERATURE MATH MATHEMATICS POLY POLITICAL SCIENCE ``` 在此示例中，“*”表示数据文件与控制文件同名，通过 `BEGIN DATA` 指定数据的开始。 #### 四、高级功能 - **加载可变长度记录** 使用 `FIELDS TERMINATED BY` 和 `OPTIONALLY ENCLOSED BY` 来处理可变长度记录： ```plaintext LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (data1, data2) BEGIN DATA 11111,AAAAAAAAAA 22222,"A,B,C,D," ``` - **加载固定位置的数据** 对于固定长度的记录，可以使用 `POSITION` 关键字指定字段的位置： ```plaintext LOAD DATA INFILE * INTO TABLE load_positional_data (data1 POSITION(1:5), data2 POSITION(6:15)) BEGIN DATA 11111AAAAAAAAAA 22222BBBBBBBBBB ``` - **跳过数据行** 可以使用 `SKIP n` 关键字来指定跳过的行数： ```plaintext LOAD DATA INFILE * INTO TABLE load_positional_data SKIP 5 (data1 POSITION(1:5), data2 POSITION(6:15)) BEGIN DATA 11111AAAAAAAAAA 22222BBBBBBBBBB ``` - **导入数据时修改数据** 可以在导入过程中对数据进行转换或修改： ```plaintext LOAD DATA INFILE * INTO TABLE modified_data (rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE,'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22) "to_date(:data3, 'DD-MON-RR')" ) ``` #### 五、导出数据 Oracle 没有直接提供导出数据的工具，但可以通过 SQL*Plus 或 PL/SQL 实现数据导出： - **使用 SQL*Plus 导出数据** ```sql SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON SPOOL oradata.txt SELECT col1 || ',' || col2 || ',' || col3 FROM tab1 WHERE col2 = 'XYZ'; SPOOL OFF ``` - **使用 PL/SQL 包 UTL_FILE 导出数据** ```sql DECLARE fp UTL_FILE.FILE_TYPE; BEGIN fp := UTL_FILE.FOPEN('c:\oradata', 'tab1.txt', 'w'); UTL_FILE.PUT(fp, '%s,%s', 'TextField', 55); UTL_FILE.FCLOSE(fp); END; / ``` 注意：在使用 UTL_FILE 之前，需要更新 `initSID.ora` 文件中的 `utl_file_dir` 参数。 #### 六、总结 SQL*Loader 是一款功能强大的数据加载工具，适用于多种场景下的数据导入任务。通过灵活地配置控制文件，用户可以轻松地实现数据的批量加载和转换，极大地提高了数据迁移和维护的效率。无论是简单的 CSV 文件导入还是复杂的固定位置数据加载，SQL*Loader 都能够提供全面的支持。