使用ORACLE生成文本文件

╰半橙微兮° 2022-06-18 01:00 326阅读 0赞

1、在使用ORACLE的存储过程生成文件

在ORACLE中生成文件是比较方便的，尤其是生成对外接口的时候，通过定时任务完成，是一个非常直接的方法，而且比编译式语言具有更加灵活的方式。

使用PL/SQL生成文件时需要使用到两个东西：1.目录对象，2.文件工具包

1. 目录对象

目录对象是通过在数据库中建立一个对象，指向数据库所有机器的目录，注意不是本地目录。如果数据库在磁阵上，最好将文件放在磁阵上，这样做双机热备的时候不至于出现问题。

添加目录可以通过PL/SQL Developer或者其它可视化工具建立，也可以通过脚本来生成，脚本有如下形式：

-- Create directory
    create or replace directory BAS_BLACK_DATA
      as '/data/appdata/metadata/dispatch/bas';

2. 文件工具包 UTL\_FILE

通过UTL\_FILE包可以完成文件读写，以下是我摘过来的一个UTF\_FILE.fopen函数的文档。

这里注意一点，我这是10g版本下的，如果在9i下，不支持"wb", "rb“ ”ab"模式。

这意味着，不能使用二制式模式打开文件或者写文件，一般在写文件时，换行符就会与平台相关，如果在LINUX下需要生成回车换行符，就得手工操作：

UTF\_FILE.put\_line(line||chr(13)); -- 通过加下chr(13) = 0x0D方式加入一个回车符，如果在windows平台，会自动生成回车换行符。如果在WINDOWS上只想生成换行符，估计只能使用UTF\_FILE.put方式手动写入换行符chr(10)。

详细参考可以通过在PL/SQL Developer上按Ctrl单击包，去查看包源码。

/*
      ** FOPEN - open file
      **
      ** As of 8.0.6, you can have a maximum of 50 files open simultaneously.
      **
      ** As of 9.0.2, UTL_FILE allows file system access for directories
      ** created as database objects.  See the CREATE DIRECTORY command.
      ** Directory object names are case sensitive and must match exactly
      ** the NAME string in ALL_DIRECTORIES.  The LOCATION parameter may be
      ** either a directory string from the UTL_FILE_DIR init.ora parameter
      ** or a directory object name.
      **
      ** IN
      **   location     - directory location of file
      **   filename     - file name (including extention)
      **   open_mode    - open mode ('r', 'w', 'a' 'rb', 'wb', 'ab')
      **   max_linesize - maximum number of characters per line, including the
      **                  newline character, for this file.
      **                  Valid values are 1 through 32767 and NULL.  A NULL
      **                  value for max_linesize indicates that UTL_FILE should
      **                  calculate an operating system specific value at runtime.
      ** RETURN
      **   file_type handle to open file
      ** EXCEPTIONS
      **   invalid_path        - file location or name was invalid
      **   invalid_mode        - the open_mode string was invalid
      **   invalid_operation   - file could not be opened as requested
      **   invalid_maxlinesize - specified max_linesize is too large or too small
      **   access_denied       - access to the directory object is denied
      */
      FUNCTION fopen(location     IN VARCHAR2,
                     filename     IN VARCHAR2,
                     open_mode    IN VARCHAR2,
                     max_linesize IN BINARY_INTEGER DEFAULT NULL)
               RETURN file_type;
      PRAGMA RESTRICT_REFERENCES(fopen, WNDS, RNDS, TRUST);

2、生成文件时注意事项

生成文件时，如果是生成定长字节的文件时，需要注意与字符集相关的函数，如果使用错误，将会直接给接口文件带来问题，这可是我的教训啊！

举个例子：生成一行文本，要求限制在100个字节内。

如果我这么写

utf_file.put_line(rpad(content, 100));

上面这样子是不是就可以了呢？有没有问题？

大多数情况下没问题，但问题肯定有，如果查看rpad文件就会说明一点：

`RPAD` returns `expr1`, right-padded to length`n` characters with `expr2`, replicated as many times as necessary. This function is useful for formatting the output of a query.

The argument `n` is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

这里的意思是说，该函数可以生成你指定的字符，而且这里的宽度一般是指在屏幕上显示的宽度，在多字符集下就会有问题。所有我们如果想处理这个问题，就需要寻求一个取得字节数的函数，并使用如下方式生成：

utf_file.put_line(content||rpad(' ', 100-lengthb(content)));

3、与文本相关函数比较

上面讲了，使用ORACLE生成文件时的注意事项，这里，再将相关的函数区别也列一下，只是需要特殊注意ORACLE中的NULL的处理问题，一般函数对于NULL返回NULL，所以如果生成文件的项为NULL就需要做特殊化处理，比如上面的输出定长100的行时，如果content为空，那该行也为空。所以正确的方式是判断字段的NULL否。

utf_file.put_line(decode(content, null, ' ', content)||
        rpad(' ', 100-lengthb(decode(content, null, ' ', content))));

1、length系列

The `LENGTH` functions return the length of `char`.`LENGTH` calculates length using characters as defined by the input character set.`LENGTHB` uses bytes instead of characters. `LENGTHC` uses Unicode complete characters.`LENGTH2` uses UCS2 code points. `LENGTH4` uses UCS4 code points.

如果想取得字节数，可以使用lengthb,如果使用UNICODE使用lengthc,如果使用UCS2使用length2，length4使用UCS4。

这里这么多字符集，其它只需要使用这些是相关标准，以及对应的字符宽度就可以了，而且这里的用语感觉也有点含糊。

相对于中文来讲，在GBK字符集情况下，length返回1，lengthb返回2，

在UTF8字符集情况下，length返回1, lengthb返回3，这是因为UTF8编码方式是这样子的，其它UTF8编码是UNICODE的一个编码方案而已。

2、vsize函数

`VSIZE` returns the number of bytes in the internal representation of`expr`.

以国际化呈现的方式返回字符串的字节数，这同样与字符集相关。

如下地址：http://space.itpub.net/16179598/viewspace-663045 对汉字在ORACLE中长度进行一些比较和实验，可以参考一下。

3、rpad函数

在前面已经说明了注意事项。