AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle学习(三)：单行函数

2022-05-17 86
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 本节主要讲Oracle单行函数

1.知识点：可以对照下面的录屏进行阅读

SQL> --字符函数
SQL> --字符串的转换
SQL> select lower('hellO WORld') 转小写,upper('hellO WORld') 转大写,initcap('hello world') 首字母大写
  2  from dual;
SQL> --substr(a,b) 从a中，第b位开始取，取右边所有的字符
SQL> select substr('Hello World',4)  from dual;
SQL> --substr(a,b,c) 从a中，第b位开始取，取c位
SQL> select substr('Hello World',4,4)  from dual;
SQL> --length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符数, lengthb('Hello World') 字节数
  2  from dual;
SQL> --instr 在母串中查找子串，找到返回下标（从1开始），否则返回0
SQL> select instr('Hello World','ll')  from dual; 
SQL> --lpad 左填充  rpad 右填充
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 --向’abcd’字符串的左/右填充’*’，填充到整个字符串的长度为10
  2  from dual;
SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;  --去掉 'Hello WorldH'中所有的’H’
SQL> --replace 替换指定位置的字符
SQL> select replace('Hello World','l','*') from dual; --将'Hello World'中的’l’替换为’*’
SQL> --数字函数
SQL> --四舍五入，第一个参数为要进行四舍五入的数，第二个参数为保留的位数，大于0的为小数点后，小于0的为小数点前的
SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三, 
  2         ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
  3  from dual;
SQL> --截断，第一个参数为要进行截断的数，第二个参数为保留的位数，大于0的为小数点后，小于0的为小数点前的
SQL> select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三,
  2         TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五
  3  from dual
SQL> --日期函数
SQL> --查询当前日期
SQL> select sysdate from dual;
SQL> --查询当前时间，具体到时分秒
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
SQL> --systimestamp时间戳，更精确
SQL> select systimestamp from dual;
SQL> --昨天 今天  明天
SQL> select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual;
SQL> --员工的工龄，
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
  2         (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
  3  from emp;
SQL> --日期不能进行相加
SQL> select hiredate+sysdate from emp;
select hiredate+sysdate from emp
            *
第 1 行出现错误: 
ORA-00975: 不允许日期 + 日期 
SQL> --last_day：本月的最后一天
SQL> select last_day(sysdate) from dual;
SQL> --MONTHS_BETWEEN ：计算两个时间之间相差的月份
SQL> select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二   --计算工龄（粗略计算和精确计算）
  2  from emp;
SQL> --add_months：指定时间加上指定月份
SQL> select add_months(sysdate,114) from dual;    --查询当前时间加上114个月份后的时间
SQL> --next_day：指定日期的下一个日期
SQL> select next_day(sysdate,'星期二') from dual;  --从今天开始算，下一个星期二
SQL> --对日期进行四舍五入
SQL> select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual; --按月，年进行四舍五入
SQL> --隐式转换的前提： 被转换对象是可以转换的
SQL> --varchar2 or char ->number  ; varchar2 or char ->date ; number -> varchar2 ; date ->varchar2 ; 
SQL> --显式转换
SQL> --number to_char character ; character to_number number ; date to_char character ; char to_date date ;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual; --将date转为char
SQL> --to_char函数中经常使用的格式：9 数字；0 零；$ 美元符；L 本地货币；. 小数点；, 千位符；
SQL> --查询员工的薪水： 货币符号，两位小数，千位符
SQL> select sal, to_char(sal,'L9,999.99') from emp;
SQL> --通用函数
SQL> --nvl2(a,b,c) 当a=null时，返回c，否则返回b
SQL> select sal*12+nvl2(comm,comm,0)  from emp;
SQL> --nullif(a,b) 当a=b时，返回null，否则返回a
SQL> select nullif('abc','abc') from dual;
SQL> --COALESCE 从左至右找到第一个不为null的值，如果全为空，则返回空
SQL> select comm,sal,COALESCE(comm,sal) from emp;
SQL> --case语句：
SQL> --根据职位涨工资 总裁1000 经理800 其他400
SQL> select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
  2                                          when 'MANAGER' then sal+800
  3                                          else sal+400
  4                                 end 涨后薪水
  5  from emp;
SQL> --decode：功能和case一样，第一个参数为表达式，之后每两个为一对：条件，值；最后一个为缺省值，相当于else的值
SQL> select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,
  2                                           'MANAGER', sal+800,
  3                                                      sal+400)涨后薪水
  4  from emp;

2.在Sqlplus下实际执行的结果录屏： 

SQL> --字符函数
SQL> select lower('hellO WORld') 转小写,upper('hellO WORld') 转大写,initcap('hello world') 首字母大写
  2  from dual;
转小写      转大写      首字母大写                                              
----------- ----------- -----------                                             
hello world HELLO WORLD Hello World                                             
SQL> host cls
SQL> --substr(a,b) 从a中，第b位开始取，取右边所有的字符
SQL> select substr('Hello World',4)  from dual;
SUBSTR('                                                                        
--------                                                                        
lo World                                                                        
SQL> --substr(a,b,c) 从a中，第b位开始取，取c位
SQL> select substr('Hello World',4,4)  from dual;
SUBS                                                                            
----                                                                            
lo W                                                                            
SQL> host cls
SQL> --length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符数, lengthb('Hello World') 字节数
  2  from dual;
    字符数     字节数                                                           
---------- ----------                                                           
        11         11                                                           
SQL> ed
已写入 file afiedt.buf
  1  select length('北京') 字符数, lengthb('北京') 字节数
  2* from dual
SQL> /
    字符数     字节数                                                           
---------- ----------                                                           
         2          4                                                           
SQL> host cls
SQL> --instr 在母串中查找子串，找到返回下标（从1开始），否则返回0
SQL> select instr('Hello World','ll')  from dual;
INSTR('HELLOWORLD','LL')                                                        
------------------------                                                        
                       3                                                        
SQL> host cls
SQL> --lpad 左填充  rpad 右填充
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右
  2  from dual;
左         右                                                                   
---------- ----------                                                           
******abcd abcd******                                                           
SQL> host cls
SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;
TRIM('H'FR                                                                      
----------                                                                      
ello World                                                                      
SQL> select replace('Hello World','l','*') from dual;
REPLACE('HE                                                                     
-----------                                                                     
He**o Wor*d                                                                     
SQL> host cls
SQL> --数字函数
SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三
  2         ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
  3  from dual;
       ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
       *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 
SQL> ed
已写入 file afiedt.buf
  1  select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三,
  2         ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
  3* from dual
SQL> /
        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.93       45.9         46         50          0                          
SQL> ed
已写入 file afiedt.buf
  1  select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三,
  2         TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五
  3* from dual
SQL> /
        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.92       45.9         45         40          0                          
SQL> host cls
SQL> --日期函数
SQL> select sysdate from dual;
SYSDATE                                                                         
--------------                                                                  
22-10月-12                                                                      
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
  2  ;
TO_CHAR(SYSDATE,'YY                                                             
-------------------                                                             
2012-10-22 15:39:21                                                             
SQL> select systimestamp from dual;
SYSTIMESTAMP                                                                    
---------------------------------------------------------------------------     
22-10月-12 03.40.05.406000 下午 +08:00                                          
SQL> host cls
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY                                                             
-------------------                                                             
2012-10-22 15:40:33                                                             
SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') from dual;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM                                                   
-----------------------------                                                   
2012-10-22 15:40:47*218000                                                      
SQL> --systimestamp时间戳
SQL> --昨天 今天  明天
SQL> select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual;
昨天           今天           明天                                              
-------------- -------------- --------------                                    
21-10月-12     22-10月-12     23-10月-12                                        
SQL> --员工的工龄
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
  2         (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
  3  from emp;
ENAME      HIREDATE               天       星期         月         年           
---------- -------------- ---------- ---------- ---------- ----------           
SMITH      17-12月-80     11632.6571 1661.80815 387.755235 31.8702933           
ALLEN      20-2月 -81     11567.6571 1652.52244 385.588569 31.6922111           
WARD       22-2月 -81     11565.6571 1652.23672 385.521902 31.6867317           
JONES      02-4月 -81     11526.6571 1646.66529 384.221902 31.5798824           
MARTIN     28-9月 -81     11347.6571 1621.09387 378.255235 31.0894714           
BLAKE      01-5月 -81     11497.6571 1642.52244 383.255235 31.5004303           
CLARK      09-6月 -81     11458.6571 1636.95101 381.955235  31.393581           
SCOTT      13-7月 -87     9233.65706 1319.09387 307.788569 25.2976906           
KING       17-11月-81     11297.6571 1613.95101 376.588569 30.9524851           
TURNER     08-9月 -81     11367.6571 1623.95101 378.921902 31.1442659           
ADAMS      13-7月 -87     9233.65706 1319.09387 307.788569 25.2976906           
ENAME      HIREDATE               天       星期         月         年           
---------- -------------- ---------- ---------- ---------- ----------           
JAMES      03-12月-81     11281.6571 1611.66529 376.055235 30.9086495           
FORD       03-12月-81     11281.6571 1611.66529 376.055235 30.9086495           
MILLER     23-1月 -82     11230.6571 1604.37958 374.355235 30.7689235           
已选择14行。
SQL> select hiredate+sysdate from emp;
select hiredate+sysdate from emp
               *
第 1 行出现错误: 
ORA-00975: 不允许日期 + 日期 
SQL> host cls
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA                                                                  
--------------                                                                  
31-10月-12                                                                      
SQL> --MONTHS_BETWEEN 计算工龄
SQL> select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二
  2  from emp;
        一         二                                                           
---------- ----------                                                           
387.755329 382.182577                                                           
385.588662 380.085802                                                           
385.521996        380                                                           
384.221996 378.666448                                                           
378.255329 372.827738                                                           
383.255329 377.698706                                                           
381.955329 376.440641                                                           
307.788662 303.311609                                                           
376.588662 371.182577                                                           
378.921996 373.472899                                                           
307.788662 303.311609                                                           
        一         二                                                           
---------- ----------                                                           
376.055329 370.634189                                                           
376.055329 370.634189                                                           
374.355329 368.989028                                                           
已选择14行。
SQL> host cls
SQL> --114月后
SQL> select add_months(sysdate,114) from dual;
ADD_MONTHS(SYS                                                                  
--------------                                                                  
22-4月 -22                                                                      
SQL> --从今天开始算（2012.10.22星期一）,下一个星期一
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDA                                                                  
--------------                                                                  
29-10月-12                                                                      
SQL> --从今天开始算（2012.10.22星期一）,下一个星期二
SQL> select next_day(sysdate,'星期二') from dual;
NEXT_DAY(SYSDA                                                                  
--------------                                                                  
23-10月-12                                                                      
SQL> host cls
SQL> --对日期进行四舍五入
SQL> select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual;
一             二                                                               
-------------- --------------                                                   
01-11月-12     01-1月 -13                                                       
SQL> --隐式转换的前提： 被转换对象是可以转换的
SQL> host cls
SQL> --显式转换
SQL> --2012-10-22 16:24:13 今天是星期一
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI                                              
----------------------------------                                              
2012-10-22 16:25:52今天是星期一                                                 
SQL> --查询员工的薪水： 货币符号，两位小数，千位符
SQL> select sal, to_char(sal,'L9,999.99') from emp;
       SAL TO_CHAR(SAL,'L9,999                                                  
---------- -------------------                                                  
       800            ￥800.00                                                  
      1600          ￥1,600.00                                                  
      1250          ￥1,250.00                                                  
      2975          ￥2,975.00                                                  
      1250          ￥1,250.00                                                  
      2850          ￥2,850.00                                                  
      2450          ￥2,450.00                                                  
      3000          ￥3,000.00                                                  
      5000          ￥5,000.00                                                  
      1500          ￥1,500.00                                                  
      1100          ￥1,100.00                                                  
       SAL TO_CHAR(SAL,'L9,999                                                  
---------- -------------------                                                  
       950            ￥950.00                                                  
      3000          ￥3,000.00                                                  
      1300          ￥1,300.00                                                  
已选择14行。
SQL> host cls
SQL> --通用函数
SQL> --nvl2(a,b,c) 当a=null时，返回c，否则返回b
SQL> select sal*12+nvl2(comm,comm,0)  from emp;
SAL*12+NVL2(COMM,COMM,0)                                                        
------------------------                                                        
                    9600                                                        
                   19500                                                        
                   15500                                                        
                   35700                                                        
                   16400                                                        
                   34200                                                        
                   29400                                                        
                   36000                                                        
                   60000                                                        
                   18000                                                        
                   13200                                                        
SAL*12+NVL2(COMM,COMM,0)                                                        
------------------------                                                        
                   11400                                                        
                   36000                                                        
                   15600                                                        
已选择14行。
SQL> host cls
SQL> --nullif(a,b) 当a=b时，返回null，否则返回a
SQL> select nullif('abc','abc') from dual;
NUL                                                                             
---                                                                             
SQL> select nullif('abc','abdc') from dual;
NUL                                                                             
---                                                                             
abc                                                                             
SQL> host cls
SQL> -- ￥1,600.00
SQL>  ￥1,250.00
SP2-0042: 未知命令 "￥1,250.00" - 其余行忽略。
SQL>  ￥2,975.00
SP2-0042: 未知命令 "￥2,975.00" - 其余行忽略。
SQL>  ￥1,250.00
SP2-0042: 未知命令 "￥1,250.00" - 其余行忽略。
SQL>  ￥2,850.00
SP2-0042: 未知命令 "￥2,850.00" - 其余行忽略。
SP2-0044: 要获取已知命令的列表, 请输入 HELP;
输入 EXIT 可退出。
SQL>  ￥2,450.00
SP2-0042: 未知命令 "￥2,450.00" - 其余行忽略。
SQL>  ￥3,000.00
SP2-0042: 未知命令 "￥3,000.00" - 其余行忽略。
SQL>  ￥5,000.00
SP2-0042: 未知命令 "￥5,000.00" - 其余行忽略。
SQL>  ￥1,500.00
SP2-0042: 未知命令 "￥1,500.00" - 其余行忽略。
SP2-0044: 要获取已知命令的列表, 请输入 HELP;
输入 EXIT 可退出。
SQL> host cls
SQL> --COALESCE 从左至右找到第一个不为null的值
SQL> select comm,sal,COALESCE(comm,sal) from emp;
      COMM        SAL COALESCE(COMM,SAL)                                        
---------- ---------- ------------------                                        
                  800                800                                        
       300       1600                300                                        
       500       1250                500                                        
                 2975               2975                                        
      1400       1250               1400                                        
                 2850               2850                                        
                 2450               2450                                        
                 3000               3000                                        
                 5000               5000                                        
         0       1500                  0                                        
                 1100               1100                                        
      COMM        SAL COALESCE(COMM,SAL)                                        
---------- ---------- ------------------                                        
                  950                950                                        
                 3000               3000                                        
                 1300               1300                                        
已选择14行。
SQL> host cls
SQL> --根据职位涨工资 总裁1000 经理800 其他400
SQL> select ename,job,sal
  2  from emp;
ENAME      JOB              SAL                                                 
---------- --------- ----------                                                 
SMITH      CLERK            800                                                 
ALLEN      SALESMAN        1600                                                 
WARD       SALESMAN        1250                                                 
JONES      MANAGER         2975                                                 
MARTIN     SALESMAN        1250                                                 
BLAKE      MANAGER         2850                                                 
CLARK      MANAGER         2450                                                 
SCOTT      ANALYST         3000                                                 
KING       PRESIDENT       5000                                                 
TURNER     SALESMAN        1500                                                 
ADAMS      CLERK           1100                                                 
ENAME      JOB              SAL                                                 
---------- --------- ----------                                                 
JAMES      CLERK            950                                                 
FORD       ANALYST         3000                                                 
MILLER     CLERK           1300                                                 
已选择14行。
SQL> select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
  2                                          when 'MANAGER' then sal+800
  3                                          else sal+400
  4                                 end 涨后薪水
  5  from emp;
ENAME      JOB         涨前薪水   涨后薪水                                      
---------- --------- ---------- ----------                                      
SMITH      CLERK            800       1200                                      
ALLEN      SALESMAN        1600       2000                                      
WARD       SALESMAN        1250       1650                                      
JONES      MANAGER         2975       3775                                      
MARTIN     SALESMAN        1250       1650                                      
BLAKE      MANAGER         2850       3650                                      
CLARK      MANAGER         2450       3250                                      
SCOTT      ANALYST         3000       3400                                      
KING       PRESIDENT       5000       6000                                      
TURNER     SALESMAN        1500       1900                                      
ADAMS      CLERK           1100       1500                                      
ENAME      JOB         涨前薪水   涨后薪水                                      
---------- --------- ---------- ----------                                      
JAMES      CLERK            950       1350                                      
FORD       ANALYST         3000       3400                                      
MILLER     CLERK           1300       1700                                      
已选择14行。
SQL> select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,
  2                                           'MANAGER', sal+800,
  3                                                      sal+400)涨后薪水
  4  from emp;
ENAME      JOB         涨前薪水   涨后薪水                                      
---------- --------- ---------- ----------                                      
SMITH      CLERK            800       1200                                      
ALLEN      SALESMAN        1600       2000                                      
WARD       SALESMAN        1250       1650                                      
JONES      MANAGER         2975       3775                                      
MARTIN     SALESMAN        1250       1650                                      
BLAKE      MANAGER         2850       3650                                      
CLARK      MANAGER         2450       3250                                      
SCOTT      ANALYST         3000       3400                                      
KING       PRESIDENT       5000       6000                                      
TURNER     SALESMAN        1500       1900                                      
ADAMS      CLERK           1100       1500                                      
ENAME      JOB         涨前薪水   涨后薪水                                      
---------- --------- ---------- ----------                                      
JAMES      CLERK            950       1350                                      
FORD       ANALYST         3000       3400                                      
MILLER     CLERK           1300       1700                                      
已选择14行。
SQL> spool off


文章标签：
关系型数据库
Oracle
关键词：
Oracle数据库一键上云学习
Oracle数据库一键上云单行函数
Oracle数据库一键上云学习单行函数
程序员囧辉
目录
相关文章
小Tomkk
|
30天前
|
Oracle 关系型数据库 网络安全
Oracle 19c 安装教程学习
Oracle 19c 安装教程学习
小Tomkk
54 2
qq_734449600
|
11月前
|
SQL Oracle 关系型数据库
oracle学习
oracle学习
qq_734449600
63 0
李振良_阿良
|
6月前
|
Oracle 关系型数据库 数据库
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
李振良_阿良
224 0
李振良_阿良
|
6月前
|
存储 Oracle 网络协议
Oracle 11gR2学习之二（创建数据库及OEM管理篇）
Oracle 11gR2学习之二（创建数据库及OEM管理篇）
李振良_阿良
148 0
李振良_阿良
|
6月前
|
Oracle 关系型数据库 数据库
Oracle 11gR2学习之一（安装篇）
Oracle 11gR2学习之一（安装篇）
李振良_阿良
51 0
王同学睡醒了
|
6月前
|
Oracle 关系型数据库 数据库连接
【Oracle学习】—新建数据库连接，超详细
【Oracle学习】—新建数据库连接，超详细
王同学睡醒了
81 0
王同学睡醒了
|
6月前
|
Oracle 关系型数据库 数据库
【Oracle学习】—Oracle11g修改用户名与密码（一）
【Oracle学习】—Oracle11g修改用户名与密码（一）
王同学睡醒了
139 0
姚远OracleACE
|
Oracle 关系型数据库 数据库
查询listener的日志排除不能登录的错误使用Oracle官方提供的ova文件建立Oracle 19c学习环境
Oracle官方提供了安装好的Oracle 19c虚拟机，打包成ova文件。可以使用这个文件建立一个oracle 19c的学习环境。
姚远OracleACE
168 0
姚远OracleACE
|
Oracle 关系型数据库 数据库
【无标题】使用Oracle官方提供的ova文件建立Oracle 19c学习环境
Oracle官方提供了安装好的Oracle 19c虚拟机，打包成ova文件。可以使用这个文件建立一个oracle 19c的学习环境。
姚远OracleACE
279 0
游客2jnzr4hskqhfw
|
SQL Oracle 关系型数据库
【Oracle学习】—新建数据库连接，超详细
按键盘上的windows键,搜索Database Configuration Assistant，单击运行即可
游客2jnzr4hskqhfw
198 0
【Oracle学习】—新建数据库连接，超详细