本文包含大量实际操作，功能可通过索引找到
目录
三五七小节为常用，其余小节可跳，大致了解即可
一、概论
函数的本质就是输入参数，得到返回值，不再赘述。Oracle提供大量丰富的函数库，本文以Oracle12c为例，分类介绍各常用函数用法。
IDE：PL/SQL Developer或Oracle SQL Developer
二、数学函数
主要用来处理数值数据，大致包含：绝对值函数、三角函数、对数函数、随机函数等等。若有错误产生，则返回NULL。
以下代码在“命令窗口”执行。
1.绝对值函数示例 ABS(n)
SQL> SELECT ABS(2), ABS(-3.3), ABS(-33) FROM dual;结果为
ABS(2) ABS(-3.3) ABS(-33)
---------- - --------- ----------
2 3.3 33
2.平方根 SQRT(n) 与求余 MOD(x,y)
MOD(x,y)返回x被y除后的余数
SQL> SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6) FROM dual; MOD(31,8) MOD(234,10) MOD(45.5,6)
---------- ----------- -----------
7 4 3.5
3.取整CEIL(n) 与 FLOOR(n)
SQL> SELECT CEIL(-3.35), CEIL (3.35) FROM dual; CEIL(-3.35) CEIL(3.35)
----------- ----------
-3 4
SQL> SELECT FLOOR(-3.35), FLOOR(3.35) FROM dual; FLOOR(-3.35) FLOOR(3.35)
------------ -----------
-4 3
Ceil往大的取
Floor往小的取（地板）
4.获取随机数DBMS_RANDOM.RANOM 与 DBMS_RANDOM.VALUE(x,y)
使用DBMS_RANDOM.VALUE(x，y)函数产生1~20之间随机数
SQL> SELECT DBMS_RANDOM.VALUE(1,20),DBMS_RANDOM.VALUE(1,20) FROM dual; DBMS_RANDOM.VALUE(1,20) DBMS_RANDOM.VALUE(1,20)
----------------------- -----------------------
1.871131765 4.727097533
5.四舍五入 ROUND(n), ROUND(x,y) 与截取TRUNC(x,y) 常用
（1） ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。
SQL> SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66) FROM dual; ROUND(-1.14) ROUND(-1.67) ROUND(1.14) ROUND(1.66)
------------ ------------ ----------- -----------
-1 -2 1 2
（2）TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为 0，则结果不带有小数点或不带有小数部分。
若y设为负数,则截去（归零）x小数点左起第y位开始后面所有低位的值。
SQL>SELECT TRUNC(1.31,1), TRUNC (1.99,1), TRUNC (1.99,0), TRUNC (19.99,-1) FROM dual; TRUNC(1.31,1) TRUNC(1.99,1) TRUNC(1.99,0) TRUNC(19.99,-1)
------------- ------------- ------------- ---------------
1.3 1.9 1 10
TRUNC (1.31,1)和TRUNC (1.99,1)都保留小数点后1位数字，返回值分别为1.3和1.9；
TRUNC (1.99,0)返回整数部分值1；
TRUNC (19.99,-1)截去小数点左边第1位后面的值，并将整数部分的1位数字置0，结果为10。
ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNC(x,y)直接截取值，并不进行四舍五入。
6.乘方POWER(x,y)、EXP(x)等
- POWER(x,y)函数返回x的y次乘方的结果值。
- EXP(x)返回e的x乘方后的值。
- 还有LN(x) 返回x的自然对数
- 正弦函数SIN(x)和反正弦函数ASIN(x)
- 余弦函数COS(x)和反余弦函数ACOS(x)
- TAN(x)返回x的正切，ATAN(x)返回x的反正切，
三、字符串函数
- LENGTH(str)返回值为字符串的字节长度
- CONCAT(s1,s2)返回结果为连接参数产生的字符串。
- INSTR(s,x)返回x字符在字符串s的位置。
- LOWER (str)可以将字符串str中的字母字符全部转换成小写字母。
- UPPER(str)则变成大写
- SUBSTR(s,m,n)函数获取指定的字符串。其中参数s代表字符串，m代表截取的位置，n代表截取长度。
- REPLACE (s1,s2,s3)是一个替换字符串的函数。其中参数s1表示搜索的目标字符串；S2表示在目标字符串中要搜索的字符串；s3是可选参数，用它替换被搜索到的字符串，如果该参数不用，表示从s1字符串中删除搜索到的字符串。
1.TRIM函数 将删除指定的前缀或者后缀的字符，默认删除空格。
TRIM（[LEADING/TRAILING/BOTH] [trim_character FROM] trim_source）
其中 LEADING 指删除 trim_source 的前缀字符；TRAILING删除trim_source的后缀字符；BOTH删除trim_source的前缀和后缀字符；trim_character指删除的指定字符，默认删除空格；trim_source指被操作的源字符串。
SQL> SELECT TRIM( BOTH 'x' FROM 'xyxbxykyx'), TRIM(' xyxyxy ') FROM dual;TRIM(BOTH'X'FROM'XYXBXYKYX') TRIM('XYXYXY')
---------------------------- --------------
yxbxyky xyxyxy
删除字符串“xyxbxykyx”两端的重复字符 “x”，而中间的“x”并不删除，结果为“yxbxyky”。
2.LTRIM(s,n)函数将删除指定的左侧字符。【R就是右侧】
其中s是目标字符串，n是需要查找的字符。如果n不指定，则表示删除左侧的空格。
SQL>SELECT LTRIM ('this is a dog', 'this') , LTRIM (' this is a dog') FROM dual; LTRIM('THISISADOG','THIS') LTRIM('THISISADOG')
-------------------------- -------------------
is a dog this is a dog
3.使用INITCAP函数将字符串中首字母转换成大写
SQL> SELECT INITCAP ('hello beautiful word ') FROM dual;
INITCAP('HELLOBEAUTIFULWORD')
-----------------------------
Hello Beautiful Word
假设有一张员工表tb_emp
--选取id为1024与1026的员工，拼接两个字段且首字母大写
SELECT M_ID,CONCAT(INITCAP(M_FN),INITCAP(M_LN)) Fullname
FROM member
WHERE M_ID IN (1024,1026);查询结果为
四、日期和时间函数
- SYSDATE()函数获取当前系统日期。
- SYSTIMESTAMP()函数获取当前系统时间，该时间包含时区信息，精确到微秒。
- DBTIMEZONE函数获取数据库所在的时区
- LAST_DAY(date)函数。该函数返回参数指定日期对应月份的最后一天。
- NEXT_DAY(date,char)函数获取当前日期向后的一周对应日期，char表示是星期几，全称和缩写都允许。
1.EXTRACT(datetime)从指定的时间中提取特定部分。
例如提取年份、月份或者时等。
SQL> SELECT EXTRACT (YEAR FROM SYSDATE), EXTRACT (MINUTE FROM TIMESTAMP '1985-10-8 12:23:40') FROM dual;
EXTRACT(YEARFROMSYSDATE) EXTRACT(MINUTEFROMTIMESTAMP'1985-10-812:23:40')
------------------------ -----------------------------------------------
2019 23
2.MONTHS_BETWEEN函数获取两个日期之间的月份数。
SQL>SELECT
MONTHS_BETWEEN(TO_DATE('1985-10-8','YYYY-MM-DD'),TO_DATE('1985-8-8','YYYY-MM-DD') one,
MONTHS_BETWEEN(TO_DATE('1985-05-8','YYYY-MM-DD'),TO_DATE('1985-07-8','YYYY-MM-DD') )TWO FROM dual; ONE TWO
---------- ----------
2 -2
五、转换函数（常用）
- ASCIISTR(char)函数将任意字符串转换为数据库字符集对应的ASCII字符串。char为字符类型。
- BIN_TO_NUM()函数实现将二进制转换成对应的十进制。
- CAST函数把数字与字符串之间进行转换操作二进制转为十进制类型。
1.TO_CHAR函数将一个数值型参数转换成字符型数据。（常用）
TO_CHAR(n，[ fmt [nlsparam] ] )
其中参数 n 代表数值型数据；参数 ftm 代表要转换成字符的格式；
nlsparam参数代表指定fmt的特征，包括小数点字符、组分隔符和本地钱币符号。
SQL> SELECT TO_CHAR (10.13245, '99.999'), TO_CHAR (10.13245) FROM dual;TO_CHAR(10.13245,'99.999') TO_CHAR(10.13245)
-------------------------- -----------------
10.132 10.13245
TO_CHAR函数还可以将日期类型转换为字符串类型。
SQL> SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD'), TO_CHAR (SYSDATE, 'HH24-MI-SS') FROM dual; TO_CHAR(SYSDATE,'YYYY-MM-DD') TO_CHAR(SYSDATE,'HH24-MI-SS')
----------------------------- -----------------------------
2019-01-02 15-06-49
2.TO_DATE函数将一个字符型数据转换成日期型数据。（常用）
TO_DATE( char [,fmt [,nlsparam] ] )
其中参数char代表需要转换的字符串。参数ftm代表要转换成字符的格式；nlsparam参数控制格式化时使用的语言类型。
SQL> SELECT TO_CHAR(TO_DATE ('2019-10-16', 'YYYY-MM-DD'),'MONTH') FROM dual;
TO_CHAR(TO_DATE('2019-10-16','YYYY-MM-DD'),'MONTH')
---------------------------------------------------
10月
取系统时间前2个月的数据
...WHERE MONTH = TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'YYYYMM');获取系统时间前1个月的数据
WHERE MONTH =
TO_CHAR(ADD_MONTHS(TO_DATE(&当前月, 'YYYYMM'), -1), 'YYYYMM');
六、系统信息函数
- USER函数返回当前会话的登录名。
- USERENV函数返回当前会话的信息
七、综合案例
oracle的sql语句中没有limit，limit是mysql中特有的，在oracle中可用rownum来表示，用于查询结果中的前N行数据。
如要查询emp表中的前5行数据，可用如下语句：
select * from emp where rownum<=5;
如何选择列表中第一个不为空的表达式？
COALESCE(expr)函数返回列表中第一个不为null的表达式。如果全部为null，则返回一个null。
SQL> SELECT COALESCE (1-2,NULL ,9-8,NULL) FROM dual;
COALESCE(NULL,9-8,NULL)
-----------------------
-1
133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
