oracle函数总结：有精确小数位的函数,字符串截取函数,大小写转换函数,字符串截取匹配函数,日期函数，系统

最新推荐文章于 2023-06-28 18:12:34 发布
涡轮5
最新推荐文章于 2023-06-28 18:12:34 发布
阅读量3.5k
点赞数
分类专栏：数据库
本文链接：https://blog.csdn.net/xuxurui007/article/details/8528799
版权
数据库专栏收录该内容
26 篇文章 0 订阅
订阅专栏
SELECT MOD(25,5) FROM DUAL;  
SELECT ROUND(40.66),ROUND(40.66,1),ROUND(40.66,-1) ,ROUND(140.66,-2) FROM DUAL;  
--Round函数(用于四舍五入),右边的数字为正数，保留几位有效数字,右边的值为负数，精确的是整数位  
SELECT TRUNC(40.66),TRUNC(40.66,1),TRUNC(40.66,-1),TRUNC(140.66,-2) FROM DUAL;  
--TRUNC函数(不进行四舍五入),右边的数字为正数，保留几位有效数字,右边的值为负数，精确的是整数位  
SELECT Upper('hello WORLD') FROM DUAL;  
-- Upper小写转大写  
SELECT Lower('hello WORLD') FROM DUAL;  
-- Lower大写转小写  
SELECT LPAD('hello WORLD',8), LPAD('hello WORLD',18,'X') FROM DUAL;  
--截取左侧N个字符,如果字符数量不够从左侧补充空格，也可以用某一字符替换  
SELECT RPAD('hello WORLD h',8), RPAD('hello WORLD',18,'X') FROM DUAL;  
--截取左侧N个字符,如果字符数量不够从右侧补充，也可以用某一字符替换  
SELECT TRIM(leading 'h' FROM 'hhhello WORLD h') FROM DUAL;  
SELECT TRIM(trailing 'h' FROM 'hhhello WORLD h') FROM DUAL;  
SELECT TRIM(both 'h' FROM 'hello WORLD h') FROM DUAL;  
--去掉首尾字符，常用于去掉首尾空格,也可以指定去掉首或尾  
SELECT LTRIM('hhheeeeello WORLD h','hel') FROM DUAL;  
--去掉左侧按照字符串中字符顺序的字符  
SELECT RTRIM('hhheeeeello WORLD h','hel') FROM DUAL;  
--去掉右侧按照字符串中字符顺序的字符  
SELECT SUBSTR('hello WORLD',1,3), SUBSTR('hello WORLD',-1,3) FROM DUAL;  
--默认从左侧开始截取,当为负数时从右侧截取  
SELECT REPLACE('hhheeeeello WORLD h','o','') FROM DUAL;  
--字符串替换,当替换的字符为空时,可以用来删除存在的特定字符,不支持大小写  
SELECT TRANSLATE('hhheeeeello WORLDhedh','hed','QP') FROM DUAL;  
--绝对匹配,左侧第一个字符对应右侧第一个,第二个对应第二个,第三个对应字符没有即对应空  
SELECT INSTR('abcdefgasdasd','as',1),INSTR('abcdefgasdasd','as',-1) FROM DUAL;  
--找匹配字符串,正数为从左边开始找的第一个匹配的字符串,负数是从右边开始找第一个匹配的字符串  
SELECT LENGTH('A123我方'), LENGTH(''), LENGTH(' ') FROM DUAL;  
--查找字符串长度,这里中文也占1位,为空和为NULL的值是不一样的  
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;  
--日期型转化为字符型,后面是格式  
SELECT TO_NUMBER('100') FROM DUAL;  
--字符型转换为日期型  
SELECT TO_DATE('20111110', 'yyyy-mm-dd ') FROM DUAL;  
--字符型转换成日期型,后面是格式  
select decode('a2','a1','false1','a2','true1','defalut') from dual;  
--假如a2=a1,返回false1,如果a2=a2，返回true1,都不满足返回defalut。相当与if...elseif...else  
SELECT NVL('13', '12') FROM DUAL;  
--假如非空,则返回原值,否则返回后面的  
   
   
--不常用函数  
case... when ...then else ...end  
select empno, ename, sal,  
case deptno  
when 10 then '财务部'  
when 20 then '研发部'  
when 30 then '销售部'  
else '未知部门'  
end 部门  
from emp;  
 Oracle数据库中日期的默认格式为DD-MON-YY。可以通过设置NLS_DATE_FORMAT参数设置当前会话的日期格式，通过NLS_LANGUAGE参数设置表示日期的字符集。  
--例如：  
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';  
alter session set nls_language='AMERICAN';  
Month_between 返回整數  
SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;  
--正数表示返回12月之后的函数,负数表示返回12月之前的函数  
select next_day(sysdate,'星期一') from dual  
--next_day 返回从 Sysdate 开始的下一个星期一，亦可以用数字表示，1表示下个星期日，2表示下个星期一  
select current_date from dual;  
SELECT SYSDATE,CURRENT_DATE FROM DUAL;  
--sysdate和current_date可能相差一秒  
SELECT last_day(SYSDATE) FROM DUAL;  
--当前月的最后一天  
SELECT NEXT_DAY(SYSDATE,1),NEXT_DAY(SYSDATE,4),NEXT_DAY(SYSDATE,'Thursday'),next_day(sysdate, 'Friday ') FROM DUAL;  
--如果为数字,返回下周的某一天(周日为1,周一为2,依次计算),否则返回从这周开始找,如果在这周返回这周的日期;否则，返回下一个星期的星期几  
SELECT  months_between(SYSDATE, add_months(sysdate, -1)) FROM DUAL;  
--计算月份之间相差多少  
SELECT GREATEST(15,5,75,8) "Max",LEAST(15,5,75,8) "Min" FROM DUAL;  
--取出字符序列中的最大值、最小值  
SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;  
--假如取的值和前一个字符相同,则返回NULL,否则返回字符值  
select nvl2('A', 'B', 'C'),nvl2(null, 'B', 'C')  from dual;  
--如果A等于B,则返回  
SYS_CONNECT_BY_PATH(col,c)  
-- 该函数只能应用于树状查询  
select  
 SYS_CONTEXT('USERENV','TERMINAL') terminal,  
 SYS_CONTEXT('USERENV','DB_NAME') db_name,  
 SYS_CONTEXT('USERENV','HOST') host,  
 SYS_CONTEXT('USERENV','OS_USER') os_user,  
 SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,  
 SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol  
 from dual ;  
--返回当前用户信息,还有好几项如：language等不列举了
------------------------------------------------------------------------------------------------
SELECT d.bussno,    
       (SELECT substr(to_char(l.makedate,'YYYY-MM-DD'), 0, 10) || ' ' || l.maketime    
          FROM ocr_loaddetail l    
         WHERE l.bussno = d.bussno    
           and l.loadstate = 0) as loaddate,    
       --载入时间，拼出日期和时间    
       (substr(to_char(d.recogenddate,'YYYY-MM-DD'), 0, 10) || ' ' || d.recogendtime) as regdate,    
       (substr(to_char(d.proofstartdate,'YYYY-MM-DD'), 0, 10) || ' ' || d.proofstarttime) as psdate,    
       (substr(to_char(d.proofenddate,'YYYY-MM-DD'), 0, 10) || ' ' || d.proofendtime) as pedate,    
       (substr(to_char(d.givehlisdate,'YYYY-MM-DD'), 0, 10) || ' ' || d.givehlistime) as ghdate,    
       d.proofoperator,    
       round(to_number(to_date((substr(to_char(d.proofenddate,'YYYY-MM-DD'), 0, 10) || ' ' || d.proofendtime),'yyyy-mm-dd hh24:mi:ss')-    
       to_date((substr(to_char(d.proofstartdate,'YYYY-MM-DD'), 0, 10) || ' ' || d.proofstarttime),'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60) as aging,    
       --round()取整，小数部分四舍五入。to_number()转化成数字。to_date()将字符串转化成时间。to_char()日期转化成字符串。substr()拼接字符串    
       round(to_number(to_date((substr(to_char(d.givehlisdate,'YYYY-MM-DD'), 0, 10) || ' ' ||d.givehlistime),'yyyy-mm-dd hh24:mi:ss') -    
                 to_date((SELECT substr(to_char(l.makedate,'YYYY-MM-DD'), 0, 10) || ' ' || l.maketime FROM ocr_loaddetail l WHERE l.bussno = d.bussno and l.loadstate = 0),'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60) as sysdealtime    
  FROM ocr_docdetail d    
 WHERE 1 = 1    
   and d.ProofStartDate >= date '2011-10-28'    
   and d.ProofStartDate <= date '2013-01-21'    
   and d.managecom like '86%'    
 order by d.managecom asc, d.proofstartdate asc