Oracle中常用函数总结
来源：互联网 发布：linux怎么设置ip地址 编辑：程序博客网 时间：2024/06/07 04:20
1.模糊查询like
%表示零或多个字符
_表示一个字符
对于特殊符号可以使用ESCAPE标识符来查找
select * from emp where ename like '%*_%'escape'*';
上面的escape表示*后面的字符不被当作特殊字符处理，就是普通的'_'符。
-------------------------------------------下面才是正题-----------------------------------------------------
2.字符函数的使用
- Upper 将字符串转为大写
- select Upper('aabbcc') from dual;
- select * from emp where ename=UPPER('smith');
-
- Lower 将字符串转为小写
- select Lower('AABBCC') from dual;
- select * from emp where ename=LOWER('SMITH');
-
- initcap 返回字符串并将首字母转为大写
- select initcap(ename) from emp;
-
- Concat 将后一个字符串连接到前一个字符串的尾部
- select concat('a','b') from dual;
- select 'a' || 'b' from dual;
-
- Substr 返回截取的字符串
- Substr(字符串,截取开始位置,截取长度)
- substr('Hello World',0,1)
- substr('Hello World',1,1)
- substr('Hello World',2,4)
- substr('Hello World',-3,3)
- 测试：
- select substr('Hello World',-3,3) value from dual;
-
- length 返回字符串的长度
- select length(ename) from emp;
-
- replace 替换字段中指定的字符串
- replace(原字段，“原字段旧内容“,“原字段新内容“,)
- select replace(ename,'a','A') from emp;
-
- instr 在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。
- instr(sourceString,destString,start,appearPosition)
- 其中sourceString代表源字符串；
- destString代表要从源字符串中查找的子串；
- start代表查找的开始位置，这个参数可选的，默认为1；
- appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1
- 如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。
- 返回值为：查找到的字符串的位置。
- select instr('Hello World','or') from dual;
-
- Lpad
- lpad( string, padded_length, [ pad_string ] )
- string
- 准备被填充的字符串；
- padded_length
- 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
- pad_string
- 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
- lpad('Smith',10,'*')
-
- Rpad
- rpad(string,padded_length,[pad_string])
- string
- 被填充的字符串
- padded_length
- 字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
- pad_string
- 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
- Rpad('Smith',10,'*')
-
- Trim 过滤掉首尾空格
- select trim(' Mr Smith ') from dual;
3.数值函数
- Round
- 将参数n按照n2指定的小数位进行四舍五入.不指定n2时默认n2为0,即近似到个位.如果第2个参数为负数时将对小数点左边的数作四舍五入,回到左面的|n|+1位.
- select round(345.678,2) from dual;
- select round(412,-2) from dual;
- select round(412.313,-2) from dual;
-
- Mod(n,n2)
- 返回参数n除以参数n2时的余数.
- select mod(10,2) from dual;
-
- TRUNC(n,n2) 函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
- 第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推。
- TRUNC（89.985，2）=89.98
- TRUNC（89.985）=89
- TRUNC（89.985，-1）=80
4.日期函数
- MONTHS_BETWEEN(DATE1,DATE2) 即MONTHS_BETWEEN(日期1，日期2) 函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数.
- 否则，返回数值带小数，以每天1/31月来计算月中剩余天数。如果日期1比日期2小 ，返回值为负数。
- 例：months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.29','yyyy.mm.dd'))
- 返回 12
- 注：两个参数均为同样月份的29号，所以返回一整数。
- months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.12.24','yyyy.mm.dd'))
- 返回 13.16129
- 注：两个参数表示的日期不是同一天，所以返回带小数的值。
-
- add_months(x,y)或者add_months(times,months)函数
- 这个函数用于计算在时间x之上机上Y个月后的时间值，要是Y的值为负数的话就是在这个时间点之间的时间值（这个时间-Y个月）。
- 如 select add_months(sysdate,-6) from dual;
- 该查询的结果是当前时间半年前的时间
- select add_months(sysdate,6) from dual;
- 该查询的结果是当前时间半年后的时间
-
- NEXT_DAY(date,char)
- date参数为日期型，
- char:为1~7或Monday/Mon~Sunday/ 1-星期天 2-星期一 依次类推
- select next_day(sysdate,'星期日') from dual;
- select next_day(sysdate,1) from dual;
-
- LAST_DAY 函数返回指定日期对应月份的最后一天。
- select last_day(to_date('2016.11.29','yyyy.mm.dd')) from dual;
- 返回结果：2016.11.30
- select last_day(to_date('2017.2.2','yyyy.mm.dd')) from dual;
- 返回结果：2017.2.28
5.转换函数
- to_char(type, text)
- type:可以为timestamp、int、float、numeric格式
- text：转换的模板。如：'yyyy-mm-dd hh24:mi:ss'
-
- to_date(type, text)
- 用法同上相反
- select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;
- 注：很多Java程序员也许会直接的采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。
- 原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。
-
- to_number 将字符串转换为数字数据类型
- select to_number('12')+to_number('13') from dual;
- 返回结果:15
6.通用函数
- sign(n)
- 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
-
- NVL(expr1,expr2)
- 含义是：如果oracle第一个参数为空(null)那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
- 注：Oracle会把''转化为null。但是'' 不等于' '。
-
- nvl2(expr1,expr2,expr3)
- 如果 expr1 不是 null 值，则 nvl 函数返回 expr2 ，否则就返回 expr3 。参数可以返回任何数据类型的值，但是 expr2 和 expr3 不能是 LONG 型的数据类型。
- 说明：若 expr2 和 expr3 的数据类型不同：
- <span style="white-space:pre"> </span> (1)若 expr2 是字符数据，则Oracle数据库在比较之前就会把 expr3 转换成 expr2 的数据类型除非 expr3 是null。在 这种情况下，隐式数据转换是不必要的。Oracle数据库返回 VARCHAR2 数据类型到 expr2 的字符集。
- <span style="white-space:pre"> </span>(2) 若 expr2 是数值型，则Oracle数据库决定哪个参数具有最高数值优先级，并把另一个参数的数据类型隐式转换成这种数据类型，并返回这种数据类型的数据。
-
- NULLIF（表达式1，表达式2）
- 如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。
- 注意：表达式1和表达式2应该是相同数据类型或能隐含转换成相同数据类型，表达式1不能用字符null,但''不报错。
-
- COALESCE(表达式1,表达式2,...,表达式n)
- n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。
- 注意：所有表达式必须为同一类型或者能转换成同一类型。
-
- case 表达式和java中的switch 类似
- select empno,ename,sal,case deptno
- when 10 then '财务部'
- when 20 then '开发部'
- when 30 then '行政部'
- else '未知部门'
- end dept_name
- from emp;
-
- DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
- 表示如果value等于if1时，DECODE函数的结果返then1,...,如果不等于任何一个if值，则返回else。
- 可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较。
