1、ABS(n) 求绝对值
select abs(12.42) a1,abs(-154.11) a2 from dual;
2、ACOS(n)[-1<=n<=1] 求反余弦值,返回弧度[0,PI]
select acos(-1) a1,acos(1) a2,acos(0.5) a3,acos(-0.5) a4 from dual;
3、ASIN(n)[-1<=n<=1] 求反正弦值,返回弧度[-PI/2,PI/2]
select asin(-1) a1,asin(1) a2,asin(0.5) a3,asin(-0.5) a4 from dual;
4、ATAN(n)[-PI/2<=n<=PI/2] 求反正切值,返回值为弧度[-PI/2,PI/2]
select atan(-1) a1,atan(1) a2,atan(0.5) a3,atan(-0.5) a4 from dual;
5、ATAN2(n,m) 求n/m的反正切值,返回值为弧度[-PI,PI]
select atan2(18,5) a1,atan2(11,12.14) a2,atan2(0,0.5) a3,atan2(-0.5,0) a4 from dual;
6、BITAND(n1,n2) 求n1与n2进行AND位运算的结果,如果数据为非整数,那么会将计算结果转换为整数
select bitand(18,7) a1,bitand(11,12.14) a2,bitand(0,0.5) a3,bitand(-0.5,0) a4 from dual;
ORACLE中只有BITAND而没有BITOR, BITXOR;
原因是,有了BITAND, 很容易实现BITOR和BITXOR;
BITOR(x,y)= (x + y) - BITAND(x, y);
BITXOR(x,y)= BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;
7、CEIL(n) 返回大于等于n的最小整数
select ceil(-1) a1,ceil(1.8) a2,ceil(154.6) a3,ceil(154.4) a4 from dual;
8、COS(n) 求余弦值 [n为弧度]
select cos(-1000) a1,cos(1) a2,cos(0) a3,cos(-0.5) a4 from dual;
9、COSH(n) 求双曲余弦值
select cosh(-10) a1,cosh(1) a2,cosh(0) a3,cosh(-0.5) a4 from dual;
10、EXP(n) 求(e=2.71828183)的n次方
select EXP(2) a1,EXP(-2) a2,EXP(0) a3,EXP(1.2) a4 from dual;
11、FLOOR(n) 返回小于等于n的最大整数
select floor(-1) a1,floor(1.8) a2,floor(154.6) a3,floor(154.4) a4 from dual;
12、LN(n) 返回n的自然对数[n>0]
select ln(2.37814) a1,ln(1.8) a2,ln(154.6) a3,ln(154.4) a4 from dual;
13、LOG(n1,n2) 返回以n1为底数,n2的对数[n1>0,n1!=1],[n2>0]
select LOG(0.1,12) a1,log(12,1) a2,log(1.2,1.2) a3 from dual;
14、MOD(n1,n2) 返回n1除以n2的余数,如果n2=0则返回n1
select mod(2.5,1) a1,mod(2,0) a2,mod(1,2) a3,mod(1.54,3.354) a4 from dual;
15、NANVL(n1,n2 )针对BINARY_FLOAT,BINARY_DOUBLE数据,如果n1是非数字,那么久返回n2,否则返回n1
16、POWER(n1,n2) 返回n1的n2次方 ,n1,n2为任意数字,但是如果n1为负数,那么n2必须为整数
select power(3,2) a1,power(-1.2,2) a2,power(1.2,1.2) a3,power(-1,-2) a4 from dual;
17、REMAINDER(n1,n2)返回n1除以n2的余数,与MOD函数有点区别:
MOD(n1,n2)=n1-(n1*floor(n1/n2))
REMAINDER (n1,n2)=n1-(n1*round(n1/n2))
select remainder(3.5,2),mod(3.5,2) from dual;
select remainder(1.3,2) a1,remainder(2,1.5) a2,remainder(0,1) a3 from dual;
18、ROUND(n,t) 返回四舍五入结果,t为整数,保留小数位数
select round(4.2) a1,round(4.2571,1) a2,round(4.2571,2) a3 from dual;
19、SIGN(n) n<0返回-1,n=0返回0,n>0返回1
select sign(-1.2) a1,sign(0) a2,sign(1.54) a3 from dual;
20、SIN(n) 返回正弦值,n为弧度
select sin(0) a1,sin(-1) a2,sin(1) a3 from dual;
21、SINH(n) 返回双曲正弦值
select sinh(0) a1,sinh(-1) a2,sinh(1) a3 from dual;
22、SQRT(n) 返回n开平方值[n>=0]
select sqrt(1) a1,sqrt(0) a2,sqrt(1.1) a3 from dual;
23、TAN(n) 返回n的正切值 n为弧度
selecttan(-1) a1,tan(1) a2,tan(0.5) a3,tan(-0.5) a4 from dual;
24、TANH(n) 求n的双曲正切值
select tanh(-1) a1,tanh(1) a2,tanh(0.5) a3,tanh(-0.5) a4 from dual;
25、TRUNC(n1,n2)将数字截取到小数点n2位置
select trunc(1.2345,2) a1,trunc(1.85) a2 from dual;
26、WIDTH_BUCKET(expr,min_val,max_val,num)
将范围[min_val,max_val]平均分成num份,
如果expr<=min_val,返回0
如果expr在第一份,返回0;在第二份返回2
如果expr>=max_val,则返回num+1
select WIDTH_BUCKET(tt,500,1000,5) a1
from (
select500 tt from dual unionallselect700 tt from dual unionallselect1000 tt from dual
) tb_tmp