數(shù)據(jù)庫中自動生成數(shù)據(jù)的存儲過程設(shè)計(jì)

肖連

摘要：簡要說明數(shù)據(jù)庫中存儲過程的作用，接著根據(jù)系統(tǒng)開發(fā)的需要和功能需求，主要從自動生成數(shù)據(jù)功能的角度，說明了在存儲過程中個人信息注冊時性別、出生年月、籍貫、工作地點(diǎn)等字段的生成方法。根據(jù)需要，設(shè)計(jì)數(shù)據(jù)自動生成的存儲過程，將數(shù)據(jù)插入數(shù)據(jù)庫中，并以個人信息的自動生成為例，給出了參考示例。通過三個存儲過程的設(shè)計(jì)，將一個簡單的聊天通信軟件所需要的數(shù)據(jù)模擬出10萬條賬戶信息，并建立他們之間的好友關(guān)系，從而為系統(tǒng)測試數(shù)據(jù)提供基礎(chǔ)條件。通過示例，展現(xiàn)存儲過程在基礎(chǔ)數(shù)據(jù)自動生成方面的應(yīng)用。

關(guān)鍵詞：存儲過程;自動生成;模擬數(shù)據(jù);示例;sQLServer

中圖分類號：TP311.1 文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2020）06-0018-04

1背景

存儲過程（Stored Procedure）是SQL語言編程。存儲過程結(jié)合各數(shù)據(jù)庫的特點(diǎn)，應(yīng)用SQL語言編寫的，能完成一定功能的程序。存儲過程具有執(zhí)行效率高，可重用性、可移植性、可維護(hù)性、提高安全性等功能。在知名的關(guān)系型數(shù)據(jù)庫中，都設(shè)有存儲過程的技術(shù)。存儲過程分為三種：系統(tǒng)存儲過程、擴(kuò)展存儲過程和用戶自定義存儲過程。系統(tǒng)存儲過程在SQL Server中大多數(shù)以sp-或xp_開頭，是系統(tǒng)預(yù)編譯處理好的程序，它們執(zhí)行時能完成一定的功能，供使用者調(diào)用。擴(kuò)展存儲過程是指在數(shù)據(jù)庫環(huán)境之外，使用編程語言（如C++）創(chuàng)建的外部例程形成的動態(tài)鏈接庫（DLL）。用戶自定義存儲過程，是由用戶編寫的完成用戶所需要設(shè)計(jì)的一些功能。比如，用戶開發(fā)的系統(tǒng)運(yùn)行速度有待提高，跟數(shù)據(jù)庫方面的連接有待及時響應(yīng)，這時，往往需要利用存儲過程來進(jìn)行提高效率。

存儲過程的作用一般是為了減少瀏覽器端與數(shù)據(jù)庫服務(wù)器端的數(shù)據(jù)傳輸，具有提高系統(tǒng)運(yùn)行效率的功能。存儲過程對數(shù)據(jù)進(jìn)行了預(yù)處理，減少了數(shù)據(jù)傳輸，并且，存儲過程有一次編譯，多次運(yùn)行的特點(diǎn)。一個好的系統(tǒng)里往往會有多個存儲過程的設(shè)計(jì)。存儲過程和它們所在的數(shù)據(jù)庫是一起存放的，而且存儲過程使用靈活方便，由于在功能上的優(yōu)勢，往往一個系統(tǒng)中自定義函數(shù)設(shè)計(jì)沒有存儲過程使用多。

除了提高系繞性能的作用外，存儲過程還能完成一些數(shù)據(jù)的預(yù)輸入功能。在系統(tǒng)的開發(fā)和測試階段，往往需要一些數(shù)據(jù)，較少的數(shù)據(jù)可以采用手動的輸入方式，而系統(tǒng)在投入之前需要完善的測試，尤其是抗壓能力的測試需要模擬大量的數(shù)據(jù)，此時，采用手工輸入的方式是不太現(xiàn)實(shí)的。此時，我們可以利用存儲過程生成一些系統(tǒng)需要的數(shù)據(jù)，進(jìn)行數(shù)據(jù)庫中數(shù)據(jù)的添加;在教學(xué)過程中，涉及數(shù)據(jù)庫設(shè)計(jì)，也往往需要添加大量數(shù)據(jù)，如果采用手工添加方式，容易造成數(shù)據(jù)出錯，操作疲勞等問題。可以在數(shù)據(jù)庫中添加相應(yīng)的存儲過程，完成初始數(shù)據(jù)的添加工作，為系統(tǒng)的使用打下基礎(chǔ)。文獻(xiàn)[1][3—6]分別從不同的角度來敘述存儲過程的功能和作用，但是未涉及實(shí)際應(yīng)用中存儲過程隨機(jī)數(shù)據(jù)的生成。

2存儲過程設(shè)計(jì)

在實(shí)際應(yīng)用中，根據(jù)系統(tǒng)功能分析，進(jìn)行E-R設(shè)計(jì)，根據(jù)轉(zhuǎn)換的數(shù)據(jù)模型，建立相應(yīng)的數(shù)據(jù)庫系統(tǒng)，建立表結(jié)構(gòu)。系統(tǒng)的運(yùn)行中，表中需要大量的數(shù)據(jù)，我們就可以考慮采用編寫存儲過程的方式，在需要數(shù)據(jù)時，讓系統(tǒng)自動生成數(shù)據(jù)。下面以添加個人注冊信息為例進(jìn)行分析。在各大網(wǎng)站進(jìn)行信息注冊時，我們往往需要提供注冊者的籍貫，賬號一般根據(jù)系統(tǒng)的自增字段來自動生成，籍貫信息，就涉及省、市、縣，這些信息我們在注冊時，往往由級聯(lián)列表框來實(shí)現(xiàn)，個人注冊時，由操作者按需尋找所需要的籍貫，選擇即可，可是對于一個新開發(fā)的系統(tǒng)，當(dāng)需要大量的數(shù)據(jù)去做測試時，這些數(shù)據(jù)的輸入就是一個體力活了。那么，利用關(guān)系型數(shù)據(jù)庫中存儲過程的特點(diǎn)，我們可以編寫一個自動生成一些測試數(shù)據(jù)的存儲過程，完成系統(tǒng)數(shù)據(jù)的自動生成。

系統(tǒng)設(shè)計(jì)存儲過程采用一個隨機(jī)數(shù)的值，可以根據(jù)隨機(jī)數(shù)的特點(diǎn)，來生成數(shù)據(jù)。數(shù)據(jù)表的主鍵字段，采用數(shù)據(jù)信息插入的先后順序，最小值從10000開始;第二個字段是性別字段，由于只有“男”、“女”兩個值，那么我們就可以將生成的隨機(jī)數(shù)直接截取兩段進(jìn)行賦值;第三個是籍貫字段，就需要先根據(jù)隨機(jī)數(shù)選擇省，然后再選擇市。根據(jù)所得，最后進(jìn)行數(shù)據(jù)的自動插入操作。具體流程圖如圖1所示。3存儲過程示例

根據(jù)上面的分析，設(shè)計(jì)不帶參數(shù)的存儲過程my_procl如下，該存儲過程隨機(jī)產(chǎn)生數(shù)據(jù)100萬條，在基于x64的操作系統(tǒng)windowsl0，主頻2.4GHz，內(nèi)存為8GB，SQL Server2008企業(yè)版的環(huán)境下，執(zhí)行時間約400秒。其中的一些參數(shù)是根據(jù)需求反復(fù)調(diào)試確定的，對于某些因子不能太小，小了就影響表示效果，也不能過大，過大了還需要求余處理。比如生成出生年月的時候，將一個浮點(diǎn)型值用cast函數(shù)轉(zhuǎn)換為日期型數(shù)據(jù)時，日期型數(shù)據(jù)必須符合系統(tǒng)的實(shí)際需求，系統(tǒng)經(jīng)過多次測試確定了50000這個因數(shù)。示例代碼如下所示：

其中省市的自動生成流程為：

1）生成隨機(jī)小數(shù);

2）根據(jù)生成的隨機(jī)小數(shù)，產(chǎn)生1-34之間的隨機(jī)整數(shù)，根據(jù)此值確定省的值;

3）根據(jù)生成的隨機(jī)小數(shù)，和確定的省份的值，產(chǎn)生相對應(yīng)的隨機(jī)整數(shù)，根據(jù)此值確定市區(qū)值。

采用此種方式生成的省市值類似于注冊時生成的自動聯(lián)動的下拉式菜單確定數(shù)據(jù)的方式，若需要進(jìn)一步確定城區(qū)，縣，鎮(zhèn)，村等信息，可以繼續(xù)采用隨機(jī)數(shù)進(jìn)行聯(lián)動的方式。

存儲過程編寫完畢，可以直接執(zhí)行：

exec my-proc 1

生成數(shù)據(jù)。此處舉例為不帶參數(shù)的存儲過程，具體應(yīng)用時，可是選擇參數(shù)的設(shè)置，比如，產(chǎn)生的數(shù)據(jù)量大小可以以參數(shù)的形式傳進(jìn)去，對數(shù)據(jù)的某些要求也可以以參數(shù)的形式進(jìn)行設(shè)置。

根據(jù)上面生成的用戶注冊的信息，通過下面的存儲過程rny-proc2可以生成每個用戶所對應(yīng)的表，用來記錄，用戶下面的好友信息，具體代碼如下所示：

通過語句exec my_proc2對用戶表中的所有賬號建立了相應(yīng)的用戶賬號表，采用游標(biāo)，對用戶表進(jìn)行讀取，由于存儲過程能直接使用DDL語言，因此，可以順序創(chuàng)建用戶賬號表，用來記錄用戶里面的好友信息。

基本的用戶表生成完畢，下面還可以利用存儲過程生成好友信息表，讓用戶之間建立聯(lián)系。模擬聊天通信軟件中的互加好友的功能。部分代碼示例如下所示：

執(zhí)行exec my_proc3.就可以自動隨機(jī)建立好友關(guān)系。以上模擬聊天通信軟件的數(shù)據(jù)庫，通過3個存儲過程就建立起來所需要的數(shù)據(jù)，在此基礎(chǔ)上就可以進(jìn)行數(shù)據(jù)庫中數(shù)據(jù)的應(yīng)用了。

4結(jié)束語

本文首先說明存儲過程的作用，基于系統(tǒng)開發(fā)的需要，往往需要模擬數(shù)據(jù)，這些數(shù)據(jù)靠人工方式輸人一方面數(shù)據(jù)量大，需要周期長，另一方面容易造成數(shù)據(jù)錯誤。根據(jù)系統(tǒng)功能需求，主要從生成模擬數(shù)據(jù)功能的角度，說明了存儲過程生成個人信息注冊時性別，出生年月，籍貫，工作地點(diǎn)等字段的生成方法。然后根據(jù)需要，設(shè)計(jì)數(shù)據(jù)自動生成的存儲過程，并以個人信息的自動生成為例，給出了參考示例。通過三個存儲過程的設(shè)計(jì)，將一個初步的聊天通信軟件所需要的數(shù)據(jù)模擬出10萬條賬戶信息，并建立他們之間的好友關(guān)系，從而為系統(tǒng)測試數(shù)據(jù)提供基礎(chǔ)條件。