文库

PG车辆VIN码校验

PG车辆VIN码校验

五月 12, 2017 阅读 19 字数 2694 评论 0 喜欢 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;








    相 关 文 章 返回顶部 上一篇 下一篇

  • PG递归查询

    distinct xx和count(distinct xx)的递归优化方法 当数据量大,count(distinct xx)结果会很慢,distinct结果数量较少时,可以使用递归方法优化。distinct结果多不适用。

    PG递归查询
  • postgresql增加连接数

    postgresql修改连接数,可以在postgres.conf里面进行修改max_conection,但是当max_conection过大时,就会报错。此时我把数据库连接数设置到2000,重启后错误信息可以在server.log里看到。这时需要修改系统内核配置。

    postgresql增加连接数
  • PG shared_buffer性能检测

    很多资料说这个缓存命中率不可低于99%,如果低于了99%,表明,cache效率太低了,需要增大shared buffer。总之了,当你的shared buffer命中率太低,比如60%,基本就需要检查下你配置的shared buffers是否太小了,导致你cache利用率如此之低。

    PG shared_buffer性能检测
  • PG outputdata函数

    根据表名,打印出该表的insert语句。

    PG outputdata函数

粤ICP备18103437号-1