oracle中的数值函数

来源:互联网 发布:csgo淘宝钥匙 编辑:程序博客网 时间:2024/05/24 06:33

对Oracle的数值函数多知道一些还是有好处的,做商业报表类的项目的时候会经常用到。 起码要知道oracle都给我们提供了哪些处理函数,不要到时候需要用的时候却不知道某个函数的存在而导致开发过程绕弯子。今天我就捧着一本《oracle database11g完全参考手册》将oracle提供给我们的数值函数从头捋了一遍。

主要分为三块介绍(单值函数、聚合函数、列表函数)

一、单值函数(比较简单,看一遍基本也就理解记住了)

1、基本加减乘车没有什么可说的,只需要注意一点,任何值与null一起运算 ,结果都为null,因为null代表着未知值,与null进行加减乘除运算得到的还是未知值,返回的结果还是null。
select  3+null ,3-null,3*null,3/null  from  dual  --执行结果都是null

2、nvl和nvl2 空值置换函数

以前单独写过 ,这里不说了

3、abs 绝对值函数

select abs(2.1),,abs(-2.1)  from  dual--执行结果都是2.1

4、ceil 、 floor  这两个也比较简单啦
ceil   只入不舍函数
floor  只舍不入函数
select ceil(-2.6),ceil(2.6), floor(-2.6),floor(2.6)  from  dual
执行结果依次为        -2        3       -3       2 
5、round 和 trunc 又是一对有关系的函数
round   按照给定精度进行舍入    两个参数,第一个代表要处理的数值,第二个代表精度,第二个参数可以省略,省略的时候默认值为0
trunc   按照给定精度截取数字    两个参数,第一个代表要处理的数值,第二个代表精度,第二个参数可以省略,省略的时候默认值为0
select round(2.615),round(-2.615),round(2.615,0),round(-2.615,0) ,round(2.615 ,1),round(-2.615,1),round(2.615 ,2),round(-2.615,2)  from  dual
执行结果依次为 3 -3 3 -32.6-2.62.62-2.62
select trunc(2.615),trunc(-2.615),trunc(2.615,0),trunc(-2.615,0) ,trunc(2.615 ,1),trunc(-2.615,1),trunc(2.615 ,2),trunc(-2.615,2)  from  dual
执行结果依次为1 2 -2 2 -22.6-2.62.61-2.61
注意 :  round 和 trunc 这两个函数的第二个参数可以为负值,就是从小数点开始往左数
select round(248.1,-1),round(-248.1,-1),round(248.1,-2),round(-248.1,-2)  from  dual
执行结果依次为 250 -250 200 -200
select trunc(248.1,-1),trunc(-248.1,-1),trunc(248.1,-2),trunc(-248.1,-2)  from  dual
执行结果依次为 240 -240 200 -200
6、mod 取余函数   
select mod(100,5),mod(22,23),mod(10,4),mod(-10,4),mod(-21.12,7),mod(100,0),mod(100,-21),mod(0,2)  from  dual

执行结果依次为 0 22  2  -2   -0.12 10016   0
需要注意的是数值为负值和0的情况
根据  mod(value,1) 是否等于0可以判断value值是否为整数。
7、power  和 sqrt   分别是求平方和求平方根的意思
select  power(3,2),power(2,-2),SQRT(9),sqrt(0)  from dual   
执行结果依次为 9 0.25 3 0
需要注意的地方:
sqrt是求平方根的,它就一个参数,而且必须是非负数。
oracle中 0.5可以省略成.5   今天看到.5这个字样的时候着实纠结了一会
8、sign  判断一个数值是正数  还是负数 还是0     正数返回1负数返回-1  0返回0   这个还是比较常用的
select  sign(2.1),sign(0),sign(-2.1)  from  dual
执行结果依次为 1  0  -1
9、其余的一些幂运算 、 三角函数运算的函数就不介绍了,平时的商业开发很少用到,科技工作中会比较常用吧。
二、聚集函数

我们需要一个表来演示相关的例子了,建一个表table_a(代表不同城市早、中、晚的气温数据,数据的合理性就先不要管了)

nametimenum_morningnum_nooningnum_night哈尔滨2013-12-1421.0
11.0哈尔滨2013-12-1522.028.035.0哈尔滨2013-12-16


哈尔滨2013-12-17
36.022.0哈尔滨2013-12-1823.071.011.0北  京2013-12-1433.033.030.0北  京2013-12-1525.027.0
北  京2013-12-1631.031.012.0北  京2013-12-1744.025.031.0北  京2013-12-1824.025.023.0绥   化2013-12-1419.018.021.0绥   化2013-12-1524.024.021.0绥   化2013-12-1625.025.024.0绥   化2013-12-1724.024.031.0绥   化2013-12-1821.019.012.01、聚集函数中对于null的态度是 忽略

举个例子看看: 我们查询一下各个城市最近5天每天早上的平均气温

select name ,avg(num_morning)  from  table_a  group by name     执行的结果是22   说明它忽略了两天为空的记录,根据三天计算的平均值

执行结果:

nameavg(num_morning)绥   化22.6哈尔滨22北  京31.4

再举个例子:表中有几条哈尔滨早上的气温纪录

select count(num_morning)  from table_a where name ='哈尔滨';  执行结果是3

注意:count(num_morning)可以忽略num_morning为null的数据使用count(*)就不能忽略null了  即时所有字段都为null,count(*)也不会忽略这行(起码还有rowid)

2、常用的聚合函数  max min  sum  avg  count    平时很常用了  举个例子不多说了。

(1)、查询各个城市白天的最高气温、最低气温、总和 、统计天数、平均气温

select  name, max(num_morning) 最高气温,min(num_morning) 最低气温,sum(num_morning) 总和,count(num_morning) 统计天数,avg(num_morning) 平均气温  from table_a group by  name

执行结果:

name最高气温最低气温总和统计天数平均气温绥    化2519113522.6哈尔滨232166322北    京4424157531.4

3、stddev(标准差函数)   variance(标准方差函数)   两者格式与上面的max sum等一致。

4、聚合函数中的 distinct 

所有的聚合函数都有一个distinct与all选项。  

distinct 表示该列有相同的值的时候只取一列。举个例子说明下:

select  count(distinct name),count(name),count(*),count(all name )  from  table_a 

执行结果:  3     15        15       15

三、列表函数

聚合函数与列表函数的区别:聚合函数是作用在多行的同一列上,组合函数是作用在一行的多个列上,很明显了吧!不明显的话下面有对应的例子,看完保证明显。

1、greatest(取多个数值的最大值)  least(取多个数值的最小值)

例子:  取出  1  ,3,2,1,5,4 几个数中的最大值和最小值

select greatest(1,3,2,1,5,4),least(1,3,2,1,5,4)   from   dual      返回值   5       1

可以对数值进行比较,对字符串进行比较

看完上面的例子再让我们求各个城市每天的最高气温 、最低气温是多少就很简单了,不写了

注意:这两个函数对null的态度跟单值函数一样,当成未知,结果都会返回null

2、coalesce   参数是一串值,返回是第一个出现的不为空的值,如果全都为空 那么返回null

select coalesce(null,'','',null,'1',null)  from dual       返回 1

注意:各个参数的类型必须一致。

四、运算优先级

1、符号优先级:圆括号优先级最高、其次是乘除法、再其次是加减法、当优先级相同的时候是从左向右执行。

2、and 和 or的优先级比较:  and  优先级 高于 or   

举个例子:查询早上气温大于中午并且早上气温大于晚上   或者 中午气温大于晚上的数据

我有两种写法:

select *  from table_a  where num_morning>num_nooning and num_morning>num_night          or  num_nooning>num_night;

select *  from table_a  where num_nooning>num_night          or  num_morning>num_nooning and num_morning>num_night  

经测试两个sql返回结果都一样:

哈尔滨2013-12-17     36.022.0
2 哈尔滨 2013-12-18 23.071.011.0
3 北  京 2013-12-14 33.033.030.0
4 北  京 2013-12-16 31.031.012.0
5 北  京 2013-12-17 44.025.031.0
6 北  京 2013-12-18 24.025.023.0
7 绥  化 2013-12-15 24.024.021.0
8 绥  化 2013-12-16 25.025.024.0
9 绥  化 2013-12-18 21.019.012.0

例子结果充分说明了and的优先级要大于or   优先级要是怕弄错的话就加括号强制先后执行就不怕了。

转载自:http://blog.csdn.net/caohaicheng/article/details/17404219

0 0
原创粉丝点击