最近有大量的导出数据库表为csv操作需求,网上搜索了一下,发现没有导出clob数据类型的。于是自己拿来网上的修改了下,站在别人的肩膀上写了一个可以导出csv包含clob类型的存储过程。其中所有的列值都是使用双引号引起来的。
存储过程内容如下:
create or replace procedure sql_to_csv_clob(p_query in varchar2, --sql query statement
p_dir in varchar2, --the directory of file
p_filename in varchar2, --the export filename
p_max_linesize in number default 32000 --max linesize,must less than 32787
) authid current_user is -- 调用者权限
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
l_clob clob;
begin
--open file
l_output := utl_file.fopen(p_dir, p_filename, 'w', p_max_linesize);
--define date/timestamp format
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
execute immediate 'alter session set nls_timestamp_format=''yyyy-mm-dd hh24:mi:ss.ff9''';
--open cursor
dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);
--dump table column name
for i in 1 .. l_colCnt loop
--输出表头信息,如果不输出表头注释掉如下两行
utl_file.put(l_output,
l_separator || '"' || l_descTbl(i).col_name || '"');
--判断是否是clob类型
if l_descTbl(i).col_type = 112 then
dbms_sql.define_column(l_theCursor, i, l_clob);
else
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end if;
l_separator := ',';
end loop;
utl_file.new_line(l_output); --不输出表头,慈航注释掉
--execute the query statement
l_status := dbms_sql.execute(l_theCursor);
-- generate file
--dump table column value
while (dbms_sql.fetch_rows(l_theCursor) > 0) loop
l_separator := '';--表示文件里面数据行首分隔符
for i in 1 .. l_colCnt loop
if l_descTbl(i).col_type = 112 then
dbms_sql.column_value(l_theCursor, i, l_clob);
--将clob分成3800个字符,如果clob中存在中文,请注意调整长度
utl_file.put(l_output,',"');
for j in 1..ceil(dbms_lob.getlength(l_clob)/3800) loop
utl_file.put(l_output,replace(dbms_lob.substr(l_clob,(j-1)*3800+1,j*3800),'"','""'));
end loop;
utl_file.put(l_output,'"');
else
dbms_sql.column_value(l_theCursor, i, l_columnValue);
--因为我要导入数据到mysql,其中空值null我转换成\N了
utl_file.put(l_output,
l_separator ||'"'||
nvl(trim(both ' ' from replace(l_columnValue,'"','""')),
'\N')||'"');
--utl_file.put( l_output, l_separator || '"' ||trim(both ' ' from replace(l_columnValue,'"','""')) || '"'); -- generate csv file
l_separator := ','; --separator表示文件里面数据分隔符
end if;
end loop;
utl_file.new_line(l_output);
end loop;
--close cursor
dbms_sql.close_cursor(l_theCursor);
--close file
utl_file.fclose(l_output);
exception
when others then
raise;
end sql_to_csv_clob;
命令行调用:exec sql_to_csv_clob(‘select * from test’,‘DB_DIR’,‘test.csv’);
其中,DB_DIR是数据库中的directory。
导出以后的csv,放入mysql数据,使用mysql的load命令。
load命令:
load data infile ‘/var/lib/mysql-files/test.csv’ into table db.table_name
fields
terminated by ‘,’
enclosed by ‘"’;