Oracle function for upgrading a 15-digit ID card to a 18-digit ID card

Oracle FUNCTION [SQL] www.2cto.com create or replace function FUNC_AAC00215_18 (prm_aac002 varchar2) return varchar2 is ll_ I number (4): = 18; ll_row number (5): = 0; var_aac002 varchar2 (18); ll_sfz18 number (3); ll_sfz varchar2 (3); var_sfz18 varchar (18); var_date varchar (10); dat_date date; ll_num number (2); s_sfz varchar2 (15); s_sss varchar2 (20): = 'X'; v_newaac002 varchar (18); n_length number (2); V_CHAR varchar2 (15); s_date varchar2 (15); begin if prm_aac002 is null then return ''; end if; if length (prm_aac002) <> 15 and length (prm_aac002) <> 18 then return ''; end if; if length (prm_aac002) = 18 then n_length: = length (prm_aac002 ); FOR n_ I IN 1 .. n_length LOOP V_CHAR: = SUBSTR (prm_aac002, n_ I, 1); IF V_CHAR IN ('0', '1', '2', '3', '4 ', '5', '6', '7', '8', '9') AND N_ I <18 THEN v_newaac0 02: = prm_aac002; ELSIF N_ I = 18 AND V_CHAR IN ('0', '1', '2', '3', '4', '5 ', '6', '7', '8', '9', 'x') THEN v_newaac002: = prm_aac002; else return 'indicates an invalid character! '; End if; end loop;/* 2. whether the date of birth is valid */s_date: = substr (prm_aac002, 7, 8); begin select to_date (s_date, 'yyyymmdd') into dat_date from dual; exception when others then return 'wrong Date of birth! '; End; if substr (prm_aac002, 7, 2)> 20 or substr (prm_aac002, 7, 2) <19 then return' year incorrect! '; End if;/* 3. last bit verification */while n_length> = 2 loop begin select to_number (substr (prm_aac002, 19-n_length, 1) into ll_num from dual; exception when others then return ''; end; ll_row: = ll_row + mod (power (2, (n_length-1), 11) * to_number (substr (prm_aac002, 19-n_length, 1); n_length: = n_length-1; end loop; ll_sfz18: = mod (ll_row, 11); select decode (to_char (ll_sfz18), '0', '1', '1 ', '0', '2', 'x ', '3', '9', '4', '8', '5', '7', '6', '6', '7', '5 ', '8', '4', '9', '3', '10', '2') into ll_sfz from dual; if ll_sfz <> substr (prm_aac002, 18, 1) The number of then return 'tails is incorrect! It should be '| ll_sfz; end if; RETURN v_newaac002; end if; if prm_aac002 = '000000' then return ''; end if; if s_sfz <> 'X' then s_sss: = s_sfz; else s_sss: = prm_aac002; end if; if length (prm_aac002) = 15 then var_date: = '19' | substr (s_sss, 7, 6 ); begin select to_date (var_date, 'yyyymmdd') into dat_date from dual; exception when others then return ''; end; var_aac002: = substr (s_sss, 0, 6) | '19' | substr (s_sss, 7, 9); while ll_ I> = 2 loop begin select to_number (substr (var_aac002, 19-ll_ I, 1 )) into ll_num from dual; exception when others then return ''; end; ll_row: = ll_row + mod (power (2, (ll_ I-1), 11) * to_number (substr (var_aac002, 19-ll_ I, 1); ll_ I: = ll_ I-1; end loop; ll_sfz18: = mod (ll_row, 11 ); select decode (to_char (ll_sfz18), '0', '1', '1', '0', '2', 'x', '3', '9 ', '4', '8', '5', '7', '6', '6', '7', '5', '8', '4 ', '9', '3', '10', '2') into ll_sfz from dual; var_sfz18: = var_aac002 | ll_sfz; return var_sfz18; end if; end func_aac00215_18; /show errors; use the following: [SQL] SELECT func_aac00215_18 ('123') FROM dual;