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