利用Mysql存儲(chǔ)過(guò)程方法產(chǎn)生模擬表數(shù)據(jù)

摘要：利用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程產(chǎn)生軟件開(kāi)發(fā)測(cè)試表中的模擬數(shù)據(jù)是一個(gè)非常有效的方法，而標(biāo)準(zhǔn)SQL是非過(guò)程化的語(yǔ)言，和程序設(shè)計(jì)語(yǔ)言相比，高度非過(guò)程化的優(yōu)點(diǎn)也造成其一個(gè)弱點(diǎn)：缺少流程控制能力，難以實(shí)現(xiàn)應(yīng)用業(yè)務(wù)中的邏輯控制，而在各行業(yè)應(yīng)用中存在很多很復(fù)雜的業(yè)務(wù)邏輯，簡(jiǎn)單的標(biāo)準(zhǔn)SQL語(yǔ)言不能承擔(dān)該角色，解決的辦法就是要采用面向過(guò)程的如PL/SQL、T-SQL語(yǔ)言即存儲(chǔ)過(guò)程方法。該論文采用MySQL 存儲(chǔ)過(guò)程來(lái)模擬車輛通行信息，采用參數(shù)的方法，自動(dòng)生成該參數(shù)指定行數(shù)的表記錄，供調(diào)試程序或統(tǒng)計(jì)的模擬數(shù)據(jù)源。

關(guān)鍵詞：數(shù)據(jù)庫(kù)；存儲(chǔ)過(guò)程；Mysql

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2018）20-0016-03

標(biāo)準(zhǔn)SQL是非過(guò)程化的查詢語(yǔ)言，具有操作統(tǒng)一、面向集合、功能豐富、使用簡(jiǎn)單等優(yōu)點(diǎn)。但和程序設(shè)計(jì)語(yǔ)言相比，高度非過(guò)程化的優(yōu)點(diǎn)也造成了它的一個(gè)弱點(diǎn)：缺少流程控制能力，難以實(shí)現(xiàn)應(yīng)用業(yè)務(wù)中的邏輯控制，而在各行業(yè)應(yīng)用中存在很多很復(fù)雜的業(yè)務(wù)邏輯，簡(jiǎn)單的標(biāo)準(zhǔn)SQL語(yǔ)言不能承擔(dān)該角色，這就提出可以面向過(guò)程的如PL/SQL、T-SQL數(shù)據(jù)庫(kù)語(yǔ)言進(jìn)行編程，即存儲(chǔ)過(guò)程方式，通過(guò)if then 語(yǔ)句或其他DML SQL 來(lái)生成所需要的數(shù)據(jù)。這種方式僅簡(jiǎn)單，而且容易實(shí)現(xiàn)，容易調(diào)試，并且可以包含一些業(yè)務(wù)邏輯和事務(wù)控制，是生成模擬數(shù)據(jù)的一種高效方法。

1模擬數(shù)據(jù)生成的意義背景

程序開(kāi)發(fā)離不開(kāi)數(shù)據(jù)源，比如商務(wù)網(wǎng)站開(kāi)發(fā)，表中數(shù)據(jù)是否真實(shí)，是否滿足要求，對(duì)調(diào)試程序有很大的幫助測(cè)試作用，能幫助開(kāi)發(fā)者快速發(fā)現(xiàn)程序的應(yīng)用設(shè)計(jì)是否滿足流程要求，數(shù)據(jù)查詢和統(tǒng)計(jì)是否正確，以及大數(shù)據(jù)平臺(tái)下的程序性能和網(wǎng)站的響應(yīng)時(shí)間，對(duì)用戶的真實(shí)體驗(yàn)等是非常關(guān)鍵的一個(gè)步驟，目前開(kāi)發(fā)環(huán)境下數(shù)據(jù)庫(kù)表中數(shù)據(jù)的量級(jí)常常不能滿足大記錄數(shù)據(jù)的要求，比如100萬(wàn)行記錄或更高，本論文利用存儲(chǔ)過(guò)程生成大批量數(shù)據(jù)，有助于真實(shí)數(shù)據(jù)的模擬和統(tǒng)計(jì)速度的統(tǒng)計(jì)查詢，有較好的應(yīng)用場(chǎng)景。

2數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程編程優(yōu)點(diǎn)

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)是明顯的，使用存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)：由于存儲(chǔ)過(guò)程不像解釋執(zhí)行的SQL語(yǔ)句那樣在提出操作請(qǐng)求時(shí)才進(jìn)行語(yǔ)句分析和優(yōu)化工作，因而運(yùn)行效率高，它提供在服務(wù)器端快速執(zhí)行SQL語(yǔ)句的有效途徑。

存儲(chǔ)過(guò)程降低了客戶機(jī)和服務(wù)器之間的通信量?？蛻魴C(jī)上的應(yīng)用程序只要通過(guò)網(wǎng)絡(luò)向服務(wù)器發(fā)出調(diào)用存儲(chǔ)過(guò)程的名字和參數(shù)，就可以讓關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)執(zhí)行其中的多行 SQL語(yǔ)句并進(jìn)行數(shù)據(jù)處理。

另外也方便實(shí)施企業(yè)規(guī)則?？梢园哑髽I(yè)規(guī)則的運(yùn)算程序?qū)懗纱鎯?chǔ)過(guò)程放入數(shù)據(jù)庫(kù)服務(wù)器中，由關(guān)系數(shù)據(jù)庫(kù)管理，既有利于集中控制，又能夠方便地進(jìn)行維護(hù)。當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)只要修改存儲(chǔ)過(guò)程即可。

存儲(chǔ)過(guò)程的能力大大增強(qiáng)了SQL語(yǔ)言的功能和靈活性。存儲(chǔ)過(guò)程可以用流控制語(yǔ)句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。

可保證數(shù)據(jù)的安全性和完整性。

理由1：通過(guò)存儲(chǔ)過(guò)程可以使沒(méi)有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫(kù)，從而保證數(shù)據(jù)的安全。

理由2：通過(guò)存儲(chǔ)過(guò)程可以使相關(guān)的動(dòng)作在一起發(fā)生，從而可以維護(hù)數(shù)據(jù)庫(kù)的完整性。

在運(yùn)行存儲(chǔ)過(guò)程前，數(shù)據(jù)庫(kù)已對(duì)其進(jìn)行了語(yǔ)法和句法分析，存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。并給出了優(yōu)化執(zhí)行方案。

這種已經(jīng)編譯好的過(guò)程可極大地改善SQL語(yǔ)句的性能。由于執(zhí)行SQL語(yǔ)句的大部分工作已經(jīng)完成，所以存儲(chǔ)過(guò)程能以極快的速度執(zhí)行。

客戶端調(diào)用存儲(chǔ)過(guò)程只需要傳存儲(chǔ)過(guò)程名和相關(guān)參數(shù)即可，與傳輸SQL語(yǔ)句相比自然數(shù)據(jù)量少了很多。

在存儲(chǔ)過(guò)程中，可把體現(xiàn)企業(yè)規(guī)則的運(yùn)算過(guò)程或接口程序放入數(shù)據(jù)庫(kù)服務(wù)器中，以便集中控制或當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)在服務(wù)器中改變存儲(chǔ)過(guò)程即可，無(wú)須修改任何應(yīng)用程序。

企業(yè)規(guī)則的特點(diǎn)是要經(jīng)常變化，如果把體現(xiàn)企業(yè)規(guī)則的運(yùn)算程序放入應(yīng)用程序中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，就需要修改應(yīng)用程序工作量非常之大（修改、發(fā)行和安裝應(yīng)用程序）。

如果把體現(xiàn)企業(yè)規(guī)則的運(yùn)算放入存儲(chǔ)過(guò)程中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，只要修改存儲(chǔ)過(guò)程就可以了，應(yīng)用程序無(wú)須任何變化。

另外存儲(chǔ)過(guò)程可以重復(fù)使用，可減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。同時(shí)安全性高，可設(shè)定只有某些用戶才具有對(duì)指定存儲(chǔ)過(guò)程的執(zhí)行使用權(quán)。

3 數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程編程方法

存儲(chǔ)過(guò)程是過(guò)程化SQL語(yǔ)句，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，因此稱它為存儲(chǔ)過(guò)程，使用時(shí)只需要在數(shù)據(jù)庫(kù)環(huán)境或宿主語(yǔ)言中調(diào)用即可。

存儲(chǔ)過(guò)程（Stored Procedure）是一組為了完成特定功能的SQL語(yǔ)句集。允許用戶聲明變量，存儲(chǔ)過(guò)程是由流控制和SQL語(yǔ)句書寫的過(guò)程，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中。這樣每次調(diào)用可節(jié)省調(diào)用者發(fā)送SQL請(qǐng)求的開(kāi)銷，存儲(chǔ)過(guò)程可由應(yīng)用程序通過(guò)調(diào)用來(lái)執(zhí)行，也可在數(shù)據(jù)庫(kù)中創(chuàng)建、調(diào)試或運(yùn)行。在創(chuàng)建存儲(chǔ)過(guò)程中，也可以使用游標(biāo)來(lái)處理多行記錄的操作，這樣存儲(chǔ)過(guò)程既有SQL的非過(guò)程化特性，也有CURSOR游標(biāo)的指針靈活性，面向記錄的過(guò)程化特性，所以在數(shù)據(jù)庫(kù)編程中，存儲(chǔ)過(guò)程是非常重要的一個(gè)方法。

存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。

同時(shí)，存儲(chǔ)過(guò)程可以接收和輸出參數(shù)、返回執(zhí)行存儲(chǔ)過(guò)程的狀態(tài)值，也可以嵌套調(diào)用。

4利用myql數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程方法生成汽車車牌記錄數(shù)據(jù)信息

基本的SQL是高度非過(guò)程化的語(yǔ)言。嵌入式SQL將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言，借助高級(jí)語(yǔ)言的控制功能實(shí)現(xiàn)過(guò)程化。過(guò)程化SQL是對(duì)SQL的擴(kuò)展，使其增加了過(guò)程化語(yǔ)句功能。

過(guò)程化SQL程序的基本結(jié)構(gòu)是塊（Block）。所有的過(guò)程化SQL程序都是由塊組成的。這些塊之間可以相互嵌套，每一個(gè)塊完成一個(gè)邏輯操作。

一個(gè)完整的TSQL語(yǔ)句塊由以下3大部分組成：

DECALRE 定義部分 （該聲明定義部分是可選的）。

BEGIN

執(zhí)行處理部分，一般包含流程控制，數(shù)據(jù)查詢，DML，事務(wù)處理，游標(biāo)處理等數(shù)據(jù)庫(kù)操作的功能。此部分是必須的。

END

聲明定義部分主要是變量和常量的定義：

1）變量定義

declare變量名 數(shù)據(jù)類型

2）賦值語(yǔ)句

Set 變量名=常量或表達(dá)式

Mysql TSQL程序控制語(yǔ)句

TSQL為了能夠更好地處理面向記錄的數(shù)據(jù)庫(kù)數(shù)據(jù)，必須有功能強(qiáng)大的流程控制語(yǔ)句。

過(guò)程化SQL提供了流程控制語(yǔ)句，主要有條件控制語(yǔ)句和循環(huán)控制語(yǔ)句。這些語(yǔ)句的語(yǔ)法、語(yǔ)義和一般的高級(jí)語(yǔ)言（如C語(yǔ)言）相似，這里只做概要的介紹。

1）條件控制語(yǔ)句

一般有三種形式的IF語(yǔ)句：IF-THEN語(yǔ)句、IF-THEN-ELSE語(yǔ)句和嵌套的IF語(yǔ)句。

（1）IF語(yǔ)句

IF condition THEN

Sequence_of_statements； /*條件為真是語(yǔ)句序列才被執(zhí)行*/

END IF /* 條件為假或NULL時(shí)什么也不做，控制轉(zhuǎn)移至下一個(gè)語(yǔ)句 */

2） IF-THEN 語(yǔ)句

IF condition THEN

Sequence_of_statementsl； /*條件為真時(shí)執(zhí)行語(yǔ)句序列1*/

ELSE

Sequence_of_statements2； /*條件為假或NULL時(shí)執(zhí)行語(yǔ)句序列2*/

END IF；

3） 嵌套的IF語(yǔ)句

在THEN和ELSE子句中還可以再包含IF語(yǔ)句，即嵌套IF語(yǔ)句。

2）循環(huán)控制語(yǔ)句

過(guò)程化SQL有三種循環(huán)結(jié)構(gòu)：LOOP，WHILE-DO和FOR-LOOP。

（1）WHILE-DO循環(huán)語(yǔ)句

WHILE condition DO

Sequence_of_statements；/*條件為真時(shí)執(zhí)行循環(huán)體內(nèi)的語(yǔ)句序列*/

END WHILE；

每次執(zhí)行循環(huán)語(yǔ)句之前首先要對(duì)條件進(jìn)行求值，如果條件為真則執(zhí)行循環(huán)體的語(yǔ)句序列，如果條件為假則跳過(guò)循環(huán)并把控制傳遞給下一個(gè)語(yǔ)句。

（2）LOOP循環(huán)語(yǔ)句

該循環(huán)沒(méi)有內(nèi)置循環(huán)條件，但可以通過(guò)leave 語(yǔ)句退出循環(huán)。表示形式如下：

LOOP_label：LOOP

Sequence_of_statements；

If condition then leave LOOP_label；

End if；

END LOOP；

（3）repeat循環(huán)語(yǔ)句

該語(yǔ)句執(zhí)行一次循環(huán)體，之后判斷condition條件是否為真，為真則退出循環(huán)，否則繼續(xù)執(zhí)行循環(huán)體。repeat語(yǔ)句的表示形式如下。

REPEAT

Sequence_of_statements；

UNTIL condition

END REPEAT；

5具體存儲(chǔ)過(guò)程的設(shè)計(jì)步驟

存儲(chǔ)過(guò)程有以下特性：有輸入輸出參數(shù)；可以聲明變量；有if/else， case，while等控制語(yǔ)句。通過(guò)編寫存儲(chǔ)過(guò)程，可以實(shí)現(xiàn)復(fù)雜的邏輯功能。函數(shù)的普遍特性：模塊化，封裝，代碼復(fù)用；速度快，只有首次執(zhí)行需經(jīng)過(guò)編譯和優(yōu)化步驟，后續(xù)被調(diào)用可以直接執(zhí)行，省去以上步驟。

步驟1：創(chuàng)建存儲(chǔ)過(guò)程，隨機(jī)生成車牌號(hào)

先創(chuàng)建個(gè)表（table），rand_Cid：用來(lái)存儲(chǔ)生成的序號(hào)及車牌號(hào)

CREATE TABLE rand_Cid

（

id INT AUTO_INCREMENT PRIMARY KEY，

cid CHAR（7）

）DEFAULT CHARACTER SET utf8；

步驟2：創(chuàng)建存儲(chǔ)過(guò)程ranCid2（num int），其中num參數(shù)是生成的記錄行數(shù)。 以下該過(guò)程實(shí)現(xiàn)隨機(jī)生成一個(gè)車牌號(hào)信息：

CREATE PROCEDURE ranCid2（num int）

BEGIN

DECLARE count int；

DECLARE i INT DEFAULT 0；

DECLARE sheng VARCHAR（31）；

DECLARE shi CHAR（26）；

DECLARE last CHAR（36）；

DECLARE l_sheng CHAR（1）；

DECLARE l_shi CHAR（1）；

DECLARE last_str varchar（5）；

DECLARE r_str varchar（7）；

SET shi = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'；

SET sheng= '京津冀晉蒙遼吉黑滬蘇浙皖閩贛魯豫鄂湘粵桂瓊川貴云渝藏陜甘青寧新'；

SET last = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'；

SET count =1；

SET i =1；

SET last_str = ''；

SET l_sheng = SUBSTRING（sheng，F(xiàn)LOOR（1+RAND（）*31），1）；

SET l_shi = SUBSTRING（shi，F(xiàn)LOOR（1+RAND（）*26），1）；

WHILE i < 5 DO

Set last_str=

CONCAT（last_str，SUBSTRING（last，F(xiàn)LOOR（1+RAND（）*36），1））；

SET i=i+1；

END WHILE；

SET r_str = CONCAT（l_sheng，l_shi，'-'，last_str）；

INSERT INTO rand_Cid（cid） VALUES（r_str）；

SET count =count+1；

END $

步驟3：創(chuàng)建存儲(chǔ)過(guò)程，調(diào)用ranCid2生成指定的num行記錄數(shù)據(jù)

CREATE PROCEDURE largeCid（IN num INT）

BEGIN

DECLARE i INT DEFAULT 0；

REPEAT

CALL ranCid2（5）；

SET i = i + 1；

UNTIL i>= num END REPEAT；

SELECT * FROM rand_Cid；

END $

在數(shù)據(jù)庫(kù)mysql提示符下輸入：mysql> call largeCid（10）； 運(yùn)行結(jié)果如下：

生成10行模擬表中數(shù)據(jù)，如果要生成100000行記錄，只要修改參數(shù)num，即調(diào)用存儲(chǔ)過(guò)程：call largeCid（100000），就可在表rand_Cid中生成100000記錄數(shù)據(jù)。

6 結(jié)論

通過(guò)利用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的方法，設(shè)計(jì)一個(gè)可生成輸入?yún)?shù)對(duì)應(yīng)的數(shù)據(jù)記錄，該數(shù)據(jù)記錄可以是5萬(wàn)行數(shù)據(jù)或100萬(wàn)行數(shù)據(jù)或更多，都可以輕松指定產(chǎn)生，使軟件的開(kāi)發(fā)測(cè)試數(shù)據(jù)和實(shí)際數(shù)據(jù)有相同的規(guī)模和真實(shí)的體驗(yàn)，使軟件開(kāi)發(fā)測(cè)試周期縮短，獲得了非常好的測(cè)試或調(diào)試預(yù)期效果，為進(jìn)一步開(kāi)發(fā)出實(shí)用的數(shù)據(jù)庫(kù)應(yīng)用程序提供模擬的數(shù)據(jù)源。

參考文獻(xiàn)：

[1] 俞海. 數(shù)據(jù)庫(kù)基本原理及應(yīng)用開(kāi)發(fā)教程. 南京：南京大學(xué)出版社， 2015.