用Oracle function实现Murmurhash3算法

MurmurHash3 是一种非加密型哈希算法，Oracle 中可以通过以下步骤实现：

1. 创建一个新的 PL/SQL 函数：

CREATE OR REPLACE FUNCTION murmurhash3(input VARCHAR2, seed NUMBER)
RETURN NUMBER DETERMINISTIC IS
--定义一些常量
c1 CONSTANT NUMBER := 0xcc9e2d51;
c2 CONSTANT NUMBER := 0x1b873593;
r1 CONSTANT NUMBER := 15;
r2 CONSTANT NUMBER := 13;
m CONSTANT NUMBER := 5;
n CONSTANT NUMBER := 0xe6546b64;
--初始化哈希值和长度
hash NUMBER := seed;
len NUMBER := LENGTH(input);
--定义辅助函数
FUNCTION fmix(h NUMBER) RETURN NUMBER IS
BEGIN
h := h XOR (h >> 16);
h := h * 0x85ebca6b;
h := h XOR (h >> 13);
h := h * 0xc2b2ae35;
h := h XOR (h >> 16);
RETURN h;
END fmix;
--开始计算哈希值
i NUMBER := 1;
j NUMBER := 0;
k NUMBER;
BEGIN
WHILE (i <= len) LOOP
k := ASCII(SUBSTR(input, i, 1));
k := k * c1;
k := (k << r1) OR (k >> (32 - r1));
k := k * c2;
hash := hash XOR k;
hash := (hash << r2) OR (hash >> (32 - r2));
hash := (hash * m) + n;
i := i + 4;
END LOOP;
--处理剩下的字节
k := 0;
j := len - (len MOD 4) + 1;
WHILE (j <= len) LOOP
k := k + (ASCII(SUBSTR(input, j, 1)) << ((j MOD 4) * 8));
j := j + 1;
END LOOP;
IF (j MOD 4 = 1) THEN
k := k + (ASCII(SUBSTR(input, len, 1)) << 24);
ELSIF (j MOD 4 = 2) THEN
k := k + (ASCII(SUBSTR(input, len - 1, 1)) << 24);
k := k + (ASCII(SUBSTR(input, len, 1)) << 16);
ELSIF (j MOD 4 = 3) THEN
k := k + (ASCII(SUBSTR(input, len - 2, 1)) << 24);
k := k + (ASCII(SUBSTR(input, len - 1, 1)) << 16);
k := k + (ASCII(SUBSTR(input, len, 1)) << 8);
END IF;
k := k * c1;
k := (k << r1) OR (k >> (32 - r1));
k := k * c2;
hash := hash XOR k;
--最后的混合操作
hash := hash XOR len;
hash := fmix(hash);
RETURN hash;
END murmurhash3;

2. 调用函数并传入参数：

SELECT murmurhash3('hello world', 123) FROM dual;

这将返回一个哈希值，例如：-647754747。