1单行函数的一些特点
单行函数还有以下的一些特征:
–单行函数对单行操作
–每行返回一个结果
–有可能返回值与原参数数据类型不一致(转换函数)
–单行函数可以写在SELECT、WHERE、ORDER BY子句中
–有些函数没有参数,有些函数包括一个或多个参数
–函数可以嵌套
2单行函数的分类
字符函数
数字函数
日期函数
转换函数
通用函数
3 字符函数
大小写操作函数
LOWER(列名|表达式)其它同理
LOWER,UPPER,INITCAP(将首字母大写,其它字母小写)
字符处理函数
CONCAT:连接两个值,等同于||
CONCAT('Good','String‘) 结果 GoodString
SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串,如果n1是负值,表从后向前数的abs(n1)位,如果n2省略,取n1之后的所有字符 格式:SUBSTR(column | expression,n1[,n2])
SUBSTR('String',1,3) 结果Str
LENGTH :取字符串的长度 LENGTH('String') 结果 6
INSTR:返回s1中,子串s2从n1开始,第n2次出现的位置。n1,n2默认值为1 格式:INSTR(s1,s2,[,n1],[n2])
INSTR('String','r') 结果 3
LPAD:返回s1被s2从左面填充到n1长度。 格式:LPAD(s1,n1,s2)
LPAD(sal,10,'*') 结果 ******5000
RPAD:返回s1被s2从右面填充到n1长度。 格式:RPAD(s1,n1,s2)
RPAD(sal,10,'*') 结果5000******
TRIM:去除字符串头部或尾部(头尾)的字符 格式:TRIM(leading | trailing | both trim_character From trim_source)
TRIM('S' FROM 'SSMITH') 结果MITH
REPLACE:把s1中的s2用s3替换。 格式:REPLACE(s1,s2,s3)
REPLACE('abc','b','d') 结果adc
查找公司员工编号,用户名(first_name与last_name连接成一个字符串),职位编号及last_name的长度,要求职位从第四位起匹配'ACCOUNT',同时last_name中至少包含一个’e’字母。
SELECT employee_id, CONCAT(first_name, last_name)
NAME, job_id, LENGTH (last_name) length
FROM employees
WHERE SUBSTR(job_id, 4) = 'ACCOUNT'
AND INSTR(last_name, 'e')>0;
4数字函数
ROUND(列名|表达式, n):将列或表达式所表示的数值四舍五入到小数点后的第n位。
TRUNC(列名|表达式,n):将列或表达式所表示的数值截取到小数点后的第n位。
MOD(m,n):取m除以n后得到的余数。
5日期函数
常用的日期运算如下:
–日期类型列或表达式可以加减数字,功能是在该日期上加减对应的天数。如:’10-AUG-06’+15结果是’25-AUG-06’。
–日期类型列或表达式之间可以进行减操作,功能是计算两个日期之间间隔了多少天。如:’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天。
–如果需要加减相应小时或分钟,可以使用n/24来实现
SYSDATE:返回系统日期
•MONTHS_BETWEEN:返回两个日期间隔的月数
•ADD_MONTHS:在指定日期基础上加上相应的月数
•NEXT_DAY:返回某一日期的下一个指定日期
•LAST_DAY:返回指定日期当月最后一天的日期
•ROUND(date[,'fmt'])将date按照fmt指定的格式进行四舍五入,fmt为可选项,如果没有指定fmt,则默认为DD’,将date四舍五入为最近的天。
格式码:世纪CC,年YY,月MM,日DD,小时HH24,分MI,秒SS
•TRUNC(date[,'fmt'])将date按照fmt指定的格式进行截取,fmt为可选项,如果没有指定fmt,则默认为‘DD’,将date截取为最近的天。
•EXTRACT:返回从日期类型中取出指定年、月、日
MONTHS_BETWEEN 函数演示——公司员工服务的月数。
SELECT last_name, salary, MONTHS_BETWEEN(SYSDATE,hire_date) months
FROM employees
ORDER BY months;
ADD_MONTHS 函数演示——99年公司员工转正日期。
SELECT last_name, salary, hire_date, ADD_MONTHS(hire_date,3) new_date
FROM employees
WHERE hire_date>'01-1月-1999';
NEXT_DAY 函数演示——下周一的日期。
SELECT NEXT_DAY('02-2月-06','星期一') NEXT_DAY FROM DUAL;
LAST_DAY 函数演示——06年2月2日所在月份最后一天。
SELECT LAST_DAY('02-2月-2006') "LAST DAY" FROM DUAL;
ROUND函数演示——98年入职员工入职日期按月四舍五入。
SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';
TRUNC 函数演示——98年入职员工入职日期按月截断。
SELECT employee_id, hire_date, TRUNC(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';
EXTRACT 函数语法
部门编号是90的部门中所有员工入职月份。
EXTRACT ([YEAR] [MONTH][DAY] FROM [日期类型表达式])
SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTH FROM employees WHERE department_id = 90;
6转换函数
数据类型转换分为隐式转化和显示转换
对数据进行隐式转换
对于INSERT和UPDATE操作,oracle会把插入值或者更新值隐式转换为字段的数据类型
–对于SELECT语句,oracle会把字段的数据类型隐式转换为变量的数据类型
–当比较一个字符型和数值型的值时,oracle会把字符型的值隐式转换为数值型
–当比较字符型和日期型的数据时,oracle会把字符型转换为日期型
–用连接操作符(||)时,oracle会把非字符类型的数据转换为字符类型
–如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算,则oracle会将字符类型的数据转换为合适的数据类型,这些数据类型可能是number、date、rowid等
数据类型的显示转换,通常是在字符类型,日期类型,数字类型直接进行显示转换
主要有3个显性函数
-TO_CHAR
-TO_NUMBER
-TO_DATE
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_CHAR(SYSDATE,’DDSPTH’)
TO_CHAR(SYSDATE,’DD “of” MONTH ‘)
select to_char(sysdate,'YYYY-MM-DD')
from dual;
TO_NUMBER(char[,’fmt’]) 把字符类型列或表达式转换为数字类型。
–使用格式和TO_CHAR中转换成字符类型中的格式相同
TO_DATE(char[,‘fmt’]) 把字符类型列或表达式转换为日期类型。
–格式和TO_CHAR中转换成字符类型中的格式相同。
具体格式如下:
–9:一位数字;
–0:一位数字或前导零;
–$:显示为美元符号;
–L:显示按照区域设置的本地货币符号;
–.:小数点;
–,:千位分割符;
TO_CHAR 函数进行数字到字符型格式转换
注意:进行数字类型到字符型转换,格式中的宽度一定要超过实际列宽度,否则会显示为###。
SELECT last_name, TO_CHAR(salary, '$99,999.00') salary FROM employees WHERE last_name = 'King';
SELECT last_name, TO_CHAR(salary, '$9,999.00') salary FROM employees WHERE last_name = 'King';
7RR功能
8通用函数
与空值(NULL)相关的一些函数,完成对空值(NULL)的一些操作。主要包括以下函数:
–NVL
–NVL2
–NULLIF
–COALESCE
条件处理函数:
–CASE表达式
–DECODE
NVL (表达式1, 表达式2)函数功能是空值转换,把空值转换为其他值,解决空值问题。表达式1是需要转换的列或表达式,表达式2是如果第一个参数为空时,需要转换的值。
–NVL(comm,0)
–NVL(hire_date,'01-JAN-06')
–NVL(job_id,'No Job Yet')
注意:数据格式可以是日期、字符、数字,但数据类型必须匹配。
NVL2(表达式1, 表达式2, 表达式3)函数是对第一个参数进行检查。如果第一个参数不为空,则输出第二个参数;如果第一个参数为空,则输出第三个参数。表达式1可以为任何数据类型
NULLIF (表达式1, 表达式2)函数主要是完成两个参数的比较。当两个参数不相等时,返回值是第一个参数值;当两个参数相等时,返回值是空值。
COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数,参数个数不受限制。
CASE语法
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN '低'
WHEN 0.2 THEN '中'
WHEN 0.3 THEN '高'
ELSE '无'
END) Commission
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY last_name;
DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…,][,缺省值])
SELECT last_name, commission_pct, decode(commission_pct, 0.1,'低', 0.2,'中', 0.3 , '高', '无') Commission FROM employees WHERE commission_pct IS NOT NULL ORDER BY last_name;
9嵌套函数
单行函数可以嵌套于任何层。
嵌套的函数是从最里层向最外层的顺序计算的。
SELECT employee_id,manager_id , NVL2(TO_CHAR(manager_id),to_char(manager_id),'No Manager') FROM employees