PL/SQLを使って、簡単なプログラムを作ってみようと思います。
カレンダーデータの作成を行うプログラムです。
目次
カレンダーマスタの必要性
Oracleって、日付を扱う関数は色々あるのですけど、カレンダーのように日付を羅列して表示する仕組みがないんです。（知らないだけだったらごめんなさい！）
副問い合わせやVIEWで代用することも可能ですが、日付に付随情報を持たせるなどの拡張性を考えると、マスターテーブルとして用意してしまった方がよいと私は思います。
CALENDAR_MASTERテーブルの作成
そんなわけで、まずは日付格納用のテーブルをひとつ作ります。sqlplusを起動して以下のDDLを実行してください。
「表が作成されました。」と表示されれば成功です。
|
1 2 3 4 5 6 |
/* CALENDAT_MASTERテーブルの作成 */ CREATE TABLE CALENDAR_MASTER( YMD VARCHAR2(8) NOT NULL, SEQ_NO NUMBER(4,0) NOT NULL, CONSTRAINT PK_CALENDAR_MASTER PRIMARY KEY(YMD) ); |
テーブル：CALENDAR_MASTER
| 列名 | 型 | 説明 |
| YMD | VARCHAR2(8) | 主キー。YYYYMMDD形式の日付文字列。 |
| SEQ_NO | NUMBER(4,0) | 連番 |
最初のYMDが、このテーブルの主キーであり、日付です。次のSEQ_NOが、日付の前後関係を示す値です。
SQL> SELECT COUNT(*) FROM CALENDAR_MASTER;
COUNT(*)
----------
0
まだテーブルの中は空っぽです。
データ登録プロシージャ(ダミー)の作成
んでは、試しに画面を介さないPLSQLプロシージャを一個作ってみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR REPLACE PROCEDURE PR_CALENDAR IS start_date DATE:=SYSDATE; -- 現在日付からカウントアップ BEGIN -- 現在日付から30日後までを登録。 INSERT INTO CALENDAR_MASTER(YMD,SEQ_NO) SELECT TO_CHAR(SYSDATE+ROWNUM-1,'YYYYMMDD'),ROWNUM FROM ALL_CATALOG WHERE SYSDATE+30 >= SYSDATE+ROWNUM-1; COMMIT; END; / |
これをコンパイルします。
コンパイルができたら、次はこのプロシージャを実行。
SQL> CALL PR_CALENDAR();
コールが完了しました。
先程作ったテーブルをSELECTしてみてください。
本日と30日後までの日付が、順番に並んで表示されているかと思います。
SQL> SELECT * FROM CALENDAR_MASTER;
YMD SEQ_NO
---------------- ----------
20190907 1
20190908 2
20190909 3
・・中略・・
20191005 29
20191006 30
20191007 31
31行が選択されました。
次は、このへんを画面から操作できるようにしてみましょう。
PR_CALENDARプロシージャはもう使いませんのでドロップして構いません。
SQL> DROP PROCEDURE PR_CALENDAR;
プロシージャが削除されました。
ブラウザで動くPLSQLプログラム
ここからはWebサービスを使って動く仕組みを作ります。
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR REPLACE PROCEDURE WEB_CALENDAR IS BEGIN HTP.P('<HTML>'); HTP.P('<BODY>'); HTP.P('ボタンを押してください!!'); HTP.P('<BR>'); HTP.P('<INPUT TYPE="button" value="日付登録" onClick="alert(''何も起こりません！'');"></INPUT>'); HTP.P('</BODY>'); HTP.P('</HTML>'); END; / |
このプロシージャをコンパイルしてください。
エラーがなければ、Webブラウザを立ち上げて、URLに
http://IPアドレス:8080/DAD_WEB_SERVICE/WEB_CALENDAR
と入力してください。表示されるダイアログには、ユーザー名とパスワードを入力します。
こんな画面が表示されましたでしょうか。「日付登録」ボタンを押すと…
何も起こりません。そう書いているので。まずは、Webページが表示されればOK。
では、一気にすっ飛ばして動くカタチのものにします。
WEB_CALENDARプロシージャ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
CREATE OR PLSQLREPLACE PROCEDURE WEB_CALENDAR(CALL_TYPE IN VARCHAR2 DEFAULT NULL ,START_DATE IN VARCHAR2 DEFAULT NULL ,END_DATE IN VARCHAR2 DEFAULT NULL) IS V_INFO_MSG VARCHAR2(100) := NULL; V_CALL_TYPE VARCHAR2(10):= NULL; V_START_DATE VARCHAR2(8):= NULL; V_END_DATE VARCHAR2(8):= NULL; BEGIN -- 登録ボタンを押された場合は、日付データを登録する。 IF CALL_TYPE = 'INSERT' AND TO_DATE(START_DATE,'YYYYMMDD') < TO_DATE(END_DATE,'YYYYMMDD') THEN -- 先に既存データを全部DELETE. DELETE FROM CALENDAR_MASTER; -- START_DATE～END_DATEまでレコードを登録する INSERT INTO CALENDAR_MASTER(YMD,SEQ_NO) SELECT TO_CHAR(TO_DATE(START_DATE,'YYYYMMDD')+ROWNUM-1,'YYYYMMDD'),ROWNUM FROM ALL_CATALOG WHERE END_DATE >= TO_CHAR(TO_DATE(START_DATE,'YYYYMMDD')+ROWNUM-1,'YYYYMMDD') ; COMMIT; -- 表示メッセージをセット V_INFO_MSG := START_DATE||'から'||END_DATE||'までカレンダー登録しました！'; END IF; -- 登録ボタンを押された場合の処理、ここまで。 -- 表示値の設定 SELECT NVL(MIN(YMD),TO_CHAR(SYSDATE,'YYYYMMDD')) ,NVL(MAX(YMD),TO_CHAR(SYSDATE,'YYYYMMDD')) INTO V_START_DATE,V_END_DATE FROM CALENDAR_MASTER; HTP.P('<HTML>'); -- ヘッダータグ HTP.P(' <HEAD>'); HTP.P(' <TITLE>カレンダーマスタの登録</TITLE>'); HTP.P(' </HEAD>'); -- BODY HTP.P('<BODY>'); HTP.P(V_INFO_MSG); HTP.P('<BR>'); HTP.P('<FORM METHOD="post">'); HTP.P('開始日付：<INPUT TYPE="text" name="START_DATE" value="'||V_START_DATE||'"></INPUT>'); HTP.P('終了日付：<INPUT TYPE="text" name="END_DATE" value="'||V_END_DATE||'"></INPUT>'); HTP.P('<INPUT TYPE="submit" value="日付登録" >'); HTP.P('<INPUT TYPE="hidden" name="CALL_TYPE" value="INSERT"></INPUT>'); HTP.P('</FORM>'); HTP.P('</BODY>'); HTP.P('</HTML>'); EXCEPTION WHEN OTHERS THEN ROLLBACK; HTP.P('なんかエラーでたよ！'); END; / |
上記コンパイル後に再度Webページにアクセスすると、以下のような表示になっていると思います。
開始日付「20190101」、終了日付を「20191231」にしてボタンを押すと…
こんな感じに表示されます。
テーブルをSELECTしてみると、ちゃんと1年分のレコード(365件)が登録されていることがわかります。
SQL> SELECT * FROM CALENDAR_MASTER;
YMD SEQ_NO
---------------- ----------
20190101 1
20190102 2
20190103 3
20190104 4
・・中略・・
20191230 364
20191231 365
365行が選択されました。
まとめ
日付マスタを用意しておくと、各種問い合わせの基幹テーブルとして使えて色々便利です。
ブラウザで動かす意味は、実はあまりないのですけどね…
Webアプリの使用例を書きたかったので、無理やり題材にしてしまいました。
上記プロシージャは、レコードの一括作成(DELETE～INSERT)としていますが、定期的に追加していくような仕組みの方が実務には適しているかもしれませんね。
