posts - 120,  comments - 19,  trackbacks - 0
Oracle已经内建了许多函数,不同的函数有不同的作用和用法,有的函数只能作用在一个记录行上,有的能够作用在多个记录行上,不同的函数可能处理不同的数据类型。常见的有两类,单行函数和分组函数 。

单行函数:

单行函数

分类 函数 功能 示例
字符函数 LPAD(<c1>,<i>[,<c2>]) 在字符串c1的左边添加字符串c2直到c1字符串的长度等于i

SELECT  LPAD(‘Hello!’,8,’ ’) leftpad,RPAD(‘Hello!’,8,’ ’) rightpad

FROM DUAL;

 

RPAD(<c1>,<i>[,<c2>]) 在字符串c1的右边添加字符串c2直到c1字符串的长度等于i
LOWER(<c1>) 把字符串c1转换为小写 SELECT LOWER(ename)  one,UPPER(ename) two, INITCAP(ename) FROM EMP;
UPPER(<c1>) 把字符串c1转换为大写
INITCAP(<c1>) c1字符串的每一个单词的第一个字母转换成大写字母
LENGTH(<c1>) 返回字符串c1的长度 SELECT LENGTH(‘How are you’) FROM DUAL;
SUBSTR(<c1>,<i>[,<j>]) 返回字符串c1中从第i个位置开始的j个字符(向右)。如果省略j,则返回c1中从第i个位置开始的所有字符。如果j为负,则返回字符串c1中从第i个位置开始的j个字符(向左)。 SELECT SUBSTR(‘Hello,World’,1,5) FROM DUAL;
INSTR(<c1>,<c2>[,<i>[,<j>]]) c1中从位置i开始查找c2c1中出第j次的位置,i可以为负(此时,从c1的尾部开始)

SELECT INSTR(‘Mississippi’,’i’,3,3) FROM DUAL; 返回结果11

SELECT INSTR(‘Mississippi’,’i’,-2,3) FROM DUAL; 返回结果2

 

LTRIM(<c1>,<c2>) c1前面开始去掉出现在c2的中任何前导字符集。 SELECT LTRIM(‘Mississippi’,’Mis’) FROM DUAL; 返回结果’ppi’。

SELECT RTRIM(‘Mississippi’,’ip’) FROM DUAL; 返回结果’Mississ’。
 
RTRIM(<c1>,<c2>) c1后面开始去掉出现在c2的中任何前导字符集。
数学函数 ABS(<n>) 返回n的绝对值 SELECT ABC(-2),ABS(2) FROM DUAL;
ROUND(<n1>,<n2>) n1的小数点后保留n2位(四舍五入)并返回。如果n2小于零,n1舍入到小数点左边。

SELECT ROUND(12345.678,-2),

ROUND(12345.678,2)

 FROM  DUAL;

分别返回结果:1230012345.68

 

CEIL(<n>) n 向上取整,并返回。

SELECT CEIL(5.1),CEIL(-21.4) FROM  DUAL;

分别返回:6, -21

 

FLOOR(<n>) n 向下取整,并返回。

SELECT FLOOR(5.1),FLOOR(-21.4) FROM  DUAL;

分别返回:5, -22

 

MOD(<n1>,<n2>) 返回n1n2后的余数。

SELECT MOD(14,5),MOD(8,25),MOD(-64,7) FROM DUAL;

分别返回结果:40.5-1

 

SIGN(<n>)

符号函数,n>0,返回1

n<0,返回-1

n=0,返回0

 

SELECT SIGN(-2.3),SIGN(2.3),SIGN(0) FROM DUAL;
SQRT(<n>) 返回n的平方根 SELECT SQRT(9) FROM DUAL;
TRUNC(<n1>,<n2>) 功能类似ROUND函数。但不做四舍五入。

SELECT TRUNC(123.456,2),TRUNC(123.456,-1) FROM DUAL;

分别返回结果:123.45120

 

VSIZE(n) 返回数字n的存储字节 SELECT VSIZE(123) FROM DUAL;
日期函数(日期可以进行算术运算) SYSDATE 返回相同日期 SELECT SYSDATE FROM DUAL;
ADD_MONTHS(<d>,<i>) 返回日期d 加上i个月后的新日期(i正可负)

SELECT SYSDATE, ADD_MONTHS(SYSDATE,2),

ADD_MONTHS(SYSDATE,-2)

FROM DUAL;

 

LAST_DAY(<d>) 返回日期d所在的月的最后一天。 SELECT SYSDATE,LAST_DAY(SYSDATE) FROM DUAL
MONTHS_BETWEEN(<d1>,<d2>) 返回日期d1d2大多少月数。 SELECT MONTHS_BETWEEN(’19-Dec-1999’,’19-Mar-2000’ FROM DUAL;
NEW_TIME(<d>,<tz1>,<tz2>) 将时区tz1的时间d,转换为时区tz2里的时间。 SELECT SYSDATE,NEW_TIME(SYSDATE,’CDT’,’PDT’) FROM DUAL;
NEXT_DAY(<d>,<dow>) 返回日期d后的第一个dow(dowday of week) SELECT NEXT_DAY(SYSDATE,’Monday’) FROM DUAL;
常用转换函数 TO_CHAR(<x>[,<fmt>[,<nlsparm>]]) x转换成字符串。(参数含义请看ORACLE的联机帮助) SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD’) FROM DUAL;
TO_NUMBER(<c>[,<fmt>[,<nlsparm>]]) 将字符串c转换成数字。(参数含义请看ORACLE的联机帮助) SELECT TO_NUMBER(‘123’) FROM DUAL;

TO_DATE(<c>[,<fmt>[,<nlsparm>]])

(常见的日期格式请查联机帮助。)

将字符串c转换成日期。 SELECT TO_DATE(’19-Mar-99’,’DD-Mon-YYYY’) FROM DUAL;
两个重要函数

DECODE(<x>,<m1>,<r1>[,<m2>,

<r2…>][,<d>])

(DECODE函数功能非常强大,请仔细玩味。)

 

一个功能非常强大的函数,它使得SQL非常高效。它的功能类似于一系列的if…then…else语句。

SELECT sid,serial#,username,

DECODE(command

,0,’None’

,2,’Insert’

,3,’Select’

,6,’Update’

,7,’Delete’

,8,’Drop

,’Other’) cmd

FROM V$SESSION WHERE type<>’BACKGROUND’;

 

NVL(x1,x2)

注意ORACLE中的NULL值,注意该函数作用

 

如果x1为空返回x2,否则返回x1 SELECT NVL(ename,’无姓名’)  FROM  EMP;

分组函数

  AVG([{DISTINCT|ALL}]<n>) 求返回行的指定列的平均值

SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal)

FROM SCOTT.EMP;

 

COUNT({*|[DISTINCT|ALL]}<x>) 统计返回的行数

SELECT COUNT (*), COUNT(DISTINCT mgr),COUNT(mgr)

FROM SCOTT.EMP

 

MAX([{DISTINCT|ALL}]<x>) 求返回行的指定列的最大值 SELECT MAX(sal),MAX(DISTINCT sal) FROM EMP;
MIN([{DISTINCT|ALL}]<x>) 求返回行的指定列的最小值 SELECT MIN(sal),MIN(DISTINCT sal) FROM EMP;
STDDEV([{DISTINCT|ALL}]<x>) 求返回行的指定列的标准方差 SELECT STDDEV(sal),STDDEV(DISTINCT sal) FROM EMP;
SUM() 求返回行的指定列的和 SELECT SUM(sal) FROM EMP;
VARIANCE() 求返回行的指定列的差异值  

 

 

     注意:

A、 分组函数不会处理空值,也不会返回空值

B、  所有的分组函数既可以作用于指定列的所有值上,也可以只作用于指定列的差异列值上

C、 当指定ALL选项时,分组函数作用于所有非空列值行上。当指定DISTINCT选项时,分组函数只作用于非空的且具有不同列值的行上(即,重复列值的行只计算一行);



posted on 2006-01-18 17:41 阿成 阅读(746) 评论(0)  编辑  收藏 所属分类: 数据库

只有注册用户登录后才能发表评论。


网站导航: