十七、Oracle内置函数

                                             Oracle内置函数

1、数值函数

绝对值函数&＃xff1a;SELECT ABS(-100),ABS(-100),ABS(&＃39;-100&＃39;) FROM dual取余函数&＃xff1a;SELECT MOD(-100,6),MOD(-100,10),MOD(&＃39;-100&＃39;&＃xff0c;3) FROM dual正弦函数&＃xff1a;SELECT SIN(9),SIN(&＃39;9&＃39;) FROM dual天花板函数&＃xff1a;SELECT CEIL(10),CEIL(10.2),CEIL(-10.2) FROM dual地板函数&＃xff1a;SELECT FLOOR(10),FLOOR(10.2),FLOOR(-10.2) FROM dual平方根函数&＃xff1a;SELECT SQRT(10),SQRT(3) FROM DUAL指数函数&＃xff1a;SELECT POWER(2,2),POWER(3,3) FROM dual对数函数&＃xff1a;SELECT LOG(10,100),LOG(10,2) FROM dual取整函数&＃xff1a;SELECT ROUND(100.2435,4),ROUND(100.2345,-2),ROUND(100.2345,2.25) FROM dual


2、字符函数

ASCII码转字符函数&＃xff1a;SELECT CHR(65) FROM dual字符转ASCII码函数&＃xff1a;SELECT ASCII(&＃39;杨&＃39;),ASCII(&＃39;A&＃39;) FROM dual字符串长度函数&＃xff1a;SELECT LENGTH(&＃39;字符串长度&＃39;) FROM dual截取字符串函数&＃xff1a;SELECT SUBSTR(&＃39;截取字符串长度&＃39;,1,5) FROM dual连接字符串函数&＃xff1a;SELECT CONCAT(&＃39;连接&＃39;,&＃39;字符串&＃39;),&＃39;连接&＃39;||&＃39;字符串&＃39; FROM dual 搜索函数&＃xff1a;SELECT INSTR(&＃39;查看字符串在目标字符串中的位置&＃39;,&＃39;目标&＃39;) FROM dual倒搜索函数&＃xff1a;SELECT INSTR(&＃39;查看字符串在目标字符串中的位置&＃39;,&＃39;目标&＃39;&＃xff0c;-1) FROM dual替换函数&＃xff1a;SELECT REPLACE(&＃39;这是要被替换的&＃xff0c;这还是。&＃39;,&＃39;是&＃39;,&＃39;77&＃39;)FROM dual右连接字符函数&＃xff1a;SELECT RPAD(&＃39;连接字符&＃39;,16,&＃39;连到右边&＃xff0c;共八位长度&＃39;) FROM dual左连接字符函数&＃xff1a;SELECT LPAD(&＃39;连接字符&＃39;,16,&＃39;连到右边&＃xff0c;共八位长度&＃39;) FROM dual左右去空格&＃xff1a;SELECT TRIM(&＃39; TEST &＃39;) FROM dual去除指定后缀&＃xff1a;SELECT TRIM(TRAILING &＃39;T&＃39; FROM &＃39;TEST&＃39;) FROM dual去除指定前缀&＃xff1a;SELECT TRIM(LEADING &＃39;T&＃39; FROM &＃39;TEST&＃39;) FROM dual去除指定前后缀&＃xff1a;SELECT TRIM(BOTH &＃39;T&＃39; FROM &＃39;TEST&＃39;) FROM dual


3、日期函数

--系统日期&＃xff1a;
SELECT sysdate FROM DUAL--系统timestamp&＃xff1a;
SELECT systimestamp FROM DUAL--数据库时区&＃xff1a;
SELECT dbtimezone FROM DUAL--当前时间&＃xff1a;
SELECT CURRENT_DATE FROM DUAL--SESSIONTIMEZONE函数(当前会话时区)&＃xff1a;
SELECT SESSIONTIMEZONE FROM DUAL--to_char函数&＃xff1a;
SELECT to_char(sysdate,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) FROM DUAL--ADD_MONTHS函数&＃xff08;为日期加上月&＃xff09;&＃xff1a;
SELECT to_char(ADD_MONTHS(TO_DATE(&＃39;2018-10-12&＃39;,&＃39;YYYY-MM-DD&＃39;),2),&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) FROM DUAL--LAST_DAY函数&＃xff1a;
SELECT LAST_DAY(SYSDATE) FROM DUAL--NEXT_DAY函数&＃xff08;返回指定日期下周的星期几&＃xff09;&＃xff1a;
SELECT SYSDATE,NEXT_DAY(SYSDATE,&＃39;星期二&＃39;) from dual--EXTRACT函数&＃xff1a;
select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr from systimestamp) ta
from dual --months_between函数计算两个日期之间的相差的月数&＃xff1a;
select months_between(to_date(&＃39;2014-3-21&＃39;,&＃39;yyyy-mm-dd&＃39;), to_date(&＃39;2014-1-10&＃39;,&＃39;yyyy-mm-dd&＃39;)) ONE,months_between(to_date(&＃39;2014-1-10&＃39;,&＃39;yyyy-mm-dd&＃39;), to_date(&＃39;2014-3-21&＃39;,&＃39;yyyy-mm-dd&＃39;)) TWO,months_between(to_date(&＃39;2014-1-10&＃39;,&＃39;yyyy-mm-dd&＃39;), to_date(&＃39;2014-1-10&＃39;,&＃39;yyyy-mm-dd&＃39;)) THREE from dual;--new_time函数&＃xff1a;
select to_char(sysdate, &＃39;yyyy.mm.dd hh24:mi:ss&＃39;) bj_time,to_char(new_time(sysdate, &＃39;PDT&＃39;, &＃39;GMT&＃39;), &＃39;yyyy.mm.dd hh24:mi:ss&＃39;) los_anglesfrom dual;--Trunc函数&＃xff1a;四舍五入
SELECT TRUNC(SYSDATE), -- 今天的日期为 2014-05-08TRUNC(SYSDATE, &＃39;YYYY&＃39;), --返回当年第一天 2014-01-01 yyyy/yy/year 年TRUNC(SYSDATE, &＃39;MM&＃39;), --返回当月第一天 2014-05-01 mm/month 月TRUNC(SYSDATE, &＃39;DD&＃39;), --返回当前年月日 2014-05-08 d/day 周TRUNC(SYSDATE, &＃39;D&＃39;), --(星期天)返回当前星期的第一天 2014-05-04TRUNC(SYSDATE, &＃39;HH&＃39;), --返回当前时间精确小时 2014-05-08 11:00:00TRUNC(SYSDATE, &＃39;MI&＃39;) -- 返回当前时间精确分钟 2014-05-08 11:06:00 TRUNC()函数没有秒的精确 FROM DUAL;--Round函数&＃xff1a;只是
select Round(sysdate, &＃39;Q&＃39;) Rnd_Q, --2014-04-01Round(sysdate, &＃39;Month&＃39;) Rnd_Month, --2014-05-01Round(sysdate, &＃39;WW&＃39;) Rnd_Week, --2014-05-07Round(sysdate, &＃39;W&＃39;) Rnd_Week_again, --2014-05-08Round(sysdate, &＃39;DDD&＃39;) Rnd_day, --2014-05-08Round(sysdate, &＃39;DD&＃39;) Rnd_day_again, --2014-05-08Round(sysdate, &＃39;DAY&＃39;) Rnd_day_of_week, --2014-05-11Round(sysdate, &＃39;D&＃39;) Rnd_day_of_week_again, --2014-05-11Round(sysdate, &＃39;HH12&＃39;) Rnd_hour_12, --2014-05-08 12:00:00Round(sysdate, &＃39;HH24&＃39;) Rnd_hour_24, --2014-05-08 12:00:00Round(sysdate, &＃39;MI&＃39;) Rnd_minute --2014-05-08 11:52:00from dual;


3、字符函数

--字符串转ASCII类型字符串函数
SELECT ASCIISTR(&＃39;测试字符串转ASCII编码&＃39;) from dual--二进制转十进制函数
SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,1),BIN_TO_NUM(1,1,1) FROM DUAL --数据类型转换函数
SELECT CAST(&＃39;123.4567&＃39; AS NUMBER(10,2)),CAST(123.4567 AS VARCHAR2(8)),CAST(SYSDATE AS VARCHAR2(14)) FROM DUAL;--CHARTOROWID
SELECT CHARTOROWID(&＃39;AAAToYAAEAAAALzAAC&＃39;) FROM DUAL--ROWIDTOCHAR&＃xff0c;返回vchar类型
SELECT ROWIDTOCHAR(ROWID) FROM SYS_DECLARE--返回vchar2类型
SELECT ROWIDTONCHAR(ROWID) FROM SYS_DECLARE--字符串在字符集间的转换函数&＃xff0c;US7ASCII目标字符类型&＃xff0c;WE8ISO8859P1原字符
SELECT CONVERT(&＃39;测试字符串&＃61;&＃61;&＃61;&＃61;&＃61;&＃39;,&＃39;US7ASCII&＃39;, &＃39;WE8ISO8859P1&＃39;) FROM DUAL--to_date函数
select to_date(&＃39;20170615&＃39;,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;) from dual;//mi是分钟
select to_date(&＃39;20170615&＃39;,&＃39;yyyy-MM-dd HH24:mm:ss&＃39;) from dual;//mm会显示月份--to_number函数
select to_number(&＃39;000012134&＃39;) from dual;
select to_number(&＃39;88877&＃39;) from dual; --TO_CHAR函数
select TO_CHAR(sysdate, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) from dual
select TO_CHAR(123,&＃39;$99,999.9&＃39;) from dual;


4、NULL函数

--返回表达式列表中&＃xff0c;结果不为null的表达式
SELECT COALESCE(NULL,9-1,NULL) FROM DUAL--LNNVL函数&＃xff1a;排除指定条件的数据&＃xff08;null也排除&＃xff09;
select * from EMP t where LNNVL(t.deptno>10)--NVL函数值替换&＃xff1a;如果comm为null用-1替代
select ename,NVL(comm, -1) from emp;--NVL2函数&＃xff1a;第一个comm为空用-1&＃xff0c;不为空用第二个commonselect ename,nvl2(comm,comm,-1) from emp;


5、集合函数

--AVG函数&＃xff1a;计算指定列值的平均数--COUNT函数&＃xff1a;计算指定列的记录条数--MAX、MIN计算指定列中的最大或者最小值