Category Archives: Code

Oracle PL/SQLとUNIXタイムスタンプ

Posted on by eight
Reply

Oracleのdate型とUNIXタイムスタンプを相互に変換するPL/SQL関数が必要になった。
作ってみた。

create or replace function to_unix_timestamp(dt date) return number is
begin
    return (dt - to_date('1970-01-01','YYYY-MM-DD')) * 86400;
end;
/
create or replace function from_unix_timestamp(ts number) return date is
begin
    return to_date(trunc(ts / 86400, 0) + 2440588, 'J') + (mod(ts, 86400) / 86400);
end;
/
select to_unix_timestamp(sysdate) from dual;
select to_char(from_unix_timestamp(to_unix_timestamp(sysdate)),'YYYY-MM-DD HH24:MI:SS') from dual;

消費税を計算するOracle PL/SQLパッケージ

Posted on by eight
Reply

消費税込み金額を計算したり、税抜き金額を計算したりするPL/SQLパッケージ。

消費税率が変わったらTAX_CALENDARという名前のテーブルに税率を追加すればいい。

これで消費税が上がっても大丈夫。10％でも20％でも平気。どんとこい。

TAX_RATE2は、テーブルを使わずに、FUNCTION内で日付とレートをハードコードして判断している。税率が変わったら書き直さないといけない。税率変わるたびにメンテナンス＝ソース変更とリコンパイルが必要だけれど、まあ、運用環境によってはこっちがいい場合も多いか。シンプルだし速いはず。

/*
|| 消費税率のカレンダーテーブル
*/
DROP TABLE TAX_CALENDAR;
/
-- TAX CALENDAR TABLE
CREATE TABLE TAX_CALENDAR (
  DtS date NOT NULL,
  DtE date NOT NULL,
  TAX number(3,2)
);
-- TAX TABLE
ALTER TABLE TAX_CALENDAR ADD CONSTRAINT DISP_TAX_CALENDAR_PK PRIMARY KEY (DtS,DtE);
/
/*
|| TAX_CALENDAR INSERTION
*/
DELETE FROM TAX_CALENDAR;
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1900-01-01'),to_date('1989-04-01'),0.000);
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1989-04-01'),to_date('1997-04-01'),0.03);
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1997-04-01'),to_date('2999-04-01'),0.05);

CREATE OR REPLACE PACKAGE TAX_PKG AS
  -- 日付で消費税率を返す
  FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  -- 日付と金額で消費税額を返す
  FUNCTION TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  -- 日付と金額で税込金額を返す
  FUNCTION INCLUDE_TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(TAX_RATE,WNDS);
  PRAGMA RESTRICT_REFERENCES(TAX,WNDS);
  PRAGMA RESTRICT_REFERENCES(INCLUDE_TAX,WNDS);
END TAX_PKG;
/

CREATE OR REPLACE PACKAGE BODY TAX_PKG AS
    /*
    || 日付で消費税率を返す
    ||  TAX_CALENDARテーブルを参照する
    */
    FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    ATax number;
    BEGIN
        SELECT TAX INTO ATAX FROM TAX_CALENDAR WHERE i_date >= DtS AND i_date < DtE;
        RETURN ATAX;
    END;
    /*
    || 日付で消費税率を返す
    */
    FUNCTION TAX_RATE2(i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        IF i_date < to_date('89-04-01') THEN
            RETURN 0.00;
        ELSIF i_date < to_date('97-04-01') THEN
            RETURN 0.03;
        ELSE
            RETURN 0.05; --現状
        END IF;
    END;
    -------------------------------------------------------
    -- 日付と金額で消費税額を返す
    -------------------------------------------------------
    FUNCTION TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        RETURN ROUND(i_price * TAX_RATE(i_date));
    END;
    -------------------------------------------------------
    -- 日付と金額で税込金額を返す
    -------------------------------------------------------
    FUNCTION INCLUDE_TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        RETURN ROUND(i_price + (i_price * TAX_RATE(i_date)));
    END;
END TAX_PKG;
/

-------------------------------------------------------
-- 消費税計算のテスト用
-------------------------------------------------------
select tax_pkg.tax_rate('89-03-31'),
 tax_pkg.tax_rate('89-04-01'),
 tax_pkg.tax_rate('97-03-31'),
 tax_pkg.tax_rate('97-04-01'),
 tax_pkg.tax_rate(SYSDATE)
from dual;

select tax_pkg.tax(101,'89-03-31'),
 tax_pkg.tax(101,'89-04-01'),
 tax_pkg.tax(101,'97-03-31'),
 tax_pkg.tax(101,'97-04-01'),
 tax_pkg.tax(101,SYSDATE)
from dual;

select tax_pkg.include_tax(101,'89-03-31'),
 tax_pkg.include_tax(101,'89-04-01'),
 tax_pkg.include_tax(101,'97-03-31'),
 tax_pkg.include_tax(101,'97-04-01'),
 tax_pkg.include_tax(101,SYSDATE)
from dual;

ユーザ管理をするOracle PL/SQLパッケージ

Posted on by eight
Reply

ユーザIDやパスワードは、OracleのDBMS_OBFUSCATION_TOOLKITパッケージを使って暗号化して保管するPL/SQLパッケージ。DBMS_OBFUSCATION_TOOLKITを使っているので、Oracle9i以降が必要（たぶん）。

ユーザ情報管理機能の必要なアプリケーションで使うと便利かもしれない。

もう使ってはいけないとどこかで読んだような気もするmd5でハッシュをとっている部分は、簡単な変更で入れ替えることもできるはず。

このパッケージを使うことで、アプリケーションからは暗号化/復号化の処理、テーブルの列構成の詳細を隠蔽することができるはず。

小道具というにはちょっと大きいかも。

http://gist.github.com/233572

/**
* ユーザ認証管理パッケージ
* テーブルUSER_AUTHとパッケージAUTH_PKGで構成される。
* ユーザIDとパスワードはMD5ダイジェストとして格納される。
* Oracle9i以降専用!!
*
* ユーザIDの管理（あるユーザIDは誰か？など）は当パッケージを利用するシステムの責任
* ユーザIDを登録（REGIST_USER）、削除（DROP）、パスワード認証（AUTHENTICATE）などを管理する
*
* クラス：ユーザIDのクラス。ユーザIDはクラス内でユニークである必要がある。
* ユーザID：何文字でもいい。内部ではMD5のハッシュで管理している。クラス内でユニークとなること。
* パスワード：何文字でもいい。内部ではMD5のハッシュで管理している

* 複数のクラスを管理する場合は、SET_USERCLASSを呼び出してから他の操作をすること。
* USERCLASS()で現在どのクラスを使っているかを知ることができる。
* 最初にAUTH_PKG.SET_USERCLASS(ユーザクラス）を呼び出して使うクラスの設定を初期化する。
* この初期化をしないと、クラスとして「DEFAULT」が設定される。
* 初期化しないで使う場合は、全関数でユーザクラスを最初の引数に指定する。
*
* RETURN_CODE_TRUEとRETURN_CODE_FALSEはFUNCTIONが返す論理値に割り当てる文字列を指定する。
* RETURN_CODE_TRUEにはTRUEの時に返す文字列。デフォルトは'Y'。
* RETURN_CODE_FALSEにはFASEの時に返す文字列。デフォルトは'N'。
*
* テーブル名
* USER_AUTH
*
* 順序名
* SEQ_USER_AUTH_ID
*
*/

/******************************************************************
 ユーザ認証管理パッケージ用の認証テーブル USER_AUTHの作成
*******************************************************************/
DROP SEQUENCE SEQ_USER_AUTH_ID;
CREATE SEQUENCE SEQ_USER_AUTH_ID;

DROP TABLE USER_AUTH;
CREATE TABLE USER_AUTH(
 AuthId number(9,0) NOT NULL,
 UserClass varchar2(10) NOT NULL,
 UserId char(32) NOT NULL,
 Password char(32) NOT NULL,
 PasswordChanged date DEFAULT SYSDATE,
 Updated date DEFAULT SYSDATE,
 Created date NOT NULL
);
COMMENT ON TABLE USER_AUTH IS 'ユーザ認証システム：認証テーブル';
COMMENT ON COLUMN USER_AUTH.AuthID IS '認証ID AUTH_ID_SEQで発番された連番';
COMMENT ON COLUMN USER_AUTH.UserClass IS 'ユーザIDの属しているクラス';
COMMENT ON COLUMN USER_AUTH.UserID IS 'ユーザID：ユーザ入力値のMD5メッセージダイジェスト。';
COMMENT ON COLUMN USER_AUTH.Password IS 'パスワード：ユーザ入力値のMD5メッセージダイジェスト';
COMMENT ON COLUMN USER_AUTH.PasswordChanged IS '最後にパスワードを変更した日時';
COMMENT ON COLUMN USER_AUTH.Updated IS '更新日時';
COMMENT ON COLUMN USER_AUTH.Created IS '作成日時';

CREATE UNIQUE INDEX USER_AUTH_USERID ON USER_AUTH(UserClass,UserID);
ALTER TABLE USER_AUTH ADD CONSTRAINT USER_AUTH_PK PRIMARY KEY (AuthID);

/******************************************************************
 AUTH_PKG本体の作成
*******************************************************************/
CREATE OR REPLACE PACKAGE AUTH_PKG AS
 --グローバル変数定義

 --TRUE/FALSEやOK/NGやYES/NOに変更してもいい
 RETURN_CODE_TRUE VARCHAR2(10) := 'Y';
 RETURN_CODE_FALSE VARCHAR2(10) := 'N';

 --USERCLASSを設定しない場合のデフォルトユーザクラス
 DEFAULT_USERCLASS CONSTANT VARCHAR2(10) := 'DEFAULT';

 --初期登録でパスワードを指定しなかった場合にデフォルトで指定するパスワード
 DEFAULT_PASSWORD CONSTANT VARCHAR2(32) := 'changeOnInstall';

 -- パッケージのユーザクラスをセットする
  PROCEDURE SET_USERCLASS(i_userclass IN VARCHAR2);

 -- 設定されているユーザクラスを返す
  FUNCTION USERCLASS RETURN VARCHAR2;

 --パスワード変更
  PROCEDURE CHANGE_PASSWORD(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2);
  PROCEDURE CHANGE_PASSWORD(i_userid IN VARCHAR2, i_password IN VARCHAR2);

 --ユーザ完全削除
  PROCEDURE DROP_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2);
  PROCEDURE DROP_USER(i_userid IN VARCHAR2);

 --ユーザ登録
  --パスワードを指定しないとデフォルト値が設定される
  PROCEDURE REGIST_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2);
  PROCEDURE REGIST_USER(i_userid IN VARCHAR2, i_password IN VARCHAR2);

 --ユーザの存在チェック
  FUNCTION USER_EXISTS(i_userclass IN VARCHAR2, i_userid IN VARCHAR2) RETURN CHAR;
  FUNCTION USER_EXISTS(i_userid IN VARCHAR2) RETURN CHAR;

 --認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
  FUNCTION AUTHENTICATE(i_userid IN VARCHAR2, i_password IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION AUTHENTICATE(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2) RETURN VARCHAR2;

 --最後にパスワードを変更した日時を返す
  FUNCTION PASSWORD_CHANGED(i_userclass IN VARCHAR2, i_userid IN VARCHAR2) RETURN DATE;
  FUNCTION PASSWORD_CHANGED(i_userid IN VARCHAR2) RETURN DATE;

 --最後にパスワードを変更した日時が有効な日数(i_days)を過ぎていたらRETURN_CODE_TRUEを返す
  FUNCTION PASSWORD_EXPIRED(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_days IN NUMBER) RETURN CHAR;
  FUNCTION PASSWORD_EXPIRED(i_userid IN VARCHAR2, i_days IN NUMBER) RETURN CHAR;

 --指定した文字数のランダムな文字列を返す。初期パスワードの生成に。
  FUNCTION RANDOM_STRING(i_string_length IN NUMBER DEFAULT 6) RETURN VARCHAR2;

 --MD5 基本的には内部利用を想定しているけれど、別に外で使っても良い
  FUNCTION MD5(input_string IN VARCHAR2) RETURN VARCHAR2;

 --暗号化した文字列を返す 現バージョンはMD5を使った1方向だけに対応。
  FUNCTION ENCRYPT(input_string IN VARCHAR2, ENCRYPT_METHOD IN VARCHAR2 DEFAULT 'MD5') RETURN VARCHAR2;
END AUTH_PKG;

/

CREATE OR REPLACE PACKAGE BODY AUTH_PKG AS
--パッケージローカルのセッション内変数
CURRENT_USERCLASS VARCHAR2(10) := DEFAULT_USERCLASS;
SEED_COUNTER number := 1;

----------------------------------------------------------
-- md5ダイジェストを得るためのストアードファンクション
-- USER_AUTHテーブルのpasswordフィールドにはmd5ダイジェストを記録する
----------------------------------------------------------
FUNCTION MD5(input_string IN VARCHAR2) RETURN VARCHAR2
IS
hex_digest varchar2(32);
digest varchar2(16);
BEGIN
 digest := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => input_string);
 SELECT Lower(RAWTOHEX(digest)) INTO hex_digest FROM dual;
 RETURN hex_digest;
END;

----------------------------------------------------------
-- 受け取った文字列を暗号化して返す
----------------------------------------------------------
FUNCTION ENCRYPT(input_string IN VARCHAR2, ENCRYPT_METHOD IN VARCHAR2 DEFAULT 'MD5') RETURN VARCHAR2
IS
BEGIN
 IF ENCRYPT_METHOD = 'MD5' THEN
    RETURN MD5(input_string);
 ELSE
  RAISE_APPLICATION_ERROR(-20003,'暗号化方法' || ENCRYPT_METHOD || 'が私には理解できません。');
 END IF;
END;

----------------------------------------------------------
-- P 初期化する
----------------------------------------------------------
PROCEDURE SET_USERCLASS( i_userclass IN VARCHAR2)
IS
BEGIN
 CURRENT_USERCLASS := i_userclass;
END;

----------------------------------------------------------
-- F CURRENT_USERIDを返す
----------------------------------------------------------
FUNCTION USERCLASS RETURN VARCHAR2
IS
BEGIN
 RETURN CURRENT_USERCLASS;
END;

----------------------------------------------------------
-- P CHANGE_PASSWORD
----------------------------------------------------------
PROCEDURE CHANGE_PASSWORD
 ( i_userclass IN VARCHAR2,
   i_userid IN VARCHAR2,
   i_password IN VARCHAR2)
IS
 ret_val CHAR(1) := RETURN_CODE_TRUE;
BEGIN
 ret_val := USER_EXISTS(i_userclass, i_userid);
 IF ret_val = RETURN_CODE_TRUE THEN
   UPDATE USER_AUTH
    SET
     password = ENCRYPT(i_password),
     PasswordChanged=SYSDATE,
     Updated=SYSDATE
    WHERE userclass = i_userclass
     AND userid = ENCRYPT(i_userid);
 END IF;
END;

PROCEDURE CHANGE_PASSWORD
 ( i_userid IN VARCHAR2,
   i_password IN VARCHAR2)
IS
BEGIN
 CHANGE_PASSWORD(CURRENT_USERCLASS, i_userid, i_password);
END;

----------------------------------------------------------
-- P DROP_USER 完全にレコードを削除する
----------------------------------------------------------
PROCEDURE DROP_USER (i_userclass IN VARCHAR2, i_userid IN VARCHAR2)
IS
BEGIN
 DELETE USER_AUTH
  WHERE userclass = i_userclass
   AND userid = ENCRYPT(i_userid);
END;

PROCEDURE DROP_USER (i_userid IN VARCHAR2)
IS
BEGIN
 DROP_USER(CURRENT_USERCLASS, i_userid);
END;

----------------------------------------------------------
-- P REGIST_USER
----------------------------------------------------------
PROCEDURE REGIST_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2)
IS
BEGIN
 IF USER_EXISTS(i_userclass, i_userid)=RETURN_CODE_TRUE THEN
  RAISE_APPLICATION_ERROR(-20002,'ご指定のユーザ' || i_userid || 'は既に存在しています。');
 ELSE
  INSERT INTO USER_AUTH(authid,userclass,userid,password,updated,created)
   VALUES(SEQ_USER_AUTH_ID.NEXTVAL,i_userclass,ENCRYPT(i_userid),ENCRYPT(i_password),SYSDATE,SYSDATE);
 END IF;
END;

PROCEDURE REGIST_USER( i_userid IN VARCHAR2, i_password IN VARCHAR2)
IS
BEGIN
 REGIST_USER(CURRENT_USERCLASS, i_userid, i_password);
END;

----------------------------------------------------------
-- F USER_EXISTS ユーザが存在したらRETURN_CODE_TRUE、存在しなければRETURN_CODE_FALSEを返す
----------------------------------------------------------
FUNCTION USER_EXISTS(i_userclass IN VARCHAR2, i_userid IN VARCHAR2)
RETURN CHAR
IS
 r number;
BEGIN
 SELECT COUNT(*) INTO r
  FROM USER_AUTH
  WHERE userclass=i_userclass
   AND userid = ENCRYPT(i_userid);
 IF r > 0 THEN
  RETURN RETURN_CODE_TRUE;
 ELSE
  RETURN RETURN_CODE_FALSE;
 END IF;
END;

FUNCTION USER_EXISTS( i_userid IN VARCHAR2)
RETURN CHAR
IS
BEGIN
 RETURN USER_EXISTS(CURRENT_USERCLASS, i_userid);
END;

----------------------------------------------------------
-- F AUTH
----------------------------------------------------------
--認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
FUNCTION AUTHENTICATE(
   i_userclass IN VARCHAR2,
   i_userid IN VARCHAR2,
   i_password IN VARCHAR2
   ) RETURN VARCHAR2
IS
 r number;
BEGIN
  SELECT COUNT(*) INTO r
  FROM USER_AUTH
  WHERE userclass=i_userclass
   AND userid = ENCRYPT(i_userid)
   AND password = ENCRYPT(i_password);
 IF r > 0 THEN
  RETURN RETURN_CODE_TRUE;
 ELSE
  RETURN RETURN_CODE_FALSE;
 END IF;
END;

--認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
FUNCTION AUTHENTICATE(
   i_userid IN VARCHAR2,
   i_password IN VARCHAR2
   ) RETURN VARCHAR2
IS
BEGIN
  RETURN AUTHENTICATE(CURRENT_USERCLASS, i_userid, i_password);
END;

----------------------------------------------------------
-- F PASSWORD
----------------------------------------------------------
--パスワードの変更日時を返す。
FUNCTION PASSWORD_CHANGED(
   i_userclass IN VARCHAR2,
   i_userid IN VARCHAR2
   ) RETURN DATE
IS
 r date;
BEGIN
  SELECT PasswordChanged INTO r
  FROM USER_AUTH
  WHERE userclass=i_userclass
   AND userid = ENCRYPT(i_userid);
  RETURN r;
END;

--パスワードの変更日時を返す。
FUNCTION PASSWORD_CHANGED(
   i_userid IN VARCHAR2
   ) RETURN DATE
IS
BEGIN
  RETURN PASSWORD_CHANGED(CURRENT_USERCLASS, i_userid);
END;

----------------------------------------------------------
-- F PASSWORD_EXPIRED
----------------------------------------------------------
--パスワードの有効期限が過ぎているかどうかをチェックしてRETURN_CODEを返す。
FUNCTION PASSWORD_EXPIRED(
   i_userclass IN VARCHAR2,
   i_userid IN VARCHAR2,
   i_days IN NUMBER
   ) RETURN CHAR
IS
 r date;
BEGIN
 SELECT PASSWORD_CHANGED(i_userclass,i_userid) + i_days INTO r FROM DUAL;
 IF r < SYSDATE THEN
  RETURN RETURN_CODE_TRUE;
 ELSE
  RETURN RETURN_CODE_FALSE;
 END IF;
END;

FUNCTION PASSWORD_EXPIRED(
   i_userid IN VARCHAR2,
   i_days IN NUMBER
   ) RETURN CHAR
IS
 r date;
BEGIN
  RETURN PASSWORD_EXPIRED(CURRENT_USERCLASS, i_userid);
END;

----------------------------------------------------------
-- F ランダムな文字列を返す
----------------------------------------------------------
FUNCTION RANDOM_STRING(i_string_length IN NUMBER DEFAULT 6) RETURN VARCHAR2
IS
seed number;
r char(32);
BEGIN
 seed := to_number(to_char(sysdate,'SSSSS')) + SEED_COUNTER;
 SEED_COUNTER := SEED_COUNTER + 1;
 dbms_random.initialize(seed);
 dbms_random.seed(seed);
 r := MD5(to_char(abs(dbms_random.random())));
 RETURN substr(r,-i_string_length,i_string_length);
END;

END AUTH_PKG;

ISBN10桁13桁変換Oracle PL/SQLパッケージ

Posted on by eight
Reply

去年作ったモノですが、気がつくと結構あちこちで使っていた。
手元ではLinux版のOracle9iで動作しています。

/* 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;
*/

Gist:239168

Pythonでモジュラス10ウエイト3とisbn10to13

Posted on by eight
Reply

Guidelines For Shipping Container Labeling

http://www.bisg.org/docs/shipping_label_guidelines_09-2005.pdf

こんなのをやるにあたって、ちゃんとしたモジュラス10ウエイト3のチェックデジットを計算する必要があったので作った。


ついでに、isbnlib.pyよりもシンプルに10桁ISBNを13桁にする関数も。

Python版

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""モジュラス10ウエイト3とISBNのチェックデジット計算。Python版。
"""
def m10w31(code):
    """モジュラス10ウエイト3チェックデジットの計算
    """
    s = list(str(code))
    s.reverse()
    sum = 0

    w = 3
    for i in s:
        sum = sum + (int(i) * w)
        if w == 3: w = 1
        else: w = 3

    d = sum % 10
    if d<>0: d = 10 - d
    return str(d)

def isbn10to13(isbn10):
    """10桁ISBNを13桁ISBNに変換
    """
    s = '978'+isbn10[:9]
    return s+m10w31(s)

def ssccBarCode(isbn13):
    """ SSCC bar code
     See, http://www.bisg.org/docs/shipping_label_guidelines_09-2005.pdf
    """
    PRFX1='01'
    PRFX2='1'
    code = isbn13[:12]
    return "(%s)%s %s-%s" % (PRFX1, PRFX2, code, m10w31(PRFX2+code))

if __name__=='__main__':
    data = ('4770025364','4770023230','4770015419','4881359290','4883731626','4873112109')
    for isbn10 in data:
         isbn13 = isbn10to13(isbn10)
         print " %s -> %s -> %s" % (isbn10,isbn13,ssccBarCode(isbn13))

Delphi版は関数だけ

function m10w31(code: string): integer;
/**
 Delphi版　モジュラス１０ウエィト３のチェックデジット計算。
*/
var
  i,w,sum: integer;
begin
  w := 3;
  sum := 0;
  for i := length(code) downto 1 do
  begin
    sum := sum + strtoint(code[i]) * w;
    if w = 3 then w := 1
    else w := 3;
  end;
  Result := sum mod 10;
  if Result<>0 then Result := 10 - Result;
end;

最新版はgithubをどうぞ。
Python版、Delphi版

ISBNをいろいろするライブラリ

Posted on by eight
2

ISBNの13桁化（ＩＳＢＮ（国際標準図書番号）規格改定等について お知らせ）に伴って、既存のデータベースの10桁を変換するために、作ったものです。

  • 10桁、13桁ISBNのチェックデジットの計算
  • 10桁ISBNと13桁JANの相互変換
  • チェックデジットなしのコードをチェックデジットを付けた10桁、13桁ISBNに変換

入力値は最低9桁あれば、10桁、13桁のISBNに変換できるようにしています。

2段目JANコード（C分類、価格）の意味も表示できます。

追記

isbnlib.py

※最新版はGist:227568をどうぞ。

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""ISBN（国際標準図書番号）規格改定

http://www.isbn-center.jp/whatsnew/kikaku.html

ISBNコードをいろいろするライブラリ。isbnlib.py
"""
import os,sys

#13桁ISBNの接頭文字
ISBN13_PREFIX = ('978','979')
#書籍JANの接頭文字（ISBNと同様）
JAN_PREFIX_1ST = ISBN13_PREFIX
#書籍JAN2段目の接頭文字
JAN_PREFIX_2ND = ('192','191')
#2005年12月現在、10桁に対して付加する接頭文字
ISBN13=ISBN13_PREFIX[0]

def cd10(code):
  """10桁ISBNのチェックデジットを計算して返す。

http://www.kikuchi.biz-web.jp/barcodefile/ISBN.html

  """
  s = str(code)[:9]
  w = 10
  a = 0
  for i in range(len(s)):
    a = a + (int(s[i]) * w)
    w -= 1
  d = 11 - (a % 11)
  if d == 10: d = "X"
  if d == 11: d = "0"
  return d

def cd13(code):
  """13桁ISBNのチェックデジットを計算して返す。

http://www.isbn-center.jp/whatsnew/kikaku.html

  """
  s = str(code)[:12]
  a = b = 0
  for i in range(0,len(s),2): a = a + int(s[i])
  for i in range(1,len(s),2): b = b + int(s[i])
  d = (a + (b*3)) % 10
  if d>0: d = 10 - d
  return d

def cdjan(s): return cd13(s)

class ISBN(object):
  """10桁と13桁のISBNを相互変換するクラス
  """
  def __init__(self, anyisbn):
    """クラスの初期化
    9桁（チェックデジットなし）、10桁、13桁（JAN）のいずれの形式でも
    受け入れる。逆にそれ以外は受け入れない。
    """
    self.raw = None
    anyisbn = anyisbn.strip().upper().replace('ISBN','').replace('-','')
    if len(anyisbn) in (9,10):
      self.raw = anyisbn[:9]
    elif len(anyisbn)==13:
      self.raw = anyisbn[3:12]
    else:
      raise ValueError,"ISBN [%s] length error. should be 9 or 10 or 13" % anyisbn

  def isbn10(self): return "%s%s" % (self.raw,self.old_checkdegit())
  def oldstyle(self): return self.isbn10()
  def old_checkdegit(self): return cd10(self.raw)

  def isbn13(self): return "%s%s%s" % (ISBN13,self.raw,self.new_checkdegit())
  def newstyle(self): return self.isbn13()
  def new_checkdegit(self): return cd13(ISBN13+self.raw)

class CCode(object):
  """Cコード（バーコードの2段目に含まれている）の内容を保持しているクラス。
  """
  _target = (u"一般",u"教養",u"実用",u"専門","None",u"婦人",u"学参I (小・中学生対象)",u"学参II(高校生対象)",u"児童 (中学生以下対象)",u"雑誌扱い")
  _style = (u"単行本",u"文庫",u"新書",u"全集・双書",u"ムック・その他",u"事・辞典",u"図鑑",u"絵本",u"磁性媒体など",u"コミック")
  _ndc = (u"総記",u"百科事典",u"年鑑","None",u"情報科学","None","None","None","None","None",
  u"哲学",u"心理（学）",u"倫理（学）","None",u"宗教",u"仏教",u"キリスト教","None","None","None",
  u"歴史・総記",u"日本歴史",u"外国歴史",u"伝記","None",u"地理",u"旅行","None","None","None",
  u"社会科学総記",u"政治（含む国防軍事）",u"法律",u"経済・財政・統計",u"経営","None",u"社会",u"教育","None",u"民族風習",
  u"自然科学総記",u"数学",u"物理学",u"化学",u"天文・地学",u"生物学","None",u"医学・薬学","None","None",
  u"工学工業総記",u"土木",u"建築",u"機械",u"電気",u"電子・通信",u"海事",u"採鉱・冶金",u"その他工業","None",
  u"産業総記",u"農林業",u"水産業",u"商業","None",u"交通通信業","None","None","None","None",
  u"芸術総記",u"絵画・彫刻",u"写真・工芸",u"音楽・舞踏",u"演劇・映画",u"体育・スポーツ",u"諸芸・娯楽",u"家事","None",u"コミック・劇画"
  u"語学総記",u"日本語",u"英米語","None",u"ドイツ語",u"フランス語","None",u"外国語","None","None",
  u"文学総記",u"日本文学総記",u"日本文学詩歌",u"日本文学小説","None",u"日本文学・評論・随筆その他","None",u"外国文学小説",u"外国文学その他","None")

  def __init__(self,ccode=None):
    self.raw = ccode
    if self.raw:
     self.target = int(ccode[0:1])
     self.targetv = self._target[self.target]
     self.style = int(ccode[1:2])
     self.stylev = self._style[self.style]
     self.ndc = int(ccode[2:4])
     self.ndcv = self._ndc[self.ndc]
  def __str__(self): return self.raw

class BookJAN(ISBN):
  """2段目バーコードのデータをCコードと価格に分類するクラス。
  Cコードの細かい内容はCCodeクラスが管理する。
  """
  def __init__(self, input):
    if len(input)==13:
      if input[:3] in JAN_PREFIX_1ST:
         super(BookJAN,self).__init__(input)
         self.ccode = None
         self.price = None
      elif input[:3] in JAN_PREFIX_2ND:
         self.raw = input
         self.ccode = CCode(self.raw[3:7])
         self.price = int(self.raw[7:12])
      else:
       raise Exception,"[%s] does not valid Book JAN code" % input
    else:
     raise Exception,"[%s] does not valid Book JAN code" % input

def test(encoding='euc-jp'):
  """テストルーチン"""
  tests = ('494999908','4798108545',
    '4-7741-2228-9','ISBN4-7741-2228-9','isbn4774122289',
    'ISBN450123456','12345678901','9784398470270','9784817180148')

  print "="*20
  print " ISBN"
  print "="*20

  for c in tests[0:4]:
    isbn = ISBN(c)
    print "Raw   ",isbn.raw,"[<-",c,"]"
    print "Old    ",isbn.oldstyle()
    print "New ",isbn.newstyle()
    print ""

  print "="*20
  print " Book JAN"
  print "="*20
  tests = ('9784398470270','9784817180148')
  for c in tests[0:4]:
    jan = BookJAN(c)
    print "RAW   ",jan.raw
    print "New   ",jan.newstyle()
    print ""

  print "="*20
  print " Book JAN CCode"
  print "="*20
  tests = ('1910193003605','1923055038004')
  for c in tests[0:4]:
    jan = BookJAN(c)
    print "RAW   ",jan.raw
    if jan.ccode:
     print "C-CODE   ",jan.ccode
     print "C-CODE.Target ",jan.ccode.target,' :',jan.ccode.targetv.encode(encoding)
     print "C-CODE.Style  ",jan.ccode.style,' :',jan.ccode.stylev.encode(encoding)
     print "C-CODE.NDC    ",jan.ccode.ndc,':',jan.ccode.ndcv.encode(encoding)
     print "PRICE   ",jan.price
    print ""

  idx = 0
  cc = 0
  for c in CCode()._ndc:
    print idx,
    if c:
      print c.encode(encoding)
      cc += 1
    else:
      print c
    idx += 1
  print cc,'/',len(CCode()._ndc)

if __name__=='__main__':
  encoding = "euc-jp"
  if sys.platform == 'win32': encoding="cp932"
  test(encoding)