首页 > 代码库 > Oracle 常用系统函数

Oracle 常用系统函数

字符函数

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;

数字函数

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;

日期函数

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‘))