DB2中15位与18位身份证号码转换过程

来源：互联网发布：电脑系统监控软件编辑：程序博客网时间：2024/06/07 05:49

CREATE PROCEDURE "ZHCX"."PROC_GETID" (OUT OUTARG VARCHAR(60), IN sfz VARCHAR(60))
LANGUAGE SQL
    READS SQL DATA
BEGIN
    declare osfz VARCHAR(60);
    declare i int;
    declare ai int;
    declare wi int;
    declare v_sum int;
    declare v_mod int;
    declare result int;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET OUTARG=sfz;

    set osfz = sfz;
    set v_sum = 0;
    IF length(osfz) = 15 then
        set osfz = substr(osfz,1,6) || '19' || substr(osfz,7,9);
        set i = 2;

        while i <= 18
        do
            set ai = cast(substr(osfz,19 - i,1) as int);
            set wi = mod(POWER (2, (i - 1)),11) ;
            set v_sum = v_sum + ai * wi;
            set i = i + 1;
        end while;

set v_mod = mod(v_sum,11) ;
set result = 12 - v_mod;

            IF result >= 10 then
               IF result = 10 then
                   --RETURN osfz || 'X';
                   SET OUTARG=osfz || 'X';
               ELSE
                   set result = result - 11;
                   --RETURN osfz || ltrim(cast(result as char));
                   SET OUTARG=osfz || ltrim(cast(result as char));
               end if;
            ELSE
               --RETURN osfz || ltrim(cast(result as char));
               SET OUTARG=osfz || ltrim(cast(result as char));
            end if;
    ELSE if length(osfz) = 18 then
       --RETURN substr(osfz,1,6) || substr(osfz,9,9);
       SET OUTARG=substr(osfz,1,6) || substr(osfz,9,9);
    ELSE
        --RETURN sfz;
        SET OUTARG=sfz;
    end if;end if;

SET OUTARG=sfz;
END