Oracle常用函数

一、通用函数

NULLIF（表达式1，表达式2） 如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。
注意：表达式1和表达式2应该是相同数据类型或能隐含转换成相同数据类型，表达式1不能用字符null,但’’不报错

COALESCE(表达式1,表达式2,…,表达式n) n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。
注意：所有表达式必须为同一类型或者能转换成同一类型

CASE 表达式和java中的switch 类似

select empno,ename,sal,
    case deptno
        when 10 then '财务部'
        when 20 then '开发部'
        when 30 then '行政部'
    else '未知部门'
    end dept_name
from emp;

DECODE(value,if1,then1,if2,then2,if3,then3,…,else) 表示如果value等于if1时，DECODE函数的结果返then1,…,如果不等于任何一个if值，则返回else。
可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较

二、数学函数

ABS(x) 返回x的绝对值

BITAND(x,y) 返回对x,y进行位与(AND)操作的结果

CEIL(x) 返回大于或等于x的最小整数(注意负数)

FLOOR(x) 返回小于或等于x的最大整数(注意负数)

EXP(x) 返回值e（自然对数的底）的x次方

LN(x) 返回x的自然对数

LOG(x) 返回以x为底y的对数

POWER(x) 返回x的y次幂

SQRT(x) 返回x的平方根

MOD(x) 返回x除以y的余数

SIGN(x) 返回x的符号

ROUND(x[,y]) 返回对x取整的结果。y为可选参数，说明对第几位小数处取整。没有指定y的时候则对x的0位小数取整；如果是负数，则对x在小数点的左边的第|y|位取整。此函数是四舍五入取整

TRUNC(x[,y]) 与ROUND类似，之不过是直接舍去尾数

三、聚合函数(常用于GROUP BY从句的SELECT查询中)

All：针对所有值
DISTINCT：表示只对不同值

四、字符串函数

ASCII(arg1) 返回字符的ASCII码值，如果数据库设置为ASCII,则采用的是ASCII码字符.如果设置为EBCDIC,则采用的是EBCDIC字符

CHR(arg1,[using nchar_cs]) 返回由参数arg1的代码所指定的字符.所返回的字符依赖于oracle所使用的底层字符编码设置

CONCAT(arg1,arg2) 返回arg1与arg2的字符串拼接结果.等同于arg1||arg2

INITCAP(arg1) 将参数arg1转换为每个单词的首字母大写的格式

INSTR(arg1,to_find,pos,occurrence) 在arg1中查找to_find子字符串,并返回一个整数表示它出现的位置.参数pos指定在参数arg1中开始搜索的位置.如果pos为正,则在该数字指定的位置开始搜索.如果pos为负,则在该数字指定的位置开始搜索,但位置指定是从字符串结尾处开始的.并且反向搜索.参数occurrence指定搜索第几次出现的了字符串

LENGTH(arg1) 返回参数arg1的长度.arg1可以是char,varchar2,nchar,nvarchar2,clob或nclob

LOWER(arg1) 返回参数arg1的小写形式

UPPER(arg1) 返回参数arg1的大写形式

TRIM([LEADING][TRAILING][BOTH] char FROM source) Trim返回varchar2 类型的字符串,其中前导的、尾随的字符char被从source中剪裁。如果指定leading 则与char匹配的前导字符被剪裁。如果指定trailing,则与char匹配的尾随字符被剪裁。指定both,则字符串的首尾都被剪裁。如果没有给出char则默认为空白。如果只指定source参数，则将从source参数中删除尾随和前导的空白

LTRIM(arg1,arg2) 在arg1左面删除连续出现的arg2.不指定arg2则默认删除空格

RTRIM(arg1,arg2) 在arg1右面删除连续出现的arg2.不指定arg2则默认删除空格

REPLACE(arg1,search,replace) 该函数用replace 参数替换出现arg1里的所有search参数

TRANSLATE(arg1,match,replace) 类似于replace函数.差别在于,translate允许一次执行多个字符替换(但只是单个字符的替换)

RPAD(arg1,n,arg3) 在字符串arg1右边连续重复填充arg3直到填充后的字符串总长度到达n为止

SOUNDEX(arg1) 返回arg1的语音学表示.通常用于在某个表中执行搜索,以便查找那些相互之间发音相同但拼写不同的单词

SUBSTR(arg1,pos.len) 从arg1中指定的位置pos开始向右侧截取指定长度len的子字符串.如果pos为正则从字符串arg1左侧开始计数,如果为负则从字符串右侧开始计数.如果未指定len,则返回至该字符串从位置pos开始到串尾的子串

NVL(VALUE1, VALUE2) 如果X是空值，返回VALUE，否则返回X；这里VALUE1和VALUE2必须是同一数据类型

NVL2(X, VALUE1, VALUE2) 如果X是空值，返回VALUE1，否则返回VALUE2

NANVI(X, VALUE) 如果X不是数字，那么返回VALUE，否则返回X

五、日期和时间函数

SYSDATE 用来得到系统的当前日期

1	SELECT SYSDATE FROM DUAL;

ADD_MONTHS 增加或减去月份

1 2	SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL; SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

LAST_DAY 返回日期的最后一天

1 2	SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份

1	SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;

NEW_TIME(date,’this’,’that’) 给出在this时区等于other时区的日期和时间

1	SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;

简写时区
💦 AST OR ADT 大西洋标准时间
💦 HST OR HDT 阿拉斯加—夏威夷时间
💦 BST OR BDT 英国夏令时
💦 MST OR MDT 美国山区时间
💦 CST OR CDT 美国中央时区
💦 NST 新大陆标准时间
💦 EST OR EDT 美国东部时间
💦 PST OR PDT 太平洋标准时间
💦 GMT 格伦威治标准时间
💦 YST OR YDT Yukon标准时间

NEXT_DAY 返回与指定日期在同一个星期或之后一个星期内的，你所要求的星期天数的确切日期
❤ 星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

1	SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;

CURRENT_DATE 当前会话时区中的当前日期

1	SELECT CURRENT_DATE FROM DUAL;

CURRENT_TIMESTAMP 以timestamp with time zone数据类型返回当前会话时区中的当前日期

1	SELECT CURRENT_TIMESTAMP FROM DUAL;

DBTIMEZONE() 以timestamp with time 返回时区

1	SELECT DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE 返回会话时区其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变

EXTRACT 找出日期或间隔值的字段值

TRUNC(for dates) TRUNC函数为指定元素而截去的日期值
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date 一个日期值
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回当年第一天
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回当月第一天
SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回当前星期的第一天
SELECT TRUNC(SYSDATE,'DD') FROM DUAL;--返回当前年月日

----  上月最后一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL;
----: 上个月的今天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL;
---- 上个月第一天
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL;
---  按照每周进行统计
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;
---  按照每月进行统计
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;
----  按照每季度进行统计
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;
---  按照每年进行统计
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

六、数据转换函数

TO_CHAR(DATE,’FORMAT’) 把对应的数据转换为字符串类型

TO_DATE(STRING,’FORMAT’) 将字符串转化为ORACLE中的一个日期

TO_NUMBER(str) 将给出的字符转换为数字

RUNC(for number) 按照指定的精度截取一个数

TRUNC(number[,decimals]) 返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

CHARTOROWID 把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数string必须是包含外部格式的ROWID的18字符的字符串

ROWIDTOCHAR 将ROWID数据类型转换为字符类型，将ROWID类型的数值rowid转换为其外部的18字符的字符串表示

CONVERT(c,dset,sset) 将源字符串 sset从一个语言字符集转换到另一个目的dset字符集

HEXTORAW(x) 将一个十六进制构成的字符串x转换raw

RAWTOHEX 将RAW类数值rawvalue转换为一个相应的十六进制表示的字符串. rawvalue中的每个字节都被转换为一个双字节的字符串. RAWTOHEX和HEXTORAW是两个相反的函数

TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符即将指定字符转换为全角并返回char类型字串

DUMP(s,fmt,start,length) DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
fmt含4种格式：8||10||16||17，分别表示8进制，10进制，16进制和单字符，默认为10进制。start参数表示开始位置，length表示字串数长度

EMPTY_BLOB()，EMPTY_CLOB() 这两个函数都是用来对大数据类型字段进行初始化操作的函数

七、控制流函数（常用在PL/SQL语句中）

流程控制：
- IF (表达式) then 执行的语句 end if; （end if ：结束IF）　　判断满足一种情况的条件
- IF (表达式) then 执行的语句 else 执行的语句 end if；　　判断满足两种情况的条件
- IF (表达式) then 执行的语句 elseif 执行的语句 elseif 执行的语句 …. end if; 　　判断满足多种情况的条件
- Case (表达式) when （条件表达式结果1 ）then 执行语句1 when（条件表达式结果2） then 执行的语句 2 else 执行的语句 n end Caase; （end case 结束 Case）
- Case when (条件表达式1) then 执行语句1 when (条件表达式2) then 执行语句2 else 执行语句n end case;
循环控制：
- LOOP 语句段； exit [when/if 条件表达式] end Loop；　　至少执行一次相当于 do｛｝ while（条件表达式）；当满足when/if条件时，使用exit退出，否则，end Loop;
- while 条件表达式 LOOP 执行语句 end LOOP ;　　只有条件表达式为 true时候就会运行；
- for 循环变量 IN [reverse] 初始值表达式 … 终值表达式 LOOP 执行语句 END　LOOP