GBase 8a之对齐实现oracle函数ora_hash

问题描述oracle的函数ora_hash在gbase8a上没有与之对应的函数。在迁移、改造、调试过程中不方便。改造方式1. 可以通过创建自定义函数进行仿制仅参考未充分测试是否可以完全替代DELIMITER // DROP FUNCTION IF EXISTS ora_hash // CREATE FUNCTION ora_hash( expr TEXT, max_bucket BIGINT , seed_value BIGINT ) RETURNS BIGINT UNSIGNED DETERMINISTIC -- 相同输入一定返回相同结果符合Oracle BEGIN DECLARE hash_val BIGINT UNSIGNED; DECLARE result_val BIGINT UNSIGNED; DECLARE mod_value BIGINT UNSIGNED; -- 规则1输入为NULL直接返回NULL IF expr IS NULL THEN RETURN NULL; END IF; -- 规则2限制max_bucket在Oracle合法范围 0 ~ 4294967295 IF max_bucket 4294967295 THEN SET max_bucket 4294967295; END IF; -- 规则3计算哈希值使用 SHA1 哈希转无符号整数贴近Oracle行为无负数 -- 拼接种子值和Oracle种子逻辑一致 SET hash_val CAST(CONV(SUBSTRING(SHA1(CONCAT(expr, seed_value)), 1, 16), 16, 10) AS UNSIGNED); -- 规则4计算模数Oracle核心公式 SET mod_value max_bucket 1; -- 规则5计算最终结果 IF mod_value 1 THEN -- 对应 max_bucket0直接返回0 SET result_val 0; ELSE SET result_val hash_val % mod_value; END IF; RETURN result_val; END // DELIMITER ;2. 部分测试效果gbase select ora_hash(南大通用,10,0); ------------------------------- | ora_hash(南大通用,10,0) | ------------------------------- | 9 | ------------------------------- 1 row in set (Elapsed: 00:00:00.00) gbase gbase gbase select ora_hash(南大通用,10,123); --------------------------------- | ora_hash(南大通用,10,123) | --------------------------------- | 7 | --------------------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(ndty,3,0); ---------------------- | ora_hash(ndty,3,0) | ---------------------- | 1 | ---------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(ndty,3,999); ------------------------ | ora_hash(ndty,3,999) | ------------------------ | 3 | ------------------------ 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(2026-04-03,20,0); ----------------------------- | ora_hash(2026-04-03,20,0) | ----------------------------- | 1 | ----------------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(2026-04-03,20,123); ------------------------------- | ora_hash(2026-04-03,20,123) | ------------------------------- | 10 | ------------------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(12345,20,0); ---------------------- | ora_hash(12345,20,0) | ---------------------- | 3 | ---------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(12345,20,123); ------------------------ | ora_hash(12345,20,123) | ------------------------ | 13 | ------------------------ 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(13.6,20,0); --------------------- | ora_hash(13.6,20,0) | --------------------- | 8 | --------------------- 1 row in set (Elapsed: 00:00:00.00) gbase select ora_hash(13.6,20,789); ----------------------- | ora_hash(13.6,20,789) | ----------------------- | 10 | ----------------------- 1 row in set (Elapsed: 00:00:00.00)原理说明ORA_HASH 函数说明1参数ORA_HASH(expr [, max_bucket [, seed ]])expr任意标量类型字符串 / 数字等为 NULL 则返回 NULLmax_bucket返回值上限默认 4294967295范围 0 ~ 4294967295seed随机种子默认 0相同种子 相同输入 相同结果2返回值范围0 ~ max_bucketmax_bucket0 → 固定返回 0