Oracle常用系统函数
2 字符函数
1. replace( 字符串1,字符串2,字符串3)
replace( char, search_string, replace_string)
功能:在“字符串1”中搜索“字符串2”,并将其替换为“字符串3”。
例如下面的命令是将所有员工名字中出现的”a”替换为”中国”。
sql>selectreplace(ename, 'a', '中国') from scott.emp;
2. instr(c1, c2, i, j)
功能:在一个字符串中搜索指定的字符,返回发现指定的字符的位置。其中:
c1被搜索的字符串
c2希望搜索的字符串
i 搜索开始位置,默认为1
j 第j次出现,默认为1
例如下面的命令是找出”oracletraning” 第二个ra出现的位置。
sql>selectinstr('oracle traing' , 'ra',1,2) from dual;
3. ascii(单个字符)
功能:返回与指定字符对应的十进制数。
sql>selectascii ('a') a, ascii('a') a , ascii (' ') space from dual;
说明:dual是oracle内部提供的一个用于临时数据计算的特殊表,它只有一列dummy。
4. chr(整数)
功能:给出整数,返回对应的字符。
sql>selectchr(54740) zhao, chr(65) char65 from dual;
5. concat(字符串1,字符串2)
功能:连接两个字符串。
selectconcat('0532-', '96656') || '拨 0' 崂山矿泉订水 fromdual;
selectconcat (ename, '是优秀员工') from scott.emp;
该函数和|| 的作用是一样的。
6. initcap(字符串)
功能:返回字符串并将字符串的第一个字母变为大写。
selectinitcap('smith') upp from dual;
selectinitcap(ename) ename from scott.emp;
7. length(字符串)
功能:返回字符串的长度
例如:查询雇员姓名,姓名字符长度,工资及工资数字长度。
selectename, length(ename), sal, length(to_char(sal)) from scott.emp;
例如:请查询名字的字符长度为4的雇员
select* from scott.emp where length(ename) =4;
selectlength('李明') from dual; --长度为2,不区分英汉,都占1个字符
说明:
the length functionsreturn the length of char. length calculates length usingcharacters as defined by the input character set.
--返回以字符为单位的长度.
lengthb usesbytes instead of characters.
--返回以字节为单位的长度.
lengthc usesunicode complete characters.
--返回以unicode完全字符为单位的长度.
length2 usesucs2 code points.
--返回以ucs2代码点为单位的长度.
length4 usesucs4 code points.
--返回以ucs4代码点为单位的长度.
下面的例子比较了不同长度计算函数的差异:
createtable s(a char(5), b nchar(5), c varchar(5), d nvarchar2(5));
insertinto s values('aa','aa','aa','aa');
insertinto s values('你好','你好','你好','你好');
insertinto s values('你好!','你好!','你好!','你好!');
selectlength(a), a, length(b), length(c), length(d) from s;
selectlengthb(a),a,lengthb(b),lengthb(c),lengthb(d)from s;
selectlengthc(a),a,lengthc(b),lengthc(c),lengthc(d)from s;
8. lower(字符串)
功能:返回字符串,并将所有的字符小写。
selectlower('abbbccdd') abbbccdd from dual;
9. upper(字符串)
功能:返回字符串,并将所有的字符大写。
selectupper('abbbccdd') abbbccdd from dual;
10. substr(string,start, count)
功能:取子字符串,从start开始,取count个。
selectsubstr('13370840627',3,5) from dual;
例如:请把雇员名字首字母小写,其他字母大写。
selectlower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from scott.emp;
11. rpad和lpad函数
功能:在列的右/左边粘贴字符
例如:显示page1要占15个字符,不足的部分左/右边用*.占位。
selectlpad('page 1',15, '*.') "lpad example " from dual;
selectrpad('page 1',15, '*.') "rpad example " from dual;
12. ltrim和rtrim
功能:删除左边/右边出现的字符串
举例如下:
selectltrim('qingdao university', 'q ') from dual;
13. soundex
功能:返回一个与给定字符串读音相同的字符串
create table table1(xm varchar(8) );
insert intotable1 values('weather');
insert intotable1 values('wether');
insert intotable1 values('goose')
select xmfrom table1 where soundex(xm)=soundex('weather');
14. trim('s’from 'string')
功能:去掉指定字符串前后的某些字符。
例如:
selecttrim(0 from 0098123400) "trim example " from dual;
15. to_char(datetime,string format)
功能:将日期型转换为字符串。
to_char(number,stringformat)
功能:将数值转换为字符串
例如:
selectto_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
selectename,to_char(sal, 'l99g999d99') from scott.emp;
说明:
9:显示数字,并忽略前面0
0:显示数字,如位数不足,则用0补齐
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前加美元
l:在数字前加本地货币符号
c:在数字前加国际货币符号
g:在指定位置显示组分隔符
d:在指定位置显示小数点符号
16. to_number(string)
功能:将给出的字符串转换为数字。
例如:
selectto_number('1999') year from dual;
17. decode函数
功能:相当于一条if语句
举例:
create tablestudent(sno char(2), sex char(1),birthday date)-- 创建学生表
insert intostudent values('01','m','18-8月-1992'); --添加记录
insert intostudent values('02','t', '9-5月-1993'); --添加记录
insert intostudent values('03','f' ,'18-1月-1994'); --添加记录
insert intostudent values('04',null,'11-8月-1993'); --添加记录
select *from student; -- 查询学生表
/*查询学生的学号和性别信息,如果性别值为m则显示male,性别值为f则显示female,如果为空值则显示unknow,否则则显示invalid*/
selectsno,sex,decode(sex, 'm','male','f','female',null,'unknow','invalid')fromstudent;
举例:查询student表,统计1992、1993、1994各年出生的学生人数。
selectto_char(trunc(birthday,'year'),'yyyy'),count(*)
from student
whereto_char(trunc(birthday,'year'),'yyyy') in ('1994','1993','1992')
group byto_char(trunc(birthday,'year'),'yyyy')
在sql疑难问题中,decode函数常常发挥非常灵活的作用。其中一个就是为了某种目的将一个表的行转换成列。例如:
selectsum(decode(to_char(trunc(birthday,'year'),'yyyy'),'1994',1,0) ) birth_1994,
sum(decode(to_char(trunc(birthday,'year'),'yyyy'),'1993',1,0)) birth_1993,
sum(decode(to_char(trunc(birthday,'year'),'yyyy'),'1992',1,0)) birth_1992
from student
whereto_char(trunc(birthday,'year'),'yyyy') in ('1994','1993','1992')
18. greatest函数
功能:返回一组表达式中的最大值,即比较字符的编码大小。
举例:
selectgreatest('ad','ac','aa') from dual;
19. least函数
功能:返回一组表达式中的最小值,即比较字符的编码大小。
举例:
selectleast('ad','ac','aa') from dual;
20. uid 函数
功能:返回标识当前用户的唯一整数
举例:
show user
select uidfrom dual;
selectusername,user_id from dba_users where user_id=uid;
2 数字函数
1. abs
功能:取绝对值
selectabs(100),abs(-100) from dual;
2. acos
功能:给出反余弦的值
selectacos(-1) from dual;
3. asin
功能:给出反正弦的值
selectasin(0.5) from dual;
4. atan
功能:返回一个数字的反正切值
selectatan(1) from dual
5. ceil
功能:返回大于或等于给出数字的最小整数
selectceil(3.1415926) from dual;
6. cos
功能:返回一个给定数字的余弦
selectcos(-3.14) from dual;
7. exp
功能:返回一个数字e的n次方
selectexp(2),exp(1) from dual;
8. floor
功能:对给定的数字取整数(舍掉小数位)
selectfloor(234.56) from dual;
9. ln
功能:返回一个数字的对数值
selectln(1), ln(2) from dual;
10. log(n1,n2)
功能:返回以n1为底的n2的对数
selectlog(2,1), log(2,4) from dual;
11. mod(n1,n2)
功能:返回一个n1除以n2的余数
selectmod(10,3) mod(2,3) from dual;
12. power(n1,n2)
功能:返回n1的n2次方
selectpower(2,5) from dual;
13. round
功能:按照指定的精度进行舍入(四舍五入)
14. trunc
功能:按照指定的精度进行舍入(用于截取,没有指定截取到第几位,默认取整数)。
举例:
selectround(55.5),trunc(55.5),round(-55.5), trunc (-55.5)from dual;
selecttrunc(12.345,2), trunk(12.234,-2) from dual;
selectto_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh, to_char(trunc(sysdate,’mi’), ’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;
selecttrunc(sysdate) from dual --2014-3-18今天的日期为2014-3-18
select trunc(sysdate, 'mm') from dual --2014-3-1返回当月第一天.
select trunc(sysdate,'yy') from dual--2014-1-1 返回当年第一天
select trunc(sysdate,'dd') from dual--2014-3-18 返回当前年月日
select trunc(sysdate,'yyyy') from dual--2014-1-1 返回当年第一天
select trunc(sysdate,'d') from dual--2014-3-16 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual --2014-3-1814:00:00 当前时间为14:41
select trunc(sysdate, 'mi') from dual --2014-3-1814:41:00 trunc()函数没有秒的精确
15. sign
功能:取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
select sign(12),sign(-10),sign(0)from dual;
16. sqrt(n)
功能:返回数字n的平方根
selectsqrt(64) ,sqrt(10)from dual;
17. avg(distinct| all)
功能:返回平均值
selectavg(sal) from scott.emp;
selectavg(sal) from emp;
18. max(distinct| all)
功能:返回最大值
selectmax(sal) from scott.emp;
19. min(distinct| all)
功能:返回最小值
selectmin (sal) from scott.emp;
20. stddev(distinct| all)
功能:求标准差
selectstddev (sal) from scott.emp;
21. variance(distinct| all)
功能:求协方差
selectvariance (sal) from scott.emp;
2 日期函数
1. add_months
功能:增加或减去月份
举例:
selecthiredate, add_months(hiredate,2) from scott.emp;
selectto_char(add_months(to_date('199912', 'yyyymm'),2), 'yyyymm') from dual;
举例:请查询最近三个月入职的员工(把hiredate增加3个月,如果新日期大于当前日期则满足查询要求)
select *from emp where add_months(hiredate,3)>=sysdate
select *from emp where hiredate>=add_months(sysdate,-3)
2. last_day
功能:返回当月日期的最后一天
selectlast_day(sysdate) from dual;
3. months_between(date1, date2)
功能:给出date1-date2的月份
举例:
selectmonths_between (to_date('2013.05.20', 'yyyy.mm.dd'), to_date('2014.04.20', 'yyyy.mm.dd') ) mon_betw from dual;
4. new_time(date, 'this', 'that')
功能:给出时间date在this’时区对应that’时区的日期和时间
举例:
selectto_char(sysdate, 'yyyy.mm.dd hh24:mi:ss')bj_time, to_char(new_time(sysdate, 'pdt', 'gmt'), 'yyyy.mm.dd hh24:mi:ss')los_angles from dual;
说明:this,that对应的时区及其简写,大西洋标准时间:ast或adt;阿拉斯加_夏威夷时间:hst或hdt;英国夏令时:bst或bdt;美国山区时间:mst或mdt;美国*时区:cst或cdt;新大陆标准时间:nst;美国东部时间:est或edt;太平洋标准时间:pst或pdt;格林威治标准时间:gmt;yukou标准时间:yst或ydt。
5. next_day(date, 'day')
功能:给出日期date和星期x以后计算下一个星期的日期
selectnext_day('18-5月-2013', '星期五') next_dayfrom dual; --下一个星期五是多少号?
6. to_date (string, 'format')
功能:将字符串转换成oracle中的一个日期(format的格式)
注意:插入date列时默认以(日-月-年)格式。
yy:两位数字的年份 2004 ---> 04
yyyy:四位数字的年份 2004年
mm: 两位数字的月份 8月 --à08
dd: 2位数字的天 30号 -à30
hh24: 8点---à 20
hh12: 8点 ----à 08
mi、ss ----à显示分钟\秒
举例:
insert into scott.emp(empno,hiredate)
values(2222,to_date('1988-11-11', 'yyyy-mm-dd'))