一个数据库存储过程的例子

--集計処理
CREATE PROCEDURE SET_TIMESTAMP
AS
BEGIN
--初期化TIMESTAMP_TBL
DELETE FROM TIMESTAMP_TBL;
-- 1.SOTI_BUNRUI_MSTテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'SOTI_BUNRUI_MST',
(SELECT MAX(UPD_DATE) FROM SOTI_BUNRUI_MST),
'system',
CAST('now' AS TIMESTAMP)
);
-- 2.SOTI_MSTテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'SOTI_MST',
(SELECT MAX(UPD_DATE) FROM SOTI_MST),
'system',
CAST('now' AS TIMESTAMP)
);
-- 3.KIKI_MSTテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'KIKI_MST',
(SELECT MAX(UPD_DATE) FROM KIKI_MST),
'system',
CAST('now' AS TIMESTAMP)
);
-- 4.KAKAKUMST_TBLテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'KAKAKUMST_TBL',
(SELECT MAX(UPD_DATE) FROM KAKAKUMST_TBL),
'system',
CAST('now' AS TIMESTAMP)
);
-- 5.SOTI_BUNRUI_TBLテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'SOTI_BUNRUI_TBL',
(SELECT MAX(UPD_DATE) FROM SOTI_BUNRUI_TBL),
'system',
CAST('now' AS TIMESTAMP)
);
-- 6.SOTI_TBLテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'SOTI_TBL',
(SELECT MAX(UPD_DATE) FROM SOTI_TBL),
'system',
CAST('now' AS TIMESTAMP)
);
-- 7.SOTIKIKI_LINK_TBLテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'SOTIKIKI_LINK_TBL',
(SELECT MAX(UPD_DATE) FROM SOTIKIKI_LINK_TBL),
'system',
CAST('now' AS TIMESTAMP)
);
-- 8.KIKI_LINK_TBLテーブルの最新更新日をTIMESTAMP_TBLに登録
INSERT INTO TIMESTAMP_TBL (
TBL_NAME,
MEMT_TIMESTAMP,
UPD_ID,
UPD_DATE
) VALUES (
'KIKI_TBL',
(SELECT MAX(UPD_DATE) FROM KIKI_TBL),
'system',
CAST('now' AS TIMESTAMP)
);
WHEN ANY DO
BEGIN
EXCEPTION;
END
END;