巧用存儲過程進行數據處理

[摘 要]本文介紹一種在財務軟件數據庫中查找重復數據、解決數據冗余的方法。對于龐大的財務數據庫來說，合理地使用存儲過程、巧妙地采用游標，就能在大量的數據中找出重復多余的數據，從而減少數據的冗余，提高數據庫的效率，從而保證財務數據的正確性。

[關鍵字]存儲過程；游標；SQLServer 2000；數據冗余

[中圖分類號]TP311．138SQ [文獻標識碼]A [文章編號]1673-0194(2006)07-0062-03

1 引 言

隨著數據庫技術的發展，數據庫業已深入應用到各行各業。我們發現對大多數企業用戶來說，在財務軟件系統進行升級時，都可能面臨著不同數據庫之間的數據重用問題。而在財務數據重用的處理過程中，可能由于處理的難度，會出現部分數據冗余的現象，這在財務管理方面是決不允許出現的。針對這種情況，我們可以使用存儲過程和游標來實現在海量的數據中查找重復記錄，來解決這一數據冗余的問題。

2 使用存儲過程和游標的優點

對于這個在大量的數據中查找重復記錄的迭代過程來說，其迭代次數是不確定的，是由表中數據的多少來決定的。這種情況如果在應用程序中編寫代碼是比較復雜的，也較難解決。而使用數據庫中的存儲過程和游標，就有如下優點：第一，可以簡化所開發的財務應用程序，無論何種高級語言程序只需要調用存儲過程序即可；第二，采用存儲過程和游標結合運行，可以根據檢索條件直接由財務數據庫管理系統得到需要的數據，可以減少數據和應用程序的通信次數，尤其在C／S或者B／S結構中減少網絡通話具有重大意義；最后，可以加快數據的處理速度，存儲過程的預編譯和管理系統的直接處理，以及減少通信都可以加快數據的處理速度。

3 存儲過程和游標的語法

現在結合MSSQLServer環境來介紹一下存儲過程和游標的語法。

存儲過程(Storedprocedure)是一組預先編譯好的、存放在數據庫服務器上的一組TransactSQL代碼，作為一個獨立的數據庫對象，可以作為一個單元被用戶的應用程序調用。之所以叫它存儲過程，是因為它是保存在數據庫服務器上的一組編譯好的代碼，執行時不必再次進行編譯，就可直接在數據庫服務器上執行，它只向客戶端返回結果集，從而提高了程序的運行效率。其語法具體如下：

CREATE PROCEDURE [OWNER]．[PROCEDURENAME]

[{@parameterdatatype}[varyfing][=default][output]][…n]

AS

SQl_statement

其中“[]”內是可選的，語法中PROCEDURE NAME為定義的存儲過程名，@parameterdatatype定義的是存儲過程的參數以及參數類型，根據需要確定參數，參數間用逗號隔開，如果參數是游標類型可以使用[output]。AS后面是存儲過程的具體SQL語句，其中可以定義變量，定義變量語法是declare@variantdatatype，不同的變量寫在另一行。

游標是類似于C語言指針的一種結構，屬于SQL中的一種數據類型。游標提供了一種在服務器內部處理數據集的方法，它可以識別一個數據集合內部指定的工作行，從而可以有選擇地按行進行操作。

游標的操作包括：聲明(DECLARE)、打開(OPEN)、取數(FETCH)、關閉(CLOSE)、釋放(DEALLOCATE)。聲明游標是對游標的定義與描述，說明它的名字與數據來源，這是游標生命歷程的開始；打開游標會生成一個類似表的數據集；取數就是從數據集中取出一行中的某些列數據到變量中；關閉游標會解除對游標的封鎖，同時釋放某些相應的資源，游標關閉后可以再次打開；釋放游標就是將游標所占用的所有計算機資源釋放出來，這是游標生命歷程的終止。由于游標可以將其中的某些數據傳到變量中，因此在需要將數據集中的每一行數據取出來進行分析、計算與更新操作的時候，使用游標就變得非常方便。其基本語法如下：

Declare cursor_name cnrsor

[localIGlobal][Forward_only IScroll]

[static lkeysetldynamic lfast_forward]

[read_onlylscrolUocks[optimistics][type_waming]

Forselect statement

lforupdate[of column_name[---n]]]

語法中cursor_name為定義的游標名，For后面的語句select_statement是游標的檢索語句。

4 問題的解決

在企業財務系統升級的數據重用過程中，出現部分重復數據會嚴重影響現行系統的正常使用。發生這種情況的原因可能是多方面的：一方面，可能是原軟件設計因考慮不周而導致用戶錄入了重復記錄；另一方面，也可能是在進行異構數據庫數據轉換時產生了重復數據。而對于這些重復記錄，用戶很難再通過現行的財務應用程序進行有效地處理。很容易處理成為本應保留成一條記錄，而不小心將重復記錄全部刪除的結果。

因此軟件維護人員就面對的一個這樣的問題：如何在大量的數據中找出那些重復記錄，并將它們合理有效地處理呢?要是能容易知道這些記錄的標示碼，處理起來還比較簡單，但在無法知道是哪些記錄有重復的情況下，是不可能打開企業管理器中的表，從第一條找到最后一條的。這時我們就可以利用存儲過程和游標來解決這類問題。下面就結合在軟件維護時碰到的實際問題來進行分析：

所處理的數據庫名為：MGMIS XDB，其中有一個表名為：CJ_JBXX，關鍵字為：Sibh。創建一個名為：K_FIND_CF的存儲過程。

設計思路是：先將表中的數據按關鍵字排序，這樣要是有重復記錄時，重復記錄就會排在一起；然后定義三個游標，這里分別為：saml，findl，find2。suml是用來求整個表的總記錄數，findl指向前一條記錄，find2指向findl的下一條記錄，比較findl，find2是否相同，若相同就把find2游標所指的關鍵字保存到臨時表T_find中(注意：考慮到數據的安全性，這里沒有把數據直接刪除，而是把重復記錄存到另一個表中，當然也可以直接刪除數據，只要稍做修改)；當用“查詢分析器”執行這個存儲過程時，可用select*from T_find，就可以在下面的網格中看到重復記錄了。

本存儲過程的關鍵就是findl，find2這兩個游標。先定義游標find1：

declare findl scroll cursor

for

select gjbh from CJ_JBXX

open find1

再定義游標find2：

declare find2 scroll cursor

for

select gibh from CJ_JBXX

open find2

注意，此時雖然定義了兩個游標，但它們都是指向表頭，并沒有形成先后關系。接著就要用“fetchfirstfromfind2into@yb2”讓find2指向第一條記錄，此時find2就在findl的下一條記錄，接著再用while進行循環遍歷整個表直到最后一條記錄，其中當執行完下面兩句

fetch next from find1 into @yb1

fetch next from find2 into @yb2時，find1指向第一條記錄，find2指向第二條記錄，一直向下查找。當滿足@yb1=@yb2(也就是findl和find2所指的關鍵字相同時)，就另存到表T_find中。另外，因為當find2到最后一條記錄時， 由于不滿足while(@@fetch_status<>-1)條件，所以游標find2不再向下走，而findl卻還沒有到達最后一條記錄，仍然指向下一個記錄，即最后一條記錄，此時最后一條記錄就被認為是重復記錄。為了避免最后一條記錄被認為是相同的記錄，要加上(@@fetch_status<>-1)判斷條件，才不會出現這種情況。

現列出整個存儲過程代碼如下，供大家參考：CREATE PROCEDURE [K_FIND_CF]——此存儲過程用于查找表中的重復值

AS

if exists(select*from sysobjects where id=object_id(N'[dbo].[T_find]')

and OBJECTPROPERTY(id，N'lsUserTahle')=1)

droptable[dbo]．[T_find]

CREATETABLE [dbo]．[T_find](——創建一個臨時表，用于保存重復記錄[cfz)[char)(16))

——先對整個表進行按關鍵字gjbh從小到大排序select*from CJ_BXX orderby gjbh——可修改成你要找的表名和字段名

declare suml scroll cursor——定義一個游標，求GJ_JBXX的總記錄

for

selectcount(*)from CJ_JBXX——可修改成你要找的表名

open sum1

declare@zshu int——定義一個變量，保存CJ_JBXX表中的總記錄

fetch firstfrom suml into@zshu

close suml

deallocate suml

declarefindl scroll cursor

for

selectSjbhfromCJ_JBXX——可修改成你要找的表名和字段名

openfind1

declare find2 scroll cursor

for

selectSjbhfromCJ_JBXX——可修改成你要找的表名和字段名

openfind2

declare@yb1 char(16)

declare@yb2 char(16)

fetchfirstfromfind2into@yb2——find2一開始指向第一條記錄

while(@@fetclstatus<>-1)——if@@fetch_status(游標當前所指的行) 在最后一行時，——@@fetch_status的值為-1，其它情況都為0

begin

fetch nextfromfindl into@yb1

fetch nextfrom find2 into@yb2

if(@yb1=@yb2)and(@@fetch_status<>—1)——必須有and(@@fetch_status<>-1)這個判斷條件，否則最后一條記錄將認為是重復的

insertintodbo．T_find(cfz)values(@yb2)

end

closefind1

deallocate find1

close find2

dealloeate fing2

select*from T_find

GO

5 結論

利用上述方法可以很好地實現查詢財務數據庫中的重復值，實際上盡管它是從處理財務軟件過程中總結出來的，但實際上可以應用于多種類型的數據庫維護中。本文介紹的例程只用到一個關鍵字，還可根據需要采用幾個關鍵字復合查詢。同時還可修改成通用的查詢存儲過程，只需將表名和關鍵字通過外部變量來進行傳遞。