1.这是自定义校验身份证号和残疾证号的自定义函数
CREATE OR REPLACE Function Func_checkdisabilitycard (p_idcard in varchar2) Return Number
--返回值:0:错误的证件号码;1:正确的身份证号码;2:正确的残疾证号码
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 20 THEN
-- 20位
IF not regexp_like(SUBSTRB(p_idcard, -2, 2),'(^[0-9])') then
return 0;
end if;
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][0-9]{2}$';
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][0-9]{2}$';
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)) and
to_number(substr(p_idcard, -2,1)) >= 1 and--残疾证号倒数第二位1-7之间代表残疾类型
to_number(substr(p_idcard, -2,1)) <= 7 and
to_number(substr(p_idcard, -1,1)) >= 1 and--残疾证号倒数第一位1-4之间代表残疾等级
to_number(substr(p_idcard, -1,1)) <= 4 THEN
RETURN 2;
ELSE
RETURN 0;
END IF;
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 Func_checkdisabilitycard;以下是在存储过程中直接调用该函数,但是debug到这里就卡死。。
insert into TB_DATA_CHECK_BFZRR_DEAL
select t.aaa001
from TB_HU_ALL partition(PAR_2016) t, TB_CY_ALL partition(PAR_2016) s
where Func_checkdisabilitycard(s.aad003) <> '2'
and t.status <> '0'
and s.status <> 0'
and s.aad008 = '04'
求大神帮忙解决,已经困扰我好几周了!!!谢谢!谢谢!
CREATE OR REPLACE Function Func_checkdisabilitycard (p_idcard in varchar2) Return Number
--返回值:0:错误的证件号码;1:正确的身份证号码;2:正确的残疾证号码
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 20 THEN
-- 20位
IF not regexp_like(SUBSTRB(p_idcard, -2, 2),'(^[0-9])') then
return 0;
end if;
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][0-9]{2}$';
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][0-9]{2}$';
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)) and
to_number(substr(p_idcard, -2,1)) >= 1 and--残疾证号倒数第二位1-7之间代表残疾类型
to_number(substr(p_idcard, -2,1)) <= 7 and
to_number(substr(p_idcard, -1,1)) >= 1 and--残疾证号倒数第一位1-4之间代表残疾等级
to_number(substr(p_idcard, -1,1)) <= 4 THEN
RETURN 2;
ELSE
RETURN 0;
END IF;
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 Func_checkdisabilitycard;以下是在存储过程中直接调用该函数,但是debug到这里就卡死。。
insert into TB_DATA_CHECK_BFZRR_DEAL
select t.aaa001
from TB_HU_ALL partition(PAR_2016) t, TB_CY_ALL partition(PAR_2016) s
where Func_checkdisabilitycard(s.aad003) <> '2'
and t.status <> '0'
and s.status <> 0'
and s.aad008 = '04'
求大神帮忙解决,已经困扰我好几周了!!!谢谢!谢谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货