target
了解常用的数值型函数
掌握常用的字符型函数
掌握常用的日期型函数
了解转换函数
掌握null函数
掌握集合函数
了解其他函数
1. 数值型函数
1.1绝对值
ABS(n)函数。用于返回绝对值。
SQL> SELECT ABS(100),ABS(-100),ABS('100') FROM DUAL;
ABS(100) ABS(-100) ABS('100')
---------- ---------- ----------
100 100 100
1.2 取余
MOD(n2, n1)函数。该函数表示返回n2除以n1的余数。
SQL> SELECT MOD(5,2),MOD(8/3,5),MOD('10',5),MOD(-10,6),MOD(1,0) FROM DUAL;
MOD(5,2) MOD(8/3,5) MOD('10',5) MOD(-10,6) MOD(1,0)
---------- ---------- ----------- ---------- ----------
1 2.66666667 0 -4 1
1.3 判断数值正负函数
SIGN(n)函数。返回参数n的符号。正数返回1，0返回0，负数返回-1。但如果n为BINARY_FLOAT或BINARY_DOUBLE类型时，n>=0或 n=NaN 函数会返回1。
SQL> SELECT SIGN('9'), SIGN(-9), SIGN(0.00), SIGN(-2*'9') FROM DUAL;
SIGN('9') SIGN(-9) SIGN(0.00) SIGN(-2*'9')
---------- ---------- ---------- ------------
1 -1 0 -1
1.4 三角函数
COS(n)函数。用于返回参数n的余弦，n为弧度表示的角度。
SQL> SELECT COS(3.1415926),COS('3.1415926') FROM DUAL;
COS(3.1415926) COS('3.1415926')
-------------- ----------------
-1 -1
与此类函数类似的还有如下几个。
- ACOS(n)；返回n的反余弦值。
- COSH(n)；返回n的双曲余弦值,
- SIN(n)：返回n的正弦值。
- SINH(n)：返回n的双曲正弦值。
- ASIN(n)：返回n的反正弦值。
- TAN(n)：返回n的正切值。
- TANH(n)：返回n的双曲正切值,
- ATAN(n)：返回n的反正切值。
1.4 返回以指定数值为准整数的函数
(1) ceil(n)函数
其返回结果是大于等于输入参数的最小整数。
SQL> SELECT CEIL(10),CEIL('10.5'),CEIL(-10.2) FROM DUAL;
CEIL(10) CEIL('10.5') CEIL(-10.2)
---------- ------------ -----------
10 11 -10
(2) FLOOR(n)函数
其返回结果是小于或等于参数的最大整数。同CEIL函数相反。
SQL> select floor(10),floor('10.5'),floor(-10.2) from dual;
FLOOR(10) FLOOR('10.5') FLOOR(-10.2)
---------- ------------- ------------
10 10 -11
1.5 指数、对数函数
(1) SQRT(n)函数
该函数返回n的平方根。n为数字类型的时候不能为负数，将返回一个 实数，当n为BINARY_FLOAT或BINARY_DOUBLE类型时，n<0将返回Nan
SQL> SELECT SQRT(100),SQRT('53.9') FROM DUAL;
SQRT(100) SQRT('53.9')
---------- ------------
10 7.34166194
(2) POWER(n2,n1)函数
利用该函数可以得到n2的n1次幕的结果。这两个参数为任意数值，但如果n2为负数，那么n1必须为整数。
SQL> SELECT POWER(5,2),POWER('5',2),POWER(5.5,2.5),POWER(-5,2),5*5 FROM DUAL;
POWER(5,2) POWER('5',2) POWER(5.5,2.5) POWER(-5,2) 5*5
---------- ------------ -------------- ----------- ----------
25 25 70.9425384 25 25
与其相近的函数有：
EXP(n)函数，表示返回e的n次幕，e为数学常量，e = 2.71828183 ...
(3) LOG(n1,n2)函数
该函数可以返回以n1为底n2的对数，n1是除1和0以外的任意正数。 n2为正数。
SQL> select log(10,100),log(10.5,'100'),power(10,2) from dual;LOG(10,100) LOG(10.5,'100') POWER(10,2)----------- --------------- ----------- 2 1.95850074 100
与其相近的函数有：
LN(n)函数，表示返回n的自然对数。n要求大于0。
1.6 四舍五入截取函数
(1) round函数
SQL> SELECT round(100.23456,4) ,round( 100.23456,2.56), round(155.23456,-2) FROM DUAL;
ROUND(100.23456,4) ROUND(100.23456,2.56) ROUND(155.23456,-2)
------------------ --------------------- -------------------
100.2346 100.23 200
(2) TRUNC 函数
该函数的具体原型是TRUNC(n,integer)。它把数值n根据 integer的值进行截取，截取时和integer的正负有关。
参数integer要求是整数，如果不是整数， 那么它将被自动截取为整数部分；当integer为正整数时，表示n将截取到integer位小数，如果 integer为负数，则截取到小数点左第integer位，被截取部分用0代替。
SQL> SELECT trunc(100.23456,4) , trunc(100.23456,2.56) , trunc(155.23456,-2) , trunc(155.23456) from dual;
TRUNC(100.23456,4) TRUNC(100.23456,2.56) TRUNC(155.23456,-2) TRUNC(155.23456)
------------------ --------------------- ------------------- ----------------
100.2345 100.23 100 155
2. 字符型函数
2.1 ASCII码与字符转换函数
(1) CHR函数
根据相应的字符集，把给定的ASII码转换为字符.
SQL> SELECT CHR(65) || CHR(66) || CHR(67) ABC, CHR(54678) FROM DUAL;
ABC CH
--- --
ABC 諙
(2) ASCII(char)函数
返回参数首字母的ASCII码值。与CHR函数相反。
SQL> select ascii('明'),ascii('Abc'),ascii('ABC'),ascii('A') from dual;
ASCII('明') ASCII('ABC') ASCII('ABC') ASCII('A')
----------- ------------ ------------ ----------
50167 65 65 65
2.2 获取字符串长度函数
LENGTH函数。该函数可以得到指定字符串的长度，返回类型是数宇。
SQL> SELECT LENGTH('ABCDEftFGHI') FROM DUAL;
LENGTH('ABCDEFTFGHI')
---------------------
11
2.3 字符串截取函数
SUBSTR函数。该函数提供截取字符串的功能。
初始化位置为1，如果该值为负数，则表示从char的右边算起
🌰：
SQL> select substr('ABCDE你好FGHI',5,2),substr('ABCDE你好FGHI',-5,2) from dual;
SUB SUB
--- ---
E你 好F
2.4 字符串连接函数
CONCAT(char1 , char2)函数。该函数连接两个参数并返回。效果和连接符“II”相似。
SQL> SELECT CONCAT('我的','测试') A, '我的'||'测试' B FROM DUAL;
A B
-------- --------
我的测试 我的测试
2.5 字符串搜索函数
INSTR函数。该函数可以让我们在指定 的字符串中捜索是否存在另一个字符串，返回搜索出来的位置。
搜索的开始位置，默认为1。
SQL> select instr('this is a 测试！','测'),instr('this is a 测试！','s',-1) from dual;
INSTR('THISISA测试！','测') INSTR('THISISA测试！','S',-1)
--------------------------- -----------------------------
11 7
instr('this is a 测试！','s',-1)表示：从后向前搜索，s出现在字符串中的位置。
2.6 字母大小写转换函数
(1) UPPER(char)函数
该函数将指定的参数全部转换成大写字母。
SQL> SELECT UPPER('A'),UPPER('abed'),UPPER('this is a test') FROM DUAL;
U UPPE UPPER('THISISA
- ---- --------------
A ABED THIS IS A TEST
(2) LOWER(char)函数
该函数将指定的参数全部转换成小写字母。
SQL> SELECT LOWER('A'),LOWER('XBCD'),LOWER('THIS IS A TEST') FROM DUAL;
L LOWE LOWER('THISISA
- ---- --------------
a xbcd this is a test
(3) INITCAP(char)函数
该函数参数的所有单词首字母转换成大写字母。
SQL> SELECT INITCAP( 'this is a test' ) FROM DUAL;
INITCAP('THISI
--------------
This Is A Test
2.6 替换字符串函数
REPLACE 函数。
SQL> select replace('this is a test','tes','resul') from dual;
REPLACE('THISISAT
-----------------
this is a result
2.7 字符串填充函数
(1) RPAD函数
函数具体语法结构是RPAD(expr1,n[,expr2])，该函数功能是在字符串 expr1的右边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填充。
SQL> SELECT RPAD('test' ,8, 'abcd' ),RPAD( 'test',15, 'abcd' ),RPAD('test',4, 'abcd') FROM DUAL;
RPAD('TE RPAD('TEST',15, RPAD
-------- --------------- ----
testabcd testabcdabcdabc test
(2) LPAD函数
函数具体语法结构是LPAD(exprl,n[,expr2]),该函数功能是在字符串exprl 的左边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填 充。
SQL> SELECT LPAD( 'test',8, 'abcd'),LPAD( 'test' ,15, 'abcd'),LPAD( 'test',4, 'abcd') FROM DUAL;
LPAD('TE LPAD('TEST',15, LPAD
-------- --------------- ----
abcdtest abcdabcdabctest test
2.8 删除字符串首尾指定字符的函数
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(TRAILING 't' FROM 'test'),TRIM(' test ') FROM DUAL;
TRI TRIM
--- ----
tes test
3. 日期型函数
3.1 系统日期、时间函数
(1) SYSDATE函数
该函数没有参数，可以得到系统的当前日期，是很常用的函数。
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 现在时刻 FROM DUAL;
现在时刻
-------------------
2021-06-10 22:30:50
(2) SYSTIMESTAMP函数
该函数没有参数，返回系统时间，该时间包含时区信息，精确到微秒。返回类型为带时区信息的TIMESTAMP类型。
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
10-6月 -21 10.32.19.859000 下午 +08:00
3.2 为日期加上指定月份函数
ADD_MONTHS(date,integer)函数，将返回在指定的日期上加integer个月份数后的日期。各参数具体含义如下：
- date：指定的日期。
- integer：要加的月份数，该值如果为负数，则表示减去的月份数。
- 该函数有些地方需要注意，当指定的日期是月的最后一天时，最后函数返回的结果也将是新月的最后一天。而如果新的月份比指定日期月份的天数少，则函数将自动回调有效日期
SQL> SELECT
2 TO_CHAR(ADD_MONTHS(TO_DATE('2009-9-15','YYYY-MM-DD'),1), 'YYYY-MM-DD') A,
3 TO_CHAR(ADD_MONTHS(TO_DATE('2009-9-15','YYYY-MM-DD'),3), 'YYYY-MM-DD') B,
4 TO_CHAR(ADD_MONTHS(TO_DATE('2009-9-30','YYYY-MM-DD'),1),'YYYY-MM-DD') C,
5 TO_CHAR(ADD_MONTHS(TO_DATE('2010-1-30','YYYY-MM-DD'),1),'YYYY-MM-DD') D
6 From dual;
A B C D
---------- ---------- ---------- ----------
2009-10-15 2009-12-15 2009-10-31 2010-02-28
3.3 返回指定月份最后一天函数
LAST_DAY(date)函数。该函数返回参数指定日期对应月份的最后一天。
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(SYSDA
--------------
30-6月 -21
3.4 返回指定日期后一周的日期函数
NEXT_DAY(date,char)函数。该函数返回当前日期向后的一周char的对应日期，char表示的是星期几，全称和缩写都允许。但必须有效。
SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE, '星期一') FROM DUAL;
SYSDATE NEXT_DAY(SYSDA
-------------- --------------
10-6月 -21 14-6月 -21
3.5 提取指定日期特定部分的函数
EXTRACT (datetime)函数。该函数可以从指定的时间当中提取到指定的日期部分，例如 ：从给定的日期得到年、月、分等。
SQL> Select
2 EXTRACT(YEAR FROM SYSDATE ) YEAR,
3 EXTRACT(MINUTE FROM TIMESTAMP '2010-6-18 12:23:10 ' ) MIN,
4 EXTRACT(SECOND FROM TIMESTAMP '2010-6-18 12:23:10 ' ) SEC
5 FROM DUAL;
YEAR MIN SEC
---------- ---------- ----------
2021 23 10
3.6 得到两个日期之间的月份数
MONTHS_BETWEEN(date 1 ,date2)函数。该函数返回datel和date2之间的月份数。
函数两个参数都为日期型数据。当date1>date2时，如果两个参数表示日期是某月中的同一天，或它们都是某月中的最后一天，则该函数返回一整型数，否则，将返回小数。当dateL<date2时， 则返回一负值。
SQL> select
2 MONTHS_BETWEEN(TO_DATE('2010-7-11', 'YYYY-MM-DD'), TO_DATE('2010-6-1','YYYY-MM-DD')) A,
3 MONTHS_BETWEEN(TO_DATE('2010-5-31', 'YYYY-MM-DD'), TO_DATE('2010-4-30','YYYY-MM-DD')) B,
4 MONTHS_BETWEEN(TO_DATE('2010-5-31', 'YYYY-MM-DD'), TO_DATE('2010-9-30','YYYY-MM-DD')) C
5 from dual;
A B C
---------- ---------- ----------
1.32258065 1 -4
3.7 日期四舍五入、截取函数
(1) ROUND(date[,fmt])函数
该函数将date舍入到fmt指定的形式。如果参数fmt被省略， 则date将被处理到最近的一天。
SQL> select
2 TO_CHAR(ROUND(TO_DATE('2010-5-1 21:00:00','YYYY-MM-DD HH24:MI:SS')), 'YYYY-MM-DD HH24:MI:SS')
3 FROM DUAL;
TO_CHAR(ROUND(TO_DA
-------------------
2010-05-02 00:00:00
(2) TRUNC(date[,fmt])函数
该函数将date截取到fmt指定的形式。如果fmt省略，则截取到最近的日期。
SQL> SELECT
2 TO_CHAR(TRUNC(TO_DATE('2010-5-1 21:00:00', 'YYYY-MM-DD HH24:MI:SS')),'YYYY-MM-DD HH24:MI:SS')
3 from dual;
TO_CHAR(TRUNC(TO_DA
-------------------
2010-05-01 00:00:00
4. 转换函数
转换函数可以完成不同数据类型之间的转换，是平常使用比较多的函数类型之一。
4.1 字符串转ASCII类型字符串函数
ASCIISTR(char)函数。该函数可将任意字符集的字符串转换为数据库字符集对应的ASCII 字符串。char为字符类型。
SQL> SELECT ASCIISTR('这是测试！ ') FROM DUAL;
ASCIISTR('这是测试！')
--------------------------
\8FD9\662F\6D4B\8BD5\FF01
4.2 二进制转十进制函数
BIN_TO_NUM(data[,data..J)函数。该函数可以将二进制转换成对应的十进制。
SQL> SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0,0),BIN_TO_NUM(1,1,1) FROM DUAL;
BIN_TO_NUM(1) BIN_TO_NUM(1,0,0) BIN_TO_NUM(1,1,1)
------------- ----------------- -----------------
1 4 7
4.3 数据类型转化
CAST(expr as type_name)函数。该函数是进行类型转换的，可以把expr参数转成 type_name类型。基本上用于数字与字符之间以及字符与日期类型之间的转换。
SQL> SELECT CAST('123' AS INTEGER) A, CAST(123 AS VARCHAR2(8)) B, CAST(SYSDATE AS VARCHAR2(12)) C FROM DUAL;
A B C
---------- -------- ------------
123 123 10-6月 -21
4.4 字符串在字符集间转换函数
CONVERT函数。该函数用于把字符串从一个宇符集转到另一个字符集。函数的具体语法结构是
CONVERT(char,dest_char_sest[,source_char_set])
各参数的表示含义如下：
- char：等待转换的字符。
- dest_char_sest：转变后的字符集。
- source_char_set：原字符集，如果没有该参数，则默认数据库实例字符集。
SQL> SELECT CONVERT('测试','ZHS16GBK') FROM DUAL;
CONV
----
测试
4.5 一切类型转化为字符型
TO_CHAR()函数。
SQL> select
2 to_char(sysdate,'YYYY-MM-DD') A,
3 to_char(sysdate,'HH24:MI:SS') B,
4 to_char(12.34,'99.9') C,
5 to_char(12.34) D
6 from dual;
A B C D
---------- -------- ----- -----
2021-06-10 23:50:17 12.3 12.34
4.6 字符转日期型函数
TO_DATE函数。该函数可将字符型数据转换成日期型数据。
SQL> SELECT TO_DATE('2010-7-1', 'YYYY-MM-DD') A FROM DUAL;
A
--------------
01-7月 -10
4.7 字符串转数字函数
TO_NUMBER函数。该函数将字符串转成数字。
SQL> SELECT TO_NUMBER('2456.304','9999.999') FROM DUAL;
TO_NUMBER('2456.304','9999.999')
--------------------------------
2456.304
4.8 全角转半角函数
TO_SINGLE_BYTE(char)函数。该函数将全角转为半角。
SQL> SELECT TO_SINGLE_BYTE('THIS IS A TBST') FROM DUAL;
TO_SINGLE_BYTE
--------------
THIS IS A TBST
5. NULL 函数
NULL函数是用来处理空值时比较好的选择。
5.1 返回表达式为NULL的函数
COALESCE(expr)函数。返回列表中第一个不为null的表达式。如果都为null，则返回一个null。
SQL> SELECT coalesce(null, 9-9,null) FROM DUAL;
COALESCE(NULL,9-9,NULL)
-----------------------
0
5.2 排除指定条件函数
lnnvl(condition)函数。该函数可以得到除了condition要求条件之外的数据，包括NULL 的条件，通常用于WHERE条件中。
🌰：将得到PRODUCTINFO表中数量低于70的产品， 并包含数量为NULL的数据。
select * from productInfo where lnnvl(quantity >= 70);
5.3 替换NULL值函数
NVL(expr1,expr2)函数。替换NULL值，表示如果expr1为NULL值，则返回expr2的值， 否则返回expr1的值。
该函数要求两个参数类型一致，至少相互间能进行隐式的转换，否则会 提示出错。
🌰：査询PRODUCTINFO表中的数量，如果记录中有该字段为空的，则用 0替换。
select productName,nvl(quantity,0),category from productInfo;
6. 集合函数
集合函数经常配合GROUP BY或HAVING子句使用，当然它们也可以单独使用。
6.1 求平均值函数
AVG([distinct l all ]expr)函数。该函数可求取指定列的平均值，表示某组的平均值，返回数 值类型。各参数表示的具体含义如下：
- distinct：去除重复的值。
- all；表示所有的值，包括重复的值，也是默认值。
- expr：表达式。只能是数值类型。
SQL> SELECT avg(ALL PRODUCTPRICE +100) FROM PRODUCTINFO;
AVG(ALLPRODUCTPRICE+100)
------------------------
7384.71429
🌰：使用GROUP BY子句査询各类产品的平均价格。
SQL> SELECT AVG(ALL PRODUCTPRICE) FROM PRODUCTINFO GROUP BY CATEGORY;
AVG(ALLPRODUCTPRICE)
--------------------
5665.66667
9665.66667
4999
🌰：查出价格大于2000的所有产品的平均价格。
SQL> select avg(all productPrice) from productInfo where productPrice > 2000;
AVG(ALLPRODUCTPRICE)
--------------------
8165.66667
6.2 求记录数量函数
COUNT(* l [distinct ] [all ] expr )函数。该函数可以用来计算记录的数量或某列的个数。
函数 中必须指定列名，或全选使用星号。其中各参数表示的含义如下：
- *：表示计算所有记录.
- distinct:表示去除重复的记录。
- all：代表所有的，是默认选项。
- expr：要计算的对象，通常是表的列。
🌰：査询PRODUCTINFO表的所有记录数。
SELECT COUNT(*) FROM PRODUCTINFO;
🌰：査询PRODUCTINFO表的PRODUCTPRICE字段低于3000的不重复的记录数。
SELECT COUNT(distinct PRODUCTPRICE) FROM PRODUCTINFO WHERE PRODUCTPRICE < 3000;
6.3 返回最大、最小值函数
MAX( )函数。该函数可以返回指定列中的最大值，通常都用在WHERE子句中的子査询。
🌰：査询PRODUCTINFO表中价格最高的记录：
SELECT * FROM PRODUCTINFO
WHERE PRODUCTPRICE = (SELECT MAX(PRODUCTPRICE) FROM PRODUCTINFO );
同该函数效果相反但用法一致的有MIN( )函数，此函数获取指定列中的最小值。
6.4 求和函数
SUM( )函数。该函数不同于COUNT函数，它分组计算指定列的和，如果不使用分组，则函数默认把整个表作为一组。
🌰：计算不同类型产品的数量和。
SQL> SELECT SUM(QUANTITY), CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;
SUM(ALLQUANTITY) CATEGORY
---------------- ----------
21 手机
23 电视
35 平板
7. 其他函数
7.1 返回登录名函数
USER函数。该函数返回当前会话的登录名。
SQL> SELECT USER FROM DUAL;
USER
------------------------------
SCOTT
7.2返回会话以及上下文信息函数
(1) USERENV(parameter)函数
返回当前会话的信息。
例如，当参数为Language时可以返 回当前会话对应的语言、字符集等。SESSIONID可返回当前会话ID。ISDBA可返回当前用户是否DBA。
🌰：返回当前用户是否DBA用户
SQL> SELECT USERENV('ISDBA') FROM DUAL;
USEREN
------
FALSE
(2) SYS_CONTEXT(namespace,parameter)函数
该函数可以得到Oracle已经创建的context, 名为USERENV的属性对应值。
🌰：获取当前会话对应的用户名
SQL> SELECT SYS_CONTEXT('USERENV','SESSION_USER') Session_User from dual;
SESSION_USER
--------------------------------------------------------------------------------
SCOTT
7.3 表达式匹配函数
DECODE函数。该函数的具体语法是:
DECODE(expr,search, result[,search 1, resultl] [,default])
该函数的执行过程是，当expr符合条件search时就返回result的值，该过程可以重复多个，如果最后没有匹配的结果，可以返回默认值default,注意它是一对一的匹配过程。
🌰：演示PRODUCTINFO中产品数景多于100的就显示“充足”，少于或等于100 则显示“不足”。
SQL> select productName,quantity,
2 decode(sign(quantity-100),1,'充足',-1,'不足')
3 from productInfo;
PRODUCTNAME QUANTITY DECO
-------------------- ---------- ----
华为Mate40Pro 12 不足
华为P40Pro 4 不足
华为荣耀10 5 不足
华为MatePadPro 35 不足
华为智慧屏 12 不足
华为智慧屏2代 3 不足
华为智慧屏2代 3 不足
华为智慧屏2代 5 不足
已选择8行。
