熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中對COLUMNS

2013-11-13 15:41:50  來源: Oracle 

  本文詳細講述了Oracle中對COLUMNS_UPDATED()返回值解析

  需求來源

  客戶要求[某些特定的表]能[自定義預警報告]

  

  (在特定的表上)用戶可定義某些字段有修改時向有關用戶發出消息警報<內容大致是 xx 單據的 xx 單號的xx字段由 old 變為了 new>最終目的是由消息控制模塊向消息接收人報告這一變更

  

  基礎知識:

  COLUMNS_UPDATED()是一個僅可在 Insert or Update trigger 中調用的方法

  該方法返回 一個 varbinary 的值 存儲了當次Insert 或是Update 觸發器所對應的記錄在哪些字段上發生了Inserted or updated在SQLSERVER 的聯機幫助[CREATE TRIGGER]和[IF UPDATE] 中有對 COLUMNS_UPDATED () 方法的簡要描述

  

  公司要求用Trigger 實現: (為每個[特定的表]編寫一個特定的UPDATE 觸發器)主要難點是窮舉IF UPDATE(column)的方法不可行其它業務實現無問題後來仔細琢磨COLUMNS_UPDATED() 所返回的值問題得到解決

  

  這裡只是講述對COLUMNS_UPDATED()所返回的值的解析和運用就不考慮用戶指定變更字段及插入記錄到消息表的那部分實現過程了

  

  測試數據准備

  

  If exists(select * from sysobjects where id=object_id(N[dbo][T_Test]) and xtype = u) DROP Table T_Test go CREATE Table T_Test ( f_idint IDENTITY( ) Primary Key f_charChar() default f_varchar  varchar() default f_nvarchar  nvarchar() default f_datetime  datetime default getdate() f_intint default f_bigint  bigint default f_decimal  decimal( ) default f_number  numeric( ) default f_floatfloat default ) go INSERT INTO T_Test (f_char) values() INSERT INTO T_Test (f_char) values() go

  

   

  編寫Update 觸發器

  If exists(select * from sysobjects where id=object_id(N[dbo][Tri_Test_Upd]) and objectproperty(idNistrigger)=) DROP TRIGGER Tri_Test_Upd go CREATE TRIGGER Tri_Test_Upd ON T_Test WITH ENCRYPTION FOR UPDATE AS DECLARE @iRowCnt INT SET @iRowCnt = @@rowcount IF @iRowCnt < RETURN DECLARE  @sTable VARCHAR()  @sPKName VARCHAR()  @sColName VARCHAR() DECLARE  @iColCnt INT  @iColId INT DECLARE  @iTINYINT  @jTINYINT  @iSegment TINYINT  @iVal  TINYINT  @iLog TINYINT DECLARE  @sSQL VARCHAR() SET @sTable = t_test SET @sPKName = f_id 求得當前表列個數 SELECT @iColCnt = Count() FROM syscolumns WHERE id = object_id(@sTable) 個字段為一小段 SET @iSegment = CASE   WHEN @iColCnt / = @iColCnt / THEN @iColCnt / ELSE @iColCnt / + END 將數據存入 臨時表 SELECT * INTO #Inserted FROM Inserted SELECT * INTO #Deleted FROM Deleted 中間處理數據用 CREATE TABLE #Temp( f_PKVal varchar() not null primary key f_OldVal varchar() f_NewVal varchar() ) SET @i = WHILE @i < @iSegment BEGIN IF @iColCnt <  SET @iVal= COLUMNS_UPDATED() ELSE  SET @iVal= SubString(COLUMNS_UPDATED() @i + ) 等於 則表示當前小節所對應的個字段無一被改 IF @iVal =  BEGIN  SET @i = @i +  CONTINUE  END WHILE @iVal >  BEGIN  SET @j =  SET @iLog = @iVal /  WHILE @iLog >   BEGIN   SET @j = @j +   SET @iLog = @iLog /   END   得到被Update 的 列ID  SET @iColId = * @i + @j +   將Update列名 賦予 @sColName  SELECT @sColName = Sname   FROM Inserted as I Deleted as D Syscolumns as S  WHERE IF_id = DF_id   AND Sid = object_id(@sTable)   AND lid = @iColId  Truncate table #Temp   拼成動態語句  SET @sSQL = INSERT INTO #Temp (f_PkVal f_OldVal f_NewVal) +   SELECT Convert( varchar() I + @sPkName + ) +   Convert( varchar() D + @sColName + ) +   Convert( varchar() I + @sColName + ) + FROM #Inserted as I #Deleted as D + WHERE I + @sPKName + = D + @sPKName + AND I + @sColName + <> D + @sColName  EXEC(@sSQL)   測試輸出   Select f_pkVal @sColName as f_column_name f_oldVal f_newVal FROM #temp   實際上用 將信息處理後插入消息表  /*    INSERT INTO T_Message()   SELECT 要組織的內容 FROM #temp  */  SET @iVal = @iVal Power( @j)  END SET @i = @i + END DROP TABLE #Inserted DROP TABLE #Deleted DROP TABLE #Temp go

   

  測試數據

  Update T_test Set f_datetime = getdate() f_float = f_int=  上面Update 語句共修改了三個列  實際輸出 ) f_int f_int ) f_datetime May  :PM May  :PM f_datetime May  :PM May  :PM ) f_float f_float

  

  算法

  COLUMNS_UPDATED()方法返回的 varbinary是以每個小節存儲個字段(的修改狀態)的方式記錄了當前觸發器所有列的修改情形因此程序以個字段為一片段來循環處理所有字段

  SET @iVal= SubString(COLUMNS_UPDATED() @i + )程序用上面語句將一小節轉化為整型測試發現:(當且謹當這一小片只有一個字段有修改時)

  

  @iVal = = ^(); @iVal =  = ^(); @iVal =  = ^(); @iVal =  = ^(); @iVal =  = ^(); @iVal =  = ^(); @iVal =  = ^(); @iVal =  = ^();

  而當且謹當個字段有修改時:

  

  @iVal = ^() + ^() = ;

  而第 三個字段有修改時:

  

  @iVal = ^() + ^() + ^() = ;

  當個字段都有修改時:

  

  @iVal = ^() + ^() + + ^() = ;

  也就是說 無論怎樣修改@iVal的值不外乎是^n (n> and n < int)這一數組型成的[和組合](組合時每個數組成員最多出現一次)因此反過來推算: 對 @iVal 按 ^n分解 就可算得被修改列的列表


From:http://tw.wingwit.com/Article/program/Oracle/201311/17153.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.