oracle抽取mysql_抽取oracle数据到mysql数据库的实现过程

在oracle数据库迁移至mysql数据库，除了oracle数据库模型移到mysql外，还一个重要环节就是要将oracle数据库的数据移到mysql数据库，本人尝试用过多款数据迁移程序，性能都不是很好的，于是自己动手写一个针对于oracle数据库数据迁移到mysql数据程序，其具体过程如下：

1、要抽取mysql表、字段及过滤条件的配制文件imp_data.sql

2、建立一个目录ETL_DIR

3、运行oracle数据库程序P_ETL_ORA_DATA，生成各表的csv数据文件，同时也生成一个导入mysql的脚本文件imp_data.sql

4、导入mysql数据，文件内容如下

load data infile "alarm_hist_inc.csv" into table alarm_hist_inc fields terminated by "," enclosed by "^" lines terminated by "\r\n";

load data infile "button_authority.csv" into table button_authority fields terminated by "," enclosed by "^" lines terminated by "\r\n";

load data infile "c3_sms_hist_inc.csv" into table c3_sms_hist_inc fields terminated by "," enclosed by "^" lines terminated by "\r\n";

load data infile "datapermisson.csv" into table datapermisson fields terminated by "," enclosed by "^" lines terminated by "\r\n";

附：数据库脚本P_ETL_ORA_DATA

CREATE OR REPLACE PROCEDURE P_ETL_ORA_DATA

(

P_ORA_DIR VARCHAR2,

P_DATA_PATH VARCHAR2

) IS

TYPE T_REC IS RECORD(

TBN VARCHAR2(40),

WHR VARCHAR2(4000));

TYPE T_TABS IS TABLE OF T_REC;

V_TABS T_TABS := T_TABS();

V_ETL_DIR VARCHAR2(40) := P_ORA_DIR;

V_LOAD_FILE UTL_FILE.FILE_TYPE;

PROCEDURE ETL_DATA

(

P_SQL_STMT VARCHAR2,

P_DATA_PATH VARCHAR2,

P_TB_NAME VARCHAR2

) IS

BEGIN

DECLARE

V_VAR_COL VARCHAR2(32767);

V_NUM_COL NUMBER;

V_DATE_COL DATE;

V_TMZ TIMESTAMP;

V_COLS NUMBER;

V_COLS_DESC DBMS_SQL.DESC_TAB;

V_ROW_STR VARCHAR2(32767);

V_COL_STR VARCHAR2(32767);

V_SQL_ID NUMBER;

V_SQL_REF SYS_REFCURSOR;

V_EXP_FILE UTL_FILE.FILE_TYPE;

V_