达梦数据库外部表插入数据

1. 创建一个目录

CREATE OR REPLACE DIRECTORY "EXTDIR_2" AS 'd:\ext_table_2';

2. 在d:\ext_table_2目录下准备一个数据文件data.txt

内容如下：

10|9|7

4|3|2

3. 创建一个空表，预期将d:\ext_table_2\data.txt文件中的数据插入到该表

CREATE TABLE EXT_TABLE0 ( C1 INT, C2 INT, C3 INT ) ;

4. 创建一个存储过程，在存储过程中创建一个外部表，借助外部表将数据查询插入到实体表

create or replace procedure imp_ext_table(owner varchar(500), tname varchar(500),dataStr varchar(500)) as

sqlstr varchar;

begin

sqlstr='';

select 'CREATE EXTERNAL TABLE if not exists ' || owner || '.EXT_' || tname || substr(tabledef(owner,tname),

instr(tabledef(owner,tname),'('),

instr(tabledef(owner,tname),'STORAGE')-instr(tabledef(owner,tname),'(')-1) || ' FROM DATAFILE DEFAULT DIRECTORY EXTDIR_2 LOCATION(' || '''' || dataStr || ''')' ||

' PARMS(FIELDS DELIMITED BY ''|'', RECORDS DELIMITED BY 0x0d0a)'

into sqlstr from dual;

execute immediate sqlstr;

execute immediate 'insert into ' || owner || '.' || tname || ' select * from '|| owner ||'.EXT_' || tname;

commit;

execute immediate 'drop table ' || owner ||'.EXT_' || tname;

end;

5. 调用存储过程，将文件中的数据插入到实体表

call imp_ext_table(user,'EXT_TABLE0','data.txt');

6. 检查数据是否成功插入

select * from EXT_TABLE0;