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