oracle内建,oracle 内建函数（1）

oracle 内建函数(一)

数字函数

--输入和输出都是数字

--abs(x) 取绝对值

select abs(-1) from dual;--1

--ceil(x) 返回大于等于n的最小整数

select ceil(1.4),ceil(-2.5) from dual;--2,-2

--floor(x) 返回小于等于n的最大整数

select floor(1.4),floor(-2.3) from dual;--1,-3

--mod(a,b) 返回a/b的余数，如果b=0，返回a

select mod(3,2),mod(3,0) from dual; --1,3

--round(a,[b]) 四舍五入，b是指定四舍五入的位数,

--省略b，到整数；b是整数，小数点后b位；b是负数，小数点前b位

select round(23.78),round(23.78,1),round(23.78,-1) from dual;--24,23.8,20

--trunc(a,[b]) 截取数字，b同样代表截取位置，定位同上

select trunc(23.78),trunc(23.78,1),trunc(23.78,-1) from dual;--24,23.7,20

--平方根

select sqrt(3),sqrt(9) from dual;--1.732,3

--power(a,b) a的b次方

select power(sqrt(3),2),power(2,3) from dual;--3,8

--sign(number):number>0,return 1;number<0,return -1;number=0,return 0.

select sign(-7),sign(0),sign(7) from dual;

--greatest(a,b,c,d...) 返回最大的

select greatest(3,7,5,12) from dual; --12

--字符串比较 自然排序比较

select greatest('about','age','again') from dual;--age

--如果参数数据类型不通，都转换为第一个参数类型进行比较

select greatest('3',7,5,12) from dual; --7

--least(a,b,c,d...) 返回最小的，规则同greatest， 如果有一个为null，则返回null

select least(2,12,6) from dual; --2

select least('2','12','6'),least(2,34,null) from dual;--'12',null

字符函数

--ascii(char) 返回char的ascii码值

select ascii('a'),ascii('D') from dual;--97,68

--chr(n) 将ascii值转换成字符

select chr(97),chr(65) from dual;--a,A

--concat(a,b) 连接字符串a和b，作用和'||'相同

select concat('aa','bb'),'aa'||'bb' from dual; --aabb,aabb

--initcap(char) 将字符串中每个单词首字母大写

select initcap('hello oracle') from dual;--Hello Oracle

--length(char)||lengthc(char) 返回字符串的长度

select length('hello我') from dual;--6

--vsize(char) 返回字符串的bytes长度 =lengthb(char)

select vsize('hello') from dual;

--查看字符集

select * from v$nls_parameters;

--

select length('我'),lengthb('我'),vsize('我') from dual;--1,2,2

select length('hello'),vsize('hello') from dual;--5,5

--instr(char,char1) char1在char中的位置，可以指定起始位置，和第几次出现

select instr('hello','l') from dual;--3,默认从第一个字符开始，第一次匹配结束

select instr('hello','l',1,2) from dual;--4，从第一个支付开始，第二次出现位置

--lower(char) 字符串转换成小写

select lower('TOM') from dual;--tom

--upper(char) 字符转换成大写

select upper('Tom') from dual;--TOM

--lpad(char1,n,char2) 在char1字符串的左端添加char2字符串，直到插入的字符串长度达到n

--char2默认值为空格，如果char1长度大于n，返回char1左端的n个字符

select lpad('a',5,'b') from dual;--bbbba

select lpad('abcdefg',5,'h') from dual;--abcde 5

--rpad(char1,n,char2) 右端填充，规则同上面左端填充

select rpad('a',5,'big') from dual; --abigb

--ltrim(char1,char2) 从char1左端开始检测，如果当前位置字符在char2中，那么去掉该字符，直到

--遇到一个字符不在char2为止，返回char1

select ltrim(' hello') from dual;--hello 默认去除空格

select ltrim('hello','echo') from dual;--llo

--rtrim(char1,char2) 右端去掉字符，规则同上面左端去掉字符

select rtrim('oracle','leo') from dual;--orac

--substr(char,from,length) 截取字符串，from截取位置，length截取长度，

select substr('oracle',0,2) from dual;--or

select substr('oracle',2,3) from dual;--rac

select substr('oracle',2) from dual; --racle

select substr('oracle',-2,3) from dual; --le,从右面第2位数，截取3位

--trim(char1 from char2) 从char2的两边去掉char1

select trim('a' from 'abaa') from dual;--b

--translate

/*

translate(str,str_to_relpace,replace_str)

str:被操作对象

str_to_replace:在str中寻找需要被替换的字符

replace_str:对应str_to_replace串中被替换成的字符

例：translate(str,'abcd','123')

将str中所有'a'替换成'1','b'替换成'2','c'替换成'3','d'没有对应的替换字符，直接舍去.

translate方法替换和被替换字符是一一对应的。

*/

select translate('oracleoracle','ole','123') from dual;--1rac231rac23

select translate('oracleoracle','ole','12') from dual;--1rac21rac2

--replace

/*

replace(str,substr1,substr2)

将str中的所有substr1子串用substr2替换

*/

--自己体会和translate的区别

--单一字符和translate的效果相同

select replace('1111abc','1','A') from dual;--AAAAabc

select replace('1111abc','1') from dual;--abc

--多个字符就不同喽

select replace('1111abc','11','AB') from dual;--ABABabc

select translate('1111abc1','11','AB') from dual;--AAAAabcA

--translate是针对一个字符一个字符的替换，replace是字符子串的替换

日期时间函数

--to_date(str,format)将字符串转换成日期

/*

format参数：

YYYY 4位数字年份

YYY,YY,Y年份的最后3|2|1为数

MM 月份(01-12)

DD day of month(1-31)

HH=HH12 :hour of day(1-12)

HH24 :hour of day(0-23)

MI :minute(0-59)

ss:second(0-59)

*/

select to_date('2011-10-01:22:35:24','YYYY-MM-DD:HH24:MI:SS') from dual;

--to_char(date,format)将日期转换成字符串

select to_char(sysdate,'MM-DD-YY') from dual;--08-22-11

--add_months(date,months)加整个月数

select sysdate,add_months(sysdate,3),add_months(sysdate,-2) from dual;

--last_day(date) 计算date所在时间月份的最后一天

select last_day(to_date('1988/08/08','yyyy/mm/dd')),last_day(sysdate) from dual;--1988-8-31,2011-8-31 22:49:23

--当前时间

select current_date,current_timestamp, sysdate from dual;

/* 2011-8-22 23:00:24 ,22-8月 -11 11.00.23.765000 下午 +08:00,2011-8-22 23:00:23*/

--时区 没大用吧

select dbtimezone from dual;

--从日期中提取信息

select extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

--返回相差的月数

select months_between(sysdate,to_date('2011-02-15','YYYY-MM-DD')) from dual;

其他

--nvl(exp1,exp2) 如果exp1=null，返回exp2，否则返回exp1

select nvl(null,1),nvl(2,1) from dual;--1,2

--nvl2(exp1,exp2,exp3) 如果exp1=null，返回exp3；exp1 !=null,返回 exp3

select nvl2(null,1,2),nvl2(3,1,2) from dual;--2,1

--coalesce(exp1,exp2,exp3...)返回第一个不是null的exp的值

select coalesce(null,null,null,null,2,null,5) from dual;--2

--cast类型转换函数

select cast(2345 as varchar2(10)), cast(sysdate as varchar2(30)) from dual;

--decode(exp,search1,result1,search2,result2...,default);

/*

exp:被操纵对象

如果exp=searchN字符串，就返回resultN,和case语句执行差不多

default如果找不到就默认值

*/

select decode('100','99','九九','100','一百','101','一百零一',0) from dual;--一百