Oracle中，实现借助创建外部表对表进行数据插入

Oracle中，实现借助创建外部表对表进行数据插入

以向成绩表中导入外部表数据为例，数据为.csv文件数据，使用sqlPlus导入。 首先确保db_grade表（内部表）已创建

- 创建external_grade目录对象

CREATE OR REPLACE DIRECTORY external_grade AS ‘F:\ext_file’;
F:\ext_file这是我们要访问的数据文件db_grade所在的目录

若报错，出现权限不足，则返回到内部表所在用户下，授权。
select distinct privilege from dba_sys_privs where privilege like ‘%DIRECTORY%’;即可查看权限授予。
DROP ANY DIRECTORY 是删除目录的，CREATE ANY DIRECTORY 是创建目录的

现在就来授予staffuser用户创建目录的权限(可以顺便把删除目录的也给了）：
GRANT DROP ANY DIRECTORY TO staffuser;
GRANT CREATE ANY DIRECTORY TO staffuser;

- 创建外部表ext_grade

CREATE TABLE ext_grade (
register_no VARCHAR2(20),
course_no VARCHAR2(10),
work_id VARCHAR2(10),
final_grade VARCHAR2(5),
college_no VARCHAR2(10)
–registered_date VARCHAR2(20),
–registered_year NUMBER(4),
–registered_term NUMBER(1),
–makeup_flag CHAR(2)
) ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY external_grade
ACCESS PARAMETERS (
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE null)
LOCATION(‘db_grade.csv’)
) REJECT LIMIT UNLIMITED;
**注意：需参考结合外部表的列名与内部表的属性进行创建，外部表不可以有外键约束。
在将源数据文件中的数据类型转换为表定义的列数据类型的时候，有时候会出现错误，例如指定的分隔符不符合要求，因此在创建外部表时，可以指定一些子句来对外部表进行处理，如reject limit,badfile(或 nobadfile),logfile(或nologfile)等

• 使用select语句查询外部表中的数据

select work_id from ext_grade where work_id not in(select work_id from db_teacher);
select college_no from ext_grade where college_no not in(select college_no from db_college);

**注意：如果数据类型转换失败，源文件数据还是无法被读取到外部表的相应列中，因此要注意设定数据类型和数据长度，如日期的类型一般为DATE类型，但是oracle对插入DATE类型的数据格式有严格要求具体参照https://blog.csdn.net/beyondlpf/article/details/6959139，无法直接插进去的，因此在创建外部表是我修改了date数据类型

- 将外部表数据导入到内部表中

insert into db_grade
(register_no,course_no,work_id,final_grade,college_no)
select register_no,trim(course_no),work_id,final_grade,college_no from ext_grade ;

• 使用select语句查询内部表中的数据
  select * from db_garde；
  完成！
  参考并转载自https://blog.csdn.net/hst_gogogo/article/details/84995367，致谢！