Oracle 預存程序實例：月度登入統計報表

Oracle 預存程序實例：月度登入統計報表

需求：每月 1 號自動統計上個月的登入次數、登入帳號數、總帳號數，寫入報表表 RP_MONTH_CLICK。

預存程式碼

CREATE OR REPLACE PROCEDURE update_RP_MONTH_CLICK AS
  v_ym              VARCHAR2(6);   -- 年月
  v_login_cnt       INTEGER;       -- 登入次數
  v_login_acct_cnt  INTEGER;       -- 登入帳號數
  v_acct_cnt        INTEGER;       -- 總帳號數
BEGIN
  /* 取得統計年月：若昨天與今日同年月則取本月，否則取上月 */
  SELECT CASE
           WHEN TO_CHAR(SYSDATE-1,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM')
           THEN TO_CHAR(SYSDATE,'YYYYMM')
           ELSE TO_CHAR(SYSDATE-1,'YYYYMM')
         END
  INTO v_ym FROM DUAL;

  /* 登入次數 */
  SELECT COUNT(t.log_id)
  INTO v_login_cnt
  FROM SC_ACT_LOG t
  WHERE t.act_type = 5
    AND TO_DATE(TO_CHAR(t.act_time,'YYYYMM'),'YYYYMM') = TO_DATE(TO_CHAR(SYSDATE,'YYYYMM'),'YYYYMM');

  /* 登入帳號數（去重） */
  SELECT COUNT(staff_id)
  INTO v_login_acct_cnt
  FROM (
    SELECT t.staff_id
    FROM SC_ACT_LOG t
    WHERE t.act_type = 5
      AND TO_DATE(TO_CHAR(t.act_time,'YYYYMM'),'YYYYMM') = TO_DATE(TO_CHAR(SYSDATE,'YYYYMM'),'YYYYMM')
    GROUP BY t.staff_id
  );

  /* 總帳號數 */
  SELECT COUNT(t.staff_id)
  INTO v_acct_cnt
  FROM HRM_STAFF t
  WHERE t.status = 1;

  /* 寫入報表：先刪後插，避免重複 */
  DELETE FROM RP_MONTH_CLICK WHERE RMONTH = v_ym;
  INSERT INTO RP_MONTH_CLICK (ID, RMONTH, LOGIN_COUNT, LOGIN_ACCOUNT_COUNT, ACCOUNT_COUNT)
  VALUES (SEQ_SC_GLOBAL_PARAMETERS.NEXTVAL, v_ym, v_login_cnt, v_login_acct_cnt, v_acct_cnt);
  COMMIT;
END;

設計重點

• 使用 INTO 將查詢結果賦值給 PL/SQL 變數。
• 日期比較統一轉 YYYYMM 格式，避免時分秒干擾。
• DELETE + INSERT 模式確保冪等性，適合排程重跑。
• Sequence SEQ_SC_GLOBAL_PARAMETERS 提供唯一主鍵。