背景
虚谷数据库 版本:XuGu SQL Server 12.0.0
build_time: 2023-10-17 12:00:00 GA
机器: 鲲鹏arm
当前arm版本的虚谷数据库不支持随机数生成,因此编写了一个随机包提供支持。
1. arm版本-随机数_包声明
CREATE OR REPLACE PACKAGE random IS
FUNCTION value(min_value bigint, max_value bigint) return bigint;
FUNCTION string(length IN NUMBER) RETURN varchar2;
END;
2. arm版本-随机数_包体
CREATE OR REPLACE PACKAGE BODY random as
function value(min_value bigint, max_value bigint) return bigint as
div bigint := power(2, 31)-1;
tmp_value double;
ret_value bigint;
begin
tmp_value := to_number(abs(rand())) / div * (max_value - min_value);
ret_value := round(tmp_value, 0) + min_value;
return ret_value;
end;
FUNCTION string(length IN NUMBER) RETURN VARCHAR2 IS
characters VARCHAR2(62) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
random_string VARCHAR2(32767) := '';
BEGIN
FOR i IN 1..length LOOP
random_string := random_string || SUBSTR(characters, CEIL(rand_value(1, 62)), 1);
END LOOP;
RETURN random_string;
end;
end random;
3. 调用
--调用整数
select random.value(1,9)
--调用字符串
select random.string(10)