【myql存储过程查询数据集，根据标识批量插入数据】

myql存储过程查询数据集，根据标识批量插入数据

建立存储过程之前需要判断该存储过程是否存在，若存在则删除
DROP PROCEDURE IF EXISTS update_album_count;
– 创建存储过程，update_album_count为存储过程名
CREATE DEFINER=root@localhost PROCEDURE update_album_count()
– 标识存储过程开始
BEGIN
– 定义变量
DECLARE s int DEFAULT 0;
DECLARE _reveid int;
DECLARE _custid int;
DECLARE _projid int;
DECLARE extcode varchar(50) default ‘addr’;
DECLARE extname varchar(50) default ‘地址’;
DECLARE extvalue varchar(50) default null;
– 定义游标，并将sql结果集赋值到游标中，report为游标名
DECLARE report CURSOR FOR SELECT DISTINCT REVEID,CUSTID,PROJID FROM temp_ext;
– 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
– 打开游标
OPEN report;
– 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO _reveid, _custid, _projid;
– 当s不等于1时，也就是未遍历完时，会一直循环
WHILE s <> 1 DO
– 执行查询sql统计结果
set @var_sql = concat(‘select @pCount := count(1) from temp_ext where REVEID=’,rtrim(ltrim(convert(_reveid,char))) ,’ and PROJID=‘,rtrim(ltrim(convert(_projid,char))),’ and CUSTID=‘,rtrim(ltrim(convert(_custid,char))),’ and EXTCODE=‘’‘,extcode,’‘’');
– SELECT @var_sql;
set @exSql = @var_sql;
prepare stmt from @exSql;
execute stmt;
deallocate prepare stmt;
– 查询为空，则添加一条记录
if @pCount=0 then
– 执行业务逻辑
INSERT INTO crm_reveext(REVEID,CUSTID,PROJID,EXTCODE,EXTNAME,EXTVALUE,CREATED_BY,CREATED_TIME) values(_reveid,_custid,_projid,extcode,extname,extvalue,‘admin’,now());
end if;
– 当s等于1时代表遍历已完成，退出循环
FETCH report INTO _reveid, _custid, _projid;
END WHILE;
– 关闭游标
CLOSE report;
– 标识存储过程结束
END;
call update_album_count();