使用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/642366/viewspace-1067055/，如需轉載，請註明出處，否則將追究法律責任。