ORACLE的sign函数和DECODE函数 decode与case的区别

decode与case的区别
  如果条件都是“=”，用decode方便，如果涉及其它运算符，则cse更方便。
  在使用Oracle10g时发现，decode中的项目不能超过250个。
  如将系统1的地市编码转化为系统2的地市编码，数目可能会超过250个。

·DECODE函数的作用：它可以将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。
·区别于SQL的其它函数，DECODE函数还能识别和操作空值。
　　语法如下：
　　DECODE(control_value,value1,result1[,value2,result2…][,default_result]);
　　control _value 试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。
　　value1 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
　　result1 是一组成序偶的结果值。
　　default_result 未能与任何一个值匹配时，函数返回的默认值。
　　示例如下：
　　select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual
　　当x等于1时，则返回‘x is 1’。
　　当x等于2时，则返回‘x is 2’。
　　否则，返回others’。
　　在需要比较2个值的时候，我们可以配合SIGN()函数一起使用。
　　SELECT DECODE( SIGN(5 -6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero')
　　同样，也可以用CASE实现：
　　SELECT CASE SIGN(5 - 6)
　　WHEN 1 THEN 'Is Positive'
　　WHEN　-1 THEN 'Is Nagative'
　　ELSE　'Is Zero' END
　　FROM DUAL

 

 

比较大小函数 sign

函数语法：
sign(n)

函数说明：
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

示例：
一、select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

　　SIGN(100) SIGN(-100) SIGN(0)
　　———- ———- ———-
　　1 -1 0

二、a=10，b=20 
　　则sign(a-b)返回-1

 

流程控制函数 DECODE 

函数介绍　　

DECODE函数是ORACLE PL/SQL的功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其它数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢？先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的加20％；工资在8000元或以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),>=0,salary*1.15,<0,salary*1.2,salary) from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

关于DECODE

　　DECODE是Oracle公司独家提供的功能，它是一个功能很强的函数。它虽然不是SQL的标准，但对于性能非常有用。到目前，其他的数据库供应商还不能提供类似DECODE的功能，甚至有的数据库的供应商批评Oracle的SQL不标准。实际上，这种批评有些片面或不够水平。就象有些马车制造商抱怨亨利。福特的“马车”不标准一样。

1 DECODE 中的if-then-else逻辑

　　在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：

 

　　DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

 

　　Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。

 

　　需要注意的是，这里的if、then及else 都可以是函数或计算表达式。

2 DECODE 的简单例子

　　Oracle系统中就有许多数据字典是使用decode 思想设计的，比如记录会话信息的V$SESSION数据字典视图就是这样。我们从《Oracle8i/9i Reference》资料中了解到，当用户登录成功后在V$SESSION中就有该用户的相应记录，但用户所进行的命令操作在该视图中只记录命令的代码（0—没有任何操作，2—Insert…），而不是具体的命令关键字。因此，我们需要了解当前各个用户的名字及他们所进行的操作时，要用下面命令才能得到详细的结果：

 

　　select sid,serial#,username,

 

　　DECODE(command,

 

　　0,’None’,

 

　　2,’Insert’,

 

　　3,’Select’,

 

　　6,’Update’,

 

　　7,’Delete’,

 

　　8,’Drop’,

 

　　‘Other’) cmmand

 

　　from v$session where username is not null;

3 DECODE实现表的转置

　　数据库中的表是由列和行构成的一个二维表。一般列在任何数据库中都是有限的数量，而行的变化较大，如果表很大，行的数量可能大上千万行。同一列的不同行可能有不同的值，而且不是预先定义的。

 

　　例:住房公积金报表置换实例：

 

　　1.各个单位在本地经办行进行开户，开户就是将单位的基本信息和职工信息的进行登记；

 

　　2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金，系统记录有每个职工的交缴明细并在每条记录上记录有经办行的代码；

 

　　3.每月、季、半年及年终都要求将经办行 变为“列”给出个月的明细报表：

 

　　经办行：城西区 城东区

 

　　月份：

 

　　2001.01 xxxx1.xx xxxxx2.xx

 

　　2001.02 xxxx3.xx xxxxx4.xx

 

　　。 。 。 。 。 。

 

　　原来的数据顺序是：

 

　　城西区2001.01 xxxxx1.xx

 

　　城东区2001.01 xxxxx2.xx

 

　　城西区2001.02 xxxxx3.xx

 

　　城东区2001.02 xxxxx4.xx

 

　　住房公积金系统记录职工的每月交缴名细的pay_lst表结构是：

 

　　bank_code varchar2(6)NOT NULL, -- 经办行代码

 

　　acc_no varchar2(15) not null, -- 单位代码(单位帐号)

 

　　emp_acc_no varchar2(20) not null, -- 职工帐号

 

　　tran_date date not null, -- 交缴日期

 

　　tran_val Number(7,2) not null, -- 交缴额

 

　　sys_date date default sysdate, --系统日期

 

　　oper_id varchar2(10) --操作员代码

 

　　这样的表结构，一般按照将经办行作为行(row)进行统计是很容易的，但是如果希望将经办行变为列(column)这样的格式来输出就有困难。如果用DECODE函数来处理则变得很简单：

 

　　我们创建一个视图来对目前的pay_lst表进行查询。将经办行代码变为一些具体的经办行名称即可：

 

　　CREATE OR REPLACE VIEW bank_date_lst AS

 

　　Select to_char(tran_date,’yyyy.mm’),

 

　　SUM( DECODE ( bank_code,’001’, tran_val,0 )) 城西区，

 

　　SUM( DECODE ( bank_code,’002’, tran_val,0 )) 城南区，

 

　　SUM( DECODE ( bank_code,’003’, tran_val,0 )) 城东区

 

　　FROM pay_lst

 

　　GROUP BY to_char(tran_date,’yyyy.mm’);

 

　　建立视图后，可直接对该视图进行查询就可按照列显示出结果。