Oracle学习笔记（四）：常用内置函数

自己学习Oracle的相关笔记，主要备忘，并不详细，如有错误欢迎大家指正~~~

---

1. 数值型函数

• 绝对值(ABS)

SELECT ABS(10),ABS(-10) FROM DUAL;

1.png

关于DUAL表

• 取余(MOD)

MOD(n1,n2): 返回n1除以n2的余数,若n2为0，则返回n1；

SELECT MOD(7,2),MOD(15/3,5),MOD('12',2),MOD(-12,5),MOD(2,0) FROM DUAL;

2.png

• 判断正负

SIGN(n)：返回参数n的符号，正数为1，0为0，负数为-1，若n为BINARY_FLOAT或BINARY_DOUBLE，n>=0或n=NaN都会返回1

SELECT SIGN(2),SIGN('-2'),SIGN(0.00),SIGN(2*'-2') FROM DUAL;

3.png

• 三角函数

COS(n),ACOS(n),COSH(n),SIN(n),ASIN(n),SINH(n),TAN(n),ATAN(n).TANH(n):n为弧度表角度

SELECT COS(3.14159265),SIN('3.14159265'),TAN(3.14159265) FROM DUAL;

4.png

• 返回整数函数

  CEIL(n)：返回结果大于或等于输入参数的最小整数;
  FLOOR(n)：返回结果小于或等于输入参数的最大整数;

SELECT CEIL(100),CEIL('100.2'),CEIL(-9.2), FLOOR(-9.2) FROM DUAL;

5.png

• 指数，对数函数

POWER(n1,n2)：n1的n2次幂，如果n1为负数n2必须为整数；
SQRT(n)：返回n的平方根，n不为负，当n为BINARY_FLOAT或BINARY_DOUBLE时，n<0返回NaN；
EXP(n)：返回e的n次幂；
LOG(n1,n2)：以n1为底n2的对数，n1为除1，0外的任意正数；
LN(n)：返回n的自然对数；

SELECT POWER(2,3),SQRT(9),EXP(2),LOG(3,9),LN(4) FROM DUAL;

6.png

• 四舍五入

ROUND(n,integer)：将n四舍五入第二个参数指定的形式的十进制。参数integer要求是整数，若不是整数自动截取整数部分，当integer为整数，表示n被四舍五入到integer位小数，如果integer为负数，则你被四舍五入到小数点向左integer位;

SELECT ROUND(1.2345,2),ROUND(1.98765,2.3),ROUND(1234.2344,-2) FROM DUAL;

7.png

• 数字截取

TRUNC(n,integer)：将数字n按照integer的值进行截取，跟integer的正负有关，参数integer要求是整数，若不是整数自动截取整数部分，当integer为正整数，表示n截取到integer位小数，若integer为负数则截取到小数点左边第integer位，被截取的部分用0代替;

SELECT TRUNC(111.1234,3),TRUNC(111.1234,2.123),TRUNC(111.1234,-2) FROM DUAL;

8.png

2. 字符型函数

• ASCII与字符型转换函数

CHR(n[USING NCHAR_CS])：根据相应的字符集，把ASCII码转换成字符;
ASCII(char)：返回参数首字母的ASCII码;

SELECT CHR(65) ||CHR(66)||CHR(67) ABC,CHR(45679),ASCII('ABC') FROM DUAL;

9.png

• 获取字符串长度函数

LENGTH(char)：得到指定字符串长度，返回数字；

SELECT LENGTH('safsdfsdf士大夫胜多负少ASSDAS') FROM　DUAL;

10.png

• 字符串截取函数

{[SUBSTR]|[SUBSTRB]|[SUBSTRC]|[SUBSTR2]|[SUBSTR4]}(char,position[,substring_length])
-- SUBSTR: 以字符为单位；
-- SUBSTRB: 以字节为单位；
-- SUBSTRC: 以unicode字符为单位；
-- SUNSTR2: 以UCS2代码点为单位；
-- SUNSTR4: 以UCS4代码点为单位；
-- char: 原始字符串；
-- position: 要截取字符串的开始位置，初始为1，若为负数，表示从char的右边算起
-- substring_length: 要截取的长度；

SELECT SUBSTR('sdfghjhgfd',2,2),SUBSTR('asdfgh',-3,3) FROM DUAL;

11.png

• 字符串连接函数

CONCAT(cahr1,char2): 连接两个参数并返回，效果等同于||

SELECT CONCAT('my' ,'123'), 'my'||'123' FROM DUAL;

12.png

• 字符串搜索函数

{[INSTR]|[INSTRB]|[INSTRC]|[INSTR2]|[INSTR4]}(string,substring[,position[,occurrence]])
-- string: 待搜索字符串；
-- substring: 要搜索的字符串；
-- position: 搜索字符串的开始位置，初始为1，若为负数，表示从字符串的右边作为起始位置；
-- occurrence: substring第几次出现，默认是1；

SELECT INSTR('my apple','e'),INSTR('my apple','m',-1) FROM DUAL;

13.png

• 字母大小写转换

UPPER(char): 将指定参数全部转化成大写
LOWER(char): 将指定参数全部转化成小写
INITCAP(char): 所有首字母转化成大写

SELECT UPPER('my apple') up,LOWER('MY APPLE') lower,INITCAP('my apple')  cap FROM DUAL;

14.png

• 替换字符串函数

REPLACE(char,search_string[,replacement_string])
-- char: 表示搜索的目标字符串;
-- search_string: 在目标字符串中要搜索的字符串;
-- replacement_string: 可选参数，用来替换被搜索的字符串，若该参数为空，则在char中删除search_string;

SELECT REPLACE('my apple', 'pp','PP') FROM DUAL;

15.png

• 字符串填充函数

RPAD(expr1,,[,expr2]): 在字符串expr1右边用expr2填充，直到字符串长度为n为止，若expr2不存在则以空格填充；
LPAD(expr1,,[,expr2]): 在字符串expr1左边用expr2填充，直到字符串长度为n为止，若expr2不存在则以空格填充；

SELECT RPAD('123',3,'a'), LPAD('456',12,'lpad') FROM DUAL;

16.png

• 删除字符串首尾指定字符

TRIM([LEADING|TRAILING|BOTH][trim_character FROM]trim_source)
-- 删除指定的前缀或尾随字符，默认删除空格;
-- LEADING: 删除trim_source的前缀字符;
-- TRAILING:  删除trim_source的后缀字符;
-- BOTH:  删除trim_source的前缀和后缀字符;
-- trim_character: 删除指定字符，默认删除空格;
-- trim_source: 被操作的字符串；

RTRIM(char[,set]):删除char右边出现在set中的字符，set没有则删除空格；
LTRIM(char[,set]):删除char左边出现在set中的字符，set没有则删除空格；

SELECT TRIM(TRAILING 'l' FROM 'apple'), TRIM('       apple         '), RTRIM('my apple', 'ppl'), LTRIM('         my apple','a') FROM DUAL;

17.png

3. 日期函数

• 系统日期时间函数

SYSDATE:该函数没有参数，获得系统当前日期；
SYSTIMESTAMP：返回系统时间戳；

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), SYSTIMESTAMP FROM DUAL;

18.png

• 得到时区函数

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
-- DBTIMEZONE: 数据库所在时区；
-- SESSIONTIMEZONE:当前会话所在时区

19.png

• 为日期加上指定月份函数

SELECT 
TO_CHAR(ADD_MONTHS(TO_DATE('2018-1-30','YYYY-MM-DD'),1),'YYYY-MM-DD'),
TO_CHAR(ADD_MONTHS(TO_DATE('2018-1-30','YYYY-MM-DD'),-1),'YYYY-MM-DD') FROM DUAL;

20.png

• 返回会话所在时区当前日期的函数

SELECT SESSIONTIMEZONE, TO_CHAR(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

21.png

4. 转换函数

• 字符型转ASCII类型字符串函数

SELECT ASCIISTR('巅峰时代') FROM DUAL;

22.png

• 二进制转十进制函数

SELECT BIN_TO_NUM(1), BIN_TO_NUM(1,0), BIN_TO_NUM(1,1,1,1,1,1,1,1) FROM DUAL;

23.png

5. NULL函数

• 返回表达式为NULL的函数

SELECT COALESCE(NULL,9-1,NULL) FROM DUAL;
-- COALESCE(expr): 返回expr中第一个不未NULL的表达式的值，若全为NULL则返回NULL

24.png

• 替换NULL值函数

NVL(expr1,expr2): 如果expr1为NULL，则返回expr2的值，否则返回expr1的值，要求两个参数类型一致；
NVL2(expr1,expr2,expr3): 如果expr1为NULL，则返回expr2的值，不为空返回expr3的值

6. 集合函数

• 求平均值函数

AVG([distinct|all]expr)
-- distinct: 除去重复的值
-- all: 默认，包括所有值
-- expr: 表达式，只能是数值类型

-- 可以使用条件子句

• 求记录数量函数

COUNT(*|[distinct|all]expr)
-- *: 表示计算所有记录
-- distinct: 除去重复的值
-- all: 默认，包括所有值
-- expr: 表达式，只能是数值类型

-- 可以使用条件子句

• 返回最大\最小值函数

MAX|MIN([distinct|all]expr)
-- distinct: 除去重复的值
-- all: 默认，包括所有值
-- expr: 表达式，只能是数值类型

-- 可以使用条件子句

• 求和函数

SUM([distinct|all]expr)
-- distinct: 除去重复的值
-- all: 默认，包括所有值
-- expr: 表达式，只能是数值类型

-- 可以使用条件子句

7. 其他函数

• 返回登录名函数

SELECT USER FROM DUAL;

25.png

• 返回会话以及上下文信息函数

USEREVN(parameter)
-- 返回当前会话信息

SYS_CONTEXT(namespace,parameter) 得到context

SELECT USERENV('ISDBA'), USERENV('Language') FROM DUAL;

26.png

• 表达式匹配函数

DECODE(expr,search,result[,search1,result1][,default])
-- 当expr符合条件就返回result值，否则返回default

8. 比较杂乱的函数（知道有这种，用的时候再查吧）

• 提取指定日期特定部分函数
• 得到两个日期之间的月份数
• 时区时间转化函数
• 日期四舍五入、截取函数
• 数据类型转化函数
• 字符串和ROWID相互转化函数
• 字符串在字符集间转化
• 十六进制字符串与RAW类型相互转化
• 数值转化成字符串类型
• 字符串转日期类型函数
• 字符串转数字函数
• 全角转半角函数