CREATE OR REPLACE FUNCTION checkBankCard(p_bankcard IN varchar2)
RETURN varchar2 --正确时返回ok,否则返回正确的校验码
IS
curval NUMBER := 0;
total NUMBER := 0;
everyother NUMBER := 1;
v_end number;
BEGIN
IF (p_bankcard IS NULL) then
RETURN 'null';
end IF;
FOR i IN reverse 1 .. (LENGTH(p_bankcard) - 1) loop
curval := SUBSTR(p_bankcard, i, 1);
IF everyother = 1 then
everyother := 0;
curval := curval * 2;
IF (curval > 9) then
curval := curval - 9;
end IF;
else
everyother := 1;
end IF;
total := total + curval;
end loop;
IF MOD(total, 10) = 0 THEN
v_end := 0;
ELSE
v_end:= 10 - MOD(total, 10);
END IF;
if to_char(v_end) = substr(p_bankcard,lengthb(p_bankcard),1) then
return 'ok';
else
return to_char(v_end);
end if;
end;