PL/SQL-->UTL_FILE包的使用介紹

在PL/SQL中，UTL_FILE包提供了文字檔輸入和輸出互功能。也就是說我們可以通過該包實現從作業系統層級來實現檔案讀取輸入或者是寫入到作業系統檔案。通過該包也可以將其他系統的資料載入到資料庫中。如載入web伺服器日誌，使用者登入資料庫日誌乃至Oracle記錄檔等等。本文主要描述了UTL_FILE的功能以及通過執行個體示範並理解這個包下相關過程函數的用法。

1、UTL_FILE介紹
  a、實現基於作業系統層級的讀取與寫入功能
  b、該方式為基於伺服器端的文字檔訪問模式，不支援二進位檔案
  c、可以通過設定參數utl_file_dir來設定pl/sql訪問作業系統檔案的多個路徑
  d、所有使用者可以讀寫utl_file_dir參數設定的目錄，因此應考慮安全問題
  e、也可以將參數utl_file_dir置空，而通過建立directory以及授予對directory許可權來進行訪問os檔案(推薦方式)

2、UTL_FILE包中的過程和函數
a、UTL_FILE中定義的file_type為記錄類型，如下所示其成員是私人的，不能夠被直接引用或改變這個記錄的組件。

  TYPE file_type IS RECORD (
      id          BINARY_INTEGER,
      datatype    BINARY_INTEGER,
      byte_mode  BOOLEAN);

b、UTL_FILE中相關過程函數的功能說明
  FCLOSE Procedure            Closes a file
  FCLOSE_ALL Procedure        Closes all open file handles
  FCOPY Procedure            Copies a contiguous portion of a file to a newly created file
  FFLUSH Procedure            Physically writes all pending output to a file
  FGETATTR Procedure          Reads and returns the attributes of a disk file
  FGETPOS Function            Returns the current relative offset position within a file, in bytes
  FOPEN Function              Opens a file for input or output
  FOPEN_NCHAR Function        Opens a file in Unicode for input or output
  FREMOVE Procedure          Deletes a disk file, assuming that you have sufficient privileges
  FRENAME Procedure          Renames an existing file to a new name, similar to the UNIX mv function
  FSEEK Procedure            Adjusts the file pointer forward or backward within the file by the number of bytes specified
  GET_LINE Procedure          Reads text from an open file
  GET_LINE_NCHAR Procedure    Reads text in Unicode from an open file
  GET_RAW Procedure          Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
  IS_OPEN Function            Determines if a file handle refers to an open file
  NEW_LINE Procedure          Writes one or more operating system-specific line terminators to a file
  PUT Procedure              Writes a string to a file
  PUT_LINE Procedure          Writes a line to a file, and so appends an operating system-specific line terminator
  PUT_LINE_NCHAR Procedure    Writes a Unicode line to a file
  PUT_NCHAR Procedure        Writes a Unicode string to a file
  PUTF Procedure              A PUT procedure with formatting
  PUTF_NCHAR Procedure        A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
  PUT_RAW Procedure          Accepts as input a RAW data value and writes the value to the output buffer

3、示範ULT_FILE用法

a、使用UTL_FILE的主要步驟(使用directory方式)
  --先建立用於存放os檔案的目錄
  scott@USBO> ho mkdir -p /u03/database/usbo/db_utl_dir
 
  --在資料庫層面添加directory
  scott@USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';
 
  --許可權授予
  scott@USBO> grant read,write on directory db_utl_dir to public;

b、從SQL查詢寫入到資料檔案
  DECLARE
    vsfile  UTL_FILE.file_type;  --->定義用於接收檔案控制代碼的類型
    v_cnt    PLS_INTEGER := 0;
  BEGIN
    vsfile :=                     
        UTL_FILE.fopen ('DB_UTL_DIR',  --->使用fopen開啟檔案，定義了檔案路徑，檔案名稱，讀寫方式以及每一行字元的最大長度，預設為1024
                        'emp.txt',
                        'W',
                        200);
 
    FOR i IN (SELECT t.ename || ',' || t.job AS msg    --->使用了一個for迴圈來讀取scott.emp表
                FROM scott.emp t WHERE t.sal>2000)
    LOOP
        UTL_FILE.put_line (vsfile, i.msg);              --->將for迴圈查詢的內容使用put_line寫入到檔案
        v_cnt := v_cnt + 1;                            --->計數器，用於統計寫入的記錄數
    END LOOP;
 
    UTL_FILE.fflush (vsfile);
    UTL_FILE.fclose (vsfile);
    DBMS_OUTPUT.put_line (v_cnt || ' rows unloaded');
  END;
  /
 
  6 rows unloaded
 
  PL/SQL procedure successfully completed.

  --查看產生的檔案
  scott@USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

c、從資料檔案讀入並寫入到表
  scott@USBO> create table tb_emp(val varchar2(30), file_name varchar2(10));
 
  scott@USBO> exec read_demo('emp.txt','db_utl_dir');  -->調用過程來實現，代碼見文章尾部
 
  PL/SQL procedure successfully completed.
 
  scott@USBO> select * from tb_emp;
 
  VAL                          FILE_NAME
  ----------------------------- ---------------------
  JONES,MANAGER                emp.txt
  BLAKE,MANAGER                emp.txt
  CLARK,MANAGER                emp.txt
  SCOTT,ANALYST                emp.txt
  KING,PRESIDENT                emp.txt
  FORD,ANALYST                  emp.txt
 
  6 rows selected.

d、讀寫混合模式樣本
  scott@USBO> set serveroutput on;
  scott@USBO> exec rw_demo;    -->調用過程來實現，代碼見文章尾部
  14
  14
  28
  42
  56
  71
  84
 
  PL/SQL procedure successfully completed.
 
  scott@USBO> ho ls
  out.txt  x.txt
 
  scott@USBO> ho more out.txt
  JONES,MANAGER
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

e、示範中用到的過程
  --下面是讀模式的過程代碼
  CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
  --兩個傳入參數，一個用於指定檔案名稱，一個用於指定utl_file_dir目錄
  --Author : Leshami
  --Blog  : l http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF

Oracle 10g 安裝後重啟系統,用PLSQL串連報沒有監聽

ORA-03114 PLSQL過程編譯中斷連線錯誤

PLSQL 串連 Oracle簡單配置

PLSQL批量Forall操作效能提升詳解

使用Oracle SQLDeveloper串連資料庫並建立使用者

Oracle內建的PL/SQL Developer匯入匯出資料

在64位Win7系統下安裝Oracle 11g和Oracle SQL Developer用戶端

1 2 下一頁