주민등록번호 생성 및 검증 쿼리
* 단, 1900년 이전 태생과 외국인 번호(뒤7자리중 첫자리가 5,6,7,8인 경우)는 제외
-- 주민등록번호 생성
WITH t AS (
SELECT 100 n, TO_DATE('190001','yyyymm') sdt, TRUNC(sysdate) edt FROM dual)
SELECT st || MOD((11 - MOD(sum(SUBSTR(st, lv, 1)*(MOD(lv-1, 8)+2)),11)),10) jm FROM
(
SELECT xy || lpad(ROUND(dbms_random.VALUE(0, 99999)), 5, '0') st from
(
SELECT SUBSTR(ymd, 3) || (1+ROUND(dbms_random.VALUE(0,1))
+ (CASE WHEN SUBSTR(ymd, 1,4) < '2000' THEN 0 ELSE 2 end)) xy FROM (
SELECT TO_CHAR(sdt + dbms_random.VALUE(0, edt-sdt),'yyyymmdd') ymd FROM t
CONNECT BY level<=n
)
)
) a,
(SELECT level lv FROM dual CONNECT BY level<=12) b
GROUP BY st
-- 검증
WITH t AS (
SELECT '0009084103872' jm FROM dual)
SELECT decode(MOD(11 - MOD(sum(SUBSTR(jm, level, 1)*(MOD(level-1, 8)+2)),11),10),
SUBSTR(jm, 13), 'TRUE', 'FALSE') chk FROM t CONNECT BY level<=12