语言
<< 返回文章列表

openGauss / MogDB里无法使用DBMS_RANDOM？不存在的！

2021年9月18日
云和恩墨
18

经过多年的发展，国产数据库经历了从默默无闻到遍地开花，从仰望跟随到创新引领的艰难跋涉，正在赶超曾经的商业巨头，也越来越受到各行业用户的重视和青睐。去“O”浪潮已成大势。

DBMS_RANDOM对于Oracle DBA来说一定并不陌生。它是Oracle提供的一个PL/SQL包，用于生成随机数值或字符，在生产场景中经常会被用到。尽管openGauss可以实现类似于DBMS_RANDOM的功能，但函数名称不同、SQL语句也不一样，这就使得从Oracle迁移到openGauss数据库的过程变得不够“平顺”，不但必须涉及到代码改动，还增加了研发人员的额外学习成本。云和恩墨开源的compat-tools，正是为了改善这些问题而诞生的。

compat-tools作为MogDB的配套组件，是一个兼容工具集合。当从Oracle迁移到MogDB之后，在compat-tools的加持下，与DBMS_RANDOM相关的函数不需要做任何改动，就可以在MogDB中使用，其目的就是最大化地减少迁移过程中代码的修改，做到真正的“无缝转换”。实际上，除DBMS_RANDOM以外，compat-tools为从其他异构数据库迁移到MogDB之后的系统，现已实现了3个管理包、19个必要函数和53个系统视图的兼容，为后续的系统运维与应用改造提供极大便利。

早前，我们已经对compat-tools做过介绍（了解详情）。本文讲解如何通过compat-tools在MogDB数据库中使用DBMS_RANDOM随机函数包。

 

安装和使用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随机函数包
1
登录到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=#
 
2
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=#

 

3
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=#

 

4
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=#

 

5
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=#

 

6
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=#

 

7
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=#

 

8
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

→ 戳我去玩儿 ←