Created
November 20, 2009 00:16
-
-
Save eight/239168 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Oracle用 ISBNパッケージ | |
10桁と13桁のISBNを相互に変換する。チェックデジットの計算もできる。 | |
http://www.isbn-center.jp/ | |
$Id: pkg_isbn.sql,v 1.5 2005/11/07 02:12:27 ymo Exp $ | |
*/ | |
CREATE OR REPLACE PACKAGE ISBN_PKG AS | |
/* | |
|| 入力されたISBNをチェックデジットを付けたりいろいろする。 | |
|| 10桁を13桁に直したり、13桁を10桁に直すこともできる。 | |
*/ | |
FUNCTION ISBN10(i_isbn IN varchar2) RETURN varchar2; | |
FUNCTION ISBN13(i_isbn IN varchar2) RETURN varchar2; | |
/* | |
|| 入力されたISBNをチェックデジットを抜いた大事なところだけにする。 | |
*/ | |
FUNCTION REGULATE_ISBN(i_isbn IN varchar2) RETURN varchar; | |
/* | |
|| 10桁版チェックデジットを計算して返す | |
|| 入力は9文字、10文字、13文字のどれか | |
*/ | |
FUNCTION CHECK_DEGIT10( i_isbn IN varchar2) RETURN char; | |
/* | |
|| 13桁版ISBNのチェックデジットを計算して返す。 | |
|| 入力は9文字、10文字、13文字のどれか | |
*/ | |
FUNCTION CHECK_DEGIT13( i_isbn IN varchar2) RETURN char; | |
END ISBN_PKG; | |
/ | |
---------------------------------------------------------- | |
-- PACKAGE BODY | |
---------------------------------------------------------- | |
CREATE OR REPLACE PACKAGE BODY ISBN_PKG AS | |
---------------------------------------------------------- | |
-- F ISBN10 | |
---------------------------------------------------------- | |
FUNCTION ISBN10(i_isbn IN varchar2) RETURN varchar2 | |
IS | |
sRaw varchar2(9); | |
BEGIN | |
sRaw := REGULATE_ISBN(i_isbn); | |
RETURN sRaw || CHECK_DEGIT10(sRaw); | |
END; | |
---------------------------------------------------------- | |
-- F ISBN13 | |
---------------------------------------------------------- | |
FUNCTION ISBN13(i_isbn IN varchar2) RETURN varchar2 | |
IS | |
sRaw varchar2(9); | |
BEGIN | |
sRaw := REGULATE_ISBN(i_isbn); | |
RETURN '978' || sRaw || CHECK_DEGIT13(sRaw); | |
END; | |
---------------------------------------------------------- | |
-- F REGULATE_ISBN | |
---------------------------------------------------------- | |
FUNCTION REGULATE_ISBN(i_isbn IN varchar2) RETURN varchar | |
IS | |
buf varchar2(20); | |
sRaw varchar2(9); | |
BEGIN | |
buf := SUBSTRB(REPLACE(REPLACE(UPPER(TRIM(i_isbn)),'ISBN'),'-'),1,20); | |
sRaw := TRIM(SUBSTRB(buf,1,9)); | |
IF LENGTH(buf) = 9 THEN | |
sRaw := SUBSTRB(buf, 1, 9); | |
END IF; | |
IF LENGTH(buf) = 10 THEN | |
sRaw := SUBSTRB(buf, 1, 9); | |
END IF; | |
IF LENGTH(buf) = 13 THEN | |
sRaw := SUBSTRB(buf, 4, 9); | |
END IF; | |
RETURN sRaw; | |
END; | |
---------------------------------------------------------- | |
-- F CHECK_DEGIT10 | |
---------------------------------------------------------- | |
FUNCTION CHECK_DEGIT10(i_isbn IN varchar2) RETURN char | |
IS | |
vWeight PLS_INTEGER; | |
vSum PLS_INTEGER; | |
vResult PLS_INTEGER; | |
sRaw varchar2(9); | |
BEGIN | |
sRaw := REGULATE_ISBN(i_isbn); | |
vWeight := 10; | |
vSum := 0; | |
FOR i IN 1..LENGTH(sRaw) | |
LOOP | |
vSum := vSum + vWeight * to_number(SUBSTR(sRaw, i, 1)); | |
vWeight := vWeight - 1; | |
END LOOP; | |
vResult := 11 - (vSum MOD 11); | |
if vResult = 10 THEN | |
RETURN 'X'; | |
END IF; | |
if vResult = 11 THEN | |
RETURN '0'; | |
END IF; | |
RETURN SUBSTR(to_char(vResult),1,1); | |
EXCEPTION | |
WHEN OTHERS THEN | |
RETURN 'E'; | |
END; | |
---------------------------------------------------------- | |
-- F CHECK_DEGIT13 | |
---------------------------------------------------------- | |
FUNCTION CHECK_DEGIT13(i_isbn IN varchar2) RETURN char | |
IS | |
vSum1 PLS_INTEGER; | |
vSum2 PLS_INTEGER; | |
vResult PLS_INTEGER; | |
r char; | |
sRaw varchar2(12); | |
BEGIN | |
sRaw := '978' || REGULATE_ISBN(i_isbn); | |
vSum1 := 0; | |
vSum2 := 0; | |
FOR i IN 1..LENGTH(sRaw) | |
LOOP | |
IF MOD(i,2) = 1 THEN | |
vSum1 := vSum1 + to_number(SUBSTR(sRaw, i, 1)); | |
ELSE | |
vSum2 := vSum2 + to_number(SUBSTR(sRaw, i, 1)); | |
END IF; | |
END LOOP; | |
vResult := vSum1 + (vSum2 * 3); | |
vResult := 10 - to_number(SUBSTR(to_char(vResult),length(to_char(vResult)),1)); | |
IF vResult > 9 THEN | |
RETURN '0'; | |
ELSE | |
RETURN SUBSTR(to_char(vResult),1,1); | |
END IF; | |
EXCEPTION | |
WHEN OTHERS THEN | |
RETURN 'E'; | |
END; | |
END ISBN_PKG; | |
/ | |
/** | |
SELECT 'Answer => 4798108545: '||ISBN_PKG.ISBN10('479810854') FROM DUAL; | |
SELECT 'Answer => 9784798108544: '||ISBN_PKG.ISBN13('4798108545') FROM DUAL; | |
SELECT 'Answer => 4949999087: '|| ISBN_PKG.ISBN10('494999908') FROM DUAL; | |
SELECT 'Answer => 9784949999083: ' || ISBN_PKG.ISBN13('494999908') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('4798108545') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('494999908') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('ISBN4-9499-9908') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('9784883810246') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('9784883810161') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT10('9784431711438') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT13('4798108545') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT13('494999908') FROM DUAL; | |
SELECT ISBN_PKG.CHECK_DEGIT13('ISBN4-9499-9908') FROM DUAL; | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment