Oracle数据库的decode、sign、trunc函数使用详解

Oracle数据库的函数有很多，本文我们主要介绍一下decode、sign、trunc函数的使用，接下来就让我们一起来了解一下这部分内容。

一、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.

二、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 为负数，那么将指定的位数

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

关于Oracle数据库的decode、sign、trunc函数的使用就介绍到这里了，希望本次的介绍能够对您有所收获！

【编辑推荐】