oracle的文件读写

双4562

从数据库读记录写入文件中
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');