1.前言
项目驱动学习是一件行之有效的方式。最近项目中确实遇到了很多问题。当然按照之前的处理方式也未尝不可,不过想到以后的维护感觉还是想办法在改进一下吧。这次,我们主要讨论一下Oracle触发器在实际工作中的应用。同时借组SqlDevelop工具分享一下编写触发器过程的调式技巧(之前一编译就出现编译警告就无从下手)。当然,我还是初次接触调试,方法虽不尽人意,但是也不失一种方法。
2.业务描述
对于有父子及结构的数据中,层次化编码是一种很好的数据处理方式。拿身份证举例,他的每一位都代表有特殊的含义。比如130开头属于河北,110开头的属于北京等等。如果我们要查询一张表中北京人员,我们就可以使用Sql语句:
SELECT * FROM STUDENT WHERE CARD_ID LIKE '110%';
如果我们要查询张家口的人员呢,触类旁通,
SELECT * FROM STUDENT WHERE CARD_ID LIKE '1307%';
通过以上实例,我们发现不需要对张家口下属的县,县下属的乡进行遍历查找,性能提升了很多(这里有读者可能会说prior关键字可以实现这个功能。然而通过对比,我相信谁都知道查询那个简单。
那么问题来了,我们怎么去维护这个字段呢?我们可以有两种方式:
1.在每次插入更新表的时候查询数据库计算java代码处理。这样处理的好处就是我们都熟悉代码,只要逻辑屡清楚了,实现不成问题。但是他存在一下几个问题:
1)多次java代码查询数据库,会影响数据库性能;
2)我们没法办做到做的全面。如果我们对于插入或者更新的Sql遗漏了就会出现问题。同时如果后期与其他系统集成,开发人员不知道这个字段的处理逻辑或者遗忘,那将是一件很糟糕的事。
一句话概率,性能欠佳,维护困难。
2.通过Oracle触发器实现层次化编码设计。就是不管java什么时候进行对表进行update或者insert之前都要执行此处的触发器,也就不怕无限制的增加sql语句了。然而,这个回的人不多,实现难度大。
经过我们商量,决定还是先去尝试方法二:Oracle触发器。如果在项目上线之前还没有解决思路,我们暂时按照思路一处理。
对于我们的具体逻辑是这样的,我们对组织机构进行层次化编码设计。每一个层级为4位编码。也就是说第一层级为4位编码,第二层为8位编码,依次类推(不要吐槽为什么设置这么长)。
在组织机构中我们主要有三个字段,【org_id,parent_id,lev_id】,其中org_id为组织的流水id,parent_id为原有的父级节点ID(我们不能删掉他,否则会出现异常);lev_id,层级结构id,也就是我们说的表示层级结构的ID(这个字段是我们后期增加的,并不影响原有的代码逻辑。
3.代码逻辑
![c2031d15864b2c5d13e5ce8b1735a7a6.png](https://i-blog.csdnimg.cn/blog_migrate/3ba525a0485f1415424538d58898c8f8.jpeg)
1)在原表中查询父级id(parent_id)为当前插入数据的parent_id,这里找到最大的一个。
2)如果查询结果为null,则在原表中id=当前插入数据的parent_id,这里应该只有一个值。此时如果满足,则在查询的值结尾追加【0001】;如果仍为null,那值为0001.
3)如果步骤一中存在值,则在查询的值基础上+1。
4)将处理后的值更新到数据表中。
4.代码展示
CREATE OR REPLACE TRIGGER TAG_SM_ORGANIZATION BEFORE INSERT OR UPDATE ON SM_ORGANIZATION FOR EACH ROW DECLARE P_IDS INT; P_IDS_STR VARCHAR2(100); P_LEN INT; BEGIN SELECT (TO_NUMBER(MAX(PARENT_IDS))+1) INTO P_IDS FROM SM_ORGANIZATION WHERE PARENT_ID = :NEW.PARENT_ID; IF P_IDS IS NULL THEN BEGIN SELECT MAX(PARENT_IDS) || '0001' INTO P_IDS_STR FROM SM_ORGANIZATION WHERE ORGA_ID = :NEW.PARENT_ID; END; ELSE BEGIN --计算新编号总长度 P_IDS_STR := TO_CHAR(P_IDS); P_LEN := LENGTH(P_IDS_STR); P_LEN := CEIL(P_LEN/4) * 4; --位数不足左侧补0 P_IDS_STR := LPAD(P_IDS_STR,P_LEN,'0'); END; END IF; --更新新值 :NEW.LEV_ID := P_IDS_STR; END;
5.调式技巧
5.1 中间数据输出
1)在代码中加入以下语句:DBMS_OUTPUT.PUT_LINE(P_IDS);其中p_IDS为要输出的值;
2)打开日志输出开关
![30a9e155a9513c3e55dc69aed76cbb26.png](https://i-blog.csdnimg.cn/blog_migrate/f82cd5da5a262957aaef85458c0527fc.jpeg)
5.2 执行完毕,但有警告
![50f45689116fce6eae5806be3d50268a.png](https://i-blog.csdnimg.cn/blog_migrate/0c9a1a36d285afae1f5e0a218d049d61.jpeg)
1)打开左侧列表->触发器,我们发现刚才创建的触发器有红叉。
![fb22e20a871374e7404f7890ca8ee3cf.png](https://i-blog.csdnimg.cn/blog_migrate/c70885521722e64846b4adcfe2f3f101.jpeg)
2)选中我们创建的触发器,右击编辑
![1c6858008332209bbcf5b8a8f88d2978.png](https://i-blog.csdnimg.cn/blog_migrate/f319df26fbe0a53d1f5391d99c0fd91c.jpeg)
3)此时我们发现错误的地方,在倒数第二行少了一个分号。
![3135bbb416cdc3fec5b76830d378977e.png](https://i-blog.csdnimg.cn/blog_migrate/fb9afbeb1f5b078669afb71923fa72fa.jpeg)
通过以上步骤我们可以快速定位错误,加快触发器编译步骤。
本篇文章整理来自:https://blog.csdn.net/oYinHeZhiGuang/article/details/105151457,CSDN博主[surpassLiang]
觉得Oracle这个玩法怎么样,是不是又学习到了呢?觉得可以的话,您可以动动您的金手指进行转发收藏,可以自己用哦!觉得小编整理得文章好的话呢,也可以给小编点个小小的关注呐!谢谢您的支持啦!