文库

PG车辆VIN码校验

PG车辆VIN码校验

五月 12, 2017 阅读 2658 字数 2694 喜欢 0

函数使用方法:

select lcipcheckcarvin(VIN);

 

直接执行下面脚本,创建函数:

 

CREATE OR REPLACE FUNCTION lcipcheckcarvin(carvin text)

  RETURNS integer AS

$BODY$

 

declare n integer;

declare codes char[];

declare code char;

declare kv integer;

declare wv integer;

declare incode integer;

declare nine text;

declare total integer;

begin     

 

if(carvin is null or length(carvin)=0 or length(carvin)<>17) then

return 0;

end if;

 

carvin:=lower(carvin);

codes:=array[]::char[];

 

FOR n IN 1..17 loop

codes:=array_append(codes,substring(carvin,n,1)::character);

end loop;

 

nine:=substring(carvin,9,1);

 

if(nine ~ '[0-9]') then

incode:= nine::integer;

elseif('x'=nine) then

incode:= 10;

else

return 0;

end if;

 

total:=0;

 

code:='';

FOR n IN 1..array_length(codes,1) loop

code:=codes[n];

if(code='0' or code='1' or code='2' or code='3' or code='4' or code='5' or code='6' or code='7' or code='8' or code='9' or code='a' or code='b' or code='c' or code='d' or code='e' or code='f' or code='g' or code='h' or code='j' or code='k' or code='l' or code='m' or code='n' or code='p' or code='q' or code='r' or code='s' or code='t' or code='u' or code='v' or code='w' or code='x' or code='y' or code='z' ) then

if(n <> 9) then

 

if(code = '0') then

kv:=0;

elseif(code = '1') then

kv:=1;

elseif(code = '2') then

kv:=2;

elseif(code = '3') then

kv:=3;

elseif(code = '4') then

kv:=4;

elseif(code = '5') then

kv:=5;

elseif(code = '6') then

kv:=6;

elseif(code = '7') then

kv:=7;

elseif(code = '8') then

kv:=8;

elseif(code = '9') then

kv:=9;

elseif(code = 'a') then

kv:=1;

elseif(code = 'b') then

kv:=2;

elseif(code = 'c') then

kv:=3;

elseif(code = 'd') then

kv:=4;

elseif(code = 'e') then

kv:=5;

elseif(code = 'f') then

kv:=6;

elseif(code = 'g') then

kv:=7;

elseif(code = 'h') then

kv:=8;

elseif(code = 'j') then

kv:=1;

elseif(code = 'k') then

kv:=2;

elseif(code = 'l') then

kv:=3;

elseif(code = 'm') then

kv:=4;

elseif(code = 'n') then

kv:=5;

elseif(code = 'p') then

kv:=7;

elseif(code = 'q') then

kv:=8;

elseif(code = 'r') then

kv:=9;

elseif(code = 's') then

kv:=2;

elseif(code = 't') then

kv:=3;

elseif(code = 'u') then

kv:=4;

elseif(code = 'v') then

kv:=5;

elseif(code = 'w') then

kv:=6;

elseif(code = 'x') then

kv:=7;

elseif(code = 'y') then

kv:=8;  

elseif(code = 'z') then

kv:=9;    

end if;


if(n = 1) then

wv:=8;

elseif(n = 2) then

wv:=7;

elseif(n = 3) then

wv:=6;

elseif(n = 4) then

wv:=5;

elseif(n = 5) then

wv:=4;

elseif(n = 6) then

wv:=3;

elseif(n = 7) then

wv:=2;

elseif(n = 8) then

wv:=10;

elseif(n = 10) then

wv:=9;

elseif(n = 11) then

wv:=8;

elseif(n = 12) then

wv:=7;

elseif(n = 13) then

wv:=6;

elseif(n = 14) then

wv:=5;

elseif(n = 15) then

wv:=4;

elseif(n = 16) then

wv:=3;

elseif(n = 17) then

wv:=2;

end if;

 

total:= total + (kv * wv);

end if;

 

end if;

end loop;

 

if(incode = total%11) then

return 1;

else

return 0;

end if;

 

end;

 

$BODY$

  LANGUAGE plpgsql VOLATILE;









粤ICP备18103437号