常用Oracle函数

最新推荐文章于 2023-08-07 20:19:23 发布
不熬夜会做饭的靓仔
最新推荐文章于 2023-08-07 20:19:23 发布
阅读量554
点赞数
分类专栏： sql笔记文章标签： oracle 数据库
本文链接：https://blog.csdn.net/weixin_55432951/article/details/127506709
版权
sql笔记专栏收录该内容
4 篇文章 0 订阅
订阅专栏
一、字符函数
-- 常用字符函数 --
◆1.0 SUBSTR(X,START,LENGTH) ###在X中 从START开始，截取LENGTH长度的字符串
      SELECT SUBSTR('ABCDEF',2,2) FROM DUAL;   -- BC
      SELECT SUBSTR('ABCDEF',2) FROM DUAL;   -- BCDEF
      SELECT SUBSTR('ABCDEF',-2,2) FROM DUAL;   -- EF
      SELECT * FROM EMP WHERE ENAME LIKE 'S%';
      SELECT * FROM EMP WHERE SUBSTR(ENAME , 1,1)='S';

◆2.0 LENGTH(STRING) ###计算STRING所占的字符长度：返回字符串的长度，单位是字符
      SELECT 8 ,'a', LENGTH('你好111AAA') aa FROM DUAL;
      
      select * from emp1 where  length(ename)>=3;

      
      
◆3.0 LENGTHB(STRING)###计算STRING所占的字节长度：返回字符串的长度，单位是字节
      SELECT LENGTHB('你好AAAA111') FROM DUAL;
	
	 /* 1、位：
         数据存储的最小单位。每个二进制数字0或者1就是1个位；
        2、字节：  
          8个位构成一个字节；即：1 byte (字节)= 8 bit(位)；   
          1 KB = 1024 B(字节)；    
          1 MB = 1024 KB;   (2^10 B)   
          1 GB = 1024 MB;  (2^20 B)     
          1 TB = 1024 GB;   (2^30 B)
      
        3、字符：
        a、A、中、+、*、の......均表示一个字符；
        一般 utf-8 编码下，一个汉字 字符 占用 3 个 字节；
        一般 gbk 编码下，一个汉字  字符  占用 2 个 字节；
		*/	
◆4.0 REPLACE(X,OLD,NEW) 
    #案例1：SELECT REPLACE('ABCDEFG','BC','====') FROM DUAL;
	#案例2：SELECT REPLACE ('张予曦王思聪张予曦爱上了张予曦','张予曦','张一宁') as qqq  from dual;

-- 不常用的 --
◆5.0 INSTR(X,STR,START,N) ###查找函数 ,在X中查找STR,从START开始找,第N次出现的位置,
     SELECT INSTR('ABCABCABC','B') AA FROM DUAL;
 
    SELECT INSTR('ABCABCABC','B',3) FROM DUAL;
 
    SELECT INSTR('ABCABCABC','B',3,2) FROM DUAL;
	  
	  SELECT INSTR('ABCABCABC','B',3,5) FROM DUAL; -- 0  找不到就返回0
	  
◆6.0 LTRIM(X,STR)  RTRIM(X,STR)   TRIM([STR FROM] X) ###常用于去除空格或者指定的字符
      SELECT LTRIM('ABCHELLO','ABC') FROM DUAL;  --  去掉左边指定的字符 
      SELECT RTRIM( '===HELLO=====','=') aa FROM DUAL;  --  去掉右边指定的字符 
      SELECT '    HELLO',LTRIM('    HELLO    ') aa FROM DUAL;
      
     /*select length( '  HELLO'),length(LTRIM('  HELLO')) from dual;*/
      
      
      SELECT TRIM('    HELLO    ') FROM DUAL;
      SELECT TRIM('=' FROM '===HELLO=====') aa FROM DUAL;--  去掉两边边指定的字符 
      
      
      
      

◆4.0 CONCAT(X,Y) -- 拼接函数
      SELECT CONCAT(ENAME,'很棒') FROM EMP; --- 在oracle只能拼接两个 mysql里面可以拼接多个
        SELECT CONCAT('很棒'，ENAME) FROM EMP; 
      SELECT ||'_'ENAME ||'_'|| '很棒' ||'_' || sal  FROM EMP; -- 管道符
      
      

◆5.0 LOWER(X) & UPPER(X) #### 大小写转换函数
    SELECT * FROM EMP WHERE LOWER(JOB)='CLERK';
    
    SELECT lower(ename),ename from emp

◆6.0 ASCII(X) 返回X的ASCII码
    SELECT ASCII ('A') FROM DUAL;
     SELECT ASCII ('a') FROM DUAL;

二、数值函数

◆1.0 ROUND(X,Y) ###四舍五入截取
SELECT ROUND(3.1415,3) FROM DUAL;  -- 3.142
SELECT ROUND(3.1415) FROM DUAL; -- 3
SELECT ROUND(3.1415,0) FROM DUAL; -- 3

SELECT ROUND(788556,-3) FROM DUAL;  -- 789000
SELECT ROUND(788556.100,-2) FROM DUAL;  -- 788600
SELECT ROUND(788546.100,-2) FROM DUAL;-- 788500


◆2.0 TRUNC(X,Y) ###截断
SELECT TRUNC(3.1415,3) FROM DUAL;  -- 3.141
SELECT TRUNC(788556,-3) FROM DUAL;  -- 788000
SELECT TRUNC(788556.5555) FROM DUAL;-- 788556


/*
注意：
 如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。
需要注意的是，和trunc函数不同，对截取的数字要四舍五入。 */

◆3.0 CEIL ###向上取整
SELECT CEIL (-5.2) FROM DUAL ;


◆4.0 FLOOR ###向下取整
SELECT FLOOR (5.2) FROM DUAL;


◆5.0 MOD ###取余
SELECT MOD(10,3) FROM DUAL;
SELECT MOD(-10,3) FROM DUAL;
SELECT MOD(-10,-3) FROM DUAL;


◆6.0 POWER(X,Y) ###次方
SELECT  POWER(2,3) FROM DUAL;

◆7.0 ABS(X) ###绝对值
SELECT ABS(-10) FROM DUAL;
select abs(1.5) from dual;


三、日期函数

◆1.0 SYSDATE ###系统时间
      SELECT SYSDATE FROM DUAL;
◆2.0 ADD_MONTHS(X,Y) ###月份加减 
      SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
      SELECT ADD_MONTHS(date'2020-01-09',1) FROM DUAL;
      
      
◆3.0 LAST_DAY() ###返回当月最后一天
      SELECT LAST_DAY(SYSDATE) FROM DUAL;
      
      SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;
      
      SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))  FROM DUAL;  --上月的最后一天
      SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1)) + 1 FROM DUAL; -- 当月的第一天 
      

◆4.0 NEXT_DAY //指定日期的下一个星期几 对应的日期
      SELECT NEXT_DAY(SYSDATE,'星期') FROM DUAL;
	      SELECT NEXT_DAY(date'2022-5-9','星期三') FROM DUAL;-- 2022-03-20
	            
        
◆5.0 MONTHS_BETWEEN	   ####返回两个日期之间的月份数
	  SELECT MONTHS_BETWEEN(TO_DATE('20090228', 'YYYYMMDD'), TO_DATE('20080228', 'YYYYMMDD')) AS MONTHS FROM DUAL;
    
    	  SELECT abs(MONTHS_BETWEEN(date'2022-05-01',date'2022-06-01')) from dual;
    
    
	  
◆6.0 ROUND(X,Y)  X日期值  Y代表一个格式 四舍五入 
/*
Y= YEAR   四舍五入到某年的1月1号
Y= MONTH  四舍五入到某月的1号
Y= DDD    四舍五入到某天的凌晨0点0分
Y= DAY    四舍五入到某周的第一天
Y=Q
*/ 
SELECT ROUND(SYSDATE, 'YEAR'),  -- 2021/1/1 
       round(date'2022-07-01','YEAR'),
       ROUND(SYSDATE, 'MONTH'),  -- 2021/5/1
       round(date'2022-05-16','MONTH'),
       ROUND(SYSDATE, 'DDD'),    -- 2021/5/14
       round(to_date('2022-05-09 13:20:01','yyyy-mm-dd hh24:mi:ss'),'DDD'),
       ROUND(SYSDATE, 'DAY'),     -- 2021/5/16
       ROUND(date'2022-05-12', 'DAY'),
	   ROUND(date'2022-05-16', 'Q')
  FROM DUAL;
  


  SELECT ROUND(TO_DATE('2022-4-20','YYYY-MM-DD'),'Q') FROM DUAL; -- 2022/4/1
  SELECT ROUND(TO_DATE('2022-5-14','YYYY-MM-DD'),'Q') FROM DUAL;--2022/4/1
  SELECT ROUND(TO_DATE('2022-5-16','YYYY-MM-DD'),'Q') FROM DUAL;--2022/7/1
-- 日期截断  TRUNC(X,Y)
SELECT TRUNC(SYSDATE, 'YEAR'),  -- 2021/1/1 
       TRUNC(date'2022-07-01', 'YEAR'),
       TRUNC(SYSDATE, 'MONTH'),  -- 2021/5/1
       TRUNC(SYSDATE, 'DDD'),    -- 2021/5/13
       TRUNC(SYSDATE, 'DAY') ,    -- 2021/5/9
	   TRUNC(SYSDATE, 'Q')
  FROM DUAL;
  
  


-- 案例： 上个月的第一天
   SELECT  TRUNC(ADD_MONTHS(SYSDATE,-1),'MONTH') FROM DUAL; -- 计算上个月的第一天
   SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-2)) FROM DUAL;   -- 计算上上个月的最后一天
   SELECT to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'yyyy-mm-dd') FROM DUAL;   -- 计算上个月的第一天




四、转换函数
/* 
TO_CHAR    把日期或者数值转换成字符串
TO_DATE    转换成日期
TO_NUMBER  转换成数字
 */
◆1.0 TO_CHAR
1.1.1 TO_CHAR对日期的格式进行处理 ####转换成字符串

SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;
SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY/MM/DD') FROM DUAL;
SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY_MM_DD') FROM DUAL;
SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYY年MM月DD日') FROM DUAL;
SELECT SYSDATE,TO_CHAR(DATE'2020-05-09','YYYY')||'年'||TO_CHAR(DATE'2020-05-09','MM') ||'月'FROM DUAL;
SELECT to_char(to_date('2017-05-23','YYYY-MM-DD'),'YYYY"年"MM"月"DD"日"') FROM DUAL; -- 带年月日的格式
SELECT to_char(to_date('2017-05-23','YYYY-MM-DD'), 'MM"月"DD"日" YYYY"年"') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MMDD') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS NOWTIME FROM DUAL; -- 24小时制  //日期转化为字符串  
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') AS NOWTIME FROM DUAL;  -- 12小时制
SELECT TO_CHAR(SYSDATE,'YYYY') AS NOWYEAR   FROM DUAL;   //获取时间的年   
SELECT TO_CHAR(SYSDATE,'MM')    AS NOWMONTH FROM DUAL;   //获取时间的月   
SELECT TO_CHAR(SYSDATE,'DD')    AS NOWDAY    FROM DUAL;   //获取时间的日   
SELECT TO_CHAR(SYSDATE,'HH24') AS NOWHOUR   FROM DUAL;   //获取时间的时   
SELECT TO_CHAR(SYSDATE,'MI')    AS NOWMINUTE FROM DUAL;   //获取时间的分   
SELECT TO_CHAR(SYSDATE,'SS')    AS NOWSECOND FROM DUAL;   //获取时间的秒 


1.1.2 TO_CHAR对数字的格式进行处理
select 2222.00 from dual;
select to_char(2222.00) from dual;

SELECT TO_CHAR(2222.00,'FM9999999999.00') FROM DUAL;
SELECT TO_CHAR(123233455623.3400,'$999,999,999,999.0000') FROM DUAL;
SELECT TO_CHAR(12323345.3400,'L999999999999.0000') FROM DUAL;
SELECT '¥'||TO_CHAR(2222.00,'FM999999999.00') FROM DUAL;


-- 查询1981年入职的人员
  SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1982';
-- 查询1981年以后入职的人员
  SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYYMMDD')>'19811231';
 
  select HIREDATE, TO_CHAR(HIREDATE,'YYYYMMDD')from emp;
  

  
  
 
◆2.0 TO_DATE ### 对字符类型的数据进行处理
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL;
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL;
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL
SELECT TO_DATE('20210401','YYYY/MM/DD') FROM DUAL;
SELECT TO_DATE('20210401','YYYYMMDD') FROM DUAL;

-- 查询1980年12月17日入职的人员
  SELECT * FROM EMP WHERE HIREDATE=TO_DATE('19801217','YYYYMMDD');
    SELECT * FROM EMP WHERE HIREDATE=date'1980-12-17';
  
-- 查询1981年以后入职的人员
  SELECT * FROM EMP WHERE HIREDATE>TO_DATE('1981/12/31','YYYY/MM/DD');
  
  
  

◆3.0 TO_NUMBER  对字符类型的数据进行处理

/* 
TO_NUMBER函数中也有很多预定义的固定格式：
格式值	 含义
  9	    代表一个数字
  0	    强迫0显示
  $	    显示美元符号
  L	    强制显示一个当地的货币符号
  .	    显示一个小数点
  ,	   显示一个千位分隔符号 
  */

SELECT TO_NUMBER('1234') , TO_NUMBER('1234') + 1234,'1234'+1000  FROM DUAL;
SELECT TO_NUMBER('¥234234.4350','L999999.0000') FROM DUAL;
SELECT TO_NUMBER('$123,233,455,623.3400','$999,999,999,999.0000') FROM DUAL;
SELECT cast( TO_NUMBER('$123233.3400','$999999999999.0000') as number(10,4)) FROM DUAL;
SELECT cast( TO_NUMBER('$123233.3400','$999999999999.0000') as varchar2(100)) FROM DUAL;

4.0 CAST()函数：可以进行数据类型的转换。
一、转换列或值
--语法：cast( 列名/值 as 数据类型 )
-- CAST()函数的参数有两部分，源值和目标数据类型，中间用AS关键字分隔。
SELECT CAST('123' AS NUMBER(10,2)) as result from dual;
SELECT EMPNO,CAST(EMPNO AS VARCHAR2(10)) AS EMPNO FROM EMP1;




五、 空值处理函数
◆1.0 空值转换
●1.1 NVL(X,Y)  如果X为空就显示Y，不为空就还是X
SELECT COMM,NVL(COMM,0) FROM EMP;
SELECT COMM, CAST(NVL(COMM,0) AS NUMBER(10,2)) AA FROM EMP1;

-- 计算月收入
SELECT ENAME , SAL+NVL(COMM,0) FROM EMP;

●1.2 NVL2(X,Y1,Y2)  如果X不为空就显示Y1  为空就Y2
-- 查询每个人有没有奖金，有返回是，没有返回否
SELECT ENAME , COMM, NVL2(COMM,'是','否') FROM EMP;


select comm, nvl2(comm,1001,9999), nvl(comm,9999) from emp1







-- 如果有奖金就+100块，没奖金就+200
SELECT ENAME ,COMM,NVL2(COMM,COMM+100,200) FROM EMP;


#六，流程函数
/*CASE WHEN 和 DECODE()的区别：
     CASE WHEN 是一种条件判断
     DECODE()  是一种等值判断


#语法：decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

代码格式：   
    CASE WHEN JOB = 'PRESIDENT' THEN  '总裁级'

    CASE JOB  WHEN 'PRESIDENT' THEN  '总裁级'
      
    DECODE(JOB,'PRESIDENT','总裁级')*/
 
--1.0 案例1： 
查出员工的工作类型，
如果是 PRESIDENT 是总裁级，
       MANAGER 中层管理，
       ANALYST  分析师， 
       SALESMAN 销售人员，
       其他的岗位类型为 临时工
       
SELECT E.ENAME,
       E.JOB,
       DECODE(E.JOB,
              'PRESIDENT',
              '总裁级',
              'MANAGER',
              '中层管理',
              'ANALYST',
              '分析师',
              'SALESMAN',
              '销售人员',
              '临时工') 岗位类型1,
       CASE E.JOB
         WHEN 'PRESIDENT' THEN '总裁级'
         WHEN 'MANAGER'   THEN '中层管理'
         WHEN 'ANALYST'   THEN '分析师'
         WHEN 'SALESMAN'   THEN '销售人员'
         ELSE '临时工' END 岗位类型2,
		 
		 CASE 
         WHEN JOB='PRESIDENT' THEN '总裁级'
         WHEN JOB='MANAGER'   THEN '中层管理'
         WHEN JOB='ANALYST'   THEN '分析师'
         WHEN JOB='SALESMAN'   THEN '销售人员'
         ELSE '临时工' END 岗位类型3 	 
  FROM EMP E;
  
  
-- 案例2:假设公司想给这些职员加工资，其标准是：
     工资在3000元以下的加20%；工资在3000元或以上的加15%，
SELECT 
     E.ename ,
     E.sal,
     CASE WHEN E.sal >= 3000 THEN E.sal*1.15
          ELSE E.sal*1.2
      END 加薪后1,
     DECODE(SIGN(E.sal -3000) , -1,E.sal*1.2 ,E.sal*1.15 ) 加薪后2
FROM EMP E
ORDER BY e.sal DESC;

SELECT SIGN(99-1) , SIGN(3-99), SIGN(100-100) FROM DUAL; --sign 函数返回一个数字的正负标志.
         





--19.0： 统计team表中各个球队的胜负情况，要求按如下格式显示
/*

create table team (sbid number, sname varchar2(50),sresult varchar2(50),scoutry varchar2(50));
insert into TEAM (sname, sresult,scoutry,sbid)values ('奇才' , '胜' , 'CN', 4);
insert into TEAM (sname, sresult,scoutry,sbid)values ('奇才' , '胜' , 'CN', 6);
insert into TEAM (sname, sresult,scoutry,sbid)values ('湖人' , '胜' , 'CN', 5);
insert into TEAM (sname, sresult,scoutry,sbid)values ('火箭' , '胜' , 'CN', 3);
insert into TEAM (sname, sresult,scoutry,sbid)values ('火箭' , '败' , 'CN', 1);
insert into TEAM (sname, sresult,scoutry,sbid)values ('火箭' , '败' , 'CN', 2);
commit;

结果显示：
 TNAME      | 胜 |  负
 --------+------+------
  火箭   |    1 |    2
  奇才   |    2 |    0
  湖人   |    1 |    0

*/
select * from team

select 
t.sname as tname,
sum(case when t.sresult='胜' then 1 else 0 end) as 胜,
sum(case when t.sresult='败' then 1 else 0 end) as 负
from team t
group by t.sname
order by 负 desc,胜 desc