Oracle中常用函数总结

绝地灬酷狼 2022-07-14 05:49 197阅读 0赞

#### **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) --返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串
    	substr('Hello World',1,1) --返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符
    	substr('Hello World',2,4) --返回结果为 'ello'
    	substr('Hello World',-3,3) --返回结果为 'rld' *用负数则从右边开始数第几个位置，截取仍然是从左往右截取
    	测试：
    	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; --返回结果:8 indexOf
    	
    	Lpad 
    	lpad( string, padded_length, [ pad_string ] )
    	string
    　　准备被填充的字符串；
    　　padded_length
    　　填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
    　　pad_string
    　　填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
    	lpad('Smith',10,'*')  --返回结果:*****Smith
    	
    	Rpad
    	rpad(string,padded_length,[pad_string])
    	string
    　　被填充的字符串
    　　padded_length
    　　字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
    　　pad_string
    　　是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
    	Rpad('Smith',10,'*')  --返回结果为 Smith*****
    	
    	Trim 过滤掉首尾空格
    	select trim(' Mr Smith ') from dual;--返回结果:Mr Smith

#### **3.数值函数** ####

Round
    	将参数n按照n2指定的小数位进行四舍五入.不指定n2时默认n2为0,即近似到个位.如果第2个参数为负数时将对小数点左边的数作四舍五入,回到左面的|n|+1位.
    	select round(345.678,2) from dual; --四舍五入到百分位(小数点后第2位)
    	select round(412,-2) from dual; --返回结果:400
    	select round(412.313,-2) from dual; --返回结果:400
    	
    	Mod(n,n2)
    	返回参数n除以参数n2时的余数.
    	select mod(10,2) from dual; -- 返回结果:0
    	
    	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;  --返回结果：2016.12.4 16:01:52
    	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 的数据类型不同：
        (1)若 expr2 是字符数据，则Oracle数据库在比较之前就会把 expr3 转换成 expr2 的数据类型除非 expr3 是null。在 这种情况下，隐式数据转换是不必要的。Oracle数据库返回 VARCHAR2 数据类型到 expr2 的字符集。
        (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从而作出一些更有用的比较。