Internet上還有一種免費的MySQL數據庫很流行。有些時候我們需要把Oracle里的數據導入MYSQL里。
生成insert into 表名 .... 是一種很簡單直接的方法。
今年六月份從www.arikaplan.com/oracle.Html看到一個可以生成insert into 表名 ....語句的存儲過程genins_output。
我按中文習慣的時間格式YYYY-MM-DD HH24:MI:SS改了改，并新寫了一個存儲過程genins_file.sql。
它可以把小于16383條記錄表里的數據導成(insert into 表名 ....)OS下文件。
調用它之前，DBA要看看數據庫的初始化參數 UTL_FILE_DIR 是否已經正確地設置:
SQL> show parameters utl_file_dir;
可以看到該參數的當前設置。
假如沒有值，必須修改數據庫的initsid.ora文件，將utl_file_dir 指向一個你想用PL/SQL file I/O 的路徑。
重新啟動數據庫。此參數才生效。
調用它，可以把表里的數據生成(insert into 表名 ....)OS下文件的過程genins_file方法:
SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql');
表名,可變 生成OS下文件名,可變
utl_file_dir路徑名,不變(我設置的是/oracle/logs)
SQL> exit
可以在OS目錄/oracle/logs下看到insert_emp.sql文件。
注重事項: 生成(insert into 表名 ....)OS下文件最多32767行。
因為我一條insert分成兩行,所以最多處理16383條記錄的表。
在MYSQL數據庫里運行insert_emp.sql，就可以方便地把oracle數據轉移到MYSQL數據庫里。
CREATE OR REPLACE PROCEDURE genins_file(
p_table IN varchar2,
p_output_folder IN VARCHAR2,
p_output_file IN VARCHAR2)
IS
--
l_column_list VARCHAR2(32767);
l_value_list VARCHAR2(32767);
l_query VARCHAR2(32767);
l_cursor NUMBER;
ignore NUMBER;
l_insertline1 varchar2(32767);
l_insertline2 varchar2(32767);
cmn_file_handle UTL_FILE.file_type;
--
FUNCTION get_cols(p_table VARCHAR2)
RETURN VARCHAR2
IS
l_cols VARCHAR2(32767);
CURSOR l_col_cur(c_table VARCHAR2) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_cols := null;
FOR rec IN l_col_cur(p_table)
LOOP
l_cols := l_cols rec.column_name ',';
END LOOP;
RETURN substr(l_cols,1,length(l_cols)-1);
END;
--
FUNCTION get_query(p_table IN VARCHAR2)
RETURN VARCHAR2
IS
l_query VARCHAR2(32767);
CURSOR l_query_cur(c_table VARCHAR2) IS
SELECT 'decode('column_name',null,''null'','
decode(data_type,'VARCHAR2',''''''''''column_name ''''''''''
,'DATE','''''''''to_char('column_name',''YYYY-MM-DD HH24:MI:SS'')'''''''''
,column_name
) ')' column_query
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_query := 'SELECT ';
FOR rec IN l_query_cur(p_table)
LOOP
l_query := l_query rec.column_query ''',''';
END LOOP;
l_query := substr(l_query,1,length(l_query)-7);
RETURN l_query ' FROM ' p_table;
END;
--
BEGIN
l_column_list := get_cols(p_table);
l_query := get_query(p_table);
l_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
ignore := DBMS_SQL.EXECUTE(l_cursor);
--
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);
END IF;
LOOP
IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
l_insertline1:='INSERT INTO 'p_table' ('l_column_list')';
l_insertline2:=' VALUES ('l_value_list');';
UTL_FILE.put_line (cmn_file_handle, l_insertline1);
UTL_FILE.put_line (cmn_file_handle, l_insertline2);
ELSE
EXIT;
END IF;
END LOOP;
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
UTL_FILE.FCLOSE (cmn_file_handle);
END IF;
END;
/
