十七、Oracle内置函数

向右看齐 2022-04-13 03:58 436阅读 0赞

#                                              Oracle内置函数 #

### 1、数值函数 ###

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

### 2、字符函数 ###

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

### 3、日期函数 ###

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

### 3、字符函数 ###

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

### 4、NULL函数 ###

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

### 5、集合函数 ###

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220413/5ff1c473a8a54792926bf35fc14de496.png