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;