Oracle触发器原来也可以这么玩

妖狐艹你老母 2023-05-28 05:27 112阅读 0赞

## 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.代码逻辑 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70][]

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)打开日志输出开关

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 1][]

### 5.2 执行完毕，但有警告 ###

![20200327224800213.png][]

1)打开左侧列表->触发器，我们发现刚才创建的触发器有红叉。

![20200327224907574.png][]

2)选中我们创建的触发器，右击编辑

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 2][]

3)此时我们发现错误的地方，在倒数第二行少了一个分号。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 3][]

通过以上步骤我们可以快速定位错误，加快触发器编译步骤。

好了，以上就是我给大家分享的内容，希望大家喜欢。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70]: /images/20230528/8c2996e320ad47e9a77991dc68ffdf50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 1]: /images/20230528/f72a6456aa2546379e8f34a5d26afa6f.png
[20200327224800213.png]: /images/20230528/12addc9c0e9a41839d9c3b73ca329830.png
[20200327224907574.png]: /images/20230528/26cee15d699b471cac1a263c0533b324.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 2]: /images/20230528/2ce741046ef240f8a4ecd7c41be96cc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 3]: /images/20230528/0172284668b84f9fb47ef7fa2a179d31.png