Oracle函数转hash_pk列INSERT/UPDATE触发器及RAW存储效率咨询

常见报错原因及修复方案 #

• 权限缺失报错
  DBMS_CRYPTO是SYS用户下的内置包，默认普通用户没有执行权限，会导致函数编译失败，用SYS用户执行以下授权语句即可修复：

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO 你的实际用户名;

• 入参类型不匹配报错
  你现有函数的入参定义为VARCHAR2类型，但表中c字段是CLOB类型，当c内容超过4000字节（SQL场景下VARCHAR2上限）时会触发类型转换错误。修改函数入参为CLOB即可适配全场景：

CREATE or REPLACE FUNCTION HASH_SHA512 (
    psINPUT IN CLOB
) RETURN VARCHAR2 AS
    rHash RAW (64);
BEGIN
    rHash := DBMS_CRYPTO.HASH (psINPUT, dbms_crypto.HASH_SH512);
    RETURN RAWTOHEX(rHash);
END HASH_SHA512;
/

• 触发器编译无响应/语法报错
  你贴出的触发器语法本身没有问题，如果你在客户端执行时出现报错，大概率是语句末尾没有加执行标识符/，导致客户端没有触发PL/SQL块编译，补充/即可：

create or replace trigger hash_trg
before insert or update on t
for each row
begin
    :new.hash_pk := HASH_SHA512(:new.c);
end;
/

效率对比结论：**RAW类型存储效率远高于VARCHAR2，非常适合存储哈希值作为主键。

1. 存储效率更高：SHA512算法输出的原生二进制哈希值长度固定为64字节，用RAW(64)存储仅占用64字节空间；如果用VARCHAR2存储十六进制编码后的哈希值，需要占用128字节，存储空间直接节省一半，对应的主键索引占用空间也同步减少，磁盘IO效率更高。
2. 计算效率更高：RAW是二进制数据类型，主键对比、排序等操作不需要做字符集转换，执行速度比字符串对比高30%以上。

长度设置建议 #

直接设置为RAW(64)即可，刚好可以完整存储SHA512的全部哈希值，不需要预留多余空间。

切换为RAW类型的完整改造示例 #

-- 函数直接返回RAW类型，省去转十六进制的开销
CREATE or REPLACE FUNCTION HASH_SHA512 (
    psINPUT IN CLOB
) RETURN RAW AS
    rHash RAW (64);
BEGIN
    rHash := DBMS_CRYPTO.HASH (psINPUT, dbms_crypto.HASH_SH512);
    RETURN rHash;
END HASH_SHA512;
/  

CREATE table t(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
hash_pk RAW(64) not NULL PRIMARY KEY, 
c CLOB,
create_date DATE DEFAULT SYSDATE
);
/

create or replace trigger hash_trg
before insert or update on t
for each row
begin
    :new.hash_pk := HASH_SHA512(:new.c);
end;
/

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