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开始查找c2在c1中出第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;
分别返回结果: 12300 , 12345.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 > ) 返回n1模n2后的余数。 SELECT MOD( 14 , 5 ),MOD( 8 , 25 ),MOD( - 64 , 7 ) FROM DUAL;
分别返回结果: 4 , 0.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.45 、 120 。
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 > ) 返回日期d1比d2大多少月数。 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。(dow: day 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() 求返回行的指定列的差异值
单行函数:
单行函数
分类 函数 功能 示例
字符函数 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开始查找c2在c1中出第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;
分别返回结果: 12300 , 12345.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 > ) 返回n1模n2后的余数。 SELECT MOD( 14 , 5 ),MOD( 8 , 25 ),MOD( - 64 , 7 ) FROM DUAL;
分别返回结果: 4 , 0.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.45 、 120 。
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 > ) 返回日期d1比d2大多少月数。 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。(dow: day 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() 求返回行的指定列的差异值