首先我创建了这样一个函数,这个函数的作用是把15位的身份证号转换为18位的create or replace function FUN_PID15TO18(pid15 in char) return char is
TYPE array_17_number IS VARRAY(17) OF NUMBER;
TYPE array_11_char IS VARRAY(11) OF char;
Result varchar2(18);
v_check_number integer := 0;
v_check_char char(1);
v_factor array_17_number := array_17_number(7,
9,
10,
5,
8,
4,
2,
1,
6,
3,
7,
9,
10,
5,
8,
4,
2); v_mod array_11_char := array_11_char('1',
'0',
'X',
'9',
'8',
'7',
'6',
'5',
'4',
'3',
'2');
begin
if (length(pid15) = 18) then
return pid15;
elsif (length(pid15) = 15) then
result := substr(pid15, 1, 6) || '19' || substr(pid15, 7, 9);
FOR i IN 1 .. 17 LOOP
v_check_number := to_number(substr(result, i, 1)) * v_factor(i) +
v_check_number;
END LOOP;
v_check_number := mod(v_check_number, 11);
v_check_char := v_mod(v_check_number + 1);
result := result || v_check_char;
return result;
else
raise_application_error(-20001, 'Length of pid should be 15 or 18!');
end if;
end FUN_PID15TO18;
函数名是FUN_PID15TO18。现在有一张person表,里面有cetf_id这个字段,是存放身份证的,现在我要将表中所有的15位的身份证转换为18位的,请问SQL语句应该怎么写呢?
TYPE array_17_number IS VARRAY(17) OF NUMBER;
TYPE array_11_char IS VARRAY(11) OF char;
Result varchar2(18);
v_check_number integer := 0;
v_check_char char(1);
v_factor array_17_number := array_17_number(7,
9,
10,
5,
8,
4,
2,
1,
6,
3,
7,
9,
10,
5,
8,
4,
2); v_mod array_11_char := array_11_char('1',
'0',
'X',
'9',
'8',
'7',
'6',
'5',
'4',
'3',
'2');
begin
if (length(pid15) = 18) then
return pid15;
elsif (length(pid15) = 15) then
result := substr(pid15, 1, 6) || '19' || substr(pid15, 7, 9);
FOR i IN 1 .. 17 LOOP
v_check_number := to_number(substr(result, i, 1)) * v_factor(i) +
v_check_number;
END LOOP;
v_check_number := mod(v_check_number, 11);
v_check_char := v_mod(v_check_number + 1);
result := result || v_check_char;
return result;
else
raise_application_error(-20001, 'Length of pid should be 15 or 18!');
end if;
end FUN_PID15TO18;
函数名是FUN_PID15TO18。现在有一张person表,里面有cetf_id这个字段,是存放身份证的,现在我要将表中所有的15位的身份证转换为18位的,请问SQL语句应该怎么写呢?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货