首页 > 代码库 > Oracle 常用系统函数
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‘))