条形码校验--oracle 函数

在书中看到了一段关于条形码校验的标准SQL，尝试着将其用oracle 实现， 那知水平太浅，遂去论坛向大侠们讨教， 长进了不少，再次谢谢大家了，

 

本文将高手们的妙招，结果及相关知识整理出来。以便随时查看。

 

条形码的检验规则如下：
（1）：计算各奇数位的和得到S1；
（2）：计算各偶数位的和得到S2；
条形码的校验和位的计算公式是： ABS(MOD((s1-s2),10))

 

标准SQL语句如下：

CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))RETURNS INTEGERRETURN( SELECT ABS(SUM(CAST ( SUBSTRING( barcode FROM Weights.seq FOR 1) AS INTEGER) * Weights.wgt))  FROM ( VALUES (CAST( 1 AS INTEGER), CAST( -1 AS INTEGER)),  ( 2, +1),( 3, -1),( 4, +1),( 5, -1),( 6, +1),( 7, -1),( 8, +1),( 9, -1),( 10, +1),( 11, -1),( 12, +1)) AS Weights(seq, wgt)  WHERE barcode NOT SIMILAR TO '%[^0-9]%');

 

上面的sql中有两个技巧，
技巧1：判断参数全部有数字组成，similar to 双重否定，

技巧2：values（）表达式构造了一个表常量，

且看大侠们是怎样处理。

 

• 1.   方法一：

 

CREATE OR REPLACE FUNCTION func_identify_barcode(i_str VARCHAR2)RETURN VARCHAR2 ASv_sign NUMBER;v_result NUMBER;BEGIN-- exp：i_str 为： abc1-- 连接后的字符串:'0123456789abc1', 比'0123456789' 要长，-- 在'0123456789abc1'而不在0123456789中的字符，由于没有替代的字符，-- abc将要从 i_str(abc1)中删去，只剩下1，所以经过translate函数处理后返回1，其长度也为：1。IF length(i_str) != length(translate(i_str, '0123456789' || i_str, '0123456789'))OR length(i_str) != 12 THEN--{ RETURN NULL;--} END IF;v_sign := 1;v_result := 0;FOR i IN 1 .. length(i_str) LOOP--{v_sign := -v_sign;v_result := v_result + substr(i_str, i, 1) * v_sign;--}END LOOP;RETURN abs(mod(v_result,10));END;/--SELECT func_identify_barcode('92949a789012') result from dual;

 

• 方法一技巧：

     IF  length(i_str) != length(translate(i_str, '0123456789' || i_str, '0123456789')) OR length(i_str) != 12 THEN ...

 

运用TRANSLATE 判断 参数全部是数字字符， 好！

附上其语法：

 

/*
一、语法：
  TRANSLATE(string,from_str,to_str)
二、目的
    返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE   是   REPLACE   所提供的功能的一个超集。如果   from_str   比   to_str   长，那么在   from_str   中而不在   to_str   中的额外字符将从   string   中被删除，因为它们没有相应的替换字符。to_str   不能为空。Oracle   将空字符串解释为   NULL，并且如果TRANSLATE   中的任何参数为NULL，那么结果也是   NULL。
三、允许使用的位置
    过程性语句和SQL语句。
四、示例
  SELECT   TRANSLATE( 'abcdefghij ', 'abcdef ', '123456 ')   FROM   dual;
  TRANSLATE   (
  --------------
  123456ghij

  SELECT   TRANSLATE( 'abcdefghij ', 'abcdefghij ', '123456 ')   FROM   dual;
  TRANSL
  ----------
  123456
*/ 

 

 

 

• 2.   方法二：

 

CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)RETURN INTEGERASnum NUMBER:=0;BEGIN    BEGIN       SELECT ABS(MOD(SUM( SUBSTR( my_barcode, Weights.seq, 1)  * Weights.wgt),10))       INTO num       FROM ( SELECT LEVEL seq,Power(-1,level-1) wgt               FROM dual              CONNECT BY LEVEL<13)              Weights       WHERE  NOT REGEXP_LIKE(my_barcode, '[^[:digit:]]');    END;RETURN  num;END;-- select Barcode_CheckSum('121212121212') from daul; 

 

 

•  方法二技巧：

       1,  NOT REGEXP_LIKE(my_barcode,'[^[:digit:]]');

          

要了解更多Oracle正则表达式呢，可以看看minitoy转载的相关资料：
http://blog.csdn.net/minitoy/archive/2010/11/02/5981729.aspx
http://blog.csdn.net/minitoy/archive/2010/11/05/5990191.aspx
http://blog.csdn.net/minitoy/archive/2010/11/05/5990220.aspx

          

                

       2, 使用LEVEL 伪列构造临时表： Weights 

              SELECT LEVEL seq,Power(-1,level-1) wgt
              FROM dual
              CONNECT BYLEVEL<13

              

               -- Power 冪运算