MySQL存储过程使用游标、临时表实现动态SQL查询

.3.24

用能第二周周二上午，任务需求：编写存储过程查询监测设备状态以及目标监测设备状态。（任务完成）

改存储过程包含了大量相关知识，特此记录，以便此后使用！

为什么要是用存储过程？

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可以将复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

有一点需要注意的是，一些网上盛传的所谓的存储过程要比sql语句执行更快的说法，实际上是个误解，并没有根据，包括微软内部的人也不认可这一点，所以不能作为正式的优点，希望大家能够认识到这一点。

什么是游标？

游标一般用于把通过声明SELECT得到的结果集的内容在用于其它的SQL语句中。但是游标执行会影响脚本执行速度，所以使用时请慎重。

游标的四个步骤：声明游标、打开游标、提取数据、关闭游标。

MySQL与Oracle的游标不同，Oracle可以直接使用封装好的游标关键字使用游标，MySQL只能分四步骤逐步实现游标。

MySQL中游标的使用步骤：

使用DECLARE CURSOR语句将SQL 游标与 SELECT 语句相关联。另外，DECLARE CURSOR 语句还定义游标的特性，例如游标名称以及游标是只读还是只进。（MySQL为顺序只读游标）

使用OPEN语句执行SELECT语句并填充游标。

使用FETCH INTO语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 SQL 语句可以引用那些变量来访问提取的数据值。SQL 游标不支持提取行块。

使用CLOSE语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个OPEN语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用 DECLARE 语句来重新生成游标。

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllStatus`(-- 新建一个存储过程IN `IN_SID` varchar(11), -- 大系统IDIN `IN_SSID` varchar(11),-- 小系统IDIN `IN_DeviceId` varchar(11), -- 检测仪器IDIN `IN_MPLabel` varchar(60),-- 检测目标位置标签IN `IN_DataDate` VARCHAR(8),-- 当前日期，格式为年份+月份+日，八位数：IN `IN_DataMinute` VARCHAR(4) -- 当前时间，格式为小时+分钟，四位数：1216（12点16分）)BEGIN-- @** 全局变量SET @M_IN_SID = CONCAT('%',IN_SID,'%');SET @M_IN_SSID = CONCAT('%',IN_SSID,'%');SET @M_IN_DeviceId = CONCAT('%',IN_DeviceId,'%');SET @M_IN_MPLabel = CONCAT('%',IN_MPLabel,'%');SET @M_IN_DataMinute = IN_DataMinute;SET @M_WHERE_IN_DataDate = IN_DataDate;SET @InspectStatus = '';-- D，先换算为总分钟，然后换算为96个集合中的某个顺序，查询结果示例：45-- SUBSTR字符串截取（目标字符串，截取顺序从1开始，截取几个字符）SET @DataMinute_D = SUBSTR(@M_IN_DataMinute,1,2)*60 + SUBSTR(@M_IN_DataMinute,3,2);-- MOD取余操作（目标数，余数）SET @M_num = (@DataMinute_D-MOD(@DataMinute_D,15))/15 + 1;-- M，换算为分钟能被15整除的时间，查询结果示例：0000-- LPAD在将字符串补齐，缺少部分从设置字符从左开始补充（目标字符串，长度，补齐字符）SET @DataMinute_M = LPAD((@M_IN_DataMinute - MOD(SUBSTR(@M_IN_DataMinute,3,2),15)),4,0);-- DECLARE 必须在BEGIN下面，因此我们在这里定义一个BEGINBEGIN -- 声明游标中用到的变量（DECLARE 局部变量）DECLARE lb VARCHAR(32);DECLARE J VARCHAR(10);DECLARE T VARCHAR(10);DECLARE D VARCHAR(10);DECLARE mS VARCHAR(10);-- 声明变量DONE用于退出loop循环DECLARE DONE INT DEFAULT FALSE;-- 1.声明游标 CUR CURSOR FOR，获取符合查询条件的设备信息DECLARE CUR CURSOR FORSELECT MPLabel,JMark,TMark,DMark,mStatusFROM metro_monitorpoint WHERE SID LIKE @M_IN_SIDAND SSID LIKE @M_IN_SSID AND DeviceId LIKE @M_IN_DeviceIdAND MPLabel LIKE @M_IN_MPLabelORDER BY mStatus;-- 若游标FETCH失败，则给变量DONE一个TRUEDECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;-- 创建临时表用于存储状态结果CREATE TEMPORARY TABLE if not exists tem_status (MPLabel VARCHAR(32),Device_StatusVARCHAR(5),Inspect_Status VARCHAR(32));-- 2.打开游标OPEN CUR;-- 开始循环read_loop:LOOP-- 3.提取数据FETCH CUR INTO lb,J,T,D,mS;-- 判断是否退出loop循环IF DONE THENLEAVE read_loop;END IF;-- 如果JMark为D类型，则做出如下操作IF J = 'D' THEN-- 查询状态集合,在集合96个状态中截取目标状态。若为"?"，则赋值为-1，查询结果：1,0,-1,函数REPLACE（目标字符串，目标替换字符串，被替换字符串），INTO * （将查询结果保存至全局变量，不知道为啥使用局部变量保存未成功）SET @sqlstr = CONCAT('SELECT REPLACE(substr(',J,D,',',@M_num,',1),''?'',''-1'') INTO @InspectStatus',' FROM mt_rd_',J,T,' WHERE DataDate = ',@M_WHERE_IN_DataDate);PREPARE cmd FROM @sqlstr;EXECUTE cmd;-- 将结果存入临时表INSERT INTO tem_statusVALUES(lb,mS,@InspectStatus);-- 如果JMark为M类型ELSE-- 查询结果示例：-7.16086e-16SET @sqlstr = CONCAT('SELECT ',J,D,'_',@DataMinute_M,' INTO @InspectStatus',' FROM mt_rd_',J,T,' WHERE DataDate = ',@M_WHERE_IN_DataDate);PREPARE cmd FROM @sqlstr;EXECUTE cmd;-- 将结果存入临时表INSERT INTO tem_statusVALUES(lb,mS,@InspectStatus);END IF;END LOOP;-- 返回临时表中保存的状态SELECT * FROM tem_status;-- 4.关闭游标CLOSE CUR;-- 销毁临时表DROP TABLE tem_status;END;END