将oracle SQL语句执行结果导入文本文件

来源：互联网发布：上单js出装编辑：程序博客网时间：2024/05/21 21:46

将oracle SQL语句执行结果导入文本文件

1 创建directory
create or replace directory DIR
as 'd:/temp';
为用户赋予该directory的权限

grant all on directroy dir to public;

2 创建函数function

create or replace function tofile(p_query in varchar2,
                                  p_separator in varchar2,
                                  p_dir in varchar2,
                                  p_filename in varchar2)
return number
is
l_cursor      integer default dbms_sql.open_cursor;
l_output      utl_file.file_type;
l_columnValue varchar2(2000);
l_status      integer;
l_colCnt      number default 0;
l_separator   varchar2(10) default '';
l_cnt number default 0;
rec_tab       dbms_sql.desc_tab;
col_num       number;
begin
l_output := utl_file.fopen( p_dir,p_filename,'w' );
dbms_sql.parse( l_cursor ,p_query, dbms_sql.native );

/*写入该SQL查询结果的列名,以空格隔开*/
dbms_sql.describe_columns(l_cursor,l_colCnt,rec_tab);
col_num := rec_tab.first;
if( col_num is not null) then
     loop
       utl_file.put( l_output , l_separator||rec_tab(col_num).col_name);
       col_num := rec_tab.next(col_num);
       l_separator :=' ';
      EXIT WHEN (col_num IS NULL);
     end loop;
end if;
    utl_file.new_line( l_output );

/*写入SQL查询结果的列值*/
for i in 1.. l_colCnt loop
         dbms_sql.define_column( l_cursor , i,
                                 l_columnValue,2000);
end loop;
l_status :=dbms_sql.execute(l_cursor);
loop
     exit when ( dbms_sql.fetch_rows( l_cursor ) <= 0 );
     l_separator :='';
     for i in 1 .. l_colCnt loop
        dbms_sql.column_value( l_cursor ,i,
                              l_columnValue);
        utl_file.put( l_output , l_separator || l_columnValue );
        l_separator := p_separator;
     end loop;
     utl_file.new_line( l_output );
     l_cnt := l_cnt+1;
end loop;

dbms_sql.close_cursor( l_cursor );
utl_file.fclose( l_output );
return l_cnt;
end tofile;

3 创建执行该function的存储过程procedure

create or replace procedure test_tofile
as
    l_rows       number;
    l_filename   varchar2(100);
    v_sql        varchar2(100);
    l_temp       varchar2(100);
begin
    v_sql := 'select to_char(sysdate,''yyyymmdd'') from dual';

EXECUTE IMMEDIATE v_sql
INTO l_temp;

   l_filename := 'dbperform_'|| l_temp || '.txt';
   l_rows := tofile( 'SELECT fs.tablespace_name TABLESPACE_NAME,
                              df.totalspace TABLESPACE_TOTAL_SIZE,
                              (df.totalspace - fs.freespace) MB_USED,
                              fs.freespace MB_FREE,
                              round(100 * (fs.freespace / df.totalspace),2) PCT_FREE
                       FROM
                       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name ) df,
                       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace FROM dba_free_space GROUP BY tablespace_name ) fs
                        WHERE df.tablespace_name = fs.tablespace_name(+)
                        order by PCT_FREE ASC',
                        ',', 'DIR', l_filename );

dbms_output.put_line(l_rows);
end;

/*这里的'DIR'指明了directory dir,不过,由于是在WINDOWS环境,需要大写该directory名*/

4 调用该存储过程

SQL>exec test_tofile();