项目驱动型人才
需求:查询未使用过的字段
菜鸟教程:存储过程
DELIMITER $$
CREATE PROCEDURE insert_avaliable_ispk()
BEGIN
-- 记录游标是否循环完
DECLARE finished int DEFAULT 0;
-- 记录游标是否循环完
DECLARE finished2 int DEFAULT 0;
-- 拼接后的字段值
DECLARE field_name_true VARCHAR(50) DEFAULT "SPARE_";
-- 拼接后的字段值
DECLARE attr_id VARCHAR(50) DEFAULT "";
-- 拼接后的字段值
DECLARE attr_sortsn int DEFAULT 1;
-- 循环计数器
DECLARE num INT DEFAULT 1;
-- 判断有没有相同的值,相同即代表被用过
DECLARE same INT DEFAULT 0;
-- 循环游标时候每一行的值
DECLARE item_field_name varchar(50);
-- 主键字段是否存在
DECLARE ispkexist INT DEFAULT 0;
-- 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select distinct field_name from c2_bdg_ds_attr;
-- 定义游标,并将sql结果集赋值到游标中
DECLARE report2 CURSOR FOR select distinct id,SORT_SN from c2_bdg_ds_attr where CATALOG_ID='xB1k7wkLSCeGy1noGJFcuw';
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
SELECT count(*) INTO ispkexist from c2_bdg_ds_attr where NAME_EN = "IS_PK";
-- 开始循环找可用的字段
loop_label: LOOP
-- 已经有了,不找了 ITERATE loop_label;相当于continue
IF ispkexist <>0 THEN
LEAVE loop_label;
END IF;
-- 到了65还没找到就无了
IF num >65 THEN
LEAVE loop_label;
END IF;
-- 拼接字段名
set field_name_true = CONCAT(field_name_true,num);
-- 打开游标
open report;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into item_field_name;
-- 当s不等于1,也就是未遍历完时,会一直循环
while finished<>1 do
-- 执行业务逻辑,找到相等的了就置为1
if item_field_name = field_name_true then
set same=1;
end if;
-- 将游标中的值再赋值给变量,供下次循环使用
fetch report into item_field_name;
-- 当s等于1时表明遍历以完成,退出循环
end while;
-- 关闭游标
close report;
-- 如果same未被置为1的话表明这个字段是可用的
if same=0 then
-- 找到结果了,提前跟loop说拜拜了
LEAVE loop_label;
end if;
-- 没找到,初始化信息准备找第二个
set finished=0;
set same=0;
set num=num+1;
set field_name_true="SPARE_";
END LOOP;
-- 找到可用字段了,可以正常插入
if field_name_true<>"SPARE_" then
set finished =0 ;
-- 打开游标,更新排序号
open report2;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report2 into attr_id,attr_sortsn;
-- 当s不等于1,也就是未遍历完时,会一直循环
while finished<>1 do
-- 执行业务逻辑,找到相等的了就置为1
UPDATE c2_bdg_ds_attr SET SORT_SN=attr_sortsn+1 WHERE ID=attr_id;
-- 将游标中的值再赋值给变量,供下次循环使用
fetch report2 into attr_id,attr_sortsn;
-- 当s等于1时表明遍历以完成,退出循环
end while;
-- 关闭游标
close report2;
INSERT INTO c2_bdg_ds_attr (ID,CATALOG_ID ,NAME_CN,NAME_EN,INPUT_WAY,ATTR_VALUES,AVAILABLE,REQUIRED,BUILT_IN,BUILT_IN_TYPE,VALUE_MAX_LENGTH,GRID_SPAN,FIELD_NAME,SORT_SN,DESCRIPTION,CREATE_BY,UPDATE_BY)
VALUES ('8229f5cc-3d3c-bc08-dffa-6f129b0df70e', 'xB1k7wkLSCeGy1noGJFcuw', '是否主键', 'IS_PK', '2', '是,否', '1', '0', '0', '3', 50, 12, field_name_true, 1, '', 'admin', 'admin');
end if;
END
$$
DELIMITER ;
CALL insert_avaliable_ispk();
DROP PROCEDURE IF EXISTS insert_avaliable_ispk;
其中DELIMITER 定好结束符为"$$“, 然后最后又定义为”;“, MYSQL的默认结束符为”;".
详细解释:
其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。
默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,
那么回车后,mysql将会执行该命令。
mysql存储过程之循环遍历查询结果集demo
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS init_reportUrl;
-- 创建存储过程
CREATE PROCEDURE init_reportUrl()
BEGIN
-- 定义变量 如果是@定义的相当于全局变量
DECLARE s int DEFAULT 0;
DECLARE report_id varchar(255);
DECLARE report_url varchar(256);
-- 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select reportId,reportUrl from patrolReportHistory;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into report_id,report_url;
-- 当s不等于1,也就是未遍历完时,会一直循环
while s<>1 do
-- 执行业务逻辑
update patrolreporthistory set reportUrl = CONCAT('patrolReport.html?monitorId=',substring(report_url,15,1),'&reportId=',report_id) where reportId=report_id;
-- 将游标中的值再赋值给变量,供下次循环使用
fetch report into report_id,report_url;
-- 当s等于1时表明遍历以完成,退出循环
end while;
-- 关闭游标
close report;
END;
-- 执行存储过程
call init_reportUrl()
mysql 存储过程复制A表数据到B表
create procedure sys_message_user_for_busbase()
begin
-- 声明一个标志done, 用来判断游标是否遍历完成
DECLARE done INT DEFAULT 0;
-- 声明一个变量,用来存放从游标中提取的数据
-- 特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLARE tid varchar(50) DEFAULT NULL;
DECLARE tname varchar(50) DEFAULT NULL;
DECLARE thead varchar(255) DEFAULT NULL;
DECLARE tmobile varchar(255) DEFAULT NULL;
-- 声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR select id,shop_name, mobile_phone,shop_logo from hx_busbase;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
open cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO tid,tname, thead,tmobile;
if not done then
-- 这里就可以使用 tname, tpass 对应的信息了
if (select count(1) from hx_message_user where uid=tid) = 0 THEN
INSERT INTO hx_message_user(id,head,mobile,username,uid,remark) VALUES(tid,thead,tmobile,tname,tid,"busbase");
end if;
#select tid,tname, tpass,(select count(1) from btable where id=tid) as count;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end