暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / DBMS_RANDOM随机函数包 ：在MogDB数据库中生成随机数（数字、字符串和日期）

DBMS_RANDOM随机函数包 ：在MogDB数据库中生成随机数（数字、字符串和日期）

mogdb 2021-09-06
805


compat-tools介绍

compat-tools是一个兼容工具集合，旨在为从其他异构数据库，迁移到 MogDB之后的系统，创建必要的函数，以及系统视图的兼容。为后续的系统运维与应用改造提供便利。


获取compat-tools

若要安装 compat-tools，请从https://gitee.com/enmotech/compat-tools下载中心下载该工具的最新版本。


compat-tools功能

  1. runMe.sql: 总调度脚本
  2. Oracle_Views.sql: 兼容Oracle数据库数据字典及视图
  3. Oracle_Functions.sql: 兼容Oracle数据库函数
  4. Oracle_Packages.sql: 兼容Oracle数据库管理包
  5. MySQL_Views.sql: 兼容MySQL数据库数据字典及视图 //TODO
  6. MySQL_Functions.sql: 兼容MySQL数据库函数 //TODO


compat-tools支持的数据库版本

  • MogDB 2.0
  • MogDB 1.1


安装和使用compat-tools的方法

  1. 获取compat-tools：https://gitee.com/enmotech/compat-tools

  2. 将下载的文件存放到自定义的目录中（本文以/opt/compat-tools-0902为例）

    [root@mogdb-kernel-0005 compat-tools-0902]# pwd
/opt/compat-tools-0902
[root@mogdb-kernel-0005 compat-tools-0902]# ls -l
total 228
-rw-r--r-- 1 root root   9592 Sep  2 14:40 LICENSE
-rw-r--r-- 1 root root      0 Sep  2 14:40 MySQL_Functions.sql
-rw-r--r-- 1 root root      0 Sep  2 14:40 MySQL_Views.sql
-rw-r--r-- 1 root root  41652 Sep  2 14:40 Oracle_Functions.sql
-rw-r--r-- 1 root root  34852 Sep  2 14:40 Oracle_Packages.sql
-rw-r--r-- 1 root root 125799 Sep  2 14:40 Oracle_Views.sql
-rw-r--r-- 1 root root   4708 Sep  2 14:40 README.md
-rw-r--r-- 1 root root    420 Sep  2 14:40 runMe.sql
    复制

  3. 切换到omm用户

    su - omm
    复制

  4. 用omm用户执行以下脚本（26000是数据库所在的端口号）

    gsql -d mogdb -p 26000 -f /opt/compat-tools-0902/runMe.sql
    复制


开始测试DBMS_RANDOM随机函数包

登录到mogdb数据库

[omm@mogdb-kernel-0005 ~]$ gsql -d mogdb -p 26000
gsql ((MogDB 2.0.1 build f892ccb7) compiled at 2021-07-09 16:12:59 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
mogdb=#
复制



SEED

SEED可以用来指定伪随机数生成器的种子，增加其随机性。seed可支持二进制整数，也支持长度最大为2000的字符串。如果要始终生成相同的伪随机数集，请始终使用相同的随机数种子。

declare
BEGIN
  DBMS_OUTPUT.put_line('Run 1 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;
  DBMS_OUTPUT.put_line('Run 2 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;
END;
/
NOTICE:  Run 1 : seed=0
CONTEXT:  SQL statement "CALL dbms_output.put_line('Run 1 : seed=0')"
PL/pgSQL function inline_code_block line 3 at PERFORM
NOTICE:  i=1 : value=2.53745232429355
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=2 : value=7.749117821455
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=3 : value=1.86734489817172
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=4 : value=8.83418704243377
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  i=5 : value=6.19573155790567
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  Run 2 : seed=0
CONTEXT:  SQL statement "CALL dbms_output.put_line('Run 2 : seed=0')"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  i=1 : value=2.53745232429355
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=2 : value=7.749117821455
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=3 : value=1.86734489817172
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=4 : value=8.83418704243377
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  i=5 : value=6.19573155790567
CONTEXT:  SQL statement "CALL dbms_output.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10))"
PL/pgSQL function inline_code_block line 12 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制


VALUE

VALUE函数用于生成一个指定范围的随机数。 若不指定范围，将生成一个大于等于0小于1的38位随机小数（小数点后38位）

DECLARE
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('value= ' || DBMS_RANDOM.value());
  END LOOP;
END;
/
NOTICE:  value= .785799258388579
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .692194153089076
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .368766269646585
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .87390407640487
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value= .745095098391175
CONTEXT:  SQL statement "CALL dbms_output.put_line('value= ' || DBMS_RANDOM.value())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
复制

若指定了范围，则会生成一个大于等于最小值且小于最大值的随机数，同时小数点后的取值限制在最大值范围内。

declare
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100));
  END LOOP;
END;
/
NOTICE:  value(1,100)= 45.158544998616
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 36.0190920610912
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 73.5194435422309
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 26.7619780991226
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  value(1,100)= 40.035083710216
CONTEXT:  SQL statement "CALL dbms_output.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制

可按要求使用TRUNC或者ROUND改变随机数位数。例如，要生成1到10之间的随机整数，将小数点后的值舍去并给最大值加1。

mogdb=# select TRUNC(DBMS_RANDOM.value(1,11)) ;
 trunc
-------
     6
(1 row)
mogdb=#
复制


STRING

STRING 函数返回指定长度的随机字符串。OPT 参数定义了字符串的类型。 详细如下：

  • 'u', 'U' - 返回一个由大写字母组成的字符串
  • 'l', 'L' - 返回一个由小写字母组成的字符串
  • 'a', 'A' - 返回一个由大写字母和小写字母组成的字符串
  • 'x', 'X' - 返回一个由大写字母和数字组成的字符串
  • 'p', 'P' - 返回一个由任意的可打印字符组成的字符串

LEN参数定义了返回字符串的长度。

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
  END LOOP;
END;
/
NOTICE:  string('x',10)= i5S6XOZxrA
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= HGvRm75w19
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= N9WsQGJl6l
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= hDlPevVgRb
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('x',10)= ZdSd8x8RKx
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制

STRING和VALUE函数结合可以生成各种长度的字符串。

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))));
  END LOOP;
END;
/
NOTICE:  string('L',?)= kcyzowdxqbyzu
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= ohzpljyatsplqtbbus
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= hbrjsfeevoi
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= lfsapmytdamvwcw
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  string('L',?)= pcvtxnwzomkqwpfzes
CONTEXT:  SQL statement "CALL dbms_output.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制


NORMAL

NORMAL函数返回一组随机数。

declare
BEGIN
  FOR cur_rec IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('normal= ' || DBMS_RANDOM.normal());
  END LOOP;
END;
/
NOTICE:  normal= .838851847718988
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.523612260373397
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.241931681458075
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= -.120847761874286
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  normal= .360125112757284
CONTEXT:  SQL statement "CALL dbms_output.put_line('normal= ' || DBMS_RANDOM.normal())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制


RANDOM

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('random= ' || DBMS_RANDOM.random());
  END LOOP;
END;
/
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -1023930867
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= 1068572119
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= 95361253
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -712638729
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  This function is deprecated with Release 11gR1, although currently supported, it should not be used.
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  random= -1251059926
CONTEXT:  SQL statement "CALL dbms_output.put_line('random= ' || DBMS_RANDOM.random())"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制


Generating Random Dates

目前没有生成随机日期的特定函数，但是可以为现有日期添加随机数来产生随机日期。下面的示例为生成下一年的随机日期。

declare
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)));
  END LOOP;
END;
/
NOTICE:  date= 2021-10-06 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-05-09 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-04-07 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2021-11-29 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  date= 2022-06-04 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)))"
PL/pgSQL function inline_code_block line 4 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制

通过进行正确除法运算，可以给日期添加小时、秒或分钟。

DECLARE
  l_hours_in_day NUMBER := 24;
  l_mins_in_day  NUMBER := 24*60;
  l_secs_in_day  NUMBER := 24*60*60;
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('hours= ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)));
  END LOOP;
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('mins = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)));
  END LOOP;
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('secs = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)));
  END LOOP;
END;
/
NOTICE:  hours= 2021-10-13 22:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-10-10 00:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-07 02:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-26 11:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  hours= 2021-09-19 22:00:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('hours= ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)))"
PL/pgSQL function inline_code_block line 6 at PERFORM
NOTICE:  mins = 2021-09-04 00:01:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 11:56:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 00:53:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 00:21:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  mins = 2021-09-04 12:38:00
CONTEXT:  SQL statement "CALL dbms_output.put_line('mins = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)))"
PL/pgSQL function inline_code_block line 9 at PERFORM
NOTICE:  secs = 2021-09-04 00:10:28
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:15:31
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:09:07
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:06:54
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
NOTICE:  secs = 2021-09-04 00:06:32
CONTEXT:  SQL statement "CALL dbms_output.put_line('secs = ' || (TRUNC(SYSDATE)+ (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)))"
PL/pgSQL function inline_code_block line 12 at PERFORM
ANONYMOUS BLOCK EXECUTE
mogdb=#
复制


Generating Random Data

DBMS_RANDOM 包用于生成随机测试数据。通过将其与查询结合，可以快速生成大量数据。

mogdb=# CREATE TABLE random_data (
  id           NUMBER,
  small_number NUMBER(5),
  big_number   NUMBER,
  short_string VARCHAR2(50),
  long_string  VARCHAR2(400),
  created_date DATE,
  CONSTRAINT random_data_pk PRIMARY KEY (id)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "random_data_pk" for table "random_data"
CREATE TABLE
mogdb=#
复制
mogdb=# INSERT INTO random_data
SELECT generate_series(1,29999),
       TRUNC(DBMS_RANDOM.value(1,5)) AS small_number,
       TRUNC(DBMS_RANDOM.value(100,10000)) AS big_number,
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS short_string,
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(100,400))) AS long_string,
       TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date;
INSERT 0 29999
mogdb=#
mogdb=#  select count(*) from random_data;
 count
-------
 29999
(1 row)
mogdb=#
复制
mogdb
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
307次阅读
2025-04-17 10:41:41
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
201次阅读
2025-04-16 09:52:02