営業日を判断するOracle PL/SQLパッケージ

現実逃避から少しでもエンジンがかかるように、コードを公開してみよう。

OracleのPL/SQLで書いた営業日/稼働日判定用のストアード・パッケージ。いろいろと調べてみたのだけれど、ぴったりくるものが見当たらなくて書いてみたもの。まだリリースまで時間があって、それほどテストされていないけれど、そんなに複雑でもないので大丈夫ではないかと。IS_WORKDAYがCHARを返すのはカッコ悪いけれど、いろいろあってこういう仕様。直すのは簡単なので気になる人は直してください。

仕様としては、1)土曜日と日曜日は営業日じゃない、2)休日テーブル(holidays)に日付があったらそれは休日。wkd_pkg.is_workdayを直すことで、土曜日は営業日にしたり、水曜日を休日にしたりできるので、その辺りは適当に。仕様はExcelにあるworkdayとnetworkdayを参考にしているけれど、厳密には一緒じゃない(と思う)。

このパッケージには4つの関数があります。1つはオーバーロードしたものなので、実質的には3つ。

1. FUNCTION WORKDAY( i_startdate IN date, i_days IN number) RETURN DATE;
開始日からi_days営業日後の日付を返す、 i_daysが正数なら未来、負数なら過去、開始日は数えない。

2. FUNCTION IS_WORKDAY( i_date IN date) RETURN CHAR;
指定した日付が営業日ならYを返す。営業日じゃなければNを返す。

3. FUNCTION NETWORKDAY( i_startdate IN date, i_enddate IN date) RETURN NUMBER;
開始日と終了日の間に含まれる営業日の日数を返す。日数には開始日と終了日も含まれる。
続きを読む

length(to_char(1, ’00’))=3

知らなかった。2じゃないってこと。符号の1文字分だけ増えるんだな。考えてみればそりゃそうか。
あんまり数字をこうやって扱うことがないから。そんなのが必要な時ってたいてい設計が悪いんじゃん。(言い訳)

PostgreSQL 8.4
pguser=# select length(to_char(1,'00'));
length
--------
3
(1 行)

Oracle 9i
SQL> select length(to_char(1,'00')) from dual;

LENGTH(TO_CHAR(1,'00'))
-----------------------
3

cx_Oracleが見つけるOracleクライアントライブラリを変えたい

C:\Python25\python.exeを起動すると、C:\oracle9i\bin\oci.dllがシステムで最初に見つかるけれど、cx_Oracleの想定するバージョン(10.2)と違うのでシンボルが見つからなくて例外が起きる。

Python 2.5を使うときは常に10.2のOracleクライアントを使う(が見つかる)ようにしたい。でも他のアプリケーションには影響がないようにしたい。

以下のファイルを作成することで解決。

#C:\Python25\Lib\site-packages\sitecustomize.py
import  os
ORA_PATH=Z:\instantclient_10_2
#環境変数のPATHを書き換える
os.environ['PATH'] = "%s%s" % (ORA_PATH, os.environ['PATH'])
#print os.environ['PATH']

ちなみに、今回初めて、Oracle Instant Clientを使ってみたけど、これは楽でいいね。Oracle8用のも欲しいがなあ。

OracleでFIRST_DAY

先輩に聞く前に、

  • まずよく考えみて、
  • わからなければ調べて、
  • それでもダメなら聞く、

という癖をつけないとなあ。厳しいぞ。

/* Oracle PL/SQLで日付からその月の1日を求める*/

/* to_date、to_charを使えるなら、考えればできる */
create or replace function first_day(thedate date) return date is
begin
    return to_date(to_char(thedate,'YYYY-MM')||'-01','YYYY-MM-DD');
end;

/* 調べてみれば、こっちがスマートだとわかる */
create or replace function first_day(thedate date) return date is
begin
    return trunc(thedate,'MONTH');
end;

/***** ついでに *****/

/* 年の最初の日 1月1日 */
create or replace function first_day2(thedate date) return date is
begin
    return trunc(thedate,'YEAR');
end;

/* 週の始まり日曜日(※NLSの設定次第) */
create or replace function first_day3(thedate date) return date is
begin
    return trunc(thedate,'DAY');
end;

最後に問題。月の日数を求めるにはどうする?

WindowsでOracle9i client版のSQL*Plusにはまる

sqlplusを実行するバッチファイルをいじっていたら、アプリケーションエラーが出てきて、Microsoftに連絡するかどうかを100回ぐらい聞かれた。

コマンドプロンプトで再現できるようになったのは、2時間後。

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:>echo %SQLPLUS%
%SQLPLUS%
C:\>set SQLPLUS=hoge
C:>echo %SQLPLUS%
hoge
C:\>sqlplus.exe
(ここでアプリケーションエラーが出る)

SQLPLUSという名前の環境変数が定義されているとダメみたい。

バッチのはじめのほうにあったUNIX版からの名残の環境変数を消してみると動いた。

なんだそれ。

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

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;