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

[物润声无]

一在书上看到一段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 写？ 整了好长时间没搞定，求教。

[gelyon]

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

[minitoy]

15解释的比较清楚了.
[^[:digit:]]表示的是字段含有非数字字符,前面再加个not取反,所以整个的意思是取不含有非数字字符的串,也就是全是数字字符构成的串.
[Quote=引用 17 楼 gjswxhb 的回复:]
引用 15 楼 gelyon 的回复:
引用 14 楼 gjswxhb 的回复:
引用 13 楼 gelyon 的回复:
SQL code


--创建函数：通过参数来构建常量表 ，返回条形码结果
CREATE OR REPLACE FUNCTION Barcode_CheckSum(my_barcode IN VARCHAR2)
RETURN NUMBER
IS
result……
[/Quote]

[minitoy]

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

[心中的彩虹]

什么



正则？？？

[物润声无]

自己也写了一个过程式的方法，没有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;

[zwjcome]

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

[gelyon]

这下明白了，刚开始我没理解你的需求，只是看这你给的函数有点奇怪，按照自己思路写了，原来是这样的，呵呵，学习了。。。。

[物润声无]

[Quote=引用 23 楼 gelyon 的回复:]
这下明白了，刚开始我没理解你的需求，只是看这你给的函数有点奇怪，按照自己思路写了，原来是这样的，呵呵，学习了。。。。
[/Quote]

又用刚学到的改写了一下，

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 errors

SQL> 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; 这条语句够简单为什么还会出错？

[物润声无]

[Quote=引用 24 楼 gjswxhb 的回复:]
引用 23 楼 gelyon 的回复:
这下明白了，刚开始我没理解你的需求，只是看这你给的函数有点奇怪，按照自己思路写了，原来是这样的，呵呵，学习了。。。。


又用刚学到的改写了一下，

SQL code

SELECT ABS(SUM( SUBSTR( '121212121212', Weights.seq, 1) * Weights.wgt))
FROM (SELE……
[/Quote]
问题解决：

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 created

SQL> 
SQL> select Barcode_CheckSum('111111111111') from dual;

BARCODE_CHECKSUM('111111111111
------------------------------
                             0

[gelyon]

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

[物润声无]

--晕，漏了一个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]%');

[物润声无]

[Quote=引用 2 楼 gelyon 的回复:]
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
[/Quote]

二楼的实现了 常量表，
补充题意：
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

大家再思考思考啊，期盼中！

[物润声无]

总结一下结贴了去

CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
AS

num 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;

[物润声无]

CREATE OR REPLACE FUNCTION func_identify_barcode(i_str VARCHAR2)
RETURN VARCHAR2 AS

v_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;

[心中的彩虹]

[Quote=引用 6 楼 gjswxhb 的回复:]
引用 2 楼 gelyon 的回复:
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,……
[/Quote]

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

等下

[心中的彩虹]

[Quote=引用 27 楼 wkc168 的回复:]
引用 6 楼 gjswxhb 的回复:
引用 2 楼 gelyon 的回复:
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……
[/Quote]

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

[物润声无]

[Quote=引用 26 楼 gelyon 的回复:]
SQL code

--写那么多union all
--还不如这样直接点
SELECT LEVEL seq,Power(-1,level) wgt
FROM dual
CONNECT BY LEVEL<=12
[/Quote]
太棒了，又简化了，

[物润声无]

简化如下：

CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
AS

num 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;

[物润声无]

上面的少了mod

CREATE OR REPLACE FUNCTION Barcode_CheckSum( my_barcode IN VARCHAR)
RETURN INTEGER
AS

num 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;

[gelyon]