oracle sql 单行函数
函数是SQL的一个非常强有力的特性，函数能够用于：执行数据计算、修改单个数据项、操纵输出进行行分组、格式化显示的日期和数字、转换列数据类型。SQL函数分为单行函数和多行函数。
单行函数：这些函数仅对单个行进行运算，并且每行返回一个结果，其中单行函数还有很多种类型，比如：字符、数字、日期、转换
多行函数：这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。
单行函数的特点：只对一行值进行操作，如有多行值，将分别对多行值进行操作，而不会将多行值作为一个整体进行操作。
语法格式：function_name(arg1,arg2,...)
function_name---是函数的名字
arg1,arg2---是由函数使用的任意参数，可以由一个列名或者一个表达式提供
常用的单行函数有：
字符函数
数字函数
日期函数
转换函数
通用函数
字符函数：
字符函数包括：大小写处理函数、字符处理函数
大小写处理函数：LOWER UPPER INITCAP
字符处理函数：CONCAT SUBSTR LENGTH INSTR LPAD|RPAD TRIM REPLACE
LOWER：将指定字符串内字符变为小写 如：select lower('what is this') from dual;
UPPER：将指定字符串内字符变为大写 如：select upper('what is this') from dual;
INITCAP：将字符串中单词的第一个字母转换为大写，其它则转换为小写
如：select initcap('what is this') from dual;
CONCAT：连接字符串，等同于|| 如：
SQL> select concat('aa','bb') from dual;
CONC
----
aabb
SUBSTR：截取指定长度的字符串。
n1---开始长度
n2---截取的字符串长度，如果为空，默认截取到字符串结尾
注意：如果n1为0的时候，则从第一个字符算起。
同时当n1>0的时候，oracle默认是从左向右确认起始位置进行截取。如：
SQL> select substr('what is this',5,3) from dual;
SUB
---
is
但是如果n1<0的时候，oracle默认是从右向左确认起始位置进行截取。如：
SQL> select substr('what is this',-6,3) from dual;
SUB
---
s t
当n1>c1.length的时候则返回为空。
LENGTH：返回指定字符串的长度。如：
SQL> select length('1234567') from dual;
LENGTH('1234567')
-----------------
7
INSTR：至于INSTR函数的作用看如下命令
SQL> select instr('abcdefg','e',-3) from dual;
INSTR('ABCDEFG','E',-3)
-----------------------
5
格式：instr(c1,c2[,n1[,n2]])
作用：返回c2在c1中的位置。其中c1是原字符串，c2是要寻找的字符串，n1是查询起始位置（正值表示从
左到右、负值表示从右到左。而大小表示位置，比如3表示左边第3处开始；-3表示右边第三处开始），如
果为0，则返回的也是0。n2是第几个匹配项的意思。
LPAD：返回指定长度等于n的字符串，这里有几个注意点：
nn>c1.length并且c2 is null则以空格从左向右补充字符长度至n并返回
n>c1.length并且c2 is not null则以指定字符c2从左向右补充c1长度至n并返回 如：
SQL> select lpad('what is this',5),lpad('what is this',25),lpad('what is this',25,'-') from
dual;
LPAD( LPAD('WHATISTHIS',25) LPAD('WHATISTHIS',25,'-')
----- ------------------------- -------------------------
what what is this -------------what is this
RPAD：返回指定长度等于n的字符串，基本与上面的LPAD相同，只是补充字符是从右向左，方向正好与
LPAD相反
数字函数：
数字函数包括：ROUND、TRUNC、MOD
ROUND：四舍五入函数
TRUNC：截断函数，不进行四舍五入
MOD：求余函数
对于这三类数字函数的用法我用三个例子来说明一下：
ROUND函数：
SQL> select round(23.56),round(23.56, 1),round(23.56, -1) from dual;
ROUND(23.56) ROUND(23.56,1) ROUND(23.56,-1)
------------ -------------- ---------------
24 23.6 20
TRUNC函数：
SQL> select trunc(23.56),trunc(23.56, 1),trunc(23.56, -1) from dual;
TRUNC(23.56) TRUNC(23.56,1) TRUNC(23.56,-1)
------------ -------------- ---------------
23 23.5 20
TRUNC(n1[,n2])返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
MOD函数：
SQL> select mod(24,5) from dual;
MOD(24,5)
----------
4
MOD(n1,n2)函数返回n1除n2的余数，而如果n2=0则返回n1的值
日期函数
oracle数据库以内部数字格式存储日期，表示世纪、年、月、日、小时、分和秒
世纪信息可从sysdate函数中获得，但是默认情况下日期字段中的世纪部分是不显示在屏幕上的
data数据类型总是以4位内部数字存储年信息：两位数字代表世纪、两位数字代表年
系统日期
current_date用来返回当前session所在时区的默认时间
SQL> select current_date from dual;
CURRENT_DATE
-----------------
20100514 11:10:49
SQL> alter session set nls_date_format = 'mm-dd-yyyy';
Session altered.
SQL> select current_date from dual;
CURRENT_DA
----------
05-14-2010
current_date和sysdate是一样的，但是如果同时用current_date和sysdate时，可能会有点差别。
SQL> select sysdate from dual;
SYSDATE
----------
05-14-2010
用日期计算
运算 结果 说明
date+number 日期 加上一个天数到一个日期
date-number 日期 从一个日期上减一个天数
date-date 天数 用一个日期减去另一个日期
date+number/24 日期 加上一个小时到一个日期上
日期函数
months_between用来返回两个月份之间的月份差
SQL> select months_between(sysdate,sysdate) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE)
-------------------------------
0
SQL> select months_between(sysdate,add_months(sysdate,3)) from dual;
MONTHS_BETWEEN(SYSDATE,ADD_MONTHS(SYSDATE,3))
---------------------------------------------
-3
add_months返回指定日期月份+n之后的值，n可以为任何整数
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDAT
-----------------
20100614 16:19:03
SQL> select add_months(sysdate,-2) from dual;
ADD_MONTHS(SYSDAT
-----------------
20100314 16:22:35
next_day用来指定n2天后的第一天
SQL> select next_day(sysdate,5) from dual;
NEXT_DAY(SYSDATE,
-----------------
20100520 16:25:14
SQL> select next_day(sysdate,'sunday') from dual;
NEXT_DAY(SYSDATE,
-----------------
20100516 16:29:22
注意：如果是用星期字符，则它的兴起形式需要与当前session默认时区中的星期形式相同
last_day用来指定时间所在月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
20100531 16:36:16
转换函数
在某些情况下，oracle服务器使用一种数据类型的数据，但是在一些特定情况下我们希望使用一种不同数据类型的数据，如果这种情况发生，oracle服务器自动转换数据为期望的数据类型。这种数据类型的转换可以被oracle服务器隐式进行，或由用户显式进行。
隐式数据类型转换
隐式数据类型转换用转换函数进行。转换函数使其从一种数据类型转换到另一种数据类型。且第一个数据类型为输入数据类型，第二个数据类型为输出数据类型。不建议用它！
显示数据类型转换
SQL提供了三种函数来从一种数据类型转换到另一种数据类型：
to_char
to_number
to_date
其中to_char函数又可以分为三小类：
转换字符--->字符(将nchar,nvarchar2,clob,nclob类型转换成char类型)
SQL> select to_char('AABBCC') from dual;
TO_CHA
------
AABBCC
转换时间--->字符(将data,timestamp,timestamp with time zone转换成varchar2)
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2010-05-14 17:12:29
转换数值--->字符(将制定数值转换为varchar2类型并返回)
SQL> select to_char(-100,'l99g999d99mi') from dual;
TO_CHAR(-100,'L99G99
--------------------
$100.00-
to_date将char,nchar,varchar2,nvarchar2转换为日期类型
SQL> select to_date(2454336,'j') from dual;
TO_DATE(2454336,'
-----------------
20070823 00:00:00
SQL> select to_date('2010-5-14 17:19:00','yyyy-mm-dd hh24:mi:ss') from dual;
TO_DATE('2010-5-1
-----------------
20100514 17:19:00
to_number将char,nchar,varchar2,nvarchar2型字串转换为数值类型并返回
SQL> select to_number('-100.00','9g999d99') from dual;
TO_NUMBER('-100.00','9G999D99')
-------------------------------
-100
嵌套函数
单行函数能够被嵌套任意层次，嵌套函数的计算式从最里层到最外层的。
看下面的例子：
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDAT
-----------------
20100614 16:19:03
当然我们还可以继续嵌套，这里只需要有这样的灵活性即可！！！！！
通用函数
通用函数并不针对某种具体的数据类型，它们可以对几乎所有的数据类型进行操作，因此被称为通用函数！
常用的通用函数有：
nvl、nvl2
nullif
coalesce
nvl：如果字符1为null，则oracle将字符2自动转换成字符1的类型
SQL> select nvl(null,'12') from dual;
NV
--
12
nvl2：如果字符1非空则返回字符2，如果字符1为空则返回字符3
SQL> select nvl2('a','b','c'),nvl2(null,'b','c') from dual;
N N
- -
b c
nullif:当字符1=字符2的时候返回空值，当字符1不等于字符2时返回字符1
SQL> select nullif('a','b'),nullif('a','a') from dual;
N N
- -
a
coalesce用于返回序列中的第一个非空值
SQL> select coalesce(null,5,6,null,9) from dual;
COALESCE(NULL,5,6,NULL,9)
-------------------------
5
