利用Mysql存储过程方法产生模拟表数据

摘要：利用数据库存储过程产生软件开发测试表中的模拟数据是一个非常有效的方法，而标准SQL是非过程化的语言，和程序设计语言相比，高度非过程化的优点也造成其一个弱点：缺少流程控制能力，难以实现应用业务中的逻辑控制，而在各行业应用中存在很多很复杂的业务逻辑，简单的标准SQL语言不能承担该角色，解决的办法就是要采用面向过程的如PL/SQL、T-SQL语言即存储过程方法。该论文采用MySQL 存储过程来模拟车辆通行信息，采用参数的方法，自动生成该参数指定行数的表记录，供调试程序或统计的模拟数据源。

关键词：数据库；存储过程；Mysql

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2018）20-0016-03

標准SQL是非过程化的查询语言，具有操作统一、面向集合、功能丰富、使用简单等优点。但和程序设计语言相比，高度非过程化的优点也造成了它的一个弱点：缺少流程控制能力，难以实现应用业务中的逻辑控制，而在各行业应用中存在很多很复杂的业务逻辑，简单的标准SQL语言不能承担该角色，这就提出可以面向过程的如PL/SQL、T-SQL数据库语言进行编程，即存储过程方式，通过if then 语句或其他DML SQL 来生成所需要的数据。这种方式仅简单，而且容易实现，容易调试，并且可以包含一些业务逻辑和事务控制，是生成模拟数据的一种高效方法。

1模拟数据生成的意义背景

程序开发离不开数据源，比如商务网站开发，表中数据是否真实，是否满足要求，对调试程序有很大的帮助测试作用，能帮助开发者快速发现程序的应用设计是否满足流程要求，数据查询和统计是否正确，以及大数据平台下的程序性能和网站的响应时间，对用户的真实体验等是非常关键的一个步骤，目前开发环境下数据库表中数据的量级常常不能满足大记录数据的要求，比如100万行记录或更高，本论文利用存储过程生成大批量数据，有助于真实数据的模拟和统计速度的统计查询，有较好的应用场景。

2数据库存储过程编程优点

存储过程的优点是明显的，使用存储过程具有以下优点：由于存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语句分析和优化工作，因而运行效率高，它提供在服务器端快速执行SQL语句的有效途径。

存储过程降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出调用存储过程的名字和参数，就可以让关系数据库管理系统执行其中的多行 SQL语句并进行数据处理。

另外也方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库管理，既有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只要修改存储过程即可。

存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

可保证数据的安全性和完整性。

理由1：通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

理由2：通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

在运行存储过程前，数据库已对其进行了语法和句法分析，存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。并给出了优化执行方案。

这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多。

在存储过程中，可把体现企业规则的运算过程或接口程序放入数据库服务器中，以便集中控制或当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。

企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。

如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。

另外存储过程可以重复使用，可减少数据库开发人员的工作量。同时安全性高，可设定只有某些用户才具有对指定存储过程的执行使用权。

3 数据库存储过程编程方法

存储过程是过程化SQL语句，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只需要在数据库环境或宿主语言中调用即可。

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。允许用户声明变量，存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。这样每次调用可节省调用者发送SQL请求的开销，存储过程可由应用程序通过调用来执行，也可在数据库中创建、调试或运行。在创建存储过程中，也可以使用游标来处理多行记录的操作，这样存储过程既有SQL的非过程化特性，也有CURSOR游标的指针灵活性，面向记录的过程化特性，所以在数据库编程中，存储过程是非常重要的一个方法。

存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

4利用myql数据库存储过程方法生成汽车车牌记录数据信息

基本的SQL是高度非过程化的语言。嵌入式SQL将SQL语句嵌入程序设计语言，借助高级语言的控制功能实现过程化。过程化SQL是对SQL的扩展，使其增加了过程化语句功能。

过程化SQL程序的基本结构是块（Block）。所有的过程化SQL程序都是由块组成的。这些块之间可以相互嵌套，每一个块完成一个逻辑操作。

一个完整的TSQL语句块由以下3大部分组成：

DECALRE 定义部分 （该声明定义部分是可选的）。

BEGIN

执行处理部分，一般包含流程控制，数据查询，DML，事务处理，游标处理等数据库操作的功能。此部分是必须的。

END

声明定义部分主要是变量和常量的定义：

1）变量定义

declare变量名 数据类型

2）赋值语句

Set 变量名=常量或表达式

Mysql TSQL程序控制语句

TSQL为了能够更好地处理面向记录的数据库数据，必须有功能强大的流程控制语句。

过程化SQL提供了流程控制语句，主要有条件控制语句和循环控制语句。这些语句的语法、语义和一般的高级语言（如C语言）相似，这里只做概要的介绍。

1）条件控制语句

一般有三种形式的IF语句：IF-THEN语句、IF-THEN-ELSE语句和嵌套的IF语句。

（1）IF语句

IF condition THEN

Sequence_of_statements； /*条件为真是语句序列才被执行*/

END IF /* 条件为假或NULL时什么也不做，控制转移至下一个语句 */

2） IF-THEN 语句

IF condition THEN

Sequence_of_statementsl； /*条件为真时执行语句序列1*/

ELSE

Sequence_of_statements2； /*条件为假或NULL时执行语句序列2*/

END IF；

3） 嵌套的IF语句

在THEN和ELSE子句中还可以再包含IF语句，即嵌套IF语句。

2）循环控制语句

过程化SQL有三种循环结构：LOOP，WHILE-DO和FOR-LOOP。

（1）WHILE-DO循环语句

WHILE condition DO

Sequence_of_statements；/*条件为真时执行循环体内的语句序列*/

END WHILE；

每次执行循环语句之前首先要对条件进行求值，如果条件为真则执行循环体的语句序列，如果条件为假则跳过循环并把控制传递给下一个语句。

（2）LOOP循环语句

该循环没有内置循环条件，但可以通过leave 语句退出循环。表示形式如下：

LOOP_label：LOOP

Sequence_of_statements；

If condition then leave LOOP_label；

End if；

END LOOP；

（3）repeat循环语句

该语句执行一次循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环体。repeat语句的表示形式如下。

REPEAT

Sequence_of_statements；

UNTIL condition

END REPEAT；

5具体存储过程的设计步骤

存储过程有以下特性：有输入输出参数；可以声明变量；有if/else， case，while等控制语句。通过编写存储过程，可以实现复杂的逻辑功能。函数的普遍特性：模块化，封装，代码复用；速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤。

步骤1：创建存储过程，随机生成车牌号

先创建个表（table），rand_Cid：用来存储生成的序号及车牌号

CREATE TABLE rand_Cid

（

id INT AUTO_INCREMENT PRIMARY KEY，

cid CHAR（7）

）DEFAULT CHARACTER SET utf8；

步骤2：创建存储过程ranCid2（num int），其中num参数是生成的记录行数。 以下该过程实现随机生成一个车牌号信息：

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，FLOOR（1+RAND（）*31），1）；

SET l_shi = SUBSTRING（shi，FLOOR（1+RAND（）*26），1）；

WHILE i < 5 DO

Set last_str=

CONCAT（last_str，SUBSTRING（last，FLOOR（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：创建存储过程，调用ranCid2生成指定的num行记录数据

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 $

在数据库mysql提示符下输入：mysql> call largeCid（10）； 运行结果如下：

生成10行模拟表中数据，如果要生成100000行记录，只要修改参数num，即调用存储过程：call largeCid（100000），就可在表rand_Cid中生成100000记录数据。

6 结论

通过利用数据库存储过程的方法，设计一个可生成输入参数对应的数据记录，该数据记录可以是5万行数据或100万行数据或更多，都可以轻松指定产生，使软件的开发测试数据和实际数据有相同的规模和真实的体验，使软件开发测试周期缩短，获得了非常好的测试或调试预期效果，为进一步开发出实用的数据库应用程序提供模拟的数据源。

参考文献：

[1] 俞海. 數据库基本原理及应用开发教程. 南京：南京大学出版社， 2015.