ORACLE如何从URL下载ZIP包并读取内部CSV文件用于SELECT查询

整体逻辑基于Oracle内置包实现全内存处理，无需文件落地，可直接通过SELECT语句查询结果。

前置权限配置 #

使用SYS账号执行以下语句给业务用户开通必要权限：

-- 授予内置包执行权限
GRANT EXECUTE ON UTL_HTTP TO 你的业务用户名;
GRANT EXECUTE ON UTL_COMPRESS TO 你的业务用户名;
GRANT EXECUTE ON DBMS_LOB TO 你的业务用户名;

-- 11g及以上版本需要配置ACL网络访问规则
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'file_download_acl.xml',
    description => 'Allow http file download',
    principal => '你的业务用户名',
    is_grant => TRUE,
    privilege => 'connect'
  );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'file_download_acl.xml',
    host => 'www.xyxy.com',
    lower_port => 80,
    upper_port => 443
  );
  COMMIT;
END;
/

第一步：实现getzip下载函数 #

传入URL返回ZIP包的二进制BLOB内容：

CREATE OR REPLACE FUNCTION getzip(p_url VARCHAR2) RETURN BLOB IS
  v_blob BLOB;
  v_http_req UTL_HTTP.REQ;
  v_http_resp UTL_HTTP.RESP;
  v_raw RAW(32767);
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
  v_http_req := UTL_HTTP.BEGIN_REQUEST(p_url, 'GET', 'HTTP/1.1');
  v_http_resp := UTL_HTTP.GET_RESPONSE(v_http_req);
  
  LOOP
    UTL_HTTP.READ_RAW(v_http_resp, v_raw, 32767);
    DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);
  END LOOP;
  
  UTL_HTTP.END_RESPONSE(v_http_resp);
  RETURN v_blob;
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_http_resp);
    RETURN v_blob;
  WHEN OTHERS THEN
    IF UTL_HTTP.IS_OPEN(v_http_resp) THEN
      UTL_HTTP.END_RESPONSE(v_http_resp);
    END IF;
    RAISE;
END getzip;
/

第二步：定义返回结构和getcsv解析函数 #

先定义结果集类型 #

-- 单行数据结构，对应CSV的5个字段
CREATE OR REPLACE TYPE t_csv_row AS OBJECT (
  name VARCHAR2(100),
  lastname VARCHAR2(100),
  age NUMBER,
  direction VARCHAR2(200),
  phone VARCHAR2(50)
);
/
-- 结果集集合类型
CREATE OR REPLACE TYPE t_csv_tab IS TABLE OF t_csv_row;
/

实现getcsv管道函数 #

传入ZIP的BLOB，解压后解析指定CSV返回可查询的表结构：

CREATE OR REPLACE FUNCTION getcsv(p_zip_blob BLOB) RETURN t_csv_tab PIPELINED IS
  v_zip_list UTL_COMPRESS.ZIP_FILE_LIST;
  v_csv_raw RAW(32767);
  v_csv_clob CLOB;
  v_offset NUMBER := 1;
  v_line VARCHAR2(32767);
  v_fields SYS.ODCIVARCHAR2LIST;
  v_row t_csv_row;
BEGIN
  -- 读取ZIP包内文件列表
  v_zip_list := UTL_COMPRESS.GET_FILE_LIST(p_zip_blob);
  FOR i IN 1..v_zip_list.COUNT LOOP
    -- 匹配目标CSV文件
    IF v_zip_list(i).FILENAME = 'directions_clients.csv' THEN
      -- 解压得到CSV二进制内容
      v_csv_raw := UTL_COMPRESS.UNZIP(p_zip_blob, v_zip_list(i).FILENAME);
      v_csv_clob := UTL_RAW.CAST_TO_VARCHAR2(v_csv_raw);
      
      -- 逐行解析CSV
      v_offset := 1;
      WHILE v_offset <= DBMS_LOB.GETLENGTH(v_csv_clob) LOOP
        -- 读取单行内容
        v_line := DBMS_LOB.SUBSTR(v_csv_clob, INSTR(v_csv_clob, CHR(10), v_offset) - v_offset, v_offset);
        v_offset := INSTR(v_csv_clob, CHR(10), v_offset) + 1;
        
        -- 跳过表头行
        IF INSTR(v_line, 'name,lastname,age,direction,phone') > 0 THEN
          CONTINUE;
        END IF;
        EXIT WHEN v_line IS NULL;
        
        -- 按逗号拆分字段
        v_fields := SYS.ODCIVARCHAR2LIST();
        v_fields.EXTEND(5);
        FOR j IN 1..5 LOOP
          v_fields(j) := TRIM(REGEXP_SUBSTR(v_line, '[^,]+', 1, j));
        END LOOP;
        
        -- 输出行数据
        v_row := t_csv_row(
          v_fields(1), v_fields(2), TO_NUMBER(v_fields(3)), v_fields(4), v_fields(5)
        );
        PIPE ROW(v_row);
      END LOOP;
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END getcsv;
/

调用测试 #

直接使用你期望的语法查询即可：

SELECT * FROM TABLE(getcsv(getzip('http://www.xyxy.com/test.zip')));

注意事项

• 如果CSV内容超过32K，需要调整代码使用分块读写逻辑，避免单次读写超出RAW类型长度限制
• 如果CSV字段包含逗号、双引号等特殊格式，建议替换正则拆分逻辑为更完善的CSV解析规则
• 访问HTTPS协议的URL时，需要提前在Oracle端配置钱包，导入目标站点的SSL根证书

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