デチューンの前にダミーのストアドプロシージャ
前回、複雑なSQLをデチューンするという話を書きましたが、弊社ではまずはダミーのストアドプロシージャを作るということを推奨していますので、ダミーのストアドプロシージャから作成します。
出力する内容が以下になるので、その通りのダミービューを作ります。
請求書NO
納品書NO
行NO
売上日
得意先ID
丸め単位
商品ID
商品区分
単価
数量
消費税
ダミービューの作成
-- ダミービューの作成 CREATE VIEW xTEST_FUNC_VIEW AS SELECT 900001 AS 請求書NO , 1000001 AS 行NO , 1 AS 納品書NO , TO_DATE('20100617', 'YYYYMMDD') AS 売上日 , 500001 AS 得意先ID , 2 AS 丸め単位 , 12345678 AS 商品ID , 'A' AS 商品区分 , 255 AS 単価 , 10 AS 数量 , 127 AS 消費税 -- FROM DUAL Oracleの場合必要 UNION ALL SELECT 900001 , 1000001 , 2 , TO_DATE('20100617', 'YYYYMMDD') , 500001 , 2 , 12345679 , 'B' , 355 , 20 , 355 -- FROM DUAL Oracleの場合必要
エクセルでジェネレート
先ほどのソースでは2行しかダミーデータができませんから、手作業でダミーのビュー・ストアドプロシージャ・ファンクションでスタブを作ることは、全く効率的ではありません。しかし、エクセルでパラメータと戻りの型を決め、ダミーデータを書き込めるようにすれば、ジェネレートすることは可能です。（エクセルマクロを弊社で販売しております）
全部を解説するのはとっても大変なので、弊社のツールでデータを作ったのでダウンロードしてみてください。
（バッチファイルのパス・接続情報などは各自で書き換える必要があります）
MySQL/PostgreSQL版もありますので、info@g1sys.co.jp へ、お問い合わせください。
一応、誤差配賦したデータにしましたので、ダミーデータの作成には少し時間が掛かりましたが、数字を適当にするならば数分で作ることが可能でしょう。
実行するときは、
SELECT * FROM TABLE(TEST_PKG.TEST_FUNC(NULL, NULL))
exec TEST_PROC NULL, NULL
でOKです。
実行例）
本番プログラムに書換
Oracleはあまりに長くなるので、SQLServerで説明すると、
CREATE PROCEDURE TEST_PROC ( @pFrom datetime -- 期間開始日 , @pTo datetime -- 期間終了日 ) AS SET NOCOUNT ON; -- 本番時は以下のSQLを修正し、このコメントを削除する。 SELECT * FROM xTEST_PROC_VIEW WHERE 1 = 1 ; GO
を利用してひとまずユーザインターフェース側を完成させて、
CREATE PROCEDURE TEST_PROC ( @pFrom datetime -- 期間開始日 , @pTo datetime -- 期間終了日 ) AS SET NOCOUNT ON; SELECT a.請求書NO, a.納品書NO, a.行NO, a.売上日, a.得意先ID , a.丸め単位, a.商品ID, a.商品区分, a.単価, a.数量 , CASE WHEN a.丸め単位 = 1 THEN a.明細消費税 WHEN a.丸め単位 = 2 THEN a.明細消費税 + CASE WHEN ABS(a.納品書消費税 - a.納品明細合計) >= 納品SEQ THEN SIGN(a.納品書消費税 - a.納品明細合計) ELSE 0 END WHEN a.丸め単位 = 3 THEN a.明細消費税 + CASE WHEN ABS(a.請求書消費税 - a.請求明細合計) >= 請求SEQ THEN SIGN(a.請求書消費税 - a.請求明細合計) ELSE 0 END END AS 消費税 FROM (SELECT uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID , um.商品ID, p.商品区分, um.単価, um.数量 , um.単価 * um.数量 AS 明細合計 , fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) AS 明細消費税 , SUM(fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法)) OVER (PARTITION BY uh.納品書NO) AS 納品明細合計 , fn丸め( SUM(um.単価 * um.数量) OVER (PARTITION BY uh.納品書NO) * p.消費税率, t.丸め方法) AS 納品書消費税 , ROW_NUMBER() OVER (PARTITION BY uh.納品書NO ORDER BY um.単価 * um.数量 DESC) AS 納品SEQ , SUM(fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法)) OVER (PARTITION BY uh.請求書NO) AS 請求明細合計 , fn丸め( SUM(um.単価 * um.数量) OVER (PARTITION BY uh.請求書NO) * p.消費税率, t.丸め方法) AS 請求書消費税 , ROW_NUMBER() OVER (PARTITION BY uh.請求書NO ORDER BY um.単価 * um.数量 DESC) AS 請求SEQ , t.丸め方法, t.丸め単位 FROM 売上 uh INNER JOIN 売上明細 um ON uh.売上ID = um.売上ID INNER JOIN 得意先 t ON uh.得意先ID = t.得意先ID INNER JOIN 商品 p ON um.商品ID = p.商品ID WHERE uh.売上日 BETWEEN @pFrom AND @pTo ) a; GO
と書き換えれば完成です。
重要なのは、SELECT句のカラムの型とエリアスをダミービューに合わせればOKです。
是非、ダウンロードして試してみてください。
あと、マクロ買ってね（笑）お問い合わせは info@g1sys.co.jp まで。
