oracle常用函数
数字函数
1、四舍五入函数:round(value,precision),precision为负数为保持到整数几位
例如，函数round(45.926,2)返回45.93
round(45.926,0)返回46
round(45.926,-1)返回50
2、对数字直接截取:trunc(value,precision),precision为负数为保持到整数几位
例如，函数trunc(45.926,2)返回45.92
trunc(45.926,0)返回45
trunc(45.926,-1)返回40
3、求余数函数mod(value1,value2)
例如，函数mode(16,3)返回1
字符串函数
1、大小写转化
lower:将大写字母转化为小写字母
例如，lower('Sql Course')返回sql course
upper:将小写字母转化为大写字母
例如，upper('Sql Course')返回SQL COURSE
initcap:将字符串中的所有单词的第一个字母转化为大写字母，其他字符转化为小写字母
例如，initcap('SQL COURSE')返回Sql Course
2、字符串处理
length(str),求一个字符串的长度
例如length('sql')返回3
concat(str1,str2)，将字符串str2连接在字符串str1后，返回连接后的字符串
例如concat('sql','course')返回sqlcourse
substr(str,from,len)返回Str中从第一from个字符开始，长度为len的字串，如果剩余字符串长度的已经小于len，则仅返回剩余的字符串.如果from为正数，说明是从左向右数from个字符，如果from为负数，说明是从右向左数from个字符，取len长度的字符串都是从左向右数。
例如：substr('string',1,3)返回str,substr('string',2,7)返回ubstr
substr('string',-2,2)返回ng
instr,函数返回要截取的字符串在源字符串中的位置。语法如下：
instr(string1,string2[,start_position,[,nth_appeareance]])
string1 源字符串，要在此字符串中查找。
string2 要在string1中查找的字符串.
start_position 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
如果String2在String1中没有找到，instr函数返回0.
例如 instr('abcdefg','b')返回2,instr('abcdefg','h')返回0
instr('abcdefg','e',2)返回5
instr('abc abc','a',1,2) 返回5
instr('abc','bc',-1,1) 返回2
instr('abc bc bc','bc',-1,2)返回5
3、日期处理
获取当前时间select sysdate from dual;
可以对日期数据加或者减少某个数值
例如，select sysdate+1 from dual 返回明天的这个时间
select sysdate-1 from dual 返回昨天的这个时间
可以对两个日期数据做差，查询两个日期数据相差的天数
例如，select (sysdate+1)-(sysdate-1) from dual 返回2
日期函数：
a、month_between(date1,date2),计算两个日期相差几个月
b、add_months(date,interval),将日期date加上interval个月之后返回一个新的日期，如果interval为正，表示加，为负，说明是减
c、last_day(date)计算指定日期所在月的最后一天
d、round(date,str)获得date按年或者按月四舍五入之后的日期，如果str='YEAR'说明是按年舍入，如果str='MONTH',说明是按月舍入
e、trunc(date,str)获得date按年或者月截取之后的日期，如果str='YEAR'说明是按年截取，如果str='MONTH',说明是按月截取
f、next_day(date,token) 取得从当前日期开始遇到的第一个指定星期几的日期,token有以下几种表示方式
数值1-7分别代表从星期日到星期六
字符串，字符串与具体数据库的日期所在的语言环境有关，我现在本地环境默认是中文环境，所以可以表示如下next_day(sysdate,'星期一')就可以获得当前日期向后数最近的周一，如果表示如下next_day(sysdate,'MONDAY')就会提示错误ORA-01843: not a valid month，因为我本地的环境是中文，所以不能用MONDAY表示，这时执行以下命令
SQL> alter session set nls_date_language='american';
Session altered
将客户端连接的日期所处的环境修改为英文，即可使用，注意，该语句仅对当前客户端生效，其他客户端仍然使用所处的日期环境。
4、求最值函数：min求最小值，max求最大值
5、转化函数
a、nvl函数，具体语法如下nvl(val1,val2)如果val1为空值，则返回val2，否则返回val1，val1，val2数据类型匹配，否则出错。
b、nvl2函数，具体语法如下nvl2(expr,expr1,expr2)如果表达式expr不为空，则返回expr1,否则返回expr2. expr可以为任意数据类型。
c、nullif函数，具体语法如下nullif(expr1,expr2)比较两个表达式，如果相等，则返回空值，否则，返回第一个表达式。
d、to_char(date,format)将日期数据date按照指定的格式format转化为字符串输出
例如select to_char(sysdate,'YYYY-MM-DD hh:mm:ss') from dual;
to_char(number,format)也可以将数值型数据按照指定格式输出
例如 select to_char(10000.012,'$99,999.00') from dual; 返回 $10,000.01，字符可以为下面的任意一个合法字符
9 代表一个数值
0 强制显示0
$放置一个$符
L放置一个本地货币符
。显示小数点
，显示千位指示符
如果输入非法字符，会提示ORA-08481:invalid number format model
e、to_date(str,format)将一个字符串str转化为日期类型的数据,format为str的作为日期字符串显示的格式
f、to_number(str，format)将一个字符串转化为数值类型的数据，format为str作为数值数据显示的格式
6、coalesce,语法coalesce(expr1,expr2,.....exprm)返回表达式列表里面的第一个非空表达式。
7、case表达式实现逻辑的if else，具体语法如下
CASE expr
WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr
END
8.、统计函数count(*)
a、select count(*) from v$session;返回v$ssession中数据行数
b、select count(username) from v$session 返回username不为空的记录数量
c、select count(distinct(username)) from v$session 返回不重复的，非空的username的记录的数量。
9、如果在查询中使用分组函数，在查询列表中出现的字段列表必须出现在group by中，否则，就会提示错误ORA-00979:not a group by expression,例如下面的查询语句就会提示上面的错误
select table_name,max_trans,count(*) from user_tables group by table_name;
同时，不能在where子句中出现分组函数，分组函数可以出现在select列表中，如果需要对分组函数的结果进行过滤，则要使用having子句，例如
SQL> SELECT E.DEPTNO,MAX(E.SAL) FROM EMP E GROUP BY E.DEPTNO HAVING MAX(E.SAL) > 1000;
分组函数省略列中的空值，count(字段),sum(字段),avg(字段)都会省略列中的空值，可以通过nvl做空值处理迫使分组函数包含空值，select sum(nvl(字段，0)) from ...
浙公网安备 33010602011771号