Oralce常用的系统函数

字符类函数

• ASCLL(c)函数和CHR(i)函数

–分别求得字符”Z、H、D和空格”的ASCII值。

SELECT ASCII('Z') Z, ASCII('H') H, ASCII('D') D, ASCII(' ') SPACE FROM DUAL;

–返回字符

SELECT CHR(90), CHR(72), CHR(68), (32) S FROM DUAL;

• CONCAT(s1,s2)函数

–连接俩个字符串

SELECT CONCAT('Hello', 'World!') INFORMATION FROM DUAL;

• INITCAP(s)函数

–将字符串首字母大写,其余字母小写。

SELECT INITCAP('oh mY God!') INFORMATION FROM DUAL;

• INSTR(s1,s2,i,j)函数

–在字符串”oracle 11g”中，从第3个字符开始查询字符串“1”第2次出现的位置。
–i为负数将从右至左查询

SELECT INSTR('oracle 11g', '1', 3, 2) FROM DUAL;

• LENGTH(s)函数

–查询字符串长度

SELECT LENGTH('hello') FROM DUAL;

–查询雇员名称长度大于5的雇员信息及所在部门信息。

SELECT *
  FROM EMP E
 INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO
 WHERE LENGTH(E.ENAME) > 5;

• LOWER(s)函数和UPPER(s)函数

–将字符串转换为小写

SELECT LOWER('HellO!') FROM DUAL;

–将字符串转换为大写

SELECT UPPER('heLLo!') FROM DUAL;

• LTRIM(s1,s2)函数、RTRIM(s1,s2)函数个TRIM(s1,s2)函数

–删除字符串左边的指定字符串

SELECT LTRIM('###hello##!##', '#') FROM DUAL;

–删除字符串右边的指定字符串

SELECT RTRIM('###hello##!##', '#') FROM DUAL;

–删除字符串两端的指定字符串

SELECT TRIM('#' FROM '###hello##!##') FROM DUAL;

• REPLACE(s1,s2,s3)函数

–将字符串”Bad Luck Bad Gril”中的”Bad”字符串用”Good”替换掉

SELECT REPLACE('Bad Luck Bad Gril', 'Bad', 'Good') FROM DUAL;

• SUBSTR(s,i,j)函数

–截取字符串”MessageBox”中从第8个位置开始长度为3的字符串

SELECT SUBSTR('MessageBox', 8, 3) FROM DUAL;

数字类函数

• ABS(n)函数

–返回n的绝对值

SELECT ABS(-1) FROM DUAL;

• CEIL(n)函数

–返回大于或等于数值n的最小整数

SELECT CEIL(-7.9) FROM DUAL;

• COS(n)函数

–返回n的余弦值，n为弧度

SELECT COS(30) FROM DUAL;

• EXP(n)函数

–返回e的n次幂

SELECT EXP(1) FROM DUAL;

• FLOOR(n)函数

–返回小于或等于n的最大整数

SELECT FLOOR(-2.2) FROM DUAL;

• LOG(n1,n2)函数

–返回以n1为底n2的对数

SELECT LOG(4, 16) FROM DUAL;

• MOD(n1,n2)函数

–返回n1除以n2的余数

SELECT MOD(3, 2) FROM DUAL;

• POWER(n1,n2)函数

–返回n1的n2次方

SELECT POWER(3, 4) FROM DUAL;

• ROUND(n1,n2)函数

–返回舍入小数点右边n2位的n1的值，n2的默认值为0，
–这会返回小数点最接近的整数。
–如果n2为负数，就舍入到小数点左边相应的位置上，n2必须是整数。

SELECT ROUND(3.1415926, 2) FROM DUAL;

• SIGN(n)函数

–若n为负数，则返回-1，若n为正数，则返回1，若n=0，则返回0.

SELECT SIGN(-22) FROM DUAL;

• SIN(n)函数

–返回正弦值，n为弧度

SELECT SIN(30) FROM DUAL;

• SQRT(n)函数

–返回n的平方根,n为弧度

SELECT SQRT(16) FROM DUAL;

• TRUNC(n1,n2)函数

–返回结尾n2位小数的n1的值，n2默认设置为9，当n2为默认设置时，
–会将n1截尾为整数，如果n2为负值，就截尾在小数点左边响应的位上。

SELECT TRUNC(2333.1415, -2) FROM DUAL;

日期和时间类函数

• ADD_MONTHS(d,i)函数

–返回日期d加上i个月之后的结果。其中，i为任意整数

SELECT ADD_MONTHS('2015-2-22', 3) FROM DUAL;

• LAST_DAY(d)函数

–返回包含日期d月份的最后一天

SELECT LAST_DAY('2016-3-19') FROM DUAL;

• MONTHS_BETWEEN(d1,d2)函数

–返回d1和d2之间的数目，若d1和d2的日期都相同，或者都是该月的最后一天，
–则返回一个整数，否则返回的结果将包含一个小数。

SELECT MONTHS_BETWEEN('2016-3-19', '2015-3-19') FROM DUAL;

• NEW_TIME(d1,t1,t2)函数

–其中，d1是一个日期数据类型，当时区t1中的日期和时间是d1时，
–返回时区t2中的日期和时间。t1和t2是字符串。

SELECT SYSDATE BJ_TIME, NEW_TIME(SYSDATE, 'PDT', 'GMT') LOS_ANGLES FROM DUAL;

• SYSDATE()函数

–返回系统当前时间

SELECT SYSDATE FROM DUAL;

转换类函数

• CHARTOROWID(s)函数

–该函数将字符串s转换为RWID数据类型

• CONVERT(s,aset[,bset])函数

–该函数将字符串s由bset字符集转换为aset字符集

• ROWIDTOCHAR()函数

–该函数将ROWID数据类型转换为CHAR类型

• TO_CHAR(x[,format])函数

–该函数实现将表达式转换为字符串，format表示字符串格式。

SELECT TO_CHAR(SYSDATE, 'YYYY:MM:DD') FROM DUAL;

• TO_DATE(s[,format[lan]])函数

–该函数将字符串s转换成date类型，format表示字符串格式，lan表示所使用的语言

SELECT TO_DATE('2015-1-1', 'YYYY-MM-DD') FROM DUAL;

• TO_NUMBER(s[,format[lan]])函数

–该函数将返回字符串s代表的数字，返回值按照format格式进行显示，
–format表示字符串格式，lan表示所使用的语言。
–将十六进制数-18f转换为十进制数

SELECT TO_NUMBER('18', 'xxx') AS "十进制数" FROM DUAL;

聚合类函数

• AVG()函数

–计算选择列表的平均值，列表项目可以是一个列或多个列的表达式

SELECT AVG(SAL) AS "平均工资" FROM EMP;

• COUNT()函数

–返回查询结果中的记录数

SELECT COUNT(1) FROM EMP;

• MAX()函数

–返回选择列表项目中的最大数，列表项目可以使一个列或多个列的表达式。

SELECT MAX(SAL) AS "最高工资" FROM EMP;

• MIN()函数

–返回选择列表项目中的最小数，列表项目可以使一个列或多个列的表达式。

SELECT MIN(SAL) AS "最低工资" FROM EMP;

• SUM()函数

–返回选择列表项目中的数值总和，列表项目可以使一个列或多个列的表达式。

SELECT SUM(SAL) AS "工资之和" FROM EMP;

• VARIANCE()函数

–返回选择列表项目的统计方差，列表项目可以使一个列或多个列的表达式。

SELECT VARIANCE(SAL) AS "统计工资方差" FROM EMP;

• STDDEV()函数

–返回选择列表项目中的标准偏差，列表项目可以使一个列或多个列的表达式。

SELECT STDDEV(SAL) AS "工资标准偏差" FROM EMP;