一.函数:是数据库产品中提供的能够处理查询结果的方法。
二.函数类型
单行函数:对单个行进行运算,并且每行返回一个结果。
多行函数:操纵成组的行,给每个行组返回一个结果。
三.函数语法
function_name(arg1,arg2,…)
function_name:函数名
arg1,arg2,…:参数
四.单行函数分类
1.字符函数:
1.1大小写处理函数
lower:将所有字母转换成小写
upper:将所有字母转换成大写
initcap:首字母大写,其余字母小写
1.2字符处理函数
concat(arg1,arg2):连接值在一起 (concat 函数有两个输入参数)
arg1:字符串类型。字符拼接的值。
arg2:字符串类型。字符拼接的值。
substr(arg1,arg2,arg3):截取子串。
arg1:字符串类型。原字符串,
arg2:整数类型。开始位置(-1 表示原串的最后一位,-2 表示倒数第二位以此类推),
arg3:整数类型。截取个数。
length(arg1):以数字值显示一个字符串的长度。
arg1:字符串类型。计算长度的字符串。
instru(arg1,arg2):找到一个给定字符的数字位置。
arg1:字符串类型。原字符串。 arg2:字符串类型。查找内容。
lpad(arg1,arg2,arg3):用给定的字符左填充字符串到给定的长度。
arg1:字符串类型。原字符串。
arg2:整数类型。总长度。
arg3:字符串类型。填充的子字符串。
rpad(arg1,arg2,arg3):用给定的字符右填充字符串到给定的长度。
arg1:字符串类型。原字符串。
arg2:整数类型。总长度。
arg3:字符串类型。填充的子字符串。
replace(arg1,arg2,arg3):repalce 函数是用另外一个值来替代串中的某个值。 arg1:字符串类型。原字符串。
arg2:字符串类型。需要替换的子串。
arg3:字符串类型。替换的内容。
2.数字函数
round(操作数,保留位) 四舍五入 0为个位 -1十位 1为小数点后一位
trunc(操作数,截取位) 截取到截取位,后面全部舍弃,个位前截取补0
mod(除数,被除数) 求余
3.日期函数
sysdate
months_between(date1,date2) date1,date2之间相差的月数,可以为负
add_month(sysdate,n) n当前时间往后推n个月 -n为往前推n个月
next_day(date,'星期几') 距离这个日期最近的下一个星期几
last_day(date) date 的月的最后一天的日期
round(date) 按上午下午四舍五入。
round(日期,保留位数) MOUTH 看天,如果是15那就舍弃 如果是16进位
YEAR 看月,如果是6月以下舍弃 如果是7以上进位
trunc 截断日期
4.转换函数
4.1.隐式数据类型转换(不建议使用):当源数据的类型和目标数据的类型不同的时候,如果没有转换函数,就会发 生隐式转换,也称自动转换。
性能影响:隐式转换的最大问题就是转换时会导致索引的无效,进而可能导致全表扫描。当表的数 据量很大的时候,产生会很大的性能问题。
不便于阅读:由于隐式转换使得数据库编程人员和 DBA 难以了解到究竟发生了怎样的类型转换,而 且如果代码很多很长的话要查出错误就需要费很大的劲。
4.2.显示数据类型转换:通过数据库中的转换函数完成数据类型的转换。
to_char(arg1,’fmt’):将一个日期或者数字转换为字符类型。带格式化样式 fmt。
arg1:数字或者日期类型。需要转换的数据。
fmt:转换格式。
to_number(‘arg1’,fmt’):将字符串转换为数值型的格式。带格式化样式 fmt。
arg1:字符串类型。需要转换的数据。
fmt:转换格式。
to_date(‘arg1’,’fmt’):将字符串转换为日期格式。带格式化样式 fmt。
arg1:字符串类型。需要转换的数据。
fmt:转换格式。
5.通用函数
通用函数:可用于任意数据类型,并且适用于空值。
nvl(expr1, expr2) :expr1是空值,返回expr2
nvl2 (expr1, expr2, expr3) :expr1是非空,返回expr2,expr1是空值,返回expr3
nullif (expr1, expr2) :expr1,expr2相等返回空值,不等返回expr1
coalesce (expr1, expr2, ..., exprn):返回表达式中的第一个非空表达式
五.条件表达式
1.case 表达式:case 表达式可以让你在 sql 语句中使用 if-then-else 逻辑。如果没有 when … then 满足条件,并且else 子句存在,Oracle 返回 else_expr。否则, Oracle 返回 null。所有的表达式 ( expr、comparison_expr 和 return_expr) 必须是相同的数 据类型,
查询雇员,显示 last_name,job_id,salary 如果 JOB_ID 是 IT_PROG,薪水增加 10%; 如果 JOB_ID 是 ST_CLERK,薪水增加 15%;如果 JOB_ID 是 SA_REP,薪水增加 20%.对于所有其他的工作角色,不增加薪水。
select last_name,job_id,salary,CASE job_id WHEN ‘IT_PROG’ THEN salary * 1.1 WHEN ‘ST_CLERK’ THEN salary * 1.15 WHEN ‘SA_REP’ THEN salary * 1.2 END from employees;
2.decode 函数:decode 函数以一种类似于在多种语言中使用的 if-then-else 逻 辑的方法判断一个表达式。decode 函数在比较表达式 (expression) 和每个查找 (search) 值后,如果表达式与查找相同,返回结果。如果省略默认值,当没有查找值与表达式相匹配 时返回一个空值。
使用 decode 函数完成(显示 last_name,job_id,salary 如果 JOB_ID 是 IT_PROG,薪 水增加 10%;如果 JOB_ID 是 ST_CLERK,薪水增加 15%;如果 JOB_ID 是 SA_REP, 薪水增加 20%。对于所有其他的工作角色,不增加薪水。)
select last_name,job_id,salary, decode(job_id,‘IT_PROG’,salary1.1,‘ST_CLER K’,salary1.15,‘SA_REP’,salary * 1.2) from employees;