MySQL数据库下存储过程的综合运用研究

摘  要：在数据库设计过程中，仅用存储过程一种对象很难解决复杂问题。文章提出将游标、定义处理程序和流程控制语句3项内容融合应用于存储过程中以解决复杂问题；存储过程是数据库中的重要对象，同时也是这3项内容的载体，3项内容的综合运用非常关键，除了有助于缓解数据库中数据的复杂操作，还可以把数据表中大量的数据单独提取出来应用到其他代码段或文件中，从而解决数据库中的复杂问题。

关键词：存储过程；游标；处理程序

中图分类号：TP311.1  文献标识码：A  文章编号：2096-4706（2023）11-0080-04

Research on the Comprehensive Application of Stored Procedures in MySQL Database

LI Yanjie

（School of Information Engineering， Shandong Huayu University of Technology， Dezhou  253034， China）

Abstract： In the process of database design， it is difficult to solve complex problems with only one object， stored procedures. This paper proposes to integrate cursor， definition processing program and process control statement into stored procedures to solve complex problems; Stored procedures are important objects in databases and also carriers of these three contents. The comprehensive application of these three contents is crucial. In addition to helping to alleviate the complex operation of data in the database， a large amount of data in the data table can be extracted separately and applied to other code segments or files， thereby solving complex problems in the database.

Keywords： stored procedure; cursor; processing program

0  引  言

在MySQL数据库下，存储过程是非常重要的一项内容，但要发挥存储过程的重要作用，必须让存储过程结合游标、处理程序、流程控制语句对数据进行处理，这样既能发挥出游标和处理程序的优势，也能体现流程控制语句在数据库中的应用。利用数据库开发信息系统或开发网站平台时，开发人员会编写大量代码，有些功能是相似的，代码会重复编写，浪费开发人员的时间，也会增加代码的冗余，如果利用存储过程，则可以简化开发人员的工作量，并能减少数据在数据库和应用服务器之间的传输，从而有效提高数据库的处理速度，还可以提高数据库编程的灵活性。

1  相关概念

1.1  存储过程简介

存储过程是一批被编译了的语句的集合，存储在数据库的服务器端，用户仅需要通过指定存储过程名称来执行操作。存储过程具有良好的封装性，被创建之后，可在程序中被多次调用，而不必重新编写该存储过程中的SQL语句，后台管理人员可以随时对存储过程进行修改，并不会影响到调用存储过程的应用程序源代码，在存储过程中可以加入流程控制语句，类似具有了C语言程序设计的功能，可以解决数据库编程中的复杂问题。

存储过程的优点是可以处理复杂问题，并且能提高执行的性能，因为在服务器端，由于执行完1次之后，其执行过程就会存放在缓存中，后面的多次调用执行，仅需要执行缓存中的二进制代码即可，既提高了性能又节约了时间。

1.2  游标简介

游标是用来存储结果集的数据类型，用SQL语言从数据库中查询数据后，结果往往是一个含有多条记录的结果集，它放在内存的一块区域中，游标会通过循环结构，允许用户逐行地访问这些记录，按照用户自己的意愿来显示和处理每一条记录。游标不能单独使用，可以在存储过程或函数中使用。使用游标设计程序时，必须有4个步骤：声明游标、打开游标、获取数据、关闭游标。声明游标是开辟空间并存储查询结果集，此时游标在第一条记录的前面，打开游标是让游标指向查询结果集的第一条记录，获取数据是从结果集中获取单条记录，获取此条记录后，游标自动指向下一条记录。关闭游标是释放资源，无法再获取数据。

1.3  处理程序简介

处理程序用于解决数据库中的错误，由于错误在执行程序时是不确定的，当有错误出现时，需要通过处理程序解决，从而保证程序正常运行。当数据表中的记录数不确定的时候，如果用游标来获取单行数据，需要利用循环语句实现。由于记录条数不确定，导致循环次数也不确定，此时无法写出退出循环的条件语句，所以需要用事先定义好的处理程序自动处理问题。

2  综合运用设计

2.1  设计基础表和条件

存储过程在处理数据量不同的数据时是无差别的，在此设计问题时，设计的问题并不复杂，但是解决问题都需要用到存储过程、游标、处理程序和流程控制语句，通过簡单的问题简述复杂的应用。在此建立两个简单的成绩表score和score1，分别包含姓名和分数两列，代码为：

create table score（name char（10），fsh float）;

insert into score values（'zhaoli'，82），（'sunyu'， 50），（'liqiang'，95）;

create table score1（name char（10），fsh float）;--无记录

具体要求：逐行获取第一个score表中的数据，把score表中的分数大于80的记录插入到第二个表score1中，并验证代码的正确性。

2.2  分析思路

1）把表中的记录逐行取出，解决此问题需利用游标取出表中的数据，并利用变量进行存储。如果单纯用select语句查询数据，仅可以看到所有的查询结果，并且结果集并不能被存储到其他数据表中。

2）利用游标取数据的同时需要定义问题处理程序，当游标获取不到数据的时候对问题进行处理，此时定义处理程序也需要用到变量判定是否发现了问题。

3）由于是逐行获取数据，为了提高程序的可读性和简化代码，需利用流程控制语句中的循环结构，通过循环去匹配游标定位到数据表中的逐行记录。此时一定要考虑循环的次数，避免出现死循环。所有循环结束的判定条件要和定义处理程序进行结合，通过定义处理程序的变量获得退出循环的条件。

4）为了能够把获取到的数据添加到第二个表中，还需要判定存放到变量中的记录的值是否符合条件，利用条件判断语句解决问题。

5）需要把各个代码段集合成一个整体去执行，需要存储过程解决此问题。

2.3  代码实现及错误问题分析

对于没有经验的初学者而言，通常会按照以上思路直接写出以下代码：

delimiter //

create procedure cc1（）--第1行定义存储过程。

Begin--第2行和第16行是开始和结束的代码段。

declare f float default 0;

declare x char（10）;--第3行和第4行定义变量用于存放游标从数据表中取出的每一条记录的两个值。

declare t int default 0;--第5行用于给定义处理程序的变量赋值为0。

declare c cursor for select * from score;--第6行为声明游标。

declare continue handler for not found set t=1;--第7行代表定义处理程序，当不能获取数据的时候，此时设置变量t为1，从而控制循环的退出。

open c;--第8行代表打开游标。

while t<>1 do--第9-13行代表循环控制语句，通过循环取出数据并进行条件判定，符合条件的存储到score1表中。

fetch c into x，f; --第10行代表获取表中数据

if f>80 then insert into score1 values（x，f）;

end if;

end while;

close c;--第14行关闭游标

select * from score1;--第15行代表查看存入的数据，以验证代码是否正确。

end//，

通过编译可以看出，代码编译过程无语法错误，提示正确，如图1所示。

下面执行存储过程，会发现虽然编译正确，但是执行结果是错误的。根据建立数据表时输入的3条记录判断，应该有2条记录符合条件，但这里显示了3条记录，如图2所示。

通过以上执行结果发现，score1表中的最后两行是重复的，正确结果应该不重复，表中应该有两条符合条件的记录，即第1条和第2条记录。错误原因分析如下：

由于score表中有3条记录，根据定义的处理程序和循环结构，会循环4次去提取数据，每次提取完成之后，会把一条记录中的两个值赋值给变量x和f，在第4次循环取数据时，由于没有记录可以获取，此时，变量t设置为1，所以第4次并未取到值给变量，但是x和f的值是保留了第3次取数据时所赋给的值，并且符合大于80的条件，此时又把此条记录加入score2表中。又因为定义处理程序时，declare后面的关键字是continue，当处理程序发现问题后，程序会继续执行，所以score2表中出现了重复的记录。如何解决这个问题，仅需要在提取到数据的第10行代码后面加入条件即可。如图3所示。

调用修改后的存储过程cc2，可以得出正确的执行结果，代码和结果如图4所示。

2.4  优化后正确代码

针对存储过程、游标、定义处理的应用，以上代码已经是最简洁状态，无法进行优化，但在while循环结构中，有两个if条件的嵌套，使得代码的行数和可读性减弱，可以在此基础上对代码进行优化，两个if条件合并成一个if条件。结果不变，但可以提高代码的可读性，如图5所示。

在以上代码中，针对定义处理程序的语法结构，declare参数1 handler for参数2SQL语句。

参数1：exit退出当前程序，continue继续执行程序。

参数2：not found代表当fetch抓取不到数据的状态，或者游标指针走到最后一条记录后面的状态。

SQL语句：set temp=1，temp是变量，必须提前声明，其值只能是0（false）或者1（true）。在上面的代码中，用到的参数为continue，continue所代表的含义是当程序出现问题时，定义的处理程序起作用，并且代码继续执行，也可以用exit实现，此时代码不再执行，直接跳出存儲过程。除此之外，对于循环中的条件和循环而言，也可以用其他循环结构实现，提高代码的灵活性。代码为：

delimiter //

create procedure cc4（）

begin

declare f float default 0;

declare x char（10）;

declare t int default 0;

declare c cursor for select * from score  where fsh>80;--把循环结构中的if条件编辑到查询语句中，提升代码的可读性。

declare exit handler for not found set t=1;--此处用exit代替continue，当条件成立的时候，退出整个存储过程。

open c;

repeat–此处用repeat循环代替while循环。

fetch c into x，f;

insert into score1 values（x，f）;

until t=1

end repeat;

close c;

select * from score1;--此行可以去掉，因為是exit，循环截止的时候，直接结束程序运行，不会执行此行代码。

end//

3  结  论

在设计数据库的过程中，需要根据内容选择合适的数据库对象，在选择之后，还要考虑此数据库对象所需要加载的其他内容，例如仅创建一个存储过程很简单，但仅能解决简单问题，遇到复杂问题或者数据表中的数据量特别大的情况，就需要考虑知识的综合运用，运用变量、游标、循环结构、条件结构、处理程序等多项内容，进而解决复杂问题。此方案以简单数据表为例进行描述，可为基于MySQL数据库的信息系统或信息平台提供借鉴，以解决实际问题。在后续的研究中，将进一步优化案例设计，选择多个数据表的大数据量展开对比，拟通过此种方式，进一步挖掘综合运用方面的优势，提供更宝贵的经验借鉴。

参考文献：

[1] 沈黎，张本文，雷申洪.存储过程在教务管理系统中的应用研究 [J].软件，2022，43（7）：13-15.

[2] 谭凯中，秦勃，何亚文.面向过程的海洋时空数据分布式存储与并行检索 [J].中国海洋大学学报：自然科学版，2021，51（11）：94-101+134.

[3] 余艳，刘云冰，邢远秀.信息与计算科学专业数据库课程实验教学探索 [J].计算机教育，2023（1）：176-179+183.

[4] 张润，方继才.基于SQL游标的数据库应用与探析 [J].智能计算机与应用，2016，6（6）：84-86+89.

[5] 李治君，周俊杰，范延平，等.国家级国土空间基础信息平台分布式数据库设计与实现 [J].自然资源信息化，2022（5）：80-85.

作者简介：李艳杰（1978—），女，汉族，山东德州人，副教授，硕士，主要研究方向：数据挖掘技术。

收稿日期：2023-01-17

基金项目：大数据与智能信息处理研究中心建设项目