第一次使用PLSQL操作Orcale踩到的坑
前言
本来我是一个基本只用Navicat操作MySQL的扣脚程序员,在摸鱼的海洋里快乐的生活着,听歌、看新闻、刷B站、看小说等等。结果不幸的事情发生了,因为业务需要,我要用PLSQL操作Orcale。
听到这个消息,面无人色,顿时瘫倒在地。
想起了小学时在路边踩过的牛粪,中学时拖堂的老师,大学时的高数课堂。猛地,我之前的回忆反复在脑海里放映,走马灯的环节出现的貌似比我预料的要快得多。
项目经理看我趴在地上:呦,这小子,听到要用Orcale这么开心啊,这可是难得的历练。
缓了一会后,我想起了那些受到压迫,奋力反抗的先辈们,我的眼神坚定了起来,我的心又滚烫了起来,血液在身体其欢快地奔腾,全身的细胞都在告诉我:say NO。终于,我下定决定要反抗了,思虑良久,我开口了: 经理,能不去吗?(唯唯诺诺)。
经理也认真思考了我的建议:能干干,不能干滚蛋。
我:得嘞,就等您这句话了。请领导放心,绝对完成任务!(一脸奉承)
安装配置(可以写一本血泪史了): 初识PLSQL
SpringBoot里面的Orcale配置也是要了我半条老命。导入包可以参考别的大神文章:手动添加oracle驱动到maven仓库
踩过的坑
左侧文件树打不开
按理来说,这里面应该是有表的,但是… 不过通过语句操作表还是可以的,也不知道是为什么 真的绝了 = =
解决办法
面对难题他说:反复切换对象
那位要去分手的施主 请不要那么兴奋。这个对象 是说的这个
在当前用户和全部用户之间来回切换,就有概率恢复对应的功能。为什么是有概率?我也不知道,这就是玄学的力量!
手动修改,填写ID触发器不生效
我们知道Orcale的里面如果要实现自增,一般是写一个触发器 ,在插入之前获取到id列的下一个值。
但是由PLSQL提供的图形化编辑界面,就是你右键表,然后下面的那个编辑数据。
假如我现在的id是这样
上面的100X是我手动填写的,下面的3-8是我没填,由触发器自动生成的。
如果我插入一个不填写id的行,那么该行的id就是9,而不是1010。
也就是说,在图形化编辑界面,自定义ID的,不会触发该列的触发器。
这是由于触发器里面,大都有这么一个判断:NEW.ID IS NULL
不为空自然就不会触发了。
需要手动提交
在MySQL里面,因为是自动提交(autocommit),就是你操作以后,它直接帮你提交了。但是Orcale里面没有,要让DML操作更新到数据库,就需要手动COMMIT一下。
真是,人生无常大肠包小肠。
很多时候你图形化操作了以后,没有COMMIT,到时候就尴尬了,数据库里有数据,但是访问不到,各种断点调试都不行,白白浪费时间。。。。
数据更改以后 一定要记得commit!!!
Orcale和MySQL的一些小差别
时间函数
MySQL里面获取当前时间用的是:now()
Orcale里面用的是:sysdate
字符串拼接
假如要拼接 ABC 这个字符串
MySQL : concat(‘A’ , ‘B’ , ‘C’) – concat函数拼接
Orcale: ‘A’||‘B’||‘C’ – || 拼接
字符日期转换,精确到毫秒
如过要精确到毫秒,那就需要使用to_timestamp函数了。to_date是精确不到毫秒的。
部分统计数据用到的SQL
join on的使用
是的 ,你没有看错。不是左联(left join),不是右联(right join),也不是复联,而是join。
其实就相当于一个内连接(inner join)。
假如这样的的一个环境:要找出全校的学生(Student表)各个科目(subject)的总人数和不及格人数,该怎么做。
学生表可以近似的看成只有:subjectid(科目id)和grade(成绩),两列。那么可以这么写:
select sj1,s1.counts, s2.counts from
(select subjectid as sj1 , count(1) as counts from Student group by subjectid) s1
join (
select subjectid as sj2 , count(1) as counts from Student where grade >= 60 group by subjectid) s2
on s1.sj1 = s2.sj2
科目13对应总人数为11,不及格人数为7,14的科目总人数10 ,不及格人数为6
按照小时获取数据
比如我要在校园进出系统(表为campus)里,统计每天每小时进入校园的人数。那该怎么做呢?
假设该表的进出校园时间为intime,类型为日期
先获取当天24小时内进出的数据,然后按照小时分组(group by)即可。
select to_char(intime, 'hh24') , count(to_char(intime, 'hh24')) from campus
where intime >= to_date('当天开始时间' , 'yyyy-MM-dd hh24:mi:ss')
and to_date('当天结束时间' , 'yyyy-MM-dd hh24:mi:ss') >= intime
group by to_char(intime , 'hh24') order by to_char(intime , 'hh24')
rownum的使用 获取指定数据及分页
获取指定
需要利用子查询来获取指定位置的数据。
如果在子查询里直接使用where rownum = 1,获取的第一条数据,是未排序的第一条。
select * from student where rownum = 1 order by id
因为你的where条件在前,排序在后。
所以使用子查询来获取第一条。
select * from (
子查询 order by 排序 desc
) where rownum = 1
分页
对于分页查询,MySQL里面使用的是limit来实现,而Orcale还是使用rownum与子查询实现。
以下这条查询只返回子查询的第三条到第五条数据。
select * from (
select t.* , rownum as no from (
子查询
) t where rownum <= 5
) where no >= 3
我们也可以简单分析下各个子查询的作用:
最里面的就是查询出所有的数据
往外一层是五之前的的数据
最外层就是获取三之后的数据了。
触发器实现自增
MySQL里面可以自己设置自增,而Orcale中使用触发器(trigger)实现
值得注意的是,还需要为表创建序列(sequence )
具体可以看看大佬的这篇文章
数据库-玩转数据-Oracle触发器
create or replace trigger
触发器名称 before insert on 表名
for each row
begin
if inserting and :new.自增列 is null then
select 序列名称.nextval into :new.id from dual;
end if;
end;