Oracle 通過觸發器 來建立 同步暫存資料表 及處理 通過 自治事務 來解決 查詢 基表的問題

標籤：des   style   blog   color   io   os   ar   strong   for   

// 觸發器

create or replace trigger tr_sync_BD_MARBASCLASSafter INSERT or UPDATEon BD_MARBASCLASS for each rowdeclare v_cnt integer;PRAGMA AUTONOMOUS_TRANSACTION;   -- 自治事務begin    /**     * Name : tr_sync_BD_MARBASCLASS     * Desc : 物料分類 增量同步處理 觸發器     * Author : wuyf  [email protected]     * Date : 2014-09-10     */      case       when inserting then            insert into BD_MARBASCLASS_SYNC (AVERAGECOST,    AVERAGEMMAHEAD,    AVERAGEPURAHEAD,    AVGPRICE,    CODE,    CREATIONTIME,    CREATOR,    DATAORIGINFLAG,    DEF1,    DEF10,    DEF2,    DEF3,    DEF4,    DEF5,    DEF6,    DEF7,    DEF8,    DEF9,    DR,    ENABLESTATE,    INNERCODE,    MODIFIEDTIME,    MODIFIER,    NAME,    NAME2,    NAME3,    NAME4,    NAME5,    NAME6,    PK_GROUP,    PK_MARASSTFRAME,    PK_MARBASCLASS,    PK_ORG,    PK_PARENT,    TS                   ,    PARENT_PK,    PARENT_CODE,    PARENT_NAME                   ,ITF_CREATE_DATE ,ITF_CRUD,ITF_ISSYNC,ITF_MSG,ITF_SYNC_DATE,ITF_COUNT,ITF_PK)            SELECT :new.AVERAGECOST,    :new.AVERAGEMMAHEAD,    :new.AVERAGEPURAHEAD,    :new.AVGPRICE,    :new.CODE,    :new.CREATIONTIME,    :new.CREATOR,    :new.DATAORIGINFLAG,    :new.DEF1,    :new.DEF10,    :new.DEF2,    :new.DEF3,    :new.DEF4,    :new.DEF5,    :new.DEF6,    :new.DEF7,    :new.DEF8,    :new.DEF9,    :new.DR,    :new.ENABLESTATE,    :new.INNERCODE,    :new.MODIFIEDTIME,    :new.MODIFIER,    :new.NAME,    :new.NAME2,    :new.NAME3,    :new.NAME4,    :new.NAME5,    :new.NAME6,    :new.PK_GROUP,    :new.PK_MARASSTFRAME,    :new.PK_MARBASCLASS,    :new.PK_ORG,    :new.PK_PARENT,    :new.TS                   ,    MC.PK_MARBASCLASS AS PARENT_PK,    MC.CODE AS PARENT_CODE,    MC.NAME AS PARENT_NAME          -- 父級 PK,編碼,名稱                   ,TO_CHAR( sysdate,‘yyyy-MM-dd hh:mm:ss‘),‘C‘,0,‘‘,null,0,CAST( sys_guid() AS NVARCHAR2(36))                   FROM (                        SELECT :new.PK_PARENT AS PK_PARENT                                FROM DUAL                   ) T                   LEFT JOIN BD_MARBASCLASS MC     -- 物料分類(父級)                        ON T.PK_PARENT = MC.PK_MARBASCLASS                   ;       else             insert into BD_MARBASCLASS_SYNC (AVERAGECOST,    AVERAGEMMAHEAD,    AVERAGEPURAHEAD,    AVGPRICE,    CODE,    CREATIONTIME,    CREATOR,    DATAORIGINFLAG,    DEF1,    DEF10,    DEF2,    DEF3,    DEF4,    DEF5,    DEF6,    DEF7,    DEF8,    DEF9,    DR,    ENABLESTATE,    INNERCODE,    MODIFIEDTIME,    MODIFIER,    NAME,    NAME2,    NAME3,    NAME4,    NAME5,    NAME6,    PK_GROUP,    PK_MARASSTFRAME,    PK_MARBASCLASS,    PK_ORG,    PK_PARENT,    TS                   ,    PARENT_PK,    PARENT_CODE,    PARENT_NAME                   ,ITF_CREATE_DATE ,ITF_CRUD,ITF_ISSYNC,ITF_MSG,ITF_SYNC_DATE,ITF_COUNT,ITF_PK)            SELECT :new.AVERAGECOST,    :new.AVERAGEMMAHEAD,    :new.AVERAGEPURAHEAD,    :new.AVGPRICE,    :new.CODE,    :new.CREATIONTIME,    :new.CREATOR,    :new.DATAORIGINFLAG,    :new.DEF1,    :new.DEF10,    :new.DEF2,    :new.DEF3,    :new.DEF4,    :new.DEF5,    :new.DEF6,    :new.DEF7,    :new.DEF8,    :new.DEF9,    :new.DR,    :new.ENABLESTATE,    :new.INNERCODE,    :new.MODIFIEDTIME,    :new.MODIFIER,    :new.NAME,    :new.NAME2,    :new.NAME3,    :new.NAME4,    :new.NAME5,    :new.NAME6,    :new.PK_GROUP,    :new.PK_MARASSTFRAME,    :new.PK_MARBASCLASS,    :new.PK_ORG,    :new.PK_PARENT,    :new.TS                   ,    MC.PK_MARBASCLASS AS PARENT_PK,    MC.CODE AS PARENT_CODE,    MC.NAME AS PARENT_NAME          -- 父級 PK,編碼,名稱                   ,TO_CHAR( sysdate,‘yyyy-MM-dd hh:mm:ss‘),‘U‘,0,‘‘,null ,0,CAST( sys_guid() AS NVARCHAR2(36))                   FROM (                        SELECT :new.PK_PARENT AS PK_PARENT                                FROM DUAL                   ) T                   LEFT JOIN BD_MARBASCLASS MC     -- 物料分類(父級)                        ON T.PK_PARENT = MC.PK_MARBASCLASS                   ;     end case;          commit;  -- 記得 提交 事務end;

 

 

// 建立臨時 同步 中間狀態 表 表結構

--判斷表是否存在，如果存在則刪除declare       num   number; begin       select count(1) into num from all_tables where TABLE_NAME = ‘BD_MARBASCLASS_SYNC‘ and OWNER=‘NC‘;       if   num=1   then           execute immediate ‘drop table BD_MARBASCLASS_SYNC‘;       end   if; end; /
/** * Name : BD_MARBASCLASS_SYNC * Desc : 物料分類 同步表 * Author : wuyf  [email protected] * Date : 2014-09-10 */ create table BD_MARBASCLASS_SYNC as select t.*       , CAST(NULL AS nvarchar2(19)) AS ITF_CREATE_DATE         -- 同步記錄建立時間       , CAST(‘‘ AS NVARCHAR2(30)) AS ITF_CRUD                  -- 同步類型 : C:新增,U:更新,D:刪除,R:讀(一般沒有)       , 0 AS ITF_ISSYNC                                        -- 是否已同步: 0:未同步或同步失敗, 1:已同步成功       , CAST(‘‘ AS NVARCHAR2(2000)) AS ITF_MSG                 -- 同步訊息:        , CAST(NULL AS nvarchar2(19)) AS ITF_SYNC_DATE           -- 最後同步時間       , 0 AS ITF_COUNT                                         -- 同步次數        ,CAST( sys_guid() AS NVARCHAR2(36)) AS ITF_PK -- 同步主鍵       from (          SELECT                  MC.*                 ,nvl(MCP.PK_MARBASCLASS,‘‘) AS PARENT_PK    -- 父分類PK                 ,nvl(MCP.CODE,‘‘) AS PARENT_CODE            -- 父分類編碼                 ,nvl(MCP.NAME,‘‘) AS PARENT_NAME            -- 父分類名稱                 FROM BD_MARBASCLASS MC                 LEFT JOIN BD_MARBASCLASS MCP                      ON MC.PK_PARENT = MCP.PK_MARBASCLASS       ) t        where 1=0;

 

Oracle 通過觸發器 來建立 同步暫存資料表 及處理 通過 自治事務 來解決 查詢 基表的問題