扩展Oracle random_interval函数以支持日、分、秒等INTERVAL单位

现有Oracle函数random_interval仅能生成指定小时范围的随机INTERVAL DAY TO SECOND值，现需扩展该函数，通过传入'DAY'、'MINUTE'或'SECOND'字面量，使其支持生成对应时间单位范围的随机INTERVAL值：

• 调用random_interval(1,4,'DAY')可得到类似+000000002 11:24:43.000000000的结果（天范围1-4，时分秒随机）
• 调用random_interval(20,40,'MINUTE')可得到类似+000000000 00:24:44.000000000的结果（分钟范围20-40，天为0，秒随机）

原函数代码及测试结果 #

CREATE OR REPLACE FUNCTION random_interval(
      p_min_hours IN NUMBER,
      p_max_hours IN NUMBER
    ) RETURN INTERVAL DAY TO SECOND
   IS
   BEGIN
      RETURN floor(dbms_random.value(p_min_hours, p_max_hours)) * interval '1' hour
        + floor(dbms_random.value(0, 60)) * interval '1' minute
        + floor(dbms_random.value(0, 60)) * interval '1' second;
END random_interval;
/

SELECT random_interval(1, 10)  as random_val FROM dual CONNECT BY level <= 10 order by 1

测试输出：

RANDOM_VAL
+000000000 01:04:03.000000000
+000000000 03:14:52.000000000
+000000000 04:39:42.000000000
+000000000 05:00:39.000000000
+000000000 05:03:28.000000000
+000000000 07:03:19.000000000
+000000000 07:06:13.000000000
+000000000 08:50:55.000000000
+000000000 09:10:02.000000000
+000000000 09:26:44.000000000

扩展后的函数代码 #

CREATE OR REPLACE FUNCTION random_interval(
      p_min IN NUMBER,
      p_max IN NUMBER,
      p_unit IN VARCHAR2 DEFAULT 'HOUR'
    ) RETURN INTERVAL DAY TO SECOND
   IS
      v_interval INTERVAL DAY TO SECOND;
   BEGIN
      -- 参数合法性校验
      IF p_min > p_max THEN
         raise_application_error(-20001, '最小值不能大于最大值');
      END IF;
      
      IF upper(p_unit) NOT IN ('DAY', 'HOUR', 'MINUTE', 'SECOND') THEN
         raise_application_error(-20002, '仅支持DAY、HOUR、MINUTE、SECOND四种时间单位');
      END IF;
      
      -- 根据不同单位生成随机间隔
      CASE upper(p_unit)
         WHEN 'DAY' THEN
            v_interval := floor(dbms_random.value(p_min, p_max)) * INTERVAL '1' DAY
                          + floor(dbms_random.value(0, 24)) * INTERVAL '1' HOUR
                          + floor(dbms_random.value(0, 60)) * INTERVAL '1' MINUTE
                          + floor(dbms_random.value(0, 60)) * INTERVAL '1' SECOND;
         WHEN 'HOUR' THEN
            v_interval := floor(dbms_random.value(p_min, p_max)) * INTERVAL '1' HOUR
                          + floor(dbms_random.value(0, 60)) * INTERVAL '1' MINUTE
                          + floor(dbms_random.value(0, 60)) * INTERVAL '1' SECOND;
         WHEN 'MINUTE' THEN
            v_interval := floor(dbms_random.value(p_min, p_max)) * INTERVAL '1' MINUTE
                          + floor(dbms_random.value(0, 60)) * INTERVAL '1' SECOND;
         WHEN 'SECOND' THEN
            v_interval := floor(dbms_random.value(p_min, p_max)) * INTERVAL '1' SECOND;
      END CASE;
      
      RETURN v_interval;
END random_interval;
/

扩展函数测试示例 #

测试天范围 #

SELECT random_interval(1, 4, 'DAY') AS random_val FROM dual CONNECT BY level <= 5 ORDER BY 1;

示例输出：

RANDOM_VAL
+000000001 05:12:34.000000000
+000000002 11:24:43.000000000
+000000002 18:09:17.000000000
+000000003 02:45:59.000000000
+000000003 20:33:08.000000000

测试分钟范围 #

SELECT random_interval(20, 40, 'MINUTE') AS random_val FROM dual CONNECT BY level <= 5 ORDER BY 1;

示例输出：

RANDOM_VAL
+000000000 00:24:44.000000000
+000000000 00:27:11.000000000
+000000000 00:31:56.000000000
+000000000 00:36:02.000000000
+000000000 00:39:47.000000000

测试兼容原有调用方式 #

SELECT random_interval(1, 10) AS random_val FROM dual CONNECT BY level <= 3 ORDER BY 1;

示例输出：

RANDOM_VAL
+000000000 02:15:42.000000000
+000000000 06:32:19.000000000
+000000000 09:07:33.000000000

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