存储过程标准

CREATE OR REPLACE PROCEDURE IF.PROC_表名(IN F_DATE DATE)

-------------------------------------------------------------------------------
-- (C) Copyright TLB and IBM <date>
--
-- File name:           XXXXXXXX.sql
-- Procedure name:             XXXXXXX
-- Source Table:                XXXXXX
-- Target Table:                 XXXXXX
-- Project:             TLB EDW
-- Note:
-- Purpose:
--
--=============================================================================
-- Creation Date:       YYYY.MM.DD
-- Origin Author:       XXXXX
--
-- Version: %1.0%
--
-- Modification History
-- --------------------
-- Date         ByPerson        Description
-- ----------   --------------  -----------------------------------------------
-- 2013-12-25   XXXXX           XXXXXX
-------------------------------------------------------------------------------

LANGUAGE SQL

BEGIN
            /*声明异常处理变量*/
            DECLARE V_PROC_SCHEMA              VARCHAR(100);             --存储过程所在的schema
            DECLARE V_PROC_NM                  VARCHAR(100);             --存储过程名称
            DECLARE V_PROC_ACT_DT              DATE;                     --存储过程会计日期
            DECLARE V_PROC_STEPNUM             INT DEFAULT 1;            --过程内部位置标记
            DECLARE V_PROC_STEPDESC            VARCHAR(100) DEFAULT '';  --过程内部位置描述
            DECLARE V_PROC_START_TS            TIMESTAMP;                --过程开始执行时间
            DECLARE V_PROC_END_TS              TIMESTAMP;                --过程结束时间
            DECLARE V_STEP_START_TS            TIMESTAMP;                --步骤开始执行时间
            DECLARE V_STEP_END_TS              TIMESTAMP;                --步骤结束时间
            DECLARE SQLCODE,V_PROC_SQLCODE     INT DEFAULT 0;            --SQLCODE
            DECLARE V_PROC_RCOUNT              INT;                      --DML语句作用记录数
        
            /*声明存储过程使用变量*/
      DECLARE V_IF_DT                    VARCHAR(8);               --接口业务日期
            DECLARE XXX                        SMALLINT;
            DECLARE XXX                        SMALLINT;
        
            /*定义针对SQL异常情况的句柄(EXIT、WARNING方式)*/
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SET V_PROC_SQLCODE = SQLCODE;
          ROLLBACK;
          
                INSERT INTO DWMM.PROC_RUN_LOG (
                         PROC_SCHEMA,
                         PROC_NM,
                         PROC_ACT_DT,
                         PROC_STEPNUM,
                         PROC_STEPDESC,
                         PROC_STRT_TS,
                         PROC_END_TS,
                         PROC_SQLCODE)
                Values( 
                         V_PROC_SCHEMA,                                     -- PROC_SCHEMA
                         V_PROC_NM,                                         -- PROC_NM
                         V_PROC_ACT_DT,                                     -- PROC_ACT_DT
                         V_PROC_STEPNUM,                                    -- PROC_STEPNUM
                         V_PROC_STEPDESC,                                   -- PROC_STEPDESC
                         V_STEP_START_TS,                                   -- PROC_STRT_TS
                         CURRENT TIMESTAMP,                                 -- PROC_END_TS
                         V_PROC_SQLCODE)                                    -- PROC_SQLCODE
                ;

          COMMIT;
          RESIGNAL;
        END;
        
        DECLARE CONTINUE HANDLER FOR SQLWARNING
        BEGIN
          SET V_PROC_SQLCODE = SQLCODE;

                INSERT INTO DWMM.PROC_RUN_LOG (
                         PROC_SCHEMA,
                         PROC_NM,
                         PROC_ACT_DT,
                         PROC_STEPNUM,
                         PROC_STEPDESC,
                         PROC_STRT_TS,
                         PROC_END_TS,
                         PROC_SQLCODE)
                Values( 
                         V_PROC_SCHEMA,                                     -- PROC_SCHEMA
                         V_PROC_NM,                                         -- PROC_NM
                         V_PROC_ACT_DT,                                     -- PROC_ACT_DT
                         V_PROC_STEPNUM,                                    -- PROC_STEPNUM
                         V_PROC_STEPDESC,                                   -- PROC_STEPDESC
                         V_STEP_START_TS,                                   -- PROC_STRT_TS
                         CURRENT TIMESTAMP,                                 -- PROC_END_TS
                         V_PROC_SQLCODE)                                    -- PROC_SQLCODE
                ;
                
          COMMIT;
        END;
        
        /*变量赋值*/
        SET V_PROC_SCHEMA = '根据具体SCHEMA赋值';
        SET V_PROC_NM     = '存储过程名';
        SET V_PROC_ACT_DT = F_DATE;
        SET V_IF_DT = TO_CHAR(V_PROC_ACT_DT,'YYYYMMDD');
        
            --==============================数据加工开始==========================================
            SET V_PROC_STEPNUM = 0;
            SET V_PROC_STEPDESC = 'PROCEDURE START.';
            SET V_PROC_START_TS = CURRENT TIMESTAMP;
            SET V_PROC_END_TS = CURRENT TIMESTAMP;
            
            INSERT INTO DWMM.PROC_RUN_LOG (PROC_SCHEMA,PROC_NM,PROC_ACT_DT,PROC_STEPNUM,PROC_STEPDESC,PROC_STRT_TS,PROC_END_TS,PROC_SQLCODE,PROC_RCOUNT)
        VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_PROC_START_TS,V_PROC_END_TS,0,0);
        COMMIT;
        
            /*数据恢复与备份*/
            SET V_STEP_START_TS = CURRENT TIMESTAMP;
            SET V_PROC_STEPNUM = 10;
            SET V_PROC_STEPDESC = '数据恢复与备份';
            
        --此处放置处理脚本
        COMMIT;
        
        GET DIAGNOSTICS V_PROC_RCOUNT = ROW_COUNT;
            SET V_STEP_END_TS = CURRENT TIMESTAMP;
            INSERT INTO DWMM.PROC_RUN_LOG (PROC_SCHEMA,PROC_NM,PROC_ACT_DT,PROC_STEPNUM,PROC_STEPDESC,PROC_STRT_TS,PROC_END_TS,PROC_SQLCODE,PROC_RCOUNT)
        VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_STEP_START_TS,V_STEP_END_TS,V_PROC_SQLCODE,V_PROC_RCOUNT);
        COMMIT;
        
        /*临时表声明及赋值*/
            SET V_STEP_START_TS = CURRENT TIMESTAMP;
            SET V_PROC_STEPNUM = 20;
            SET V_PROC_STEPDESC = '临时表声明及赋值';
            
        DECLARE GLOBAL TEMPORARY TABLE xxxx
        (
         ) 
        ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE IN TS_USER_TMP ---TS_DM（数据量不大的时候可以放在此，否则放在TS_SGL_USR_TMP）
        PARTITIONING KEY(key);
        
            CREATE INDEX SESSION.XXX_ID ON SESSION.XXX(key);
            --此处对临时表进行赋值---
            
        GET DIAGNOSTICS V_PROC_RCOUNT = ROW_COUNT;
            SET V_STEP_END_TS = CURRENT TIMESTAMP;
            INSERT INTO DWMM.PROC_RUN_LOG (PROC_SCHEMA,PROC_NM,PROC_ACT_DT,PROC_STEPNUM,PROC_STEPDESC,PROC_STRT_TS,PROC_END_TS,PROC_SQLCODE,PROC_RCOUNT)
        VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_STEP_START_TS,V_STEP_END_TS,V_PROC_SQLCODE,V_PROC_RCOUNT);
        COMMIT;
        
            /*数据加工-XXX*/
            SET V_STEP_START_TS = CURRENT TIMESTAMP;
            SET V_PROC_STEPNUM = 30;
            SET V_PROC_STEPDESC = '数据加工-XXX';
            --加工逻辑处理
            
        GET DIAGNOSTICS V_PROC_RCOUNT = ROW_COUNT;
            SET V_STEP_END_TS = CURRENT TIMESTAMP;
            INSERT INTO DWMM.PROC_RUN_LOG (PROC_SCHEMA,PROC_NM,PROC_ACT_DT,PROC_STEPNUM,PROC_STEPDESC,PROC_STRT_TS,PROC_END_TS,PROC_SQLCODE,PROC_RCOUNT)
        VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_STEP_START_TS,V_STEP_END_TS,V_PROC_SQLCODE,V_PROC_RCOUNT);
        COMMIT;    
        
            --==============================数据加工结束==========================================
            SET V_PROC_STEPNUM = 999;
            SET V_PROC_STEPDESC = 'PROCEDURE END.';
            SET V_PROC_END_TS = CURRENT TIMESTAMP;
            INSERT INTO DWMM.PROC_RUN_LOG (PROC_SCHEMA,PROC_NM,PROC_ACT_DT,PROC_STEPNUM,PROC_STEPDESC,PROC_STRT_TS,PROC_END_TS,PROC_SQLCODE,PROC_RCOUNT)
        VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_PROC_START_TS,V_PROC_END_TS,0,0);
        COMMIT;
END@