Oracle 随机函数 DBMS_RANDOM
Jun 07, 2016 pm 04:49 PM最近在给公司开发人员造数据,这些数据要随机范围的,就用到了DBMS_RANDOM函数。这个函数不光能造一些随机数字,还能随机造一些日
最近在给公司开发人员造数据,这些数据要随机范围的,就用到了DBMS_RANDOM函数。这个函数不光能造一些随机数字,还能随机造一些日期|字符等。
DBMS_RANDOM在官方文档的 PL/SQL Packages and Types Reference-->75 DBMS_RANDOM这个位置。
--------------------------------------分割线 --------------------------------------
生成动态前缀且自增号码的Oracle函数
Spring StoredProcedure调用Oracle函数各种异常解决方法
Oracle函数之case和decode的用法区别及性能比较
Oracle函数wmsys.wm_concat的使用
SQL中Oracle函数列表速查
--------------------------------------分割线 --------------------------------------
1.产生随机数字
SQL> SELECT DBMS_RANDOM.VALUE FROM DUAL;
VALUE
----------
.360532538
SQL> SELECT DBMS_RANDOM.VALUE(0, 100) FROM DUAL;
DBMS_RANDOM.VALUE(0,100)
-------------------------------
26.8474818
这个函数在默认下回产生0-1之间的小数,如果加入参数的话,会产生参数之间的小数
2.产生整数
SQL> SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;
TRUNC(DBMS_RANDOM.VALUE(0,100))
-------------------------------
57
SQL> SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;
TRUNC(DBMS_RANDOM.VALUE(0,100))
-------------------------------
34
3.产生随机日期
SQL> SELECT TO_CHAR(TO_DATE('2014-01-02','yyyy-mm-dd'),'J')FROM DUAL;
TO_CHAR(TO_DATE('2014
---------------------
2456660
SQL> SELECT to_char(to_date(TRUNC(DBMS_RANDOM.VALUE(2456660,2456660+180)),'j'),'yyyy-mm-dd') FROM DUAL;
TO_CHAR(TO_DATE(TRUNC(DBMS_RAN
------------------------------
2014-02-24
SQL> SELECT to_char(to_date(TRUNC(DBMS_RANDOM.VALUE(2456660,2456660+180)),'j'),'yyyy-mm-dd') FROM DUAL;
TO_CHAR(TO_DATE(TRUNC(DBMS_RAN
------------------------------
2014-02-28
上面的意思是 ,想要2014-01-02以后180以内随机的日期,先计算出2014-01-02那天对应的oracle内部日期数,,然后根据这个计算便宜量。
4.随机字符
SQL> SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;
DBMS_RANDOM.STRING('A',20)
--------------------------------------------------------------------------------
egNhrpzmklLVUWHMNPbb
SQL> SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;
DBMS_RANDOM.STRING('A',20)
--------------------------------------------------------------------------------
IlMlBgNlvqwaufbTJxHN
SQL> SELECT DBMS_RANDOM.STRING('U',20) FROM DUAL;
DBMS_RANDOM.STRING('U',20)
--------------------------------------------------------------------------------
DLAMFMWNWWUEXOAQGNJT
SQL> SELECT DBMS_RANDOM.STRING('U',20) FROM DUAL;
DBMS_RANDOM.STRING('U',20)
--------------------------------------------------------------------------------
QYGPITUPJHMKTOAKFCGX
SQL> SELECT DBMS_RANDOM.STRING('L',20) FROM DUAL;
DBMS_RANDOM.STRING('L',20)
--------------------------------------------------------------------------------
nbxvjzdddxduwkvmfubp
SQL> SELECT DBMS_RANDOM.STRING('L',20) FROM DUAL;
DBMS_RANDOM.STRING('L',20)
--------------------------------------------------------------------------------
ujyqkuocrhouphozxktm
SQL>
‘U’用来生成大写字符
‘L’用来生成小写字符
‘A’用来生成大小写混合的字符
本文永久更新链接地址:

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Function to calculate the number of days between two dates in oracle

How long will Oracle database logs be kept?

The order of the oracle database startup steps is

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle
