Oracle基本函数

一、字符函数
1. ASCII(x)
功能：该函数用于返回字符x的ASCII码值
示例：SELECT ASCII('a') "a", ASCII('A') "A" FROM DUAL;

2. LENGTH(x)
功能：该函数用于返回x中字符的个数
语法：SELECT LENGTH(列名1) FROM 表名 WHERE 表达式;

3. CONCAT(x, y)
功能：该函数用于将y添加到x上，将得到的字符串作为结果返回
语法：SELECT CONCAT(列名1, 列名2) FROM 表名 WHERE 表达式;

4. LOWER(x)
功能：该函数把x中的字母转换成小写返回
语法：SELECT LOWER(列名1) FROM 表名 WHERE 表达式;

5. SUBSTR(x, start[,length])
功能：该函数返回x中从第start位处开始，长度为length（可选）的字符串
语法：SELECT SUBSTR('WELCOME', 2, 4) FROM DUAL;

6. INSTR(x, str [,start] [,occurrence])
功能：该函数在x中查找str，返回str所在的位置，可以从第start位开始查找，也可以指定一个occurrence说明str第几次出现的位置
语法：SELECT INSTR('WELCOME','E') FROM DUAL;

7. INITCAP(x)
功能：该函数将x中每个单词的首字母都转换成大写
语法：SELECT INITCAP('welcome you') FROM DUAL;

8. REPLACE(x, str, restr)
功能：该函数用于在x中查询str串，并将其替换为restr串，如果没有restr则会将原有的str删掉
语法：SELECT REPLACE('ABCDEFG', 'EF', '56') FROM DUAL;

9. RPAD(x, width [,padstr])
功能：该函数在x的右边补齐空格，得到总长为width个字符的字符串，可选的padstr用于指定重复使用那个字符中来补齐x右边的空位
语法：SELECT RPAD('ABCDEFG',10,'#') FROM DUAL;

二、数字函数
1. ABS(x)
功能：该函数用于返回x的绝对值
语法：SELECT ABS(-10) FROM DUAL;

2. MOD(x,y)
功能：该函数用于返回x除以y的余数
语法：SELECT MOD(10, 3) FROM DUAL;

3. CEIL(x)
功能：该函数用于返回大于或者等于x的最小整数值
语法：SELECT CEIL(1.1) FROM DUAL;

4. FLOOR(x)
功能：该函数用于返回小于或者等于x的最大整数值
语法：SELECT FLOOR(1.9) FROM DUAL;

5. POWER(x, y)
功能：该函数用于返回x的y次幂
语法：SELECT POWER(2, 10) FROM DUAL;

6. SQRT(x)
功能：该函数用于返回x的平方根，且x必须大于等于0
语法：SELECT SQRT(9) FROM DUAL;

7. ROUND(x [,y])
功能：该函数用于返回x的四舍五入值，可选的y表示对第几位小数取整
语法：SELECT ROUND(9.445, 2) FROM DUAL;

8. SIGN(x)
功能：该函数用于检测数字的正负，x小于0返回-1，x等于0返回0，x大于0返回1
语法：SELECT SIGN(2) FROM DUAL;

9. COS(x)
功能：该函数用于返回x的余弦值
语法：SELECT COS(0) FROM DUAL;

10. ACOS(x)
功能：该函数用于返回x的反余弦值
语法：SELECT ACOS(0) FROM DUAL;

三、转换函数
1. TO_CHAR(x [,format])
功能：该函数用于将x转换为一个字符串，可用format来指定转换格式
数字格式 ：
    9 代表一个数字
    0 强制显示0
    $ 放置一个$符
    L 放置一个浮动本地货币符
    . 显示小数点
    , 显示千位指示符
语法1：SELECT TO_CHAR(123) || 'abc' FROM DUAL;
语法2：SELECT TO_CHAR(123456.24, '999,999.99') FROM DUAL; //'999,999.99'格式，format串一定要比x长
语法3：SELECT TO_CHAR(123456.24, '$999,999.99') FROM DUAL; //在串前面增加$

2. TO_NUMBER(x [,format])
功能：该函数用于将x转换为一个数字，可用format来指定转换格式
语法1：SELECT TO_NUMBER('123.22')+100 FROM DUAL;
语法2：SELECT TO_NUMBER('-$1,234.24', '$999,999.99') FROM DUAL;

3. CAST(x AS type)
功能：该函数用于将x转换为指定的type兼容类型
语法：SELECT CAST(12345 AS VARCHAR2(10)) || 'ABC' FROM DUAL;

4. ASCIISTR(s)
功能：该函数用于将任意字符集的字符串转换为数据库字符集的ASCII字符串
语法：SELECT ASCIISTR('中国') FROM DUAL;

5. BIN_TO_NUM(x)
功能：该函数用于将二进制数字x转换为NUMBER类型
语法：SELECT BIN_TO_NUM(1,0,0,1) FROM DUAL;

四、正则表达式函数
1. 正则表达式中的元字符



2. REGEXP_LIKE(x, 正则表达式 [,选项])
功能：该函数用于在x中查找参数中的正则表达式，可选参数选项表示（c：在匹配时区别大小写，i：不区分大小写，n：允许使用可以匹配任意字符的操作符，m：将x作为一个包含多行的字符串）
语法：SELECT 列名1, ... FROM 表名 WHERE REGEXP_LIKE(TO_CHAR(列名2, 'YYYY'), '^198[6-8]$');  //匹配1986-1988年之间的数据

3. REGEXP_INSTR(x, 正则表达式 [,start[, occurrence[, return_option[, match_option]]]])
功能：该函数用于在x中查找参数中的正则表达式并返回出现的位置，可选参数start表示开始查找的位置，occurrence表示返回第几次出现，return_option说明应该返回什么整数，match_option选项同REGEXP_LIKE中的选项相同
语法：SELECT REGEXP_INSTR('Hello,Welcome', 'W[[alpha:]]{6}') FROM DUAL;  //返回以W开头的7个字母组成的字符串的位置

4. REGEXP_REPLACE(x, 正则表达式, str[,start[, occurrence[, return_option[, match_option]]]])
功能：该函数用于在x中查找参数中的正则表达式并替换为str
语法：SELECT REGEXP_INSTR('Hello,Welcome', 'W[[alpha:]]{6}', 'welcome') FROM DUAL;  //替换以W开头的7个字母组成的字符串

5. REGEXP_SUBSTR(x, 正则表达式 [,start[, occurrence[, return_option[, match_option]]]])
功能：该函数用于在x中查找参数中的正则表达式的子串，可选参数start表示开始查找的位置，occurrence表示返回第几次出现，return_option说明应该返回什么整数，match_option选项同REGEXP_LIKE中的选项相同
语法：SELECT REGEXP_SUBSTR('Hello,Welcome', 'W[[alpha:]]{6}') FROM DUAL;  //返回以W开头的7个字母组成的子串

6. REGEXP_COUNT(x, 正则表达式 [,start[, occurrence[, match_option]]])
功能：该函数用于在x中查找参数中的正则表达式出现的次数，可选参数start表示开始查找的位置，occurrence表示返回第几次出现，match_option选项同REGEXP_LIKE中的选项相同
语法：SELECT REGEXP_COUNT('Hello,Welcome', 'W[[alpha:]]{6}') FROM DUAL;  //返回以W开头的7个字母组成的子串出现的次数

五、聚合函数
1. AVG()
功能：该函数返回所提供的所有非NULL值的平均值，NULL值将会被忽略
语法：SELECT AVG(列名1) FROM 列名;

2. COUNT()
功能：该函数返回所提供的所有非NULL值的总记录数，NULL值将会被忽略
语法：SELECT COUNT(列名1) FROM 列名;

3. MAX()
功能：该函数返回提供列的最大值，可以用于任何数据类型，包括字符串和日期
语法：SELECT MAX(列名1) FROM 列名;

4. MIN()
功能：该函数返回提供列的最小值，可以用于任何数据类型，包括字符串和日期
语法：SELECT MIN(列名1) FROM 列名;

5. STDDEV()
功能：该函数返回提供列的标准差（方差的平方根）
语法：SELECT STDDEV(列名1) FROM 列名;

6. USM()
功能：该函数返回提供列的和
语法：SELECT USM(列名1) FROM 列名;

7. VARIANCE()
功能：该函数返回提供列的方差（标准差的平方）
语法：SELECT USM(列名1) FROM 列名;

六、日期时间处理函数
1. 日期格式
    YYYY、YYY、YY 分别代表4位、3位、2位的数字年
    YEAR 年的拼写
    MM 数字月
    MONTH 月的全拼
    MON 月的缩写
    DD 数字日
    DAY 星期的全拼
    DY 星期的缩写
    AM 表示上午或者下午
    HH24、HH12 12小时制或24小时制
    MI 分钟
    SS 秒钟
    SP 数字的拼写
    TH 数字的序数词

2. TO_CHAR(x [,format])
功能：该函数用于将时间值转换为字符串，还可以提供一个可选的format来说明x的格式
语法：SELECT TO_CHAR(列名1, 'YYYY-MM-DD HH24:MI:SS') FROM 列名;

3. TO_DATE(x [,format])
功能：该函数用于将字符串转换为时间值，还可以提供一个可选的format来说明x的格式，未指定格式时采用默认格式（DD-MON-YY）
语法：SELECT TO_DATE('01-7月-2009 18:18:18', 'YYYY-MM-DD HH24:MI:SS') FROM 列名;

4. SYSDATE
功能：该函数根据时区返回当前日期，省略函数后面的括号
语法：SELECT SYSDATE FROM DUAL;

5. MONTHS_BETWEEN(x,y)
功能：该函数用于计算x和y之间的月份数量
语法：SELECT MONTHS_BETWEEN(TO_DATE('2008/1/1', 'YYYY/MM/DD'),TO_DATE('2009/1/1', 'YYYY/MM/DD')) FROM DUAL;

6. ADD_MONTHS(x,y)
功能：该函数用于计算x加上y个月的结果，y可以为负数
语法：SELECT ADD_MONTHS(TO_DATE('2008/1/1', 'YYYY/MM/DD'),5) FROM DUAL;

7. NEXT_DAY(x, y)
功能：该函数用于返回给定时间x以后，在给定星期数y第一次出现的日期值
语法：SELECT NEXT_DAY(TO_DATE('2008/1/1', 'YYYY/MM/DD'),5) FROM DUAL;

8. LAST_DAY(x)
功能：该函数用于返回给定时间x的月份的最后一天的日期
语法：SELECT LAST_DAY(TO_DATE('2008/1/1', 'YYYY/MM/DD')) FROM DUAL;

9. ROUND(x[, unit])
功能：该函数用于返回给定时间x取整，默认情况下x取整为最近的一天，unit指明要取整的单元
语法：SELECT ROUND(TO_DATE('2008/1/1', 'YYYY/MM/DD'),'YYYY') FROM DUAL;

10. TRUNC(x[, unit])
功能：该函数用于返回给定时间x截断，默认情况下x截断为当天的开始时间，unit指明要截断的单元
语法：SELECT TRUNC(TO_DATE('2008/1/1', 'YYYY/MM/DD'),'YYYY') FROM DUAL;