设置种子，用于在Oracle SQL中从0和1之间的均匀分布生成随机值

Question

我有一个简单的选择查询，它使用DBMS_RANDOM.value函数从0和1之间的均匀分布中生成一个随机值。例如，我的SQL查询看起来像：

select
DBMS_RANDOM.value(0, 1),
*
from table

问题是，每次运行此查询时，都会出现一组不同的随机值。有没有办法设置种子以获得一组可重复的随机值？

Answer 1

如注释中所述，您可以使用DBMS_RANDOM.seed并提供种子值。

如果您想一致地生成相同的伪随机数集，请始终使用相同的种子。seed过程中允许的值为integer或string，最多2000个字符。

示例：

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2
3    DBMS_OUTPUT.put_line(CHR(10) || 'Set-1: Setting the SEED to 59' || CHR(10));
4    DBMS_RANDOM.seed (59); --Setting the SEED
5    FOR I IN 1 ..3 LOOP
6      DBMS_OUTPUT.put_line(DBMS_RANDOM.value(1,100));
7    END LOOP;
8
9    DBMS_OUTPUT.put_line(CHR(10) || 'Set-2: Resetting the SEED to 36. All values will be different than Set-1' || CHR(10));
10    DBMS_RANDOM.seed (36); --Setting the SEED
11    FOR I IN 1 ..3 LOOP
12      DBMS_OUTPUT.put_line(DBMS_RANDOM.value(1,100));
13    END LOOP;
14
15      DBMS_OUTPUT.put_line(CHR(10) || 'Set-3: Resetting the SEED to 59 again. All values after this reset will be same as Set-1' || CHR(10));
16    DBMS_RANDOM.seed (59); --Setting the SEED
17    FOR I IN 1 ..3 LOOP
18      DBMS_OUTPUT.put_line(DBMS_RANDOM.value(1,100));
19    END LOOP;
20
21  END;
22  /
Set-1: Setting the SEED to 59
74.14289538766981707792811687463755022131
51.25884047075217918289983306078006505072
94.75163705940204976856341395424131478654
Set-2: Resetting the SEED to 36. All values will be different than Set-1
53.86999730393101252712983137930835072954
96.38611422814157294559942815958666746821
28.70442013260713167837548432433093564504
Set-3: Resetting the SEED to 59 again. All values after this reset will be same as Set-1
74.14289538766981707792811687463755022131
51.25884047075217918289983306078006505072
94.75163705940204976856341395424131478654
PL/SQL procedure successfully completed.
SQL>