oracle both from,oracle中的函数（一）

oracle中的函数这些函数可以用在select where having  order by这些关键词后，

起着对某个常量或者某列数据进行相应的转化后在进行使用

例如

select ename,sal,round(sal,-2) from emp  where round(sal,-2)>=3000;

通过使用这些函数，可以帮助我们实现满足一些特殊要求的查询数学函数

数学函数

round：使用格式为  round(n[,m])其中[] 中的内容可以省略select round(123.456),round(123.456,1),round(123.456,2),round(123.456,-1) from dual;

结果为

ROUND(123.456) ROUND(123.456,1) ROUND(123.456,2) ROUND(123.456,-1)

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

123            123.5           123.46               120

后面的参数指定了返回值的精度，或者说是从小数点第几位开始四舍五入，

如果不带后面的参数，则默认为四舍五入取整数，该例子中采用的是常量，

from后用的是dual，在上一篇文章中介绍了dual这个表，这个表只有一行数据，

上面的sql只有一行需要返回，返回的内容由select关键字决定，在实际应用中，参数可以是一个列名，例如

select round(sal,-2) ,ename from emp;

ceil：使用格式ceil(n)select ceil(10.9),ceil(10.123) from dual;

结果为

CEIL(10.9) CEIL(10.123)

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

11           11

ceil函数的作用是将n的小数部分舍掉，整数部分加1

floor:格式floor(n)select floor(10.9),floor(10.123) from dual;

结果为

FLOOR(10.9) FLOOR(10.123)

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

10            10

与ceil不同的是该函数是将n的小数部分舍掉，整数部分不变。

在英文中ceil是天花板的意思，floor是地板的意思，

在oracle的函数中，ceil是"向上"取整数，而floor是"向下"取整数，这应该跟英文意思多少有点关系吧

abs：格式abs(n)select abs(-1),abs(0),abs(1) from dual;

结果为

ABS(-1)     ABS(0)     ABS(1)

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

1          0          1

abs函数的作用是取得n的绝对值

sign:格式sign(n)select sign(0),sign(100),sign(-100) from dual;

结果是

SIGN(0)  SIGN(100) SIGN(-100)

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

0          1         -1

sign函数的规则是n≥0，sign(n)=1; 当n<0， sign(n)=-1

sqrt:格式sqrt(n)select sqrt(4),sqrt(9.9) from dual;

结果是

SQRT(4)  SQRT(9.9)

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

2 3.14642654

sqrt的作用是取得n的平方根

exp：格式exp(n)

select exp(0) ,exp(1) from dual;

结果是

EXP(0)     EXP(1)

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

1 2.71828183

exp函数的作用是求e的n次幂的值

ln和log：格式ln(n)   log(m,n)select ln(1),log(10,100) from dual;

结果是

LN(1) LOG(10,100)

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

0           2

这两个函数中ln为自然对数，log(m,n)是以m为底求n的对数

power:格式power(m,n)select power(3,2),power(3,3) from dual;

结果是

POWER(3,2) POWER(3,3)

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

9         27

该函数的作用是求m的n次幂

mod:格式mod(m,n)

select mod(10,6),mod(1,2) from dual;

结果是

MOD(10,6)   MOD(1,2)

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

4          1

该函数的作用是m/n的余数

出了上面的函数oracle还提供了sin(n)  cos(n)  tan(n)

asin(n)  acos(n)  atan(n)sinh  cosh tanh这些三角函数，功能和数学中的一样，用法与上面所提到的方法基本一样

文本函数

oracle中的文本操作函数或者说是字符串操作函数如下

length(s)

ascii(s)

chr(n)

upper(s)

lower(s)

initcap(s)

ltrim(s[,k]

rtrim(s[,k])

trim([[option][c from ]]s)

lpad(s,n[,k])

rpad(s,n[,k])

substr(s,n[,m])

instr(s,k)

instr(s,k,n)

instr(s,k,n,m)

translate(s,v,m)

replace(s,v)

resplace(s,v,w)

concat(s1,s2)

length(s)select ename ,length(ename) from emp;

该函数的作用是返回字符串参数s的长度

ascii(s)select ename ,ascii(ename) from emp;

该函数的作用是返回字符串参数s的第一个字符对应的ascii码值

chr(n)select chr(65),chr(97) from dual;

结果

C C

- -

A a

该函数的作用是返回在ascii码中参数n对应的字符

upper(s)和lower(s)select ename,upper(ename),lower(ename) from emp;

这两个函数的作用分别是将参数s全部转换成大写和小写字母

initcap(s)

select ename,initcap(ename) from emp;

该函数的作用是将参数s的第一个字符转换成大写

ltrim(s[,k]和rtrim(s[,k])select ename,ltrim(ename,'K'),rtrim(ename,'K') from emp;

ltri(s,k)的作用：首先取得参数s的第一个字符，判断是否为字符k，如果是则

将k剪掉，再取第二个字符判断是否是k，如果是则剪掉继续判断，如果不是则结束判断

返回结果，如果不带参数k那么默认是去掉参数s左边的空格

rtrim(s[,k])是从参数s的右边开始判断

trim([[option][c from ]]s)  option=leading ,trailing,bothselect

'  aabb  '  ,

trim('  aabb  '),

trim(both from '  aabb  '),

trim(leading from '  aabb  '),

trim(trailing from '  aabb  '),

trim(leading 'a' from 'aabbaa'),

trim(trailing 'a' from 'aabbaa'),

trim(both 'a' from 'aabbaa') from dual;

结果是

'AABB'   TRIM TRIM TRIM(L TRIM(T TRIM TRI TR

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

aabb   aabb aabb aabb     aabb bbaa aab bb

trim(s)等价于trim(both from s)去掉两边的空格

trim(leading from s)去掉参数s前面的空格

trim(trailing from s)去掉参数s后面的空格

trim(leading 'a' from s)去掉参数s前面的a

trim(trailing 'a' from s)去掉参数s后面的a

trim(both 'a' from s)去掉参数s两边的a

lpad(s,n[,k])和rpad(s,n[,k])select ename,lpad(ename,10,'*'),lpad(ename,2),rpad(ename,10,'*'),rpad(ename,3) from emp;

lpad(s,n[,k])的作用是将字符串s的长度变成n，如果s长度小于n则在左边用参数k补充，如果s长度

大于n，则从左边截取n个字符返回

rpad(s,n[,k])的作用是与lpad类似，如果s长度小于n则在右边用参数k补充，如果s长度

大于n，则从右边截取n个字符返回

substr(s,n[,m])select ename,substr(ename,2,3) from emp;

该函数的作用是从参数s的第n个字符开始截取，长度为m，如果不带参数m，默认截取到s的最后

instr(s,k)  instr(s,k,n)  instr(s,k,n,m)select ename,instr(ename,'A'),instr(ename,'A',3),instr(ename,'A',4),instr(ename,'A',3,2) from emp;

instr(s,k)的作用是字符串或者字符k在s中第一次出现的位置，

instr(s,k,n)的作用是从第n个位置开始，符串或者字符k在s中第一次出现的位置

instr(s,k,n,m)的作用是从第n个位置开始，符串或者字符k在s中第m次出现的位置

translate(s,v,m)select ename,translate(ename,'A','B') from emp;

该函数的作用是用符串或者字符m替换符串或者字符n

replace(s,v)select ename ,replace(ename,'AR'),replace(ename,'A')  from emp;

该函数的作用是移除字符串s中的符串或者字符v

replace(t,v,w)select ename,replace(ename,'AR','aaaaaaa') from emp;

用字符串w替换字符串v

concat(s1,s2)select ename,job,concat(ename,job),ename||job  from emp;

该函数的作用是连接两个字符串,作用相当于||