oracle的文件读写

从数据库读记录写入文件中
create or replace function dump_csv( p_tname in varchar2, --表名
p_dir in varchar2 , --目录
p_filename in varchar2, --文件名
p_query in varchar2 DEFAULT ' where 1=1 ', --查询条件
p_separator in varchar2 default ',' ) --分隔符
return number --返回值
AUTHID CURRENT_USER --Authid Current_User 进行权限分配
is
exp_output utl_file.file_type;
exp_theCursor integer default dbms_sql.open_cursor; --创建游标
exp_columnValue varchar2(2000); --导出表的列名
exp_status integer; --执行动态SQL的状态
exp_colCnt number default 0; --统计
exp_separator varchar2(10) default ''; --分隔符
exp_cnt number default 0; --统计列的数目
str_sql VARCHAR2(2000); --要拼的SQL
substr_sql VARCHAR2(2000); --截取后的SQL
str_length INTEGER; --截取前拼好的SQL长度
cursor cursor_column --游标：通过数据字典查找出表有哪些字段
is select column_name,data_type
from user_tab_columns where table_name=upper(p_tname);
begin
exp_output := utl_file.fopen( p_dir, p_filename||'.csv', 'w' ); --打开文件流
for col in cursor_column loop --循环读取列名
if col.data_type='DATE' then --判断列的类型
str_sql:=str_sql||'to_char('||col.column_name||',''yyyy-mm-dd hh24:mi:ss''),';--如果是DATE类型格式化
else
str_sql:=str_sql||col.column_name||','; --否则累加
end if;
select length(str_sql) into str_length from dual; --获取拼好后str_sql的长度
if str_length <>0 then --判断str_sql的长度
select substr(str_sql,0,length(str_sql)-1) into substr_sql from dual; --如果大于零就截取最后一位的逗号
end if;
utl_file.put(exp_output,upper(col.column_name||',')); --写入文件
exp_colCnt:=exp_colCnt+1; --统计列的数目
end loop;
utl_file.put_line(exp_output,''); --换行
substr_sql:=rtrim(substr_sql,'||chr(9)||'); --去年空格
str_sql:='select '||substr_sql||' from '||p_tname||' '||p_query; --措查询语句
dbms_sql.parse( exp_theCursor, str_sql, dbms_sql.native ); --向服务器发出一个语句并检查这个语句的语法和语义错误
for i in 1 .. exp_colCnt loop
begin
dbms_sql.define_column( exp_theCursor, i, exp_columnValue, 2000 ); --保存记录
exception
when others then --判断异常如果为1007为EXIT,否则就抛出异常
if ( sqlcode = -1007 ) then exit;
else
raise;--抛出异常
end if;
end;
end loop;
exp_status := dbms_sql.execute(exp_theCursor); --执行此语句，因为执行的是查询，所以必须跟着Fetch_Rows函数并为单个行检索数据
loop
exit when ( dbms_sql.fetch_rows(exp_theCursor) <= 0 ); --如果记录行数小于零就EXIT;
exp_separator := ''; --分隔符
for i in 1 .. exp_colCnt loop --循环读取记录
dbms_sql.column_value( exp_theCursor, i,exp_columnValue ); --保存对应列的变量中
select convert(exp_columnValue,'ZHS16GBK','AL32UTF8') into exp_columnValue from dual;--转码
utl_file.put( exp_output, exp_separator ||exp_columnValue ); --写入文件中
exp_separator := p_separator; --分隔符赋值
end loop;
utl_file.new_line( exp_output ); --换行
exp_cnt := exp_cnt+1; --统计
end loop;
dbms_sql.close_cursor(exp_theCursor); --关闭动态游标
utl_file.fclose( exp_output ); --关闭文件流
return exp_cnt; --返回统计数
end dump_csv;

///------------------------------------------
create or replace procedure test_dump_csv --储存过程
as
exp_rows number;
begin
exp_rows := dump_csv( 'stuinfo', 'UTL_FILE_DIR', 'stuinfo',' where rownum < 100 order by id asc',','); --调用dump_csv
end;
//--------------------------
exec test_dump_csv

把文件中的记录写入数据中
create or replace procedure p_imp(p_fname varchar2)
as
v_line varchar2(2000); --记录的数据
v_sql Long; --动态sql变量
v_tname varchar2(40); --表名字
inf utl_file.file_type;
v_cloumn varchar(2000); --要插入的列信息
l_cur integer; --创建游标
v_num number; --接收插入SQL受影响行数
begin
v_tname:=substr(p_fname,1,instr(p_fname,'.')-1) ; --截取表的名字
inf:=utl_file.fopen('UTL_FILE_DIR',p_fname,'r'); --打开文件
utl_file.get_line(inf,v_line); --获取列信息
--dbms_output.put_line(v_line); --打印列信息
v_cloumn:=v_line; --给v_cloumn赋值
v_sql:='insert into '|| v_tname; --拼SQL插入语句
loop
begin
utl_file.get_line(inf,v_line); --获取记录
select convert(v_line,'AL32UTF8','ZHS16GBK') into v_line from dual;
--dbms_output.put_line(v_line); --打印记录
--select substr(v_cloumn,0,length(v_cloumn)-1) into v_cloumn from dual; --截取列最后一位的,
select REGEXP_REPLACE( v_line, ',', ''',''') into v_line from dual; --应用正则替换,为','
-- dbms_output.put_line(v_line);
--v_sql:='insert into '|| v_tname || '(' || v_cloumn || ') values ('''|| v_line ||''')';--拼SQL插入语句
v_sql:=v_sql||' select ''' || v_line ||''' from dual union all ' ; --拼SQL插入语句
--dbms_output.put_line(v_sql); --打印记录
exception --异常
when no_data_found then exit ; --如果读不到记录就exit
end;
end loop;
l_cur :=dbms_sql.open_cursor;
dbms_sql.parse(l_cur,'delete from '|| v_tname,dbms_sql.native);
v_num:=dbms_sql.execute(l_cur);
dbms_sql.close_cursor(l_cur);
--commit;
l_cur :=dbms_sql.open_cursor; --打开动态游标
select substr(v_sql,0,length(v_sql)-10) into v_sql from dual; --截取拼好的SQL( union all )
dbms_sql.parse(l_cur,v_sql,dbms_sql.native); --检察SQL是否正确
v_num:=dbms_sql.execute(l_cur); --执行插入语句
commit; --提交
dbms_sql.close_cursor(l_cur); --关闭游标
utl_file.fclose(inf); --关闭读写流
dbms_output.put_line('写入成功'); --打印信息
--exception
--when others then
--dbms_output.put_line('写入失败');
--rollback;
--utl_file.fclose(inf);
end p_imp;
//------------------
set serveroutput on;
//--------------------
exec p_imp('test.csv');