oracle生成4位随机数

select

to_char(sysdate,'yyyymmdd')||lpad(round(dbms_randomvalue(1,999999999)),9,0)

from

dual;

解释一下，to_char(sysdate,'yyyymmdd')是求得日期，也就是当前日期的年月日，||是连接符号

round(dbms_randomvalue(1,999999999)是取随机数的整数位，round是四舍五入，从1-99999999之间取

lpad(9,0)是个左补0函数，如果选出的随机数为1，则前边就是8个0补充

ceil : 小数不为零部分上取整，即向上取最近的整数select ceil(19) from dual; // 结果为2select ceil(10) from dual; // 结果为1select ceil(101) from dual; // 结果为2floor ： 小数部分下取整，即向下取最近的整数select floor(19) from dual; // 结果为1select floor(10) from dual; // 结果为1select floor(101) from dual; // 结果为1

---------数学函数

1绝对值

S:select abs(-1) value

O:select abs(-1) value from dual

2取整(大)

S:select ceiling(-1001) value

O:select ceil(-1001) value from dual

3取整（小）

S:select floor(-1001) value

O:select floor(-1001) value from dual

4取整（截取）

S:select cast(-1002 as int) value

O:select trunc(-1002) value from dual

5四舍五入

S:select round(123456,4) value 123460

O:select round(123456,4) value from dual 12346

6e为底的幂

S:select Exp(1) value 27182818284590451

O:select Exp(1) value from dual 271828182

7取e为底的对数

S:select log(27182818284590451) value 1

O:select ln(27182818284590451) value from dual; 1

8取10为底对数

S:select log10(10) value 1

O:select log(10,10) value from dual; 1

9取平方

S:select SQUARE(4) value 16

O:select power(4,2) value from dual 16

10取平方根

S:select SQRT(4) value 2

O:select SQRT(4) value from dual 2

11求任意数为底的幂

S:select power(3,4) value 81

O:select power(3,4) value from dual 81

12取随机数

S:select rand() value

O:select sysdbms_randomvalue(0,1) value from dual;

13取符号

S:select sign(-8) value -1

O:select sign(-8) value from dual -1

----------三角函数相关

14圆周率

S:SELECT PI() value 31415926535897931

O:不知道

15sin,cos,tan 参数都以弧度为单位

例如：select sin(PI()/2) value 得到1（SQLServer）

16Asin,Acos,Atan,Atan2 返回弧度

17弧度角度互换(SQLServer，Oracle不知道)

DEGREES：弧度-〉角度

RADIANS：角度-〉弧度

---------数值间比较

18 求集合最大值

S:select max(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select greatest(1,-2,4,3) value from dual

19 求集合最小值

S:select min(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select least(1,-2,4,3) value from dual

20如何处理null值(F2中的null以10代替)

S:select F1,IsNull(F2,10) value from Tbl

O:select F1,nvl(F2,10) value from Tbl

--------字符串函数

21求字符序号

S:select ascii('a') value

O:select ascii('a') value from dual

22从序号求字符

S:select char(97) value

O:select chr(97) value from dual

23连接

S:select '11'+'22'+'33' value

O:select CONCAT('11','22')||33 value from dual

23子串位置 --返回3

S:select CHARINDEX('s','sdsq',2) value

O:select INSTR('sdsq','s',2) value from dual

23模糊子串的位置 --返回2,参数去掉中间%则返回7

// 本文转自 C++Builder 研究 - >

zhangyu@orcl>select lpad(x,10,'') from test;

LPAD(X,10,'')

--------------------

人生

张三

中华人民

下面应该挺全的了,如果不够就再找

select from all_objects where object_type='FUNCTION';

ABS

ACOS

ASIN

ATAN

ATAN2

BITAND

CEIL

COS

COSH

EXP

FLOOR

LN

LOG

MOD

NANVL

POWER

REMAINDER

ROUND (number)

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC (number)

WIDTH_BUCKET

CHR

CONCAT

INITCAP

LOWER

LPAD

LTRIM

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

REGEXP_REPLACE

REGEXP_SUBSTR

REPLACE

RPAD

RTRIM

SOUNDEX

SUBSTR

TRANSLATE

TREAT

TRIM

UPPER

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT (datetime)

FROM_TZ

LAST_DAY

LOCALTIMESTAMP

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND (date)

SESSIONTIMEZONE

SYS_EXTRACT_UTC

SYSDATE

SYSTIMESTAMP

TO_CHAR (datetime)

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_DSINTERVAL

TO_YMINTERVAL

TRUNC (date)

TZ_OFFSET

ASCIISTR

BIN_TO_NUM

CAST

CHARTOROWID

COMPOSE

CONVERT

DECOMPOSE

HEXTORAW

NUMTODSINTERVAL

NUMTOYMINTERVAL

RAWTOHEX

RAWTONHEX

ROWIDTOCHAR

ROWIDTONCHAR

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

TO_CHAR (character)

TO_CHAR (datetime)

TO_CHAR (number)

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB

TO_MULTI_BYTE

TO_NCHAR (character)

TO_NCHAR (datetime)

TO_NCHAR (number)

TO_NCLOB

TO_NUMBER

TO_DSINTERVAL

TO_SINGLE_BYTE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TO_YMINTERVAL

TRANSLATE USING

UNISTR

CLUSTER_ID

CLUSTER_PROBABILITY

CLUSTER_SET

FEATURE_ID

FEATURE_SET

FEATURE_VALUE

PREDICTION

PREDICTION_COST

PREDICTION_DETAILS

PREDICTION_PROBABILITY

PREDICTION_SET

以上就是关于oracle生成4位随机数全部的内容，包括:oracle生成4位随机数、hana中的哪个函数和oracle中的lpadyi、Oracle 函数都有那些等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！