求教，用oracle实现一段sql语句（条形码的检验）

Question

一在书上看到一段sql，不知道怎么用oracle实现，请大侠们帮看看！
CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))
RETURNS INTEGER
RETURN
( SELECT ABS(SUM(CAST ( SUBSTRING( barcode 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 双重否定， 难点，oracle正则表达式不好弄啊
技巧2：values（）表达式构造了一个表常量，怎么用oracle 写？ 整了好长时间没搞定，求教。

Answer 1

--从来没有看过这种写法，凭感觉来猜的意思：with weights as(
select 1 seq,-1 wgt from dual union all 
select 2,1 from dual union all 
select 3,-1 from dual union all 
select 4,1 from dual union all 
select 5,-1 from dual union all 
select 6,1 from dual union all 
select 7,-1 from dual union all
select 8,1 from dual union all 
select 9,-1 from dual union all 
select 10,1 from dual union all 
select 11,-1 from dual union all
select 12,1 from dual 
)
select abs(sum(seq*wgt)) 
from weights
where seq>=10;

Answer 2

  貌似MS SQL的写法！

Answer 3

正则表达式可以用NOT REGEXP_LIKE(barcode,'%^[0-9]%')
values（）可以像gelyon一样用with创建公共临时表，或者用字查询select * from (子查询union all )

Answer 4

--晕，漏了一个From （barcode FROM Weights.seq FOR 1），更正如下：CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))
RETURNS INTEGER
RETURN
( 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]%');

Answer 5

二楼的实现了 常量表，
补充题意：
Barcode_CheckSum 是一个条形码的检验函数，检验规则如下：
（1）：计算各奇数位的和得到S1；
（2）：计算各偶数位的和得到S2；
条形码的校验和位的计算公式是： ABS(MOD((s1-s2),10))
标准SQL源码 如下：CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))
RETURNS INTEGER
RETURN
( 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]%');--个人认为改为oracle 语句是要注意如下3点：
(1):输入参数的格式：IN my_barcode CHAR(12)
    oracle中是:my_barcode in TYPE, 其中类型不能有大小，my_barcode IN CHAR(12)
会报错：PLS-00103。 不知道oracle为什么要这样，如果想限定参数的大小有什么办法吗？还是只能去函数里面判断？
(2): VALUES () 常量表，作为一个查找表，二楼和四楼已经提到解决方法。(3): 对输入参数my_barcode由数字字符组成的检验：
                  WHERE barcode NOT SIMILAR TO '%[^0-9]%');换句话描述：组成my_barcode 的所以字符都是数字字符。
sql里处理全称量词的方法是转换成双重否定（有更专业的术语吗？），上面描述可转换成：
                   my_barcode的字符没有一个是非数字字符
源码中WHERE barcode NOT SIMILAR TO '%[^0-9]%');里的 '%[^0-9]%'应该是：至少有一个是非数字字符的意思。那这个符号'^'在源码里应该是非的意识，而oracle里是表示以某字符打头的意思。四楼的关于正则表达式的代码执行如下，其结果是有误的，SQL> SELECT  id
  2  FROM  a
  3  WHERE
  4  NOT REGEXP_LIKE(a.id,'%^[0-9]%')
  5  ;ID
----------------------------------------------------------------
036AFCLASLAMOMYR
036AFCLASLAMONOK
大家再思考思考啊，期盼中！

Answer 6

SQL> create or replace function func_identify_barcode(i_str varchar2)
  2    return varchar2 as
  3    v_sign   number;
  4    v_result number;
  5  begin
  6    if length(i_str) !=
  7       length(translate(i_str, '0123456789' || i_str, '0123456789')) or
  8       length(i_str) != 12 then
  9      return null;
 10    end if;
 11    v_sign   := 1;
 12    v_result := 0;
 13    for i in 1 .. length(i_str) loop
 14      v_sign   := -v_sign;
 15      v_result := v_result + substr(i_str, i, 1) * v_sign;
 16    end loop;
 17    return abs(v_result);
 18  end;
 19  /
 
Function created
 
SQL> select func_identify_barcode('1a') result from dual;
 
RESULT
--------------------------------------------------------------------------------
 
SQL> select func_identify_barcode('1') result from dual;
 
RESULT
--------------------------------------------------------------------------------
 
SQL> select func_identify_barcode('123456789012') result from dual;
 
RESULT
--------------------------------------------------------------------------------
4
 
SQL> 

Answer 7

--不知道对不对？
with weights as(
   SELECT LEVEL seq,Power(-1,level) wgt 
   FROM dual
   CONNECT BY LEVEL<=12
)
SELECT 
abs(sum(Decode(Mod(seq,2),1,seq*wgt,0))-sum(Decode(Mod(seq,2),0,seq*wgt,0))) result
FROM weights
WHERE NOT REGEXP_LIKE(seq,'%[^[:digit:]] %')  ;

Answer 8

返回的是一条sql语句，如果不是纯数字的话，会返回null，就像 select × from dual where 1= 2 一样

Answer 9

with weights as(
SELECT LEVEL seq,Power(-1,level) wgt 
FROM dual
CONNECT BY LEVEL<=12
)
SELECT abs(sum(Decode(Mod(seq,2),1,seq*wgt,0))-sum(Decode(Mod(seq,2),0,seq*wgt,0))) result
FROM weights
WHERE 
NOT REGEXP_LIKE(seq,'[^[:digit:]]')  ;   --上面正则表达式错的，应该是这样

Answer 10

--创建函数：通过参数来构建常量表 ，返回条形码结果
CREATE OR REPLACE FUNCTION Barcode_CheckSum(my_barcode IN VARCHAR2)
RETURN NUMBER 
IS
result NUMBER;
BEGIN
      with weights as(
           SELECT LEVEL seq,Power(-1,level) wgt 
           FROM dual
           CONNECT BY LEVEL<=my_barcode
      )
      SELECT abs(sum(Decode(Mod(seq,2),1,seq*wgt,0))-sum(Decode(Mod(seq,2),0,seq*wgt,0))) into result
      FROM weights
      WHERE 
      NOT REGEXP_LIKE(seq,'[^[:digit:]]')  ;        RETURN result;
END;--测试：
SELECT Barcode_CheckSum('12') result FROM dual;--结果：
      RESULT
      ------
          78

Answer 11

-- 不好意思，可能是我没把需求说明白，标准条形码有13位，其中最后一位是校验位，
-- 我们这个函数的结果是得到条形码的校验位，该函数的输入参数 my_barcode 是一个标准条形码的前12位，我们现在要得到条形码的校验位，如果输入参数有误，是得不到的结果，上面的正则表达式是对的：NOT REGEXP_LIKE(a.foracid,'[^[:digit:]]') 这个[:digit:] 是 匹配数字， '[^[:digit:]]' 这个组合能解释一下吗？ 

Answer 12

REGEXP_LIKE(seq,'[^[:digit:]]') 匹配包含了任意非数字字符，
前面加not 就是匹配全部都是数字字符的需求不清楚，只能写到这里了，sorry

Answer 13

八楼的结果是对的，上面做了一下简单注释，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 
*/

Answer 14

谢谢啊，提问的目的就是想弄明白，WHERE barcode NOT SIMILAR TO '%^[0-9]%') 和 value()常量表 在oracle 中怎么实现的，现在知道了，但不知道为什么是这样，请指教。这个[:digit:] 是 匹配数字， 
'[^[:digit:]]' 这个组合能解释能在详细点吗？   NOT REGEXP_LIKE(seq,'[^[:digit:]]') 表示：在seq中，没有一个字符是非数字字符，
对这部分分解一下：
(1) not （seq 中至少有一个非数字字符）=》
         REGEXP_LIKE(seq,'[^[:digit:]]')表示：seq 中至少有一个非数字字符
又因为：这个[:digit:] 是 匹配数字，表示任意一个数字字符 
所以进一步替换： REGEXP_LIKE(seq,'[^{任意一个数字字符}]')
剩下的部分：REGEXP_LIKE(seq,'[^ ]') 应是：将 任意一个数字字符  转换成 seq 中至少有一个非数字字符？
这一步是怎么转换的啊？能再解释详细点吗？
一般^在正则表达式里表示匹配字符串的开头位置，难道在[] 里 ^ 变成了 '非' ？也就是说：[] 有转意字符的功能？为什么'[^[:digit:]]' 不是表示的如下意思：任意一个非数字字符 ？没弄明白，不知道是不是问多了，还是想知道原因， 谢谢了,

Answer 15

如果你要了解更多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

Answer 16

15解释的比较清楚了.
[^[:digit:]]表示的是字段含有非数字字符,前面再加个not取反,所以整个的意思是取不含有非数字字符的串,也就是全是数字字符构成的串.

Answer 17

WHERE NOT REGEXP_LIKE(seq,'[^[:digit:]]')  ;  

Answer 18

自己也写了一个过程式的方法，没有8楼的精巧。CREATE OR REPLACE FUNCTION BarcodeCheckSum( my_barcode IN VARCHAR)
RETURN INTEGER AS
    v_my_barcode VARCHAR(12):= NULL;
    barcode_checkers INTEGER:=0;
    idx INTEGER :=1;
    sgn INTEGER:=-1;
    num_flg NUMBER:=NULL;
BEGIN
    -- 这个begin块可以不用，只是为了便于维护，才加了一个v_my_barcode 变量，
    -- 如果 my_barcode 的名字需要修改时，只用改一处即可
    BEGIN
    --{
        v_my_barcode := trim(my_barcode);
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('my_barcode too long');
        RETURN -1;
    --}
    END;
    
    ---- check length
    IF length(v_my_barcode) <>12 THEN
    --{
        RETURN -1;
        dbms_output.put_line('my_barcode length is not right');
    --}
    END IF;    ----check all char is numchar exception point
    -- 利用 to_number判断是不是全部是数字字符，
    -- 但单用to_number不能排除有小数点的情况，
    -- 所以用like排除一下
    -- 如果有小数点，num_flg就不为空， 再在下一个if块中判断
    -- 值得一提的时，虽然others可以捕获所有异常，但是如果一个异常被前一个when捕获，
    -- when other 就不会再进行捕获
    BEGIN
    --{
        SELECT to_number(v_my_barcode) 
        INTO num_flg
        FROM dual WHERE v_my_barcode  LIKE '%.%';
    EXCEPTION 
    WHEN no_data_found THEN
        num_flg := NULL;
    WHEN OTHERS THEN
        dbms_output.put_line('my_barcode there are some NAN');
        dbms_output.put_line(SQLCODE||'   '|| SQLERRM);
        RETURN -2;
    --}
    END;    --check all char is numchar, exception point
    IF num_flg IS NOT NULL THEN
    --{
        dbms_output.put_line('my_barcode there have one point');    
        RETURN -2;
    --}
    END if;    --计算各奇数位的和减去各偶数位的和
    WHILE idx <13 LOOP
    --{        sgn := -sgn;
        barcode_checkers := barcode_checkers + to_number(substr(v_my_barcode,idx,1))*sgn;
        idx := idx + 1;
    --}
    END LOOP;
    --  
    RETURN ABS(MOD(barcode_checkers,10));END BarcodeCheckSum;
/
        
select BarcodeCheckSum('111111114111') AS barcode from dual;

Answer 19

又用刚学到的改写了一下，SELECT ABS(SUM( SUBSTR( '121212121212', Weights.seq, 1)  * Weights.wgt))
   FROM (SELECT 1 seq, 1 wgt FROM dual
         UNION ALL
         SELECT 2 , -1 FROM dual
         UNION ALL
         SELECT 3 , 1 FROM dual
         UNION ALL
         SELECT 4 , -1 FROM dual
         UNION ALL
         SELECT 5 , 1 FROM dual
         UNION ALL
         SELECT 6 , -1 FROM dual
         UNION ALL
         SELECT 7 , 1 FROM dual
         UNION ALL
         SELECT 8 , -1 FROM dual
         UNION ALL
         SELECT 9 , 1 FROM dual
         UNION ALL
         SELECT 10 , -1 FROM dual
         UNION ALL
         SELECT 11 , 1 FROM dual
         UNION ALL
         SELECT 12 , -1 FROM dual)
    Weights
   WHERE  NOT REGEXP_LIKE('121212121212', '[^[:digit:]]');上面的sql没问题，但是放到函数里却有问题：
函数如下：CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
AS
BEGIN
RETURN   SELECT ABS(SUM( SUBSTR( '121212121212', Weights.seq, 1)  * Weights.wgt))
   FROM (SELECT 1 seq, 1 wgt FROM dual
         UNION ALL
         SELECT 2 , -1 FROM dual
         UNION ALL
         SELECT 3 , 1 FROM dual
         UNION ALL
         SELECT 4 , -1 FROM dual
         UNION ALL
         SELECT 5 , 1 FROM dual
         UNION ALL
         SELECT 6 , -1 FROM dual
         UNION ALL
         SELECT 7 , 1 FROM dual
         UNION ALL
         SELECT 8 , -1 FROM dual
         UNION ALL
         SELECT 9 , 1 FROM dual
         UNION ALL
         SELECT 10 , -1 FROM dual
         UNION ALL
         SELECT 11 , 1 FROM dual
         UNION ALL
         SELECT 12 , -1 FROM dual)
    Weights
   WHERE  NOT REGEXP_LIKE('121212121212', '[^[:digit:]]')
   AND ROWNUM<2;
END;
LINE/COL ERROR
-------- ---------------------------------------------------------------------5/10     PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:       ( - + ; case mod new not null <an identifier>  
百度一个类似的答案说：高版本的语法低版本未必支持.但是下面这个也不行：
SQL> CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
  2  RETURN num_test_tab
  3  AS
  4  BEGIN
  5  RETURN  SELECT 5 FROM dual WHERE ROWNUM<2;
  6  END;
  7  /Warning: Function created with compilation errorsSQL> show err
Errors for FUNCTION DBREAD.BARCODE_CHECKSUM:LINE/COL ERROR
-------- -------------------------------------------------------------5/9      PLS-00103: Encountered the symbol "SELECT" when exp......SELECT 5 FROM dual WHERE ROWNUM<2; 这条语句够简单为什么还会出错？

Answer 20

问题解决：
SQL> CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
  2  RETURN INTEGER
  3  AS
  4  
  5  num NUMBER:=0;
  6  BEGIN
  7      BEGIN
  8       SELECT ABS(SUM( SUBSTR( my_barcode, Weights.seq, 1)  * Weights.wgt))
  9       INTO num
 10     FROM (SELECT 1 seq, 1 wgt FROM dual
 11           UNION ALL
 12           SELECT 2 , -1 FROM dual
 13           UNION ALL
 14           SELECT 3 , 1 FROM dual
 15           UNION ALL
 16           SELECT 4 , -1 FROM dual
 17           UNION ALL
 18           SELECT 5 , 1 FROM dual
 19           UNION ALL
 20           SELECT 6 , -1 FROM dual
 21           UNION ALL
 22           SELECT 7 , 1 FROM dual
 23           UNION ALL
 24           SELECT 8 , -1 FROM dual
 25           UNION ALL
 26           SELECT 9 , 1 FROM dual
 27           UNION ALL
 28           SELECT 10 , -1 FROM dual
 29           UNION ALL
 30           SELECT 11 , 1 FROM dual
 31           UNION ALL
 32           SELECT 12 , -1 FROM dual)
 33      Weights
 34     WHERE  NOT REGEXP_LIKE(my_barcode, '[^[:digit:]]');
 35  
 36      END;
 37  
 38  RETURN  num;
 39  END;
 40  /Function createdSQL> 
SQL> select Barcode_CheckSum('111111111111') from dual;BARCODE_CHECKSUM('111111111111
------------------------------
                             0

Answer 21

--写那么多union all
--还不如这样直接点
SELECT LEVEL seq,Power(-1,level) wgt 
FROM dual
CONNECT BY LEVEL<=12

Answer 22

从这里看明白你 的意思
补充题意：
Barcode_CheckSum 是一个条形码的检验函数，检验规则如下：
（1）：计算各奇数位的和得到S1；
（2）：计算各偶数位的和得到S2；
条形码的校验和位的计算公式是： ABS(MOD((s1-s2),10))
标准SQL源码 如下：等下

Answer 23

SQL> ed
已写入 file afiedt.buf  1  create or replace function fun_dt(str varchar2) return number
  2  as
  3  sum1 number:=0;
  4  sum2 number:=0;
  5  s1 varchar2(10);
  6  s2 varchar2(10);
  7  num varchar2(100);
  8  begin
  9  select decode(sign(length(str)-length(regexp_replace(str,'[[:alpha:]]',''))),0,str,null)
 10  into num from dual;
 11  if num is null or length(num)<12 then
 12  return null;
 13  else
 14  for i in 1..length(num) loop
 15  if mod(i,2)=0 then
 16  select substr(num,i,1) into s1 from dual;
 17  sum1:=sum1+s1;
 18  else
 19  select substr(num,i,1) into s2 from dual;
 20  sum2:=sum2+s2;
 21  end if;
 22  end loop;
 23  return abs(mod((sum1-sum2),10)) ;
 24  end if;
 25  exception
 26  when others then
 27  dbms_output.put_line(sqlcode||'  '||sqlerrm);
 28* end;
SQL> /函数已创建。SQL> select fun_dt('123456789012') f1,
  2  fun_dt('12s3456789012') f2,
  3  fun_dt('12233') f3,
  4  fun_dt('111111111111') f2
  5  from dual
  6  /        F1         F2         F3         F2
---------- ---------- ---------- ----------
         4                                0

Answer 24

简化如下：CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
ASnum NUMBER:=0;
BEGIN
    BEGIN
       SELECT ABS(SUM( SUBSTR( my_barcode, Weights.seq, 1)  * Weights.wgt))
       INTO num
       FROM ( SELECT LEVEL seq,Power(-1,level) wgt 
              FROM dual
              CONNECT BY LEVEL<=12)
              Weights
       WHERE  NOT REGEXP_LIKE(my_barcode, '[^[:digit:]]');
    END;RETURN  num;
END;

Answer 25

上面的少了modCREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
ASnum NUMBER:=0;
BEGIN
    BEGIN
       SELECT MOD(ABS(SUM( SUBSTR( my_barcode, Weights.seq, 1)  * Weights.wgt)),10)
       INTO num
       FROM ( SELECT LEVEL seq,Power(-1,level) wgt 
              FROM dual
              CONNECT BY LEVEL<=12)
              Weights
       WHERE  NOT REGEXP_LIKE(my_barcode, '[^[:digit:]]');
    END;RETURN  num;
END;

Answer 26

总结一下结贴了去CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
ASnum 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;

Answer 27

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;