-- å‡ºè·æ˜Žç´°TBã®ã‚¹ãƒ†ãƒ¼ã‚¿ã‚¹æ›´æ–°SP CREATE OR REPLACE FUNCTION f_forward_detail_state_update( IN _orderno integer, -- å—æ³¨No IN _orderrowno integer, -- å—æ³¨è¡ŒNo IN _forwarddate date, -- å‡ºè·æ—¥ IN _forwardstate integer, -- 進æ—çŠ¶æ³ IN _returnreason varchar, -- è¿”å“ç†ç”± IN upduser varchar, -- 更新ユーザ IN pc varchar, -- 更新マシン IN count integer, -- 更新カウンタ OUT errcd integer, OUT errmsg varchar, OUT code integer) AS $BODY$ DECLARE cntcheck integer; checkdate date; -- å‡ºè·æ—¥ checkreason varchar; -- è¿”å“ç†ç”± rec RECORD; _kubun integer; BEGIN -- -- パラメータãƒã‚§ãƒƒã‚¯ -- -- å¿…é ˆãƒã‚§ãƒƒã‚¯ IF _orderno IS NULL THEN RAISE EXCEPTION '引数 å—æ³¨Noã¯å¿…é ˆã§ã™'; END IF; IF _orderrowno IS NULL THEN RAISE EXCEPTION '引数 å—æ³¨è¡ŒNoã¯å¿…é ˆã§ã™'; END IF; -- å‡ºè·æ—¥ã¨è¿”å“ç†ç”±å–å¾— SELECT forwarddate, returnreason, updcnt INTO rec FROM t_forward_detail WHERE orderno = _orderno AND orderrowno = _orderrowno AND delflg = FALSE; IF FOUND THEN checkdate := rec.forwarddate; checkreason := rec.returnreason; END IF; -- 更新カウンタãƒã‚§ãƒƒã‚¯ IF rec.updcnt IS NOT NULL AND rec.updcnt <> count THEN RAISE EXCEPTION '更新カウンタãŒä¸æ£ã§ã™'; END IF; -- 処ç†ãƒã‚§ãƒƒã‚¯ IF _forwardstate = 4 THEN -- å‡ºè·æ¸ˆã¿ ã®ã¨ã IF _forwarddate IS NULL THEN RAISE EXCEPTION '引数 å‡ºè·æ—¥ã¯å¿…é ˆã§ã™'; END IF; checkdate :=_forwarddate; ELSIF _forwardstate = 8 THEN IF _returnreason IS NULL THEN RAISE EXCEPTION '引数 è¿”å´ç†ç”±ã¯å¿…é ˆã§ã™'; END IF; checkreason := _returnreason; END IF; IF rec.updcnt IS NULL THEN INSERT INTO t_forward_detail(orderno, orderrowno, forwarddate, forwardstate, returnreason, updusrnm, updpcnm) VALUES(_orderno, _orderrowno, _forwarddate, _forwardstate, _returnreason, upduser, pc); ELSE -- ステータス更新 UPDATE t_forward_detail SET forwarddate = checkdate, forwardstate = _forwardstate, returnreason = checkreason, updusrnm = upduser, updpcnm = pc WHERE orderno = _orderno AND orderrowno = _orderrowno; END IF; errcd := 1; code := _orderno; errmsg := '完了'; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;