数学函数
Oracle学习网d ? C0_ T(o [D
?vqU;K|ZO 1.绝对值 Oracle学习网*D9X�k9f,}1O
Oracle学习网)jZq/VV$n
S:select abs(-1) value
&ak;`xQ]4|
Oracle学习网$^.wn3Cwb#@@
O:select abs(-1) value from dual
qi)K G0ucW
Oracle学习网+_P:h6KE:X.m2P+f+/
2.取整(大) Oracle学习网0M{rh|3K Ca
.u Dzw"O5W�vi S:select ceiling(-1.001) value Oracle学习网2^|PhI4x
Oracle学习网E/{|8Z ^*Y%b*hP2V4h
O:select ceil(-1.001) value from dual Oracle学习网;IL ZP7Oy,?k@-b
Oracle学习网 CORX5O:f
3.取整(小)
r4xVC$zm
lZ9?v o$c S:select floor(-1.001) value
t!Ko@t#CGF
Oracle学习网E-gl/|6p2|
O:select floor(-1.001) value from dual Oracle学习网h/j"o*P#iw
X-bVZ)?ry&l(g 4.取整(截取)
JaL[A0r+h
Oracle学习网.z4wbgCcH$QUDR
S:select cast(-1.002 as int) value
/[ Z^9E8VS
Oracle学习网(~ BO4YYEM
O:select trunc(-1.002) value from dual Oracle学习网Nr"ft3k2Q|`#u+X
Oracle学习网qi b1xQ'//Z'G
5.四舍五入
h!kr3P]M�@
6H1QY$P7O5P;f S:select round(1.23456,4) value 1.23460
$dR*/9M5b'I|1u2f
Oracle学习网e/^VcPe'W K%|
O:select round(1.23456,4) value from dual 1.2346 Oracle学习网%PL9_'Gm1Gj3L�Q
-Vz]e{ 6.e为底的幂
`/P)Gl6q's}
M,/HV#|g:^ S:select Exp(1) value 2.7182818284590451 Oracle学习网's rW"W-n2qrV/~7u6W
Oracle学习网Z"||.j1RiJ3|k/
O:select Exp(1) value from dual 2.71828182 Oracle学习网#m)Q5||/'L0ME g
bgR4a,?1Q%Rj^ 7.取e为底的对数 Oracle学习网.p(Kz|J3/
7a+aY&e8Q1l_-Y S:select log(2.7182818284590451) value 1 Oracle学习网7J3E$G%~ {
H+h%mj)BTR O:select ln(2.7182818284590451) value from dual; 1
l9Y6rq gfG
C;n&w&E9}L*} 8.取10为底对数
^z k5|}e#`pl
9h{ @;~/]#w]Y S:select log10(10) value 1
d/Pe /r Ia
Oracle学习网a$L [:B'{+p+x
O:select log(10,10) value from dual; 1 Oracle学习网(H(x|r�['}1Ck
:ih.X|h1uJYV U&C 9.取平方 Oracle学习网#OV-iupj,EW4[z
Oracle学习网V(_WSyY@!EJ
S:select SQUARE(4) value 16
[ R0n7z#q&q }k
Oracle学习网.D+a2R4Z&r4i2?
O:select power(4,2) value from dual 16 Oracle学习网.`-/9R:Z�K0AB
.Cf1J w;S!h 10.取平方根 Oracle学习网#`X$H^/@Y#OY
!wg5|wko&Z.Z S:select SQRT(4) value 2
z_9gA$mV(IzsF
Oracle学习网J!A E#G)M%s
O:select SQRT(4) value from dual 2 Oracle学习网O0Ct8au6e.hN
Oracle学习网3Ys*Yowd{
11.求任意数为底的幂 Oracle学习网)}8Y@?-Y!|@
z0X./$]HI2g'TID S:select power(3,4) value 81
];e3ZG!y[?O/S
Oracle学习网'l YQ'L!O)}"v)v
O:select power(3,4) value from dual 81 Oracle学习网4qh^-oox!|JVW
u/Zgp9am'fI#B9A 12.取随机数
[ p/ GM:/ E
Oracle学习网N2E$Cn/.cCGO)m
S:select rand() value
T+f"T7IZ Sd
Oracle学习网3^E |C p:/M
O:select sys.dbms_random.value(0,1) value from dual;
J?lk8mY zp:A6~ Xd
Oracle学习网eW]7R0bU9V;D
13.取符号 Oracle学习网�y F8puU;sE'myp+_
J7/ VN�rG S:select sign(-8) value -1 Oracle学习网'v Zh`P,[y+P
K&jd}k"pf O:select sign(-8) value from dual -1 Oracle学习网aZL^$l
5o3/B6?bI5[ 数学函数 Oracle学习网8tu3Dvq6G(G!]3`R
(dJ|({�g U%K 14.圆周率
Ue%E'h L'EL
3sr4w3k$f`2](y7n'~ S:SELECT PI() value 3.1415926535897931
!y2X1Y5v_X ]8^
Oracle学习网G#N%S#/I~ XLj
O:不知道
15.sin,cos,tan 参数都以弧度为单位 Oracle学习网 PX%Y'?'p [ n5ba/p#y
8I p PnE1V 例如:select sin(PI()/2) value 得到1(SQLServer) Oracle学习网H4MF!v-w0[4{ p!J
Oracle学习网.R2fB,v Tsa|a
16.Asin,Acos,Atan,Atan2 返回弧度 Oracle学习网Zii9c�`@1oL
h%]{ Zb+@#G�Pd R5M*a 17.弧度角度互换(SQLServer,Oracle不知道)
4gj1g-{L7R|'S
Oracle学习网-E%MF` E(YJU
DEGREES:弧度-〉角度 Oracle学习网(/H)h e#yJ5OO L
Oracle学习网1nD)JW'J"/.MXZb.jU
RADIANS:角度-〉弧度 Oracle学习网)XL$o3a-I&t;W�O
)D8M)tm9C9H 数值间比较
!n!qO"] }(tP`;I9pA
Oracle学习网 e6p/YN#U3D+p
18. 求集合最大值 Oracle学习网3[u$ot#_
4VD:m!N'~$k+Y0~
Oracle学习网7]qUg}!i�]
S:select max(value) value fromOracle学习网+h*I b(?|&K U/yL
(select 1 valueOracle学习网liT$H^#X
union Oracle学习网:KfP m~(VA*A
select -2 value Oracle学习网 [Z$a4t�F&rJ
union Oracle学习网d%N:i /]*Z
select 4 value
U4y"J5k'E+f union Oracle学习网tB3V9^ aAJ
select 3 value)a Oracle学习网7W*a+/-JNVl O
Oracle学习网](U,o:N&{
Oracle学习网2Z(R thr!M-bz
O:select greatest(1,-2,4,3) value from dual Oracle学习网VR$r_2_?P`
Oracle学习网1a U:k2Zq.V1PH
19. 求集合最小值
0rj.V9zCzt
#?Y,N�a`Z n'b
Oracle学习网"Y4/$O2mZ*X#Z,`p
S:select min(value) value from
*HrPmYL f"{:s (select 1 valueOracle学习网I;{K:ui[!Pq$^
union
:@$uM-`1SUY select -2 value Oracle学习网k2IM)Rk /!m,R
union
;C5U5D-g6usW/~h select 4 value Oracle学习网"Q2e+U)_n8^z
union Oracle学习网/rjyd8y(hC7_
select 3 value)a Oracle学习网O {+S3L`({D
M X.X!^l)u|7x1XP
Oracle学习网$N^5?h1rVe.K
O:select least(1,-2,4,3) value from dual
e7H0y+@$VC^${L1?h
Oracle学习网el�x v0o,O!RU
20.如何处理null值(F2中的null以10代替)
sX-Xgxm
F*y/.fqMOH/J_-z S:select F1,IsNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl Oracle学习网-]7cA;H"bT:{/eE
(T3h} `?K 数值间比较 Oracle学习网rC9]t`0kl&d
!lId(](q`"O7zS 21.求字符序号
r$c$e U*C;a1YY
Oracle学习网X;R!B-@Rs4r!^Kt-E
S:select ascii('a') value
L1`r_4C"X@_
X9J D2O0D O:select ascii('a') value from dual Oracle学习网B{WTv)l+O
Oracle学习网 [r4jS7kU7^ ]{7o
22.从序号求字符
%x9O7L$w9^5m]_"y#]
0`9H Vgy S:select char(97) value Oracle学习网)YW*lN m3[O f:R4BH
Oracle学习网Y B)W /O^O)e,S
O:select chr(97) value from dual Oracle学习网Wd]2W*MA
Oracle学习网3WMGPw(Ynrq[
23.连接 Oracle学习网7xy,Np`
Oracle学习网Tf O+nI8B
S:select '11'+'22'+'33' value
,d LFd{0D }PA
Oracle学习网5O6pi7w./9AZ
O:select CONCAT('11','22')||33 value from dual Oracle学习网!w/c+Gp8H
e`)uIGI)O2D 23.子串位置 --返回3 Oracle学习网)X;L aYg#U4d
[U|Y'P S:select CHARINDEX('s','sdsq',2) value
)G:t s2@oMX`J{)`
XJ.l:~�/H*L7~T O:select INSTR('sdsq','s',2) value from dual
,V5/l ~H/@I1a8~
Oracle学习网Z+r W/-m'F5g0s9F
23.模糊子串的位置 --返回2,参数去掉中间%则返回7
)R ZEz*{2B_
8y8C9}g8An S:select patindex('%d%q%','sdsfasdqe') value Oracle学习网@MXTh{B$Y
Oracle学习网&e,c%K N.t(Lr(W
O:oracle没发现 Oracle学习网"UmX(`o+Mc[b1?8m
0]!Nm1X7v 24.求子串 Oracle学习网/Y2q3Xw
Oracle学习网L0P.X&E0gf:E
S:select substring('abcd',2,2) value
4P[6`8suz&zh
]'N$m ]8wt7A3DC O:select substr('abcd',2,2) value from dual Oracle学习网/{)u8l1d]7lP
"~7^*hL9x5_ 25.子串代替 返回aijklmnef
3r_-MT9vc
Oracle学习网3H(vR.By0C8F%[ J2fe@7`!~
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
{V lw�S0ksF
9of{9s'av2h O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
h3I[/Q e#I
/x3H5F�`eTk9uj 26.子串全部替换 Oracle学习网w6om6Z(G
Oracle学习网7?#M8B&D]8dM@&Z
S:没发现 Oracle学习网5Zs*p5Od.d'Nvw
Oracle学习网 C(ZW/Gn D
O:select Translate('fasdbfasegas','fa','我' ) value from dual
#j`!{'Et1b
Oracle学习网!gzG+u%@[
27.长度 Oracle学习网3T5I6{MU#V#q*V3A
Oracle学习网$n5Em1`�P`#b&u,P
S:len,datalength Oracle学习网NE1S }E/|?.B
Oracle学习网4{0_%U;x7f ny~F2[9U�@
O:length
z,O5Z1~9d%ekXYlq
eY4LD4I2k5@ 28.大小写转换 lower,upper Oracle学习网 A%]-Vr+[|�}B
Oracle学习网p/y$H@,@!?:IDW:X
29.单词首字母大写 Oracle学习网 d$aXJ8Z*H0k3eD
~GyLn c�{8r S:没发现 Oracle学习网#x h`o9Q^
1gnjul7f1Z O:select INITCAP('abcd dsaf df') value from dual Oracle学习网 @K[6@c HH ^
Oracle学习网7l-|�syw s b
30.左补空格(LPAD的第一个参数为空格则同space函数)
;[|,Isz4bgB{7_
Oracle学习网-b D{,[�}#[ _u7Q
S:select space(10)+'abcd' value Oracle学习网Wo#u ?4lZl4q&ri
vf#jS�Pj O:select LPAD('abcd',14) value from dual Oracle学习网8QLF{6Z&{1B)k
~A7k/F4Z 31.右补空格(RPAD的第一个参数为空格则同space函数) Oracle学习网`#I(Y[5q5E
{&u.I-q6Qq#I S:select 'abcd'+space(10) value
z(y!d dm-ir
Mv$x d7{ O:select RPAD('abcd',14) value from dual
原文地址
Oracle学习网 V]Y;u:@6agK
Oracle学习网0H-K#p'S?&Z7l