码农之家 - 身份证
type: channel
link: http://blog.liyx.fun/index.php/tag/身份证/
items: Oracle 从身份证计算年龄

Oracle 从身份证计算年龄
type: item
date: 2023-01-30T11:11:00+08:00
description: CREATE OR REPLACE FUNCTION AGE_IN_IDCARD ( parameter VARCHAR2 ) RETURN NUMBER IS val DATE; BEGIN IF parameter is NULL THEN RETURN -1; ELSE val := CASE WHEN LENGTH(parameter) <= 15 THEN TO_DATE( '19'||NVL( SUBSTR( parameter, 7, 6 ), 'a' ), 'yyyy-MM-dd' ) ELSE TO_DATE( NVL( SUBSTR( parameter, 7, 8 ), 'a' ), 'yyyy-MM-dd' ) END; END IF; RETURN TRUNC( ( TO_CHAR( SYSDATE, 'yyyyMMdd' ) - TO_CHAR( val, 'yyyyMMdd' ) ) / 10000 ); EXCEPTION WHEN OTHERS THEN RETURN - 1; END;