Oracle触发器-校验身份证和字节长度

r囧r小猫 2023-06-25 06:19 10阅读 0赞

**一、Oracle触发器的一个简单介绍：**

**触发器：**是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

**功    能：**

1.允许、限制对表的修改

2.自动生成派生列，比如自增字段

3.强制数据一致性

4.提供审计和日志记录

5.防止无效的事务处理

6.启用复杂的业务逻辑

**触发器的组成部分：**

1.触发器名称

2.触发语句

3.触发器限制

4.触发操作

如：

** 1️⃣触发器名称**

create or replace trigger trig\_etc(触发器名称)

**命名习惯   **

create or replace trigger 触发器名称 
    
    before/after insert or update or delete on 表名
    
        for each now
    
       begin
    
          if  XXXX then
    
          end if;
    
        end；

注：这个属于常规写法吧！

2️⃣触发语句

比如：

表或试图上的DML语句

DDL语句

数据库关闭或者启动，startup shutdown等等。。。

before/after  insert or update or delete
    
       referencing old as old_value  
    
       new as new_value
    
       for each row
       
       ETC_Card  表名
       
       card_id  列名

3️⃣触发器限制

when (new_value.card_id<>1 )

限制不是必须的。此例表示如果列card\_id不等于1的时候，触发器就会执行。

其中的new\_value是代表更新之后的值

4️⃣触发操作

是触发器的主体

begin
    
          :new_value.commission_pct :=0;
    
       end;

主体很简单，就是将更新后的commission\_pct列置为0

**二、触发器实战**

由于业务要求，需要让其中一个字段不能超过8个字节，两一个字符串的第一个字节必须是1，最后一个是身份证号最后一个字节的校验，要求是字母只能是X，不能是其他字母，数字随意；

由此业务要求参数下面触发器：

create or replace trigger trg_etc
       --是在它插入数据之前触发
       before insert on Etc_Card_Info_New 
       for each row
    begin
        --往日志表中插记录
        insert into ETC_LOG VALUES(systimestamp,:new.card_no || '|' || :new.AGENTNAME,'trg_etc',0,'');
     --第一个要求是AGENTNAME列不能超过8个字节
     if length(:NEW.AGENTNAME) > 8 then
        RAISE_APPLICATION_ERROR(-20001,'经办人姓名不能超过四个汉字!');
      end if;
       --第二个要求是AGENTIDTYPE列中的字符串第一个字节必须是1
       if SUBSTR(:NEW.AGENTIDTYPE,1,1) <> '1' then
       RAISE_APPLICATION_ERROR(-20002,'个人经办人证件类型必须是以1开头的');
      end if;
      --第三个要求是身份证合法性并且对身份证最后一个字节的校验
      if SUBSTR(:NEW.AGENTIDTYPE,1,1) = '1'  then
       if fn_checkidcard(:NEW.AGENTIDTYPE) = 1 then
         if INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'0') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'1') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'2') = 0 or
           INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'3') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'4') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'5') = 0 or
           INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'6') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'7') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'8') = 0 or 
           INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'9') = 0 or INSTR(SUBSTR(:NEW.AGENTIDNUM,-1),'X') = 0  then
           RAISE_APPLICATION_ERROR(-20003,'个人经办人证件号码最后一位字母只能是X！');
          end if;
        else
          RAISE_APPLICATION_ERROR(-20004,'个人身份证号不符合规范！'); 
        end if;
       end if;
      
    END trg_etc;

注：附上校验身份证的Oracle函数脚本

CREATE OR REPLACE FUNCTION fn_checkidcard (p_idcard IN VARCHAR2) RETURN INT
    IS
       v_regstr      VARCHAR2 (2000);
       v_sum         NUMBER;
       v_mod         NUMBER;
       v_checkcode   CHAR (11)       := '10X98765432';
       v_checkbit    CHAR (1);
       v_areacode    VARCHAR2 (2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,';
    BEGIN
       CASE LENGTHB (p_idcard)
          WHEN 15
          THEN                                                            -- 15位
             IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',') = 0 THEN
                RETURN 0;
             END IF;
    
             IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 400) = 0
                OR
                (
                    MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 100) <> 0
                    AND
                    MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 4) = 0
                )
             THEN                                                          -- 闰年
                v_regstr :=
                   '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
             ELSE
                v_regstr :=
                   '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
             END IF;
    
             IF REGEXP_LIKE (p_idcard, v_regstr) THEN
                RETURN 1;
             ELSE
                RETURN 0;
             END IF;
          WHEN 18
          THEN                                                             -- 18位
             IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',') = 0 THEN
                RETURN 0;
             END IF;
    
             IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 400) = 0
                OR
                (
                    MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 100) <> 0
                    AND
                    MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 4) = 0
                )
             THEN                                                          -- 闰年
                v_regstr :=
                   '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
             ELSE
                v_regstr :=
                   '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
             END IF;
    
             IF REGEXP_LIKE (p_idcard, v_regstr) THEN
                v_sum :=
                       (  TO_NUMBER (SUBSTRB (p_idcard, 1, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 11, 1))
                       )
                     * 7
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 2, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 12, 1))
                       )
                     * 9
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 3, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 13, 1))
                       )
                     * 10
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 4, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 14, 1))
                       )
                     * 5
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 5, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 15, 1))
                       )
                     * 8
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 6, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 16, 1))
                       )
                     * 4
                   +   (  TO_NUMBER (SUBSTRB (p_idcard, 7, 1))
                        + TO_NUMBER (SUBSTRB (p_idcard, 17, 1))
                       )
                     * 2
                   + TO_NUMBER (SUBSTRB (p_idcard, 8, 1)) * 1
                   + TO_NUMBER (SUBSTRB (p_idcard, 9, 1)) * 6
                   + TO_NUMBER (SUBSTRB (p_idcard, 10, 1)) * 3;
                v_mod := MOD (v_sum, 11);
                v_checkbit := SUBSTRB (v_checkcode, v_mod + 1, 1);
    
                IF v_checkbit = upper(substrb(p_idcard,18,1)) THEN
                   RETURN 1;
                ELSE
                   RETURN 0;
                END IF;
             ELSE
                RETURN 0;
             END IF;
          ELSE
             RETURN 0;                                      -- 身份证号码位数不对
       END CASE;
    EXCEPTION
       WHEN OTHERS
       THEN
          RETURN 0;
    END fn_checkidcard;