oracle 在 PL/SQL 中验证 IBAN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7807912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Validating IBAN in PL/SQL
提问by usr-local-ΕΨΗΕΛΩΝ
I'm trying to find some ready-to-use code (yes, I mean teh codez) to validate an IBAN account number in PL/SQL.
我试图找到一些现成的代码（是的，我的意思是teh codez）来验证 PL/SQL 中的 IBAN 帐号。
Does anyone know about some samples? I think someone should have already implemented that...
有人知道一些样品吗？我认为应该有人已经实施了......
Thanks
谢谢
回答by Erno
This one is surely not copyrighted:
这个肯定没有版权：
declare
as_iban varchar2(34);
ln_iban number(36, 0);
begin
as_iban := 'enter your IBAN here';
ln_iban := to_number(substr(as_iban, 5));
ln_iban := ln_iban * 100 + (ascii(substr(as_iban, 1, 1)) - 55);
ln_iban := ln_iban * 100 + (ascii(substr(as_iban, 2, 1)) - 55);
ln_iban := ln_iban * 100 + to_number(substr(as_iban, 3, 2));
ln_iban := ln_iban mod 97;
if ln_iban is null or ln_iban <> 1 then
raise_application_error(-2e4, 'invalid IBAN: ' || as_iban);
end if;
end;
/
回答by Alexst
Function returns 1 if IBAN is correct and 0 if it's not correct
如果 IBAN 正确则函数返回 1，如果不正确则返回 0
CREATE OR REPLACE
FUNCTION fn_CheckIBAN(
pIBAN IN VARCHAR2
) RETURN INTEGER IS
lResult INTEGER;
IBAN VARCHAR2(256);
IBAN_Digits VARCHAR2(256);
l_mod NUMBER;
lTmp VARCHAR2(8);
lSCnt INTEGER := 5;
i INTEGER := 1;
---
FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
lChar VARCHAR2(1);
lNumber INTEGER;
lString VARCHAR2(255);
BEGIN
FOR i IN 1..LENGTH(IBAN) LOOP
lChar := SUBSTR(IBAN, i, 1);
BEGIN
lNumber := ASCII(lChar);
IF lNumber > 47 AND lNumber < 58 THEN
-- It's number 0 ... 9
lString := lString || TO_CHAR(lNumber - 48);
ELSE
lString := lString || TO_CHAR(lNumber - 55);
END IF;
END;
END LOOP;
RETURN lString;
END fn_GetIBANDigits;
---
BEGIN
IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);
IBAN_Digits := fn_GetIBANDigits;
LOOP
lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
EXIT WHEN lTmp IS NULL;
IF l_mod IS NULL THEN
l_mod := MOD( TO_NUMBER(lTmp), 97);
ELSE
l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
END IF;
i := i + lSCnt;
END LOOP;
IF l_mod = 1 THEN
lResult := 1;
ELSE
lResult := 0;
END IF;
RETURN(lResult);
END fn_CheckIBAN;
回答by APC
A swift Googling throws up an implementation by Alexandre Rodichevski. It's copyrighted so I'm not sure whether it's legal to use it. Anyway, find it here.
一个快速的谷歌搜索抛出了 Alexandre Rodichevski 的一个实现。它受版权保护，所以我不确定使用它是否合法。无论如何，在这里找到它。
回答by abu abdouh
my modification
我的修改
CREATE OR REPLACE FUNCTION MOHF.fn_CheckIBAN(
pIBAN IN VARCHAR2
) RETURN varchar2 IS
lResult INTEGER;
IBAN VARCHAR2(256);
IBAN_Digits VARCHAR2(256);
l_mod NUMBER;
lTmp VARCHAR2(8);
lSCnt INTEGER := 5;
i INTEGER := 1;
---
FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
lChar VARCHAR2(1);
lNumber INTEGER;
lString VARCHAR2(255);
BEGIN
FOR i IN 1..LENGTH(IBAN) LOOP
lChar := SUBSTR(IBAN, i, 1);
BEGIN
lNumber := ASCII(lChar);
IF lNumber > 47 AND lNumber < 58 THEN
-- It's number 0 ... 9
lString := lString || TO_CHAR(lNumber - 48);
ELSE
lString := lString || TO_CHAR(lNumber - 55);
END IF;
END;
END LOOP;
RETURN lString;
exception when others then return ( null);
END fn_GetIBANDigits;
---
BEGIN
IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);
IBAN_Digits := fn_GetIBANDigits;
LOOP
lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
EXIT WHEN lTmp IS NULL;
IF l_mod IS NULL THEN
l_mod := MOD( TO_NUMBER(lTmp), 97);
ELSE
l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
END IF;
i := i + lSCnt;
END LOOP;
IF l_mod = 1 THEN
lResult := 1;
ELSE
lResult := 0;
END IF;
RETURN(lResult);
exception when others then return ( IBAN);
END fn_CheckIBAN;
/
