VastbaseE100

UTL_FILE

• 提供读写操作系统文本文件程序。

cat > /tmp/test.txt
111aaa
222bbb
333ccc
444ddd
555eee
666fff
777ggg
888hhh
999iii
101010jjj
111111kkk

atlasdb=# insert into utl_file.utl_file_dir values ('/tmp');

2) 文件复制

atlasdb=# select utl_file.fcopy('/tmp', 'test.txt', '/tmp', 'test_cp.txt',3,6);
[root@localhost tmp]# cat test_cp.txt
333ccc
444ddd
555eee
666fff

3) 关闭UTL_FILE 打开的文件

atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$# vInHandle  utl_file.file_type;
atlasdb$# vOutHandle utl_file.file_type;
atlasdb$# begin  
atlasdb$#   vInHandle  := utl_file.fopen('/tmp', 'test.txt', 'R');
atlasdb$#   vOutHandle := utl_file.fopen('/tmp', 'test_cp.txt', 'W');
atlasdb$#   raise notice 'OPEN';
atlasdb$#   IF utl_file.is_open(vInHandle) THEN
atlasdb$#     utl_file.fclose(vInHandle);
atlasdb$#   END IF;
atlasdb$#    IF not utl_file.is_open(vInHandle) THEN
atlasdb$#       raise notice 'close';
atlasdb$#   END IF;
atlasdb$# end;  
atlasdb$# $$;  
NOTICE:  OPEN
NOTICE:  close

4) 查看文件的属性

atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$# ex boolean;
atlasdb$# flen int;
atlasdb$# bsize int;
atlasdb$# begin  
atlasdb$#  -- utl_file.fgetattr('/tmp'::text, 'test.txt'::text, ex, flen, bsize);
atlasdb$#  select * from  utl_file.fgetattr('/tmp'::text, 'test.txt'::text) into ex,flen,bsize;
atlasdb$#   IF ex THEN
atlasdb$#      raise notice  'File Exists';
atlasdb$#   ELSE
atlasdb$#    raise notice  'File Does Not Exist' ;
atlasdb$#   END IF;
atlasdb$#    raise notice 'FileLength: %' , flen;
atlasdb$#    raise notice 'BlockSize: %',bsize;
atlasdb$# end;  
atlasdb$# $$;  
NOTICE:  File Exists
NOTICE:  FileLength: 83
NOTICE:  BlockSize: 4096

5) 读写文件

atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$#   vInHandle utl_file.file_type;
atlasdb$#   vOutHandle utl_file.file_type;
atlasdb$#   vNewLine  VARCHAR2(250);
atlasdb$# begin  
atlasdb$#  vInHandle := utl_file.fopen('/tmp', 'test.txt','R');
atlasdb$#   LOOP
atlasdb$#  BEGIN
atlasdb$#  select * from  utl_file.get_line(vInHandle)into vNewLine ;
atlasdb$#   raise notice '%', vNewLine;
atlasdb$#  EXCEPTION
atlasdb$#    WHEN OTHERS THEN
atlasdb$#  EXIT;
atlasdb$#  END;
atlasdb$#   END LOOP;
atlasdb$# utl_file.fclose(vInHandle);
atlasdb$#  vOutHandle := utl_file.fopen('/tmp', 'test_cp.txt', 'w'); 
atlasdb$#   perform  utl_file.put_line(vOutHandle, 'hello lsxy'); 
atlasdb$#   utl_file.fflush(vOutHandle);
atlasdb$#   utl_file.fclose(vOutHandle);
atlasdb$# end;  
atlasdb$# $$; 
NOTICE:  111aaa
NOTICE:  222bbb
NOTICE:  333ccc
NOTICE:  444ddd
NOTICE:  555eee
NOTICE:  666fff
NOTICE:  777ggg
NOTICE:  888hhh
NOTICE:  999iii
NOTICE:  101010jjj
NOTICE:  111111kkk
DO

[root@localhost tmp]# cat test_cp.txt
hello lsxy

文件删除:
atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$# begin  
atlasdb$#  utl_file.fremove('/tmp', 'test_cp.txt');
atlasdb$# end;  
atlasdb$# $$;  
DO

6) 文件重命名

atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$# begin  
atlasdb$#  perform utl_file.frename('/tmp','test.txt','/tmp','test1.txt',TRUE);
atlasdb$# end;  
atlasdb$# $$;  
DO

utl_file.new_line:
do language plpgsql $$   
declare  
  vInHandle utl_file.file_type;
begin  
  vInHandle := utl_file.fopen('/tmp', 'test.txt', 'w'); 
  perform  utl_file.new_line(vInHandle, 2); 
  utl_file.fclose(vInHandle);
end;  
$$; 

atlasdb=# do language plpgsql $$   
atlasdb$# declare  
atlasdb$#    my_world  varchar2(4) := 'Zork';
atlasdb$#    vInHandle utl_file.file_type;
atlasdb$# begin  
atlasdb$#    vInHandle := utl_file.fopen('/tmp', 'test.txt', 'w'); 
atlasdb$#    perform utl_file.PUTF(vInHandle, 'Hello, world! I come from %s with %s.\n',my_world);
atlasdb$#    perform utl_file.PUT_line(vInHandle, 'Hello, world!');
atlasdb$#     vInHandle := utl_file.fclose(vInHandle); 
atlasdb$# end;  
atlasdb$# $$; 
DO