LoginSignup
0

More than 5 years have passed since last update.

【SQL】プロシージャ

Posted at

プロシージャ

決めった仕様により、プログラム開発作業をリリースまで終わってから、今後お客様から追加依頼があったら、プログラムの改修となると思われます。
普通は、その改修のコースができれば、既存システムへの影響が少ない方がラクです。
たとえば、何かしら一つのコードを追加したいとかの依頼が来られたら、プログラム側の改修として、先ずコード定義の追加、後、そのコードに関係ある処理の改修対象かとうかのスコープを決めて、コースとかを見積もって、お客様とやり取りにして検討するという形です。
こういう場合は、最初から、プログラム側処理をプロシージャとしてお客様に提案しましょう。
なぜなら、今後システムの運用上で、何が増やすか減るのかの可能性が高いと認識され、今一もやもやしてるが決められないところ、今後保守期間の改修が発生したら、コースを増やしたくない場合、今一として、その処理を丸ごとにプロシージャにしましょう。

例として挙げます。

ユーザーさんの個人のメールアドレス宛てに何件のメールが送信したか、失敗した件数を集計したいです。メール送信のステータスが 送信済(1) の以外はいろんなステータスがあるとして、どれのが集計するのはお客様次第なので、今後増やすの恐れがあります。

以下のプロシージャ処理で、今後ステータスの変更があったら、そのSQLだけ直してすぐ対応できます。

PostgreSQL

/* プロシージャ集計 */
/** Table - mails
______________________________
pno |mail_no |status |send_datetime
1   |1001    |1      |2018/04/09 11:10:41.565
1   |1002    |2      |2018/04/10 11:10:41.565
1   |1003    |2      |2018/04/11 11:10:41.565
2   |2001    |2      |2018/04/10 11:10:41.565
2   |2002    |3      |2018/04/11 11:10:41.565
______________________________
**/


-- データ型の定義
CREATE TYPE T_mail_counter AS (pno VARCHAR(5), count INTEGER);


CREATE OR REPLACE FUNCTION F_mail_send_failed(
     date_from TIMESTAMP WITH TIME ZONE, 
     date_to TIMESTAMP WITH TIME ZONE)  
RETURNS SETOF T_sms_counter AS $BODY$
----
-- 
-- 集計処理プロシージャ(F_mail_send_failed)
-- 
--    個人ナンバーごとに対象期間内メール送信失敗のデータを集計する
-- 
-- param: 
--    date_from       抽出対象期間の開始日
--    date_to         抽出対象期間の終了日
-- 
-- return:
--    array
--      pno           個人ナンバー
--      count         件数
--
----

-- 定義
DECLARE
    pno             VARCHAR(5);                  -- 個人ナンバー
    count           INTEGER;                     -- 件数
    data_set        T_sms_counter;               -- データセット
-- 集計処理
BEGIN

    FOR data_set IN 
        SELECT
            pno
            , count(*) AS count
        FROM
            mails
        WHERE
            status IN('1','2')
            AND send_datetime > date_from
            AND send_datetime < date_to
        GROUP BY
            pno
    LOOP 
        IF NOT FOUND THEN NULL;
        END IF;
        RETURN NEXT data_set;
    END LOOP;
    RETURN;
END; 
$BODY$ LANGUAGE 'plpgsql'; 

/**
result
     [param]date_from: '2018/04/10 00:00:00'
     [param]date_to  : '2018/04/11 23:59:59'
______
pno |count 
1   |2  
2   |1  
______
**/



Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0