Oracle的decode、sign、trunc函数

原文http://knowyouknowme.iteye.com/blog/574974

一、decode

 

 

在Oracle/PLSQL中,  decode 具有和 IF-THEN-ELSE 一样的功能。

decode 函数语法如下:

decode( expression , search , result [, search , result]... [, default] )

expression 要比较的表达式.

 

search 要与expression 比较的字段。.

 

result 如果expression search 一样的话,返回该结果。.

default 此参数可选,如果没有与expression 匹配上的search . 就返回此结果,如果此参数没有设置,当没有与expression匹配上的search时,返回null。

search 和 result可成对出现多次,代表各种要匹配的情况。 
 

应用于

  • Oracle 9i, Oracle 10g, Oracle 11g

例如:

You could use the decode function in an SQL statement as follows:

 

select supplier_name,decode(supplier_id,1000,‘IBM‘,10001,‘Microsoft‘,‘1002‘,‘Hewlett Packard‘,‘Gateway‘) result from suppliers;

 

上面的sql语句相当于下面的IF-THEN-ELSE :

IF supplier_id = 10000 THEN
     result := ‘IBM‘;

ELSIF supplier_id = 10001 THEN
    result := ‘Microsoft‘;

ELSIF supplier_id = 10002 THEN
    result := ‘Hewlett Packard‘;

ELSE
    result := ‘Gateway‘;

END IF;


 decode 函数会挨个匹配supplier_id 的值.


 

常见问题:

 


 

 

问题1:  现在一个阅读者想问,怎么使用decode函数来比较两个日期呢?(例如:date1 和 date2), 如果date1 > date2, decode 函数返回date2. 否则decode函数返回 date1.

回答:  要实现上述要求,可使用decode函数如下:

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

如果date1大于date2,下面表达是会等于0:

(date1 - date2) - abs(date1 - date2)

帮助性提示: 可用decode函数绑定SIGN 函数 像下面这样:

上面比较日期的语句可修改如下:

DECODE(SIGN(date1-date2), 1, date2, date1)

SIGN/DECODE 联合对于有关销售红利等数字方面的比较是非常有用的。

DECODE(SIGN(actual-target), -1, ‘NO Bonus for you‘, 0,‘Just made it‘, 1, ‘Congrats, you are a winner‘)

 


 

 

问题2:  我想知道是否可以用decode函数来确定数字范围,例如 1-10 = ‘category 1‘, 11-20 = ‘category 2‘, 比一个一个比较应该会好一点吧.

回答: 不幸的告诉你,不可以用decode函数来确定数字的范围. 可是你可以试着创建一个表达式,这个表达式可以得一个数字指定的范围, 下一个数字对应下一个指定的范围, 以此类推.

例如:

select supplier_id,decode(trunc((supplier_id-1)/10),0,‘category 1‘,1,‘category 2‘,2,‘category 3‘,‘unknown‘) result from suppliers;

 

这个例子基于这个公式:trunc ((supplier_id - 1) / 10

 

如果supplier_id在1和10之间,表达式计算值=0.

如果supplier_id在11和20之间,表达式计算值=1.

 

如果supplier_id在21和30之间,表达式计算值=3.

 

 

 

 

等等;


 

 

问题3:  我想写一个decode函数,要求如下:

如果 yrs_of_service < 1 返回 0.04
如果 yrs_of_service >= 1 and < 5 返回0.04
如果 yrs_of_service > 5 返回 0.06

这种情况我该怎么做呢?

回答:  You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

select emp_name,decode(trunc((yrs_of_service+3)/4),0,0.04,1,0.04,0.06) as perc_value from employees;

 


 

 

问题4:  decode函数的参数个数有限制吗?我得到一个错误 "ORA-00939: too many arguments for function".

回答:  是的,decode函数的最大参数个数为255个,包括expression, search, and result arguments.<!-- InstanceEndEditable -->

 

 

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

 

 

二、sign

在Oracle/PLSQL中, sign 函数返回一个数字的正负标志.

 

语法如下:sign( number )

 

        number 要测试标志的数字.

If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.

应用于:

  • Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g 

例如:

      

sign(-23) would return -1
sign(0.001) would return -1
sign(0) would return 0
sign(0.001) would return 1
sign(23) would return 1
sig(23.601)

would return 1

 

 

 

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

三、trunc(number)

在Oracle/PLSQL中, trunc function returns a number truncated to a certain number of decimal places.

trunc function 语法如下:

trunc( number, [ decimal_places ] )

number 要截取的数字.

decimal_places 要保留的小数位. 这个参数必须是个整数. 如果此参数缺省,默认保留0位小数
 

应用于:

  • Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


 

例如For example:

trunc(125.815) would return 125
trunc(125.815, 0) would return 125
trunc(125.815, 1) would return 125.8
trunc(125.815, 2) would return 125.81
trunc(125.81, 3) would return 125.81
trunc(-125.815, 2) would return -125.81
trunc(125.815, -1) would return 120
trunc(125.815, -2) would return 100
trunc(125.81, -3) would return 0

 

 如果decimal_places 大于number 本身的小数位数,返回原数字不会加0.

如:trunc(125.81,3) would return 125.81;

如果decimal_places 为负数,那么将指定的位数

Oracle的decode、sign、trunc函数,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。