Oracle中如何将查询结果集压缩为ZIP格式以减小传输体积？

针对你提到的查询select ID,product,Name,Desc From Products（结果集近1MB、无BLOB字段），除了utl_compress.lz_compress，还有以下几种实用方法实现压缩或归档到指定位置：

1. 将结果集转为CLOB后压缩并写入指定目录 #

先用DBMS_XMLGEN把查询结果转换为XML格式的CLOB，再用utl_compress.lz_compress压缩，最后通过UTL_FILE包写入服务器指定目录。示例代码如下：

DECLARE
  v_clob CLOB;
  v_compressed_blob BLOB;
  v_file UTL_FILE.FILE_TYPE;
  v_dir VARCHAR2(100) := 'YOUR_DIRECTORY'; -- 需先创建并授权的Oracle目录
  v_filename VARCHAR2(100) := 'compressed_products.lz';
BEGIN
  -- 将查询结果转为XML格式的CLOB
  v_clob := DBMS_XMLGEN.GETXML('select ID,product,Name,Desc From Products');
  
  -- 压缩CLOB为BLOB
  v_compressed_blob := UTL_COMPRESS.LZ_COMPRESS(v_clob);
  
  -- 写入指定目录
  v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'wb');
  UTL_FILE.PUT_RAW(v_file, v_compressed_blob);
  UTL_FILE.FCLOSE(v_file);
END;
/

注意：需要先通过CREATE DIRECTORY创建目录，并给当前用户授予READ,WRITE权限。

2. 使用Oracle Data Pump（EXPDP）直接导出压缩数据集 #

Data Pump支持导出时直接压缩，生成的dump文件本身就是压缩格式，可直接归档到指定路径。可通过QUERY参数指定要导出的查询，或先创建视图再导出视图：

方法A：直接用QUERY参数导出 #

expdp username/password@db schemas=your_schema dumpfile=products_compressed.dmp logfile=expdp_products.log compression=all query=Products:"WHERE 1=1"

query=Products:"WHERE 1=1"等价于导出整个Products表，若需筛选可修改WHERE条件；compression=all会压缩元数据和数据。

方法B：创建视图后导出视图 #

先创建视图：

CREATE VIEW v_products AS select ID,product,Name,Desc From Products;

然后导出视图：

expdp username/password@db schemas=your_schema dumpfile=products_compressed.dmp logfile=expdp_products.log compression=all tables=v_products

3. 在应用层实现压缩归档 #

如果是通过应用程序（如Java、Python）调用SQL查询，可在获取结果集后，将数据序列化为CSV/JSON格式，再用GZIP/ZIP等压缩算法处理，最后保存到指定位置。

Python示例（用pandas+gzip） #

import pandas as pd
import gzip
import cx_Oracle

# 连接数据库并获取结果
conn = cx_Oracle.connect('username/password@db')
df = pd.read_sql('select ID,product,Name,Desc From Products', conn)
conn.close()

# 压缩并保存为GZIP格式的CSV
with gzip.open('/path/to/save/products_compressed.csv.gz', 'wt', compresslevel=9) as f:
    df.to_csv(f, index=False)

Java示例（用GZIPOutputStream） #

import java.sql.*;
import java.io.*;
import java.util.zip.GZIPOutputStream;

public class CompressResultSet {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@db:1521:ORCL", "username", "password");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select ID,product,Name,Desc From Products");
        
        // 写入GZIP压缩文件
        FileOutputStream fos = new FileOutputStream("/path/to/save/products_compressed.csv.gz");
        GZIPOutputStream gzos = new GZIPOutputStream(fos);
        PrintWriter pw = new PrintWriter(gzos);
        
        // 写入表头
        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            pw.print(meta.getColumnName(i));
            if (i < meta.getColumnCount()) pw.print(",");
        }
        pw.println();
        
        // 写入数据行
        while (rs.next()) {
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                pw.print(rs.getString(i));
                if (i < meta.getColumnCount()) pw.print(",");
            }
            pw.println();
        }
        
        pw.close();
        gzos.close();
        fos.close();
        rs.close();
        stmt.close();
        conn.close();
    }
}

内容的提问来源于stack exchange，提问作者synccm2012