Oracle常用函数部分详解（decode,nvl）

① decode()函数简介：

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）From talbename  Where …

·含义解释：

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：

if （条件==值1）

 then　　　　

return(翻译值1)

elsif （条件==值2）

then　　　　

return(翻译值2)　　　　

......

elsif （条件==值n）

 then　　　　

return(翻译值n)

else　　　　

return(缺省值)

end if

注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；

举例说明：

现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other；

SQL如下：

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output

特殊情况：

若只与一个值进行比较

Select monthid ,decode（sale， NULL，‘---’，sale） sale from output

另：decode中可使用其他函数，如nvl函数或sign（）函数等；

NVL(EXPR1,EXPR2)

若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.

SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;

如果用到decode函数中就是

select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output

 

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，

如果取较小值就是

select monthid,decode(sign(sale-6000),-1,sale,6000) from output，即达到取较小值的目的。

  ②nvl()函数简介  NVL(x1,x2)

在如何理解NULL：

NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数   为NULL值，结果均为提个NULL值,这个规则也适合很多函数，

只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。

在这些中NVL函数时最重要的，因为他能直接处理NULL值，

NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。

下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿

column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2

不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：

update empset salary=(salary+bonus)*1.1

这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。

所以正确的语句是：

update empset salary=(salary+nvl(bonus,0)*1.1

③常用方法

[sql] view plain copy

1. select round(10.38764, 3) round from dual;     --四舍五入。10.388    
2. select trunc(10.38764, 3) trunc from dual;       --截断。10.387    
3. select to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') to_char from dual;    --日期转字符。2016-02-26 18:07:35    
4. select to_date('2000-1-1 18:18:18', 'yyyy-MM-dd hh24:mi:ss') to_date from dual;     --字符转日期。2000/1/1 18:18:18    
5. select lower('ABC') lower from dual;    --大写转小写。abc    
6. select upper('abc') upper from dual;    --小写转大写。ABC    
7. select length('How long is this sentence') length from dual;   --字符长度。25    
8. select last_day(trunc(sysdate)) last_day from dual;    --当月最后一天的日期。2016/2/29    
9. select next_day(sysdate, 7) next_day from dual;    --当前日期的下一个星期六的时间，7代表星期六。2016/2/27 18:07:35    
10. select to_number('12') to_number from dual;    --字符转数字。12    
11. select decode('y', 'y', 'yes', 'n', 'no') decode from dual;    --判断，如果y等于y,返回yes,如果等于n，返回no。yes    
12. select least(2, 8, 1, 3, 9) least from dual;    --最小数字。1    
13. select greatest(2, 8, 1, 3, 9) greatest from dual;   --最大数字。9    
14. select nvl('1', '2') nvl from dual;    --第一个参数不为null，返回第一个值，否侧返回第二个值。1    
15. select concat('con', 'nect') concat from dual;    --连接字符串。connect    
16. select abs(-6) abs from dual;   --绝对值。6    
17. select cos(1) cos from dual;   --余弦。0.54030230586814    
18. select sin(1) sin from dual;    --正弦。0.841470984807897    
19. select acos(1) acos from dual;   --反余弦。0    
20. select asin(1) asin from dual;   --反正弦。1.5707963267949    
21. select tan(1) tan from dual;   --正切。1.5574077246549    
22. select atan(1) atan from dual;  --反正切。0.785398163397448    
23. select tanh(1) tanh from dual;   --双曲函数。0.761594155955765    
24. select ln(0.7) ln from dual;     --对数。-0.356674943938732    
25. select ceil(9.6) ceil from dual;   --大于9.6最小整数。10    
26. select floor(9.6) floor from dual;     --小于9.6最大整数。9    
27. select sqrt(9) sqrt from dual;     --平方根。3    
28. select replace('a,b,c',',','') replace from dual;   --替换。abc    
29. select replace(wm_concat(a.family_name),',','') wm_concat from  (select * from  (select '张' family_name from dual    
30. union select '三丰' first_name from dual) t order by nlssort(t.family_name,'NLS_SORT=SCHINESE_PINYIN_M') desc) a;       --字段连接（附带replace用法，汉字排序，union用法）。张三丰    
31. select ltrim('  abc') ltrim from dual;     --去掉左空格    
32. select rtrim('abc   ') rtrim from dual;    --去掉右空格    
33. select substr('abcdef',3,2) from dual;       --截取第n个字符开始，长度为m的字符串。cd    
34. select add_months(trunc(sysdate),1) from dual;  -- 下一个月的日期。如果是-1，上一个月日期   

④其他方法

单行日期函数

单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。

ADD_MONTHS(,＜i＞)

返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。

LAST_DAY()

函数返回包含日期d的月份的最后一天

MONTHS_BETWEEN(,)

返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。

NEW_TIME(,,)

d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。

NEXT_DAY(,)

返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。

select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004 ROUND([,])

将日期d按照fmt指定的格式舍入，fmt为字符串。

SYADATE

函数没有参数，返回当前日期和时间。

TRUNC([,])

返回由fmt指定的单位的日期d.

单行转换函数

单行转换函数用于操作多数据类型，在数据类型之间进行转换。

CHARTORWID()

c 使一个字符串，函数将c转换为RWID数据类型。

SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')

CONVERT(,[,])

c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。

HEXTORAW()

x为16进制的字符串，函数将16进制的x转换为RAW数据类型。

RAWTOHEX()

x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。

ROWIDTOCHAR()

函数将ROWID数据类型转换为CHAR数据类型。

TO_CHAR([[,)

x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。

NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

TO_DATE([,[,）

c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。

TO_MULTI_BYTE()

c表示一个字符串，函数将c的担子截字符转换成多字节字符。

TO_NUMBER([,[,)

c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。

TO_SINGLE_BYTE()

将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用

 

其它单行函数

BFILENAME( ,)

dir是一个Directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。

DECODE(,,[,,,[])

x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。

DUMP(,[,[,[,]]])

x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。

EMPTY_BLOB()

该函数没有参数，函数返回一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。

EMPTY_CLOB()

该函数没有参数，函数返回一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。

GREATEST()

exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。

LEAST()

exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。

UID

该函数没有参数，返回唯一标示当前数据库用户的整数。

USER

返回当前用户的用户名

USERENV()

基于opt返回包含当前会话信息。opt的可选值为：

ISDBA　 　　会话中SYSDBA脚色响应，返回TRUE

SESSIONID 　返回审计会话标示符

ENTRYID 　　返回可用的审计项标示符

INSTANCE　　在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有 多个实例的情况下使用。

LANGUAGE　　返回语言、地域、数据库设置的字符集。

LANG　　　　返回语言名称的ISO缩写。

TERMINAL　　为当前会话使用的终端或计算机返回操作系统的标示符。

VSIZE()

x是一个表达式。返回x内部表示的字节数。

SQL中的组函数

组函数也叫集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同的是，在解析时所有的行都是已知的。由于这种差别使组函数与单行函数有在要求和行为上有微小的差异.