MySQL数据库下存储过程的综合运用研究_李艳杰.pdf

2023 年 6 月 10 日第 7 卷 第 11 期现代信息科技Modern Information TechnologyJun.2023 Vol.7 No.1180802023.062023.06收稿日期：2023-01-17基金项目：大数据与智能信息处理研究中心建设项目MySQL 数据库下存储过程的综合运用研究李艳杰（山东华宇工学院 信息工程学院，山东 德州 253034）摘 要：在数据库设计过程中，仅用存储过程一种对象很难解决复杂问题。文章提出将游标、定义处理程序和流程控制语句 3 项内容融合应用于存储过程中以解决复杂问题；存储过程是数据库中的重要对象，同时也是这 3 项内容的载体，3 项内容的综合运用非常关键，除了有助于缓解数据库中数据的复杂操作，还可以把数据表中大量的数据单独提取出来应用到其他代码段或文件中，从而解决数据库中的复杂问题。关键词：存储过程；游标；处理程序中图分类号：TP311.1 文献标识码：A 文章编号：2096-4706（2023）11-0080-04Research on the Comprehensive Application of Stored Procedures in MySQL DatabaseLI 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 program0 引 言在 MySQL 数据库下，存储过程是非常重要的一项内容，但要发挥存储过程的重要作用，必须让存储过程结合游标、处理程序、流程控制语句对数据进行处理，这样既能发挥出游标和处理程序的优势，也能体现流程控制语句在数据库中的应用。利用数据库开发信息系统或开发网站平台时，开发人员会编写大量代码，有些功能是相似的，代码会重复编写，浪费开发人员的时间，也会增加代码的冗余，如果利用存储过程，则可以简化开发人员的工作量，并能减少数据在数据库和应用服务器之间的传输，从而有效提高数据库的处理速度，还可以提高数据库编程的灵活性。1 相关概念1.1 存储过程简介存储过程是一批被编译了的语句的集合，存储在数据库的服务器端，用户仅需要通过指定存储过程名称来执行操作。存储过程具有良好的封装性，被创建之后，可在程序中被多次调用，而不必重新编写该存储过程中的 SQL 语句，后台管理人员可以随时对存储过程进行修改，并不会影响到调用存储过程的应用程序源代码，在存储过程中可以加入流程控制语句，类似具有了 C 语言程序设计的功能，可以解决数据DOI:10.19850/ki.2096-4706.2023.11.019库编程中的复杂问题。存储过程的优点是可以处理复杂问题，并且能提高执行的性能，因为在服务器端，由于执行完 1 次之后，其执行过程就会存放在缓存中，后面的多次调用执行，仅需要执行缓存中的二进制代码即可，既提高了性能又节约了时间。1.2 游标简介游标是用来存储结果集的数据类型，用 SQL 语言从数据库中查询数据后，结果往往是一个含有多条记录的结果集，它放在内存的一块区域中，游标会通过循环结构，允许用户逐行地访问这些记录，按照用户自己的意愿来显示和处理每一条记录。游标不能单独使用，可以在存储过程或函数中使用。使用游标设计程序时，必须有 4 个步骤：声明游标、打开游标、获取数据、关闭游标。声明游标是开辟空间并存储查询结果集，此时游标在第一条记录的前面，打开游标是让游标指向查询结果集的第一条记录，获取数据是从结果集中获取单条记录，获取此条记录后，游标自动指向下一条记录。关闭游标是释放资源，无法再获取数据。1.3 处理程序简介处理程序用于解决数据库中的错误，由于错误在执行程序时是不确定的，当有错误出现时，需要通过处理程序解决，从而保证程序正常运行。当数据表中的记录数不确定的时候，如果用游标来获取单行数据，需要利用循环语句实现。由于记录条数不确定，导致循环次数也不确定，此时无法写出退出循环的条件语句，所以需要用事先定义好的处理程序自动处理问题。81812023.062023.06第 11期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 t1 do-第 9-13 行代表循环控制语句，通过循环取出数据并进行条件判定，符合条件的存储到 score1 表中。fetch c into x,f;-第 10 行代表获取表中数据if f80 then insert into score1 values(x,f);end if;end while;close c;-第 14 行关闭游标select*from score1;-第 15 行代表查看存入的数据，以验证代码是否正确。end/，通过编译可以看出，代码编译过程无语法错误，提示正确，如图 1 所示。图 1 错误代码下面执行存储过程，会发现虽然编译正确，但是执行结果是错误的。根据建立数据表时输入的 3 条记录判断，应该有 2 条记录符合条件，但这里显示了 3 条记录，如图 2 所示。图 2 错误结果通过以上执行结果发现，score1 表中的最后两行是重复的，正确结果应该不重复，表中应该有两条符合条件的记录，即第 1 条和第 2 条记录。错误原因分析如下：由于 score 表中有 3 条记录，根据定义的处理程序和循环结构，会循环 4 次去提取数据，每次提取完成之后，会把一条记录中的两个值赋值给变量 x 和 f，在第 4 次循环取数据时，由于没有记录可以获取，此时，变量 t 设置为 1，所以第 4 次并未取到值给变量，但是 x 和 f 的值是保留了第3 次取数据时所赋给的值，并且符合大于 80 的条件，此时又把此条记录加入 score2 表中。又因为定义处理程序时，declare 后面的关键字是 continue，当处理程序发现问题后，程序会继续执行，所以 score2 表中出现了重复的记录。如何李艳杰：MySQL 数据库下存储过程的综合运用研究82822023.062023.06第 11期现代信息科技解决这个问题，仅需要在提取到数据的第 10 行代码后面加入条件即可。如图 3 所示。图 3 正确代码调用修改后的存储过程 cc2，可以得出正确的执行结果，代码和结果如图 4 所示。图 4 正确结果2.4 优化后正确代码针对存储过程、游标、定义处理的应用，以上代码已经是最简洁状态，无法进行优化，但在 while 循环结构中，有两个 if 条件的嵌套，使得代码的行数和可读性减弱，可以在此基础上对代码进行优化，两个 if 条件合并成一个 if 条件。结果不变，但可以提高代码的可读性，如图 5 所示。图 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 实现，此时代码不再执行，直接跳出存储过程。除此之外，对于循环中的条件和循环而言，也可以用其他循环结构实现，提高