单行函数的特征:
1、单行函数对单行操作
2、每行返回一个结果
3、有可能返回值与原参数数据类型不一致(转换函数)
4、单行函数可以写在SELECT、WHERE、ORDER BY子句中
5、有些函数没有参数,有些函数包括一个或多个参数
6、函数可以嵌套
单行函数的分类:
1、字符函数
2、数值函数
3、日期函数
4、转换函数
5、通用函数
6、函数嵌套
一、字符函数
字符函数:主要指参数类型是字符型,不同函数返回值可能是字符型或数字类型。
大小写转换函数:LOWER(列名|表达式):将大写或大小写混合的字符转换成小写
UPPER(列名|表达式) :将小写或大小写混合的字符转换成大写
INITCAP(列名|表达式) :将每个单词的第一个字母转换成大写,其余的字母都转换成小写
字符处理函数:
CONCAT:连接两个值,等同于|| 格式:CONCAT(column1|expression1,column2|expression2)
SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串,如果n1是负值,表示从后向前数的abs(n1)位,如果n2省略,取n1之后的所有字符 格式:SUBSTR(column | expression,n1[,n2])
LENGTH:取字符长度 格式:LENGTH(column | expression)
INSTR:返回s1中,子串s2从n1开始,第n2次出现的位置。n1,n2默认值为1 格式:INSTR(s1,s2,[,n1],[n2])
LPAD:返回s1被s2从左面填充到n1长度。 格式:LPAD(s1,n1,s2)
RPAD:返回s1被s2从右面填充到n1长度。 格式:RPAD(s1,n1,s2)
TRIM:去除字符串头部或尾部(头尾)的字符 格式:TRIM(leading | trailing | both, trim_character From trim_source)
REPLACE:把s1中的s2用s3替换。 格式:REPLACE(s1,s2,s3)
二、数字函数
1、ROUND(列名|表达式, n):将列或表达式所表示的数值四舍五入到小数点后的第n位。
2、TRUNC(列名|表达式,n):将列或表达式所表示的数值截取到小数点后的第n位。
3、MOD(m,n):取m除以n后得到的余数。
三、日期函数
常用的日期运算如下:
1、日期类型列或表达式可以加减数字,功能是在该日期上加减对应的天数。如:’10-AUG-06’+15结果是’25-AUG-06’。
2、日期类型列或表达式之间可以进行减操作,功能是计算两个日期之间间隔了多少天。如:’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天。
3、如果需要加减相应小时或分钟,可以使用n/24来实现。
常用日期函数: 1、SYSDATE:返回系统日期
2、MONTHS_BETWEEN:返回两个日期间隔的月数
3、ADD_MONTHS:在指定日期基础上加上相应的月数
4、NEXT_DAY:返回某一日期的下一个指定日期
5、LAST_DAY:返回指定日期当月最后一天的日期
6、ROUND(date[,'fmt'])将date按照fmt指定的格式进行四舍五入,fmt为可选项,如果没有指定fmt,则默认为DD’,将date四舍五入为最近的天。
7、 格式码:世纪CC,年YY,月MM,日DD,小时HH24,分MI,秒SS
8、TRUNC(date[,'fmt'])将date按照fmt指定的格式进行截取,fmt为可选项,如果没有指定fmt,则默认为‘DD’,将date截取为最近的天。
9、EXTRACT:返回从日期类型中取出指定年、月、日
四、转换函数
转换函数分为隐式数据类型转换和显式数据类型转换
1、显式数据类型转换:
通常是在字符类型、日期类型、数字类型之间进行显性转换。主要有3个显性函数:
TO_CHAR
TO_CHAR(date|number [,‘fmt’])把日期类型/数字类型的表达式或列转换为字符类型。
‘fmt’指的是需要显示的格式:
需要写在单引号中,并且是大小写敏感
可包含任何有效的日期格式
常用日期格式:
YYYY:4位数字表示年份;
YY:2位数字表示年份,但是无世纪转换(与RR区别在后面章节介绍);
RR:2位数字表示年份,有世纪转换(与YY区别在后面章节介绍);
YEAR:年份的英文拼写;
MM:两位数字表示月份;
MONTH:月份英文拼写;
DY:星期的英文前三位字母;
DAY:星期的英文拼写;
D:数字表示一星期的第几天,星期天是一周的第一天。
DD:数字表示一个月中的第几天;
DDD:数字表示一年中的第几天。
常用时间格式:
AM 或PM:上下午表示;
HH 或HH12或HH24:数字表示小时。HH12代表12小时计时,HH24代表24小时计时;
MI:数字表示分钟;
SS:数字表示秒;
一些特殊格式:
TH:显示数字表示的英文序数词,如:DDTH显示天数的序数词。
SP:显示数字表示的拼写。
SPTH:显示数字表示的序数词的拼写。
TO_NUMBER和TO_DATE函数:
TO_NUMBER(char[,’fmt’]) 把字符类型列或表达式转换为数字类型。
使用格式和TO_CHAR中转换成字符类型中的格式相同
TO_DATE(char[,‘fmt’]) 把字符类型列或表达式转换为日期类型。
格式和TO_CHAR中转换成字符类型中的格式相同。
五、其他函数:
与空值(NULL)相关的一些函数,完成对空值(NULL)的一些操作。主要包括以下函数:
1、NVL
2、NVL2
3、NULLIF
4、COALESCE
条件处理函数:
1、CASE表达式
2、DECODE
NVL (表达式1, 表达式2)函数功能是空值转换,把空值转换为其他值,解决空值问题。表达式1是需要转换的列或表达式,表达式2是如果第一个参数为空时,需要转换的值。
NVL(comm,0)
NVL(hire_date,'01-JAN-06')
NVL(job_id,'No Job Yet')
注意:数据格式可以是日期、字符、数字,但数据类型必须匹配。
六、函数嵌套:
1、单行函数可以嵌套于任何层。
2、嵌套的函数是从最里层向最外层的顺序计算的。
例:
SELECT employee_id,manager_id ,
NVL2(TO_CHAR(manager_id),to_char(manager_id),'No Manager')
FROM employees
来源:http://hi.baidu.com/ysj5125094/blog/item/d3f45a26cc4f010e908f9df2.html