Oracle同行CLOB字段比对及基于SHA512哈希的分场景插入问题

第一步：修正HASH_SHA512函数适配CLOB输入 #

你原有函数入参为VARCHAR2，仅能处理短文本，会限制CLOB最大处理长度，先修改为支持CLOB入参的版本：

CREATE or REPLACE FUNCTION HASH_SHA512 (
    psINPUT IN CLOB
    ) RETURN VARCHAR2 AS
    rHash RAW (64); -- SHA512输出为64字节512位，修正长度避免溢出
    BEGIN
    -- 处理NULL输入，NULL的哈希直接返回NULL
    IF psINPUT IS NULL THEN
        RETURN NULL;
    END IF;
    rHash := DBMS_CRYPTO.HASH (psINPUT, dbms_crypto.HASH_SH512);
    RETURN LOWER(RAWTOHEX(rHash));
    END HASH_SHA512;
/

第二步：实现CLOB比对查询 #

以下查询可输出你需要的带SAME/DIFFERENT标记的结果，兼容空值、NULL场景：

SELECT 
    seq_num,
    val,
    clob1,
    clob2,
    CASE 
        WHEN HASH_SHA512(clob1) = HASH_SHA512(clob2) 
            OR (HASH_SHA512(clob1) IS NULL AND HASH_SHA512(clob2) IS NULL)
        THEN 'SAME'
        ELSE 'DIFFERENT'
    END AS compare_result
FROM table_x;

比对逻辑说明：

• 两个CLOB的哈希值完全相等判定为相同
• 两个CLOB同时为NULL也判定为相同
• 其余场景均判定为不同

第三步：实现分场景插入table_z #

用INSERT ALL+条件判断即可实现相同插1行、不同插2行的需求，同时兼容主键非空约束：

INSERT ALL
WHEN hash1 = hash2 OR (hash1 IS NULL AND hash2 IS NULL) THEN
    INTO table_z (val, hash_val, clob_val)
    VALUES (val, NVL(hash1, 'null_hash'), clob1)
WHEN hash1 != hash2 OR (hash1 IS NULL AND hash2 IS NOT NULL) OR (hash1 IS NOT NULL AND hash2 IS NULL) THEN
    INTO table_z (val, hash_val, clob_val)
    VALUES (val, NVL(hash1, 'null_hash'), clob1)
    INTO table_z (val, hash_val, clob_val)
    VALUES (val, NVL(hash2, 'null_hash'), clob2)
SELECT 
    val,
    HASH_SHA512(clob1) hash1,
    HASH_SHA512(clob2) hash2,
    clob1,
    clob2
FROM table_x;
COMMIT;

说明：这里用NVL把NULL的哈希替换为固定字符串null_hash，避免主键列不允许为NULL的报错，如果你的业务逻辑中NULL的CLOB不需要插入，可以自行调整过滤条件。如果存在重复哈希的场景，可以将语句改为MERGE写法避免主键冲突。

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