達夢數據庫用函數實現身份證合法校驗

拿走不謝~

CREATE OR REPLACE FUNCTION CHECK_IDCARD(A_SFZ IN VARCHAR2)
RETURN VARCHAR2
IS
TYPE WEIGHT_TAB IS VARRAY(17) OF NUMBER; 
TYPE CHECK_TAB IS VARRAY(11) OF CHAR;
WEIGHT_FACTOR WEIGHT_TAB := WEIGHT_TAB(7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2);
CHECK_CODE CHECK_TAB := CHECK_TAB('1','0','X','9','8','7','6','5','4','3','2');
SUM_VAL NUMBER := 0;
MOD_VAL NUMBER;
BEGIN
IF LENGTH(A_SFZ) != 18 THEN RETURN '-1'; END IF; -- 長度校驗
FOR I IN 1..17 LOOP
IF SUBSTR(A_SFZ,I,1) NOT BETWEEN '0' AND '9' THEN RETURN '-1'; END IF; -- 數字檢查
SUM_VAL := SUM_VAL + TO_NUMBER(SUBSTR(A_SFZ,I,1)) * WEIGHT_FACTOR(I); -- ORACLE數組下標訪問
END LOOP;
MOD_VAL := MOD(SUM_VAL,11);
IF SUBSTR(A_SFZ,18,1) != CHECK_CODE(MOD_VAL+1) THEN RETURN '0'; END IF; -- 校驗碼比對
RETURN '1'; -- 驗證通過
END;