Oracle基础（常用函数和类型转换）

- 日理万妓 2022-05-31 03:59 193阅读 0赞

本篇文章主要讲Oracle中常用的函数，还有类型转换等知识。

注意：日期类型比较麻烦，但是实际应用中日期格式又是不可缺少的。

--------------------

## 单行函数 ##

单行函数：只有一个参数输入，只有一个结果输出

--1、学习lower/upper/initcap函数，使用dual哑表
    select lower('www.BAIdu.COM') from dual; --lower()转小写
    select upper('www.BAIdu.com') from dual; --upper()转大写
    select initcap('www.BAIdu.COM') from dual; --initcap()首字母大写
    
    --2、学习concat/substr函数
    --concat连接字符串（两个字符串拼接）
    select concat('hello','你好') from dual; -- 正确
    select concat('hello','你好','世界') from dual; 
    -- 错误 concat连接字符串，只能是两个参数
    
    --多个参数
    select 'hello' || '你好' || '世界' from dual; --正确
    select concat('hello',concat('你好','世界')) from dual;--正确，不建议，阅读性差
    
    --substr取子串
    select substr('hello你好',5,3) from dual;
    --5表示从第几个字符串开始算，第一个字符为1，中英文统一处理
    --3表示连续取几个字符
    
    --3、学习length/lengthb函数，编码方式为UTF8/GBK(你好)，一个中文占3/2个字节长度，一个英文一个字节
    select length('hello你好') from dual;      --length()字符长度 7
    select lengthb('hello你好') from dual;    -- lengthb()字节长度 11
    
    --4、学习instr/lpad/rpad函数，
    --instr从左向右找第一次出现的位置，从1开始
    select instr('helloworld', 'o') from dual;
    --注意：找不到返回0，大小写敏感
    --lpad如果位数不够，从左边开始补位，如果超过就截取后面多余的
    select lpad('hello',10,'#')from dual;
    --rpad从右边开始补位，如果超过就截取后面多余的
    select rpad('hello',3,'#')from dual;
    
    --5、学习trim/replace函数
    --trim取出字符串两边的字符
    select trim('o' from 'ooooheooooollooooooooooo') from dual;
    --replace替换指定字符
    select replace ('hello','l','L') from dual;
    
    --6、学习round/trunc/mod函数作用于数值型
    
    select round(3.1415,3) from dual;  --保留3位小数，按照四舍五入法
    select trunc(3.1415,3) from dual;  --保留3位小数，直接截取
    select mod(10,3) from dual;        --求余运算
    
    --当前日期：sysdate = 06-2月-18
    
    --学习round作用于日期型（month）
    select round(sysdate,'month')from dual;
    
    --学习round作用于日期型（year）
    select round(sysdate,'year')from dual;
    
    --学习trunc作用于日期型（month）
    select trunc(sysdate,'month')from dual;
    
    --学习trunc作用于日期型（year）
    select trunc(sysdate,'year')from dual;
    
    --7、显示昨天，今天，明天的日期，日期类型 +- 数值 = 日期类型
    select sysdate-1 "昨天", sysdate "今天", sysdate+1 "明天" from dual;
    
    --8、以年和月形式显示员工近似工龄，日期-日期=数值，假设：一年以365天计算，一月以30天计算
    select ename "姓名", round(sysdate-hiredate,0)/30 "月" from emp;
    select ename "姓名", round(sysdate-hiredate,0)/365 "年" from emp;
    
    --9、使用months_between函数，精确计算到年底还有多少个月（从现在到2018/12/31日）
    select months_between('31-12月-18',sysdate) from dual;
    
    --使用months_between函数，以精确月形式显示员工工龄
    select ename "姓名" ,months_between(sysdate,hiredate) from emp;
    --10、学习add_months函数，下个月今天是多少号
    select add_months(sysdate,1) from dual;
    
    --学习add_months函数，上个月今天是多少号
    select add_months(sysdate,-1) from dual;
    
    --10、学习next_day函数，从今天开始算，下一个星期三是多少号【中文平台】
    --注意：如果今天是星期一或者星期二，下一个星期三，指的是本周星期三
    -- 如果今天是三或者星期三以后，则下星期三，就是生活中所说的下周三
    select next_day(sysdate,'星期三') from dual;
    
    --学习next_day函数，从今天开始算，下下一个星期三是多少号【中文平台】
    select next_day(next_day(sysdate,'星期三'),'星期三') from dual;
    
    --学习next_day函数，从今天开始算，下一个星期三的下一个星期日是多少号【中文平台】
    select next_day(next_day(sysdate,'星期三'),'星期日') from dual;
    
    --11、学习last_day函数，本月最后一天是多少号
    select last_day(sysdate) from dual;
    
    --学习last_day函数，本月倒数第二天是多少号
    select last_day(sysdate)-1 from dual;
    
    --学习last_day函数，下一个月最后一天是多少号
    select last_day(add_months(sysdate,1)) from dual;
    
    --学习last_day函数，上一个月最后一天是多少号
    select last_day(add_months(sysdate,-1)) from dual;

--------------------

## 通用函数和条件判断函数 ##

--通用函数：参数类型可以是number或varchar2或date类型
    --1、使用NVL(a,b)通用函数，统计员工年收入，NVL()作用于任何类型，即(number/varchar2/date)
    select ename,sal*12+NVL(comm,0) from emp;
    
    --2、使用NVL2(a,b,c)通用函数，如果a不为NULL，取b值，否则取c值，统计员工年收入 
    select ename,sal*12+NVL2(comm,comm,0) from emp;
    
    --3、使用NULLIF(a,b)通用函数，在类型一致的情况下，如果a与b相同，返回NULL，否则返回a，比较10和10.0是否相同
    select NULLIF(10,10.0) from dual;
    select NULLIF(10,10.1) from dual;
    
    /*4、使用SQL99标准通用语法中的case表达式，将职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400 case 字段 when 条件1 then 表达式1 when 条件2 then 表达式2 else 表达式n end 请参考<MySQL5.X的手册>-12.2这个章节*/
    select ename "姓名",job "职位",sal "涨前工资", case job when 'ANALYST' then sal+1000 when 'MANAGER' then sal+800 else sal+400 end "涨后工资" from emp; 
    
    /*5、使用oracle专用语法中的decode()函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400 decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)*/
    select ename "姓名",job "职位",sal "涨前工资", decode(job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "涨后工资" from emp;

**单引号出现的地方如下：**  
1）字符串，例如：’hello’  
2）日期型，例如：’17-12月-80’  
3）to\_char/to\_date(日期,’YYYY-MM-DD HH24:MI:SS’)

**双引号出现的地方如下：**  
1）列别名，例如：select ename “姓 名” from emp  
2）to\_char/to\_date(日期,’YYYY”年”MM”月”DD”日” HH24:MI:SS’)‘’号中的英文字符大小写不敏感

--------------------

## 多行函数 ##

多行函数或分组函数：可有多个参数输入，只有一个结果输出

/* 函数：oracle服务器先事写好的一段具有一定功能的程序片段，内置于oracle服务器，供用户调用 单行函数：输入一个参数，输出一个结果，例如：upper('baidu.com')->BAIDU.COM 多行函数：输入多个参数，或者是内部扫描多次，输出一个结果，例如：count(*)->14 */
    
    --1、统计emp表中员工总人数
    select count(*) from emp;
    -- *号适用于表字段较少的情况下，如果字段较多，扫描多间多，效率低，项目中提倡使用某一个非null唯一的字段，通常是主键 
    
    --2、统计公司有多少个不重复的部门
    select count(distinct deptno) from emp;
    
    --3、统计有佣金的员工人数
    select count(comm) from emp;
    --注意：这些多个行函数，不统计NULL值
    
    --4、员工总工资，平均工资，四舍五入，保留小数点后0位
    select sum(sal) "总工资",round(avg(sal),0) "平均工资" from emp;
    
    --5、查询员工表中最高工资，最低工资
    select max(sal) "最高工资",min(sal) "最低工资" from emp;
    
    --6、入职最早，入职最晚员工
    select max(hiredate) "最晚入职时间",min(hiredate) "最早入职时间" from emp;
    
    --多行函数：count/sum/avg/max/min
    
    --7、按部门求出该部门平均工资，且平均工资取整数，采用截断
    select deptno "部门编号",trunc(avg(sal),0) "部门平均工资" from emp group by deptno;
    
    --8、(继续)查询部门平均工资大于2000元的部门
    select deptno "部门编号",trunc(avg(sal),0) "部门平均工资" from emp group by deptno having trunc(avg(sal),0) > 2000; 
    
    --9、(继续)按部门平均工资降序排列
    select deptno "部门编号",trunc(avg(sal),0) "部门平均工资" from emp group by deptno having trunc(avg(sal),0) > 2000 order by 2 desc;
    
    --10、除10号部门外，查询部门平均工资大于2000元的部门，方式一【having deptno<>10】
    select deptno,avg(sal) from emp group by deptno having deptno<>10;
    
    --11、除10号部门外，查询部门平均工资大于2000元的部门，方式二【where deptno<>10】
    select deptno,avg(sal) from emp where deptno<>10 group by deptno; --提倡
    
    --12、显示部门平均工资的最大值
    select max(avg(sal)) "部门平均工资的最大值" from emp group by deptno;
    
    --显示部门平均工资的最大值和该部门编号?
    select max(avg(sal)) "部门平均工资的最大值",deptno "部门编号" from emp group by deptno;
    --错误
    
    /* group by 子句的细节： 1）在select子句中出现的非多行函数的所有列，【必须】出现在group by子句中 2）在group by子句中出现的所有列，【可出现可不现】在select子句中 */
    
    /* where和having的区别： where： 1）行过滤器 2）针对原始的记录 3）跟在from后面 4）where可省 5）先执行 having： 1）组过滤器 2）针对分组后的记录 3）跟在group by后面 4）having可省 5）后执行 */
    
    /*oracle中综合语法： 1）select子句-----必须 2）from子句-------必须，不知写什么表了，就写dual 3）where子句------可选 4）group by子句---可选 5）having子句-----可选 6）order by 子句--可选，如果出现列名，别名，表达式，字段 */

--------------------

## 三大类型转换 ##

**oracle中三大类型与隐式数据类型转换**  
（1）varchar2变长/char定长–>number，例如：’123’->123  
（2）varchar2/char–>date，例如：’25-4月-15’->’25-4月-15’  
（3）number—->varchar2/char，例如：123->’123’  
（4）date——>varchar2/char，例如：’25-4月-15’->’25-4月-15’

**oracle如何隐式转换：**  
1）=号二边的类型是否相同  
2）如果=号二边的类型不同，尝试的去做转换  
3）在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

--1、查询1980年12月17日入职的员工（方式一：日期隐示式转换）
    select * from emp where hiredate = '17-12月-80';
    
    --2、使用to_char(日期，'格"常量"式')函数将日期转成字符串，显示如下格式：2018 年 02 月 26 日 星期二
    select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;
    
    --3、使用to_char(日期，'格式')函数将日期转成字符串，显示如格式：2018-02-06今天是星期二 15:59:15
    select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;
    --或 显示如格式：2018-02-06今天是星期二 3:59:55 下午
    select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;
    
    --4、使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：$1,234
    select to_char(1234,'$9,999') from dual;
    
    --使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：￥1,234
    select to_char(1234,'L9,999') from dual;
    
    --5、使用to_date('字符串','格式')函数，查询1980年12月17日入职的员工（方式二：日期显式转换）
    select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
    --或
    select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
    --或
    select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');
    
    --6、使用to_number('字符串')函数将字符串‘123’转成数字123
    select to_number('123') from dual;
    
    
    --注意：
    select '123' + 123 from dual;  --246
    select '123' || 123 from dual; --123123