一组与身份证号码相关的自定义小函数

本文链接：https://blog.youkuaiyun.com/nisjlvhudy/article/details/8571874

本文介绍了几个关于身份证号码的自定义函数，包括验证数字、日期有效性，确认身份证号码合法，计算持证人的年龄及性别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、验证是否为数字

CREATE OR REPLACE FUNCTION IS_NUMBER(STRING VARCHAR2) RETURN INTEGER IS
BEGIN
  IF (LENGTH(TRIM(TRANSLATE(STRING, ' +-.0123456789', ' '))) IS NULL) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END IS_NUMBER;

2、验证是否为日期

CREATE OR REPLACE FUNCTION IS_DATE(PARMIN VARCHAR2) RETURN NUMBER IS
  VAL DATE;
BEGIN
  VAL := TO_DATE(NVL(PARMIN, 'a'), 'yyyy-mm-dd hh24:mi:ss');
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;

3、验证身份证号码的有效性

CREATE OR REPLACE FUNCTION IS_IDCARD(P_IDCARD VARCHAR2) RETURN INTEGER IS
  IDCARDLEN INTEGER DEFAULT 0;
BEGIN
  IDCARDLEN := LENGTH(P_IDCARD);
  IF (IDCARDLEN = 18 AND IS_NUMBER(SUBSTR(P_IDCARD, 1, IDCARDLEN - 1)) = 1 AND
     IS_DATE(SUBSTR(P_IDCARD, 7, 8)) = 1) OR
     (IDCARDLEN = 15 AND IS_NUMBER(SUBSTR(P_IDCARD, 1, IDCARDLEN)) = 1 AND
     IS_DATE('19' || SUBSTR(P_IDCARD, 7, 6)) = 1) THEN
    RETURN 1;
  ELSE
    RETURN 1;
  END IF;
END IS_IDCARD;

4、根据身份证号码计算年龄

CREATE OR REPLACE FUNCTION GET_AGE_FROM_IDCARD(WORKERID IN VARCHAR2)
  RETURN VARCHAR2 IS
  AGEVALUE VARCHAR2(10);
BEGIN
  IF (LENGTH(TRIM(WORKERID)) = 18) THEN
    SELECT (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(SUBSTR(WORKERID, 7, 4)))
      INTO AGEVALUE
      FROM DUAL;
  ELSE
    IF (LENGTH(TRIM(WORKERID)) = 15) THEN
      SELECT (TO_CHAR(SYSDATE, 'yyyy') -
             TO_CHAR('19' || SUBSTR(WORKERID, 7, 2)))
        INTO AGEVALUE
        FROM DUAL;
    ELSE
      AGEVALUE := '0';
    END IF;
  END IF;
  RETURN AGEVALUE;
END GET_AGE_FROM_IDCARD;

5、根据身份证号码计算性别

CREATE OR REPLACE FUNCTION GET_SEX_FROM_IDCARD(P_IDCARD VARCHAR2)
  RETURN VARCHAR2 IS
  IDCARDLEN INTEGER;
BEGIN
  IDCARDLEN := LENGTH(P_IDCARD);
  IF IS_IDCARD(P_IDCARD) = 0 THEN
    RETURN NULL;
  END IF;
  IF IDCARDLEN = 18 AND SUBSTR(P_IDCARD, 17, 1) IN (1, 3, 5, 7, 9) THEN
    RETURN('男');
  END IF;
  IF IDCARDLEN = 18 AND SUBSTR(P_IDCARD, 17, 1) IN (2, 4, 6, 8, 0) THEN
    RETURN('女');
  END IF;
  IF IDCARDLEN = 15 AND SUBSTR(P_IDCARD, 15, 1) IN (1, 3, 5, 7, 9) THEN
    RETURN('男');
  END IF;
  IF IDCARDLEN = 15 AND SUBSTR(P_IDCARD, 15, 1) IN (2, 4, 6, 8, 0) THEN
    RETURN('女');
  END IF;
END GET_SEX_FROM_IDCARD;