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

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.