使用PL/SQL模拟SQLLOAD的功能

原文出自Thomas Kyte《expert one-on-on》

下面这个存储过程实现了从文件加载数据到数据库表的过程
注意：要设置初始化参数utl_file_dir,在下面例子中其值为/home/oracle/work/data

[@more@]

首先创建一个错误信息表：

create table badlog( errm varchar2(4000),
                     data varchar2(4000) );
                    
程序主体：

create or replace function load_data(
                     p_table     in varchar2,             -- 装载表的名字
                     p_cnames    in varchar2,             -- 插入的列名
                     p_dir       in varchar2,             -- 目录
                     p_filename  in varchar2,             -- 装载的文件名字
                     p_delimiter in varchar2 default '|'  -- 输入文件中的分隔符
)
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    l_lastLine      varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    l_errmsg        varchar2(4000);
begin
        /*
         * 打开输入文件,我们期望是简单的定界数据,且行长不超过4000字节.
         */
    l_input := utl_file.fopen( p_dir, p_filename, 'r', 4000 );

    l_buffer := 'insert into ' || p_table ||
                '(' || p_cnames || ') values ( ';
        /*
         * 通过获得当前列名列表的长度,减去去掉逗号后的长度,再加1就是列的数量.
         */
    l_colCnt := length(p_cnames)-
                  length(replace(p_cnames,',',''))+1;

    for i in 1 .. l_colCnt
    loop
        l_buffer := l_buffer || l_sep || ':b'||i;
        l_sep    := ',';
    end loop;
    l_buffer := l_buffer || ')';

        /*
         * 现在我们得到的语句如下:
         * insert into T ( c1,c2,... ) values ( :b1, :b2, ... )
         */
    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );

    loop
       /*
        * 读取数据直到没有为止.
        */
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        /*
         * 当行结尾带上分隔符时更利于分析(parse).
         */
        l_buffer := l_lastLine || p_delimiter;

        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':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;

        /*
         * 执行insert语句,出错时将信息插入badlog表中.
         */
        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                insert into badlog ( errm, data )
                values ( l_errmsg, l_lastLine );
        end;
    end loop;

    /*
     * 关闭并提交
     */
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    return l_cnt;
exception
 when others then
     dbms_sql.close_cursor(l_theCursor);
  if ( utl_file.is_open( l_input ) ) then
   utl_file.fclose(l_input);
  end if;
  RAISE;
end load_data;
/

现在准备测试环境：

drop table t1;
create table t1 ( x int, y int, z int );

[oracle@sms data]$ echo 1,2,3 > t1.dat
[oracle@sms data]$ echo 4,5,6 >>t1.dat
[oracle@sms data]$ echo 7,8,9 >>t1.dat
[oracle@sms data]$ echo 7,NotANumber,9 >> t1.dat

begin
   dbms_output.put_line(
       load_data( 'T1',
                  'x,y,z',
                  '/home/oracle/work/data',
                  't1.dat',
                  ',' ) || ' rows loaded' );
end;
/

select * from badlog;

select * from t1;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-788473/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3898/viewspace-788473/