Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

ISIN validation code in PL/SQL

ISIN = International Securities Identification Number http://en.wikipedia.org/wiki/International_Securities_Identification_Number

CREATE OR REPLACE FUNCTION sfCheckISIN(pISIN IN VARCHAR2) RETURN NUMBER
IS
  s number := 0;
  a number;
  c number;
begin
  a := case length(pISIN) when 12 then 1 else 2 end;
  
  for i in reverse 1..length(pISIN) loop
    c := ascii(substr(pISIN, i, 1));
    if c > ascii('9') then
        c := c - (ascii('A')-10);
        s := s + trunc((3 - a) * trunc(c / 10) + a * c + (a - 1) * (trunc(mod(c,10))) / 5);
    else
      c  := c - ascii('0');
      s :=  s + (a * c + (a - 1) * trunc(c / 5));
      a := 3 - a;
    end if;

  end loop;
  
  s := trunc(mod(s, 10));
  
  return trunc(mod(10 - trunc(mod(s, 10)), 10));

end sfCheckISIN;
/