ORACLE中UTL_FILE ，DBMS-SQL进行数据库与文件互操作的存储过程

关于UTL_FILE的使用技巧，在网上实用的例子不多，

本人整理了两个简单程序，供大家研究.

utl_file包的应用

数据库-SQL Server

第一步：以管理员用户登陆

如：conn sys/password@sid as sysdba

第二步：设置可操作目录

需要指定utl_file包可以操作的目录。在oracle 10g以前，可以用以下方法：

1、alter system set utl_file_dir='e:/utl' scope=spfile;

2、在init.ora文件中，配置如下：

      UTL_FILE=E:/utl或者UTL_FILE_DIR=E:/utl

在oracle 10g中建议用以下方法配置：CREATE DIRECTORY utl AS 'E:/utl';

参见oracle online:

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

第三步：授权给指定用户，以便执行utl_file

GRANT EXECUTE ON utl_file TO scott;

 

第四步：conn scott/tiger

就可以正常使用utl_file了。

 

下边是将数据库数据写入文件中:

 

create or replace procedure UP_DATA_TO_TXT

(

p_query in varchar2,

--传入相关的SELECT 语句 严格按如下格式'select id from tablename where pp=ww'

p_dir in varchar2,  --utl_file允许的路径，请查看相关文档)

p_filename in varchar2  --要生成的文件名字(形如:aa而不必是aa.txt)名字就可以了

) is

 

l_cursor number;

l_row number; --执行行数

l_id varchar2(40);

l_name varchar2(80);

l_column_value varchar2(1000);

--

l_output utl_file.file_type;

l_line varchar2(4000):=null;

--

l_colcnt integer;

l_desctbl DBMS_SQL.DESC_TAB;

l_cnt number default 0;

first_column_value varchar2(1000);

 

 

 

begin

l_cursor:=dbms_sql.open_cursor;

dbms_sql.parse(l_cursor,p_query,dbms_sql.native); --分析语句

dbms_sql.describe_columns(l_cursor,l_colcnt,l_desctbl);--渲染列，以得到列数

----

for i in 1..l_colcnt loop--定义列，

DBMS_SQL.DEFINE_COLUMN(l_cursor,i,l_column_value,1000);

 

end loop;

 

/*

DBMS_SQL.DEFINE_COLUMN(l_cursor,1,l_id,40);

DBMS_SQL.DEFINE_COLUMN(l_cursor,2,l_name,80);

*/

-----

l_row:=dbms_sql.execute(l_cursor); --执行语句

--

l_output:=utl_file.fopen(p_dir,p_filename||'.txt','w',32760);

LOOP

   IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN

   -------------------

   l_line:=null;

   for i in 2..l_colcnt loop--得到此行列的值

   DBMS_SQL.COLUMN_VALUE(l_cursor,1,first_column_value);

   DBMS_SQL.COLUMN_VALUE(l_cursor,i,l_column_value);

   

    l_line:=l_line||'|'||l_column_value;

end loop;

   /*

      DBMS_SQL.COLUMN_VALUE(l_cursor,1,l_id);

      DBMS_SQL.Column_Value(l_cursor,2,l_name);

      */

    

    ------------------

    l_line:=first_column_value||l_line;

      utl_file.put_line(l_output,l_line);

     

     

   else

       exit;

   end if;

 

end loop ;

--

utl_file.fclose(l_output);

 

dbms_sql.close_cursor(l_cursor); --关闭光标

 

EXCEPTION

 

when no_data_found then

utl_file.fclose(l_output);

when utl_file.invalid_path then

raise_application_error(-20001,'INVALID_PATH!');

when utl_file.invalid_mode then

raise_application_error(-20002,'INVALID_MODE!');

when utl_file.invalid_filehandle then

raise_application_error(-20003,'INVALID_FILEHANDLE!');

when utl_file.invalid_operation then

raise_application_error(-20004,'INVALID_OPERATION!');

when utl_file.read_error then

raise_application_error(-20005,'READ_ERROR!');

when utl_file.write_error then

raise_application_error(-20006,'WRITE_ERROR!');

when utl_file.internal_error then

raise_application_error(-20007,'INTERNAL_ERROR!');

 

WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(l_cursor) THEN

DBMS_SQL.CLOSE_CURSOR(l_cursor);

END IF;

 

RAISE;

 

 

end UP_DATA_TO_TXT;

 

下边是将TXT文件中的数据写入数据库

create or replace function f_txt_to_db(

p_table in varchar2,--表的名字

p_enames in varchar2,--形如'col1,col2。。’形式的列名

p_dir in varchar2,--目录,utl_file_dir的目录

p_filename in varchar2,--输入的数据文件名字

p_delimiter in varchar2 default '|'--输入数据文件中的分隔符

) return number is

  Result number;

 

 

  l_input utl_file.file_type;

  l_cursor integer default dbms_sql.open_cursor;

  l_buffer varchar2(4000);

  l_lastline varchar2(4000);

  l_colcnt number default 0;

  l_sep char(1) default null;

  l_status integer;

  l_cnt number;

begin

l_input := utl_file.fopen(p_dir,p_filename,'r',4000);

 

l_buffer :='insert into '||p_table||

           '('||p_enames||')values(';

--下边是用来计算‘，’逗号的个数来确定列数

l_colcnt:=length(p_enames)-

             length(replace(p_enames,',',''))+1;

            

            

       for i in 1..l_colcnt

       loop

          l_buffer:=l_buffer||l_sep||':b'||i;

          l_sep:=',';

       end loop;

       l_buffer:=l_buffer||')';

       /*

       这样就得到了形如下边的SQL语句

       insert into T(c1,c2...)values(:b1,:b2...）

       */

    dbms_sql.parse(l_cursor,l_buffer,dbms_sql.native);

   

    loop

       begin

           utl_file.get_line(l_input,l_lastline);

       exception

           when NO_DATA_FOUND then

           exit;

       end;

       l_buffer:= l_lastline||p_delimiter;

       --以下是将变量绑定相关的列值

       for i in 1.. l_colcnt

         loop

              dbms_sql.bind_variable(l_cursor,':b'||i,

                                substr(l_buffer,1,

                                  instr(l_buffer,p_delimiter)-1));

               l_buffer:=substr(l_buffer,

                         instr(l_buffer,p_delimiter)+1);

          end loop;

         

       l_status:=dbms_sql.execute(l_cursor);

       l_cnt:=l_cnt+1;

  

    end loop;                

    dbms_sql.close_cursor(l_cursor);

    utl_file.fclose(l_input);

 

  commit;

 

  return(l_cnt);

  exception

      when others then

      dbms_sql.close_cursor(l_cursor);

        if (utl_file.is_open(l_input)) then

            utl_file.fclose(l_input);

         end if;

        

         RAISE;

 

end f_txt_to_db;

 

关于UTL_FILE和DBMS_SQL的操作后台数据基本上都是如此，以上两个程序使用得一切都是动态执行，只要每次将相关参数写好就可以了。

有什么其它方面的技术支持，请联系: mxfhhh@163.com

参考书：one-on-one-oracle