2021年10月20日 星期三

PL/SQL - 身分證檢查

 

PL/SQL - 身分證檢查

http://my-plsql.blogspot.com/2009/04/plsql_22.html

參考修改成 Function


CREATE OR REPLACE FUNCTION CHK_IDN_VALID (
    p_idn IN VARCHAR2
) RETURN BOOLEAN IS
    p_chkflg_out BOOLEAN;
BEGIN
    DECLARE
        myid          VARCHAR2(10); --身分證號碼
        temp          PLS_INTEGER;
        combine_varchar2  VARCHAR2(11) := NULL;
        --權重
        weight_varchar2   VARCHAR2(11) := '19876543211';

        FUNCTION get_prechar (
            v_pre IN VARCHAR2
        ) RETURN VARCHAR2 IS

            TYPE charTable is table of varchar2(10);
            t_charTable charTable := charTable('A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','X','Y','W','Z','I','O');
            TYPE numberTable is table of varchar2(10);
            t_numberTable numberTable := numberTable('10','11','12','13','14','15','16','17','18','19    20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35');


        BEGIN
            FOR i IN 1..26 LOOP
                IF (t_charTable(i) = v_pre) THEN
                    RETURN t_numberTable(i);
                END IF;
            END LOOP;
        END get_prechar;

    BEGIN
        myid := p_idn;
        p_chkflg_out := false;

        --長度檢查
        IF (LENGTH(myid) <> 10) THEN
            RETURN p_chkflg_out;
        END IF;
        --第一碼檢查
        temp := ascii(substr(myid, 1, 1));
        IF (temp <65 OR temp > 90) THEN
            RETURN p_chkflg_out;
        END IF;

        --第二碼檢查
        temp := to_number(substr(myid, 2, 1));
        IF temp NOT IN ( 1, 2 ) THEN
           RETURN p_chkflg_out;
        END IF;

        temp := 0;
        combine_varchar2 := get_prechar(substr(myid, 1, 1)) || substr(myid, 2, 9);

        FOR i IN 1..11 LOOP
            temp := temp + (to_number(substr(combine_varchar2, i, 1)) * to_number(substr(weight_varchar2, i, 1)));
        END LOOP;

        IF (MOD(temp, 10) = 0) THEN
            p_chkflg_out := true;
        ELSE
            p_chkflg_out := false;
        END IF;

        RETURN p_chkflg_out;
    EXCEPTION
        WHEN OTHERS THEN
           Dbms_Output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           RETURN p_chkflg_out;
    END;
END chk_idn_valid;


-- Created on 2021/10/21 by SSC24 
set SERVEROUTPUT ON
declare 
  -- Local variables here
  i integer;
  idn2 varchar2(2);
  idn8 varchar2(8);
  idn varchar2(10);
begin
  -- Test statements here
  FOR i in 0..99 LOOP
    idn8 := 'F1252912';
    idn2 := lpad(i,2,'0');
    idn := idn8 || idn2;
    IF nps.CHK_IDN_VALID (idn) THEN
      Dbms_Output.put_line(idn);
    END IF;
  END LOOP;
end;


F125291209
F125291218
F125291227
F125291236
F125291245
F125291254
F125291263
F125291272
F125291281
F125291290

已順利完成 PL/SQL 程序.

標籤：