Vertica 数据库 社会统一信用证校验函数

还是Vertical数据库不能循环的问题，这里采用了函数调用的方式减少代码量。

一个码值转换函数，一个主函数，搭配使用，其中TEMP为我这边数据库的模式别名。
码值转换

CREATE FUNCTION TMP.FN_CODETSNUM(p_code Varchar)
RETURN INT
AS
BEGIN
RETURN (
CASE WHEN p_code = 'A' THEN '10'
WHEN p_code  = 'B' THEN '11'
WHEN p_code = 'C' THEN '12'
WHEN p_code = 'D' THEN '13'
WHEN p_code = 'E' THEN '14'
WHEN p_code = 'F' THEN '15'
WHEN p_code = 'G' THEN '16'
WHEN p_code = 'H' THEN '17'
WHEN p_code = 'J' THEN '18'
WHEN p_code = 'K' THEN '19'
WHEN p_code = 'L' THEN '20'
WHEN p_code = 'M' THEN '21'
WHEN p_code = 'N' THEN '22'
WHEN p_code = 'P' THEN '23'
WHEN p_code = 'Q' THEN '24'
WHEN p_code = 'R' THEN '25'
WHEN p_code = 'T' THEN '26'
WHEN p_code = 'U' THEN '27'
WHEN p_code = 'W' THEN '28'
WHEN p_code = 'X' THEN '29'
WHEN p_code = 'Y' THEN '30' 
ELSE p_code END)::INT;
END;

删除函数

drop FUNCTION   TMP.FN_CODETSNUM(p_code Varchar)

–主函数

CREATE FUNCTION TMP.FN_SOC_CD_CHK(soc_code Varchar)
RETURN varchar
AS
BEGIN
RETURN (
CASE WHEN BIT_LENGTH(soc_code)=144
   THEN 
          (CASE WHEN soc_code NOT LIKE '%I%'
    AND soc_code NOT LIKE '%O%'
    AND soc_code NOT LIKE '%Z%'
    AND soc_code NOT LIKE '%S%'
    AND soc_code NOT LIKE '%V%'
    AND regexp_like(soc_code,E'([[:upper:]]|[[:digit:]]){18}')
    THEN
    (CASE WHEN 
    ((31-(MOD((TMP.FN_CODETSNUM(SUBSTR(soc_code,1,1))*1+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,2,1))*3+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,3,1))*9+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,4,1))*27+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,5,1))*19+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,6,1))*26+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,7,1))*16+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,8,1))*17+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,9,1))*20+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,10,1))*29+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,11,1))*25+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,12,1))*13+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,13,1))*8+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,14,1))*24+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,15,1))*10+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,16,1))*30+
      TMP.FN_CODETSNUM(SUBSTR(soc_code,17,1))*28)-1,31)+1)))=TMP.FN_CODETSNUM(SUBSTR(soc_code,18,1))
    THEN 'true' ELSE 'false' END)
      ELSE 'false' END)
   ELSE 'false' END);
END;

删除函数

drop FUNCTION   TMP.FN_SOC_CD_CHK(soc_code Varchar)