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

一、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;