如何使用PL/SQL提取LONG数据类型列中存储的指定业务数据

针对你需要从LONG类型字段存储的报文中，提取Unloaded Couriers、Unloaded Volumes Couriers两类明细的需求，提供两种不同技术栈下的落地方案，可根据实际场景选择：

方案1：数据库层面直接处理（适合数据量小、实时性要求高的场景） #

注意LONG类型无法直接调用字符串函数，需先转换为CLOB/TEXT类型再做解析，以下为Oracle环境示例SQL：

-- 先将LONG转CLOB，可根据实际需求换成临时表存储
WITH msg_clob AS (
    SELECT ID, TO_LOB(MSGTEXT) AS msg_content
    FROM 你的落地表名
)
SELECT 
    ID,
    -- 提取Unloaded Couriers区块明细
    REGEXP_SUBSTR(
        msg_content,
        'Unloaded Couriers\s+-+\s+(.*?)\s+Unloaded Volumes Couriers',
        1,1,'n',1
    ) AS unloaded_couriers_detail,
    -- 提取Unloaded Volumes Couriers区块明细
    REGEXP_SUBSTR(
        msg_content,
        'Unloaded Volumes Couriers\s+-+\s+(.*)',
        1,1,'n',1
    ) AS unloaded_volumes_couriers_detail
FROM msg_clob;

提取完成后按行拆分、按/分隔字段，即可直接写入目标业务表。

方案2：ETL脚本层面处理（适合数据量大、报文格式易变动的场景） #

用脚本解析灵活性更高，以下为Python实现示例：

import cx_Oracle
import re

# 数据库连接配置
conn = cx_Oracle.connect('用户名/密码@IP:端口/服务名')
cursor = conn.cursor()

# 增量读取未处理的报文数据
cursor.execute("SELECT ID, MSGTEXT FROM 你的落地表名 WHERE 处理标识 = '未处理'")
for id_val, msg_text in cursor:
    msg_str = str(msg_text)
    # 提取Unloaded Couriers明细
    uc_match = re.search(r'Unloaded Couriers\s+-+\s+(.*?)\s+Unloaded Volumes Couriers', msg_str, re.S)
    unloaded_couriers = uc_match.group(1).strip() if uc_match else ''
    # 提取Unloaded Volumes Couriers明细
    uvc_match = re.search(r'Unloaded Volumes Couriers\s+-+\s+(.*)', msg_str, re.S)
    unloaded_volumes_couriers = uvc_match.group(1).strip() if uvc_match else ''
    
    # 此处补充明细拆分、写入目标表、更新原表处理标识的逻辑即可

注意事项 #

• 若报文中目标区块无数据（和示例中Couriers lifted区块一样标注NO *），可增加空值判断逻辑，过滤无效数据后再写入目标表
• 落地表数据量较大时建议新增处理状态字段，每次仅增量处理未处理的报文，避免全表扫描浪费资源
• LONG类型长度超过32k时，转CLOB操作需提前核对数据库相关参数配置，避免报文截断导致提取数据缺失

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