代码之家 › 专栏 › 技术社区 › John Michael

带缓冲区的utl文件oracle

utl-file plsql optimization performance oracle

John Michael · 技术社区 · 9 年前

从技术上讲,该方法是在大小小于缓冲区的最大长度时串联记录,并在长度更大时写入整个缓冲区

网站摘录(代码):

         IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
            v_buffer := v_buffer || c_eol || r.csv;
         ELSE
            IF v_buffer IS NOT NULL THEN
               UTL_FILE.PUT_LINE(v_file, v_buffer);
            END IF;
         v_buffer := r.csv;
         END IF;

-- constants 
  C_CHR                                  CONSTANT VARCHAR2(2)  := CHR(10);
  C_CHRLEN                               CONSTANT PLS_INTEGER  := LENGTH(C_CHR);
  C_MAXLEN                               CONSTANT PLS_INTEGER  := 32767;

function FN_GET_BUFFER(p_rec IN VARCHAR2, p_buffer IN VARCHAR2) RETURN VARCHAR2
  is
  begin
      IF LENGTH(p_buffer) + C_CHRLEN + LENGTH(p_rec) <= C_MAXLEN THEN
        RETURN p_buffer || C_CHR || p_rec;
      ELSE
        IF p_buffer IS NOT NULL THEN
            RETURN p_buffer;
        END IF;
        RETURN p_rec;
      END IF;
  end FN_GET_BUFFER;

procedure export as 
    l_tmp_file_name VARCHAR2(30);
    l_csv_file_name VARCHAR2(30);
    l_file          UTL_FILE.FILE_TYPE;
    l_buffer        VARCHAR2(32767);

    CURSOR cur_table
    IS
    SELECT * FROM table    
begin
    l_tmp_file_name := 'file.tmp';
    BEGIN
        l_file := UTL_FILE.FOPEN(C_DIRECTORY_PATH, l_tmp_file_name,'A',C_MAXLEN);          
        FOR rec IN cur_table
        LOOP
            l_rec := CONVERT(rec.id || ',' || rec.user ,'AL32UTF8');            
            l_buffer := l_buffer || FN_GET_BUFFER(l_rec, l_buffer);
            if l_buffer is NOT NULL then
                UTL_FILE.PUT_LINE(l_file, l_buffer);
                l_buffer := NULL;
            end if;
       END LOOP rec;    
       UTL_FILE.FCLOSE(l_file);   
       l_csv_file_name := 'file.csv';
       UTL_FILE.FRENAME(src_location => C_DIRECTORY_PATH, src_filename => l_tmp_file_name, dest_location => C_DIRECTORY_PATH, dest_filename => l_csv_file_name, overwrite => FALSE);
   EXCEPTION
      WHEN OTHERS THEN
          UTL_FILE.FCLOSE(l_file);
          UTL_FILE.FREMOVE(location => C_DIRECTORY_PATH, filename => l_tmp_file_name);
    END;
end export;

问题是我

1,user1
2,user2
3,user3
4,user4
5,
user5
6,user6
7,user7
8,user8
9,user9
10,user10
11,user11
12,user12
13,user13
14,
user14
15,user15
16,user16
17,user17
18,user19

非常感谢。

1 回复 | 直到 9 年前

APC 9 年前

问题不在于您的功能本身,而是您在调用后进行的测试:

if l_buffer is NOT NULL then
            UTL_FILE.PUT_LINE(l_file, l_buffer);
            l_buffer := NULL;
end if;

l_buffer 总是填充,从不为null。因此,测试始终为true,您为表中的每一行写入文件。您需要测试长属于

FN_GET_BUFFER()

FOR rec IN cur_table
LOOP
    l_rec := CONVERT(rec.id || ',' || rec.user ,'AL32UTF8');            

    IF LENGTH(l_buffer) + LENGTH(C_CHRLEN) + LENGTH(l_rec) > C_MAXLEN THEN
        -- buffer full, write to file 
        UTL_FILE.PUT_LINE(l_file, l_buffer);
        l_buffer := l_rec;
    ELSIF LENGTH(l_buffer) = 0 THEN 
        -- first record
        l_buffer := l_rec;
    ELSE
       -- buffer not full
       l_buffer := _l_buffer || C_CHRLEN || l_rec;
    END IF;
END LOOP rec;
if LENGTH(l_buffer) > 0 THEN
   -- end of table, write last record
   UTL_FILE.PUT_LINE(l_file, l_buffer);
end if;

警告编码的wildstyle,未测试