mysql oradba_ORACLE MYSQL互相导入数据

只是一个列子和方法具体的选项按需求添加

1、ORACLE卸载数据

DECLARE

row_result varchar2(4000);

selectsql  varchar2(4000);

qrycursor  SYS_REFCURSOR;

txt_handle UTL_FILE.file_type;

BEGIN

--selectsql  := 'select id || '','' || name || '','' || dti  from testdump where name = ''gaopeng''   and dti > to_date(''2015-03-17 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')';

selectsql  := 'select nvl(to_char(id),''\N'')||'',''||nvl(name,''\N'')||'',''||nvl(to_char(dt,''yyyy-mm-dd''),''\N'') from txt';

txt_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'testnull1.txt', 'w', 32767);

open qrycursor for selectsql;

loop

fetch qrycursor

into row_result;

exit when qrycursor%notfound;

UTL_FILE.PUT_LINE(txt_handle, row_result);

end loop;

--关闭游标

close qrycursor;

UTL_FILE.FCLOSE(txt_handle);

end;

2、导入到MYSQL

MYSQL LOAD DATA INFILE

1,gaopeng,2010-10-10

2,gaopeng2,2011-11-12

3,gaopeng3,2011-12-13

load data infile '/tmp/testnull1.txt' into table txt fields terminated by ',' LINES TERMINATED BY '\n';

3、MYSQL卸载数据

SELECT  id,name,dt into outfile "/tmp/test2.txt"

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

FROM  txt ;

4、导入到ORACLE

1,gaopeng,2010-10-10

2,gaopeng2,2011-11-12

3,gaopeng3,2011-12-13

append 可以换成 replace

按需求修改

load data

infile '/home/oradba/loadr/in.txt'

badfile '/home/oradba/loadr/bad.txt'

DISCARDFILE '/home/oradba/loadr/dc.txt'

append

into table txt

fields terminated by ','

(id,

name,

dt char "to_date(:dt,'YYYY-MM-DD')"

)

sqlldr userid=ppzhu/gelc123 control=/home/oradba/loadr/ld.ctl log=/home/oradba/loadr/log.log

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30496894/viewspace-1825218/，如需转载，请注明出处，否则将追究法律责任。