drop procedure if exists add_test;
DELIMITER $$CREATE PROCEDURE add_test()
BEGIN
#定义 变量
DECLARE varRankType VARCHAR(30);
DECLARE varRankPeroid char(2);
DECLARE varAgentId int;
DECLARE varAgentName varchar(10);
DECLARE varAmt double;
DECLARE varUpdateTime TIMESTAMP;
DECLARE varRank int DEFAULT 0;
#这个用于处理游标到达最后一行的情况
declare done int default 0;
#声明游标cursor_1,cursor_2,cursor_3,cursor_4,cursor_5
DECLARE cursor_1 CURSOR FOR SELECT 'COMM' AS rankType,'1M' AS rankPeroid, agentId, agent.name AS agentName, SUM(amount) AS amt FROM bonus JOIN agent ON bonus.agentId = agent.userId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_2 CURSOR FOR SELECT 'AGNT' AS rankType,'1M' AS rankPeroid, a.userId as agentId, a.name AS agentName, count(a.userId) AS amt FROM agent as a JOIN agent as b ON a.userId = b.rcmmByUserId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_3 CURSOR FOR SELECT 'DNUM' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, count(d.productId) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId JOIN deal as d ON a.clientId=d.clientId and a.rcmmBuildingId=d.buildingId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_4 CURSOR FOR SELECT 'DAMT' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, sum(d.totalPrice) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId JOIN deal as d ON a.clientId=d.clientId and a.rcmmBuildingId=d.buildingId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_5 CURSOR FOR SELECT 'CLNT' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, count(distinct a.clientId) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
#设置一个终止标记
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set varUpdateTime=now();
truncate table rank;
#打开游标
OPEN cursor_1; while varRank <= 1 do
#读取下一行的数据
fetch cursor_1 into varRankType,varRankPeroid,varAgentId,varAgentName,varAmt;
set varRank=varRank+1;
INSERT INTO rank(rankType,rankPeroid,agentId,agentName,amt,rank,updateTime) values (varRankType,varRankPeroid,varAgentId,varAgentName,varAmt,varRank,varUpdateTime);
END while;
#关闭游标
CLOSE cursor_1;
set done=0;
#打开游标
OPEN cursor_2;
while varRank <= 1 do
#读取下一行的数据
fetch cursor_2 into varRankType,varRankPeroid,varAgentId,varAgentName,varAmt;
set varRank=varRank+1;
INSERT INTO rank(rankType,rankPeroid,agentId,agentName,amt,rank,updateTime) values (varRankType,varRankPeroid,varAgentId,varAgentName,varAmt,varRank,varUpdateTime);
END while;
#关闭游标 CLOSE cursor_2; END;
#调用存储函数add_test CALL add_test() 代码如上,我想写的存储过程,能够将查询出的结果插入rank表中,代码中只写了2个游标,请大神提示下应该怎么改,现在只能插入rankType为COMM的数据,如何插入其他的?
DELIMITER $$CREATE PROCEDURE add_test()
BEGIN
#定义 变量
DECLARE varRankType VARCHAR(30);
DECLARE varRankPeroid char(2);
DECLARE varAgentId int;
DECLARE varAgentName varchar(10);
DECLARE varAmt double;
DECLARE varUpdateTime TIMESTAMP;
DECLARE varRank int DEFAULT 0;
#这个用于处理游标到达最后一行的情况
declare done int default 0;
#声明游标cursor_1,cursor_2,cursor_3,cursor_4,cursor_5
DECLARE cursor_1 CURSOR FOR SELECT 'COMM' AS rankType,'1M' AS rankPeroid, agentId, agent.name AS agentName, SUM(amount) AS amt FROM bonus JOIN agent ON bonus.agentId = agent.userId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_2 CURSOR FOR SELECT 'AGNT' AS rankType,'1M' AS rankPeroid, a.userId as agentId, a.name AS agentName, count(a.userId) AS amt FROM agent as a JOIN agent as b ON a.userId = b.rcmmByUserId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_3 CURSOR FOR SELECT 'DNUM' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, count(d.productId) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId JOIN deal as d ON a.clientId=d.clientId and a.rcmmBuildingId=d.buildingId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_4 CURSOR FOR SELECT 'DAMT' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, sum(d.totalPrice) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId JOIN deal as d ON a.clientId=d.clientId and a.rcmmBuildingId=d.buildingId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
DECLARE cursor_5 CURSOR FOR SELECT 'CLNT' AS rankType,'1M' AS rankPeroid, agentId, b.name AS agentName, count(distinct a.clientId) AS amt FROM clientrcmm as a JOIN agent as b ON a.agentId = b.userId GROUP BY agentId ORDER BY amt DESC LIMIT 100;
#设置一个终止标记
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set varUpdateTime=now();
truncate table rank;
#打开游标
OPEN cursor_1; while varRank <= 1 do
#读取下一行的数据
fetch cursor_1 into varRankType,varRankPeroid,varAgentId,varAgentName,varAmt;
set varRank=varRank+1;
INSERT INTO rank(rankType,rankPeroid,agentId,agentName,amt,rank,updateTime) values (varRankType,varRankPeroid,varAgentId,varAgentName,varAmt,varRank,varUpdateTime);
END while;
#关闭游标
CLOSE cursor_1;
set done=0;
#打开游标
OPEN cursor_2;
while varRank <= 1 do
#读取下一行的数据
fetch cursor_2 into varRankType,varRankPeroid,varAgentId,varAgentName,varAmt;
set varRank=varRank+1;
INSERT INTO rank(rankType,rankPeroid,agentId,agentName,amt,rank,updateTime) values (varRankType,varRankPeroid,varAgentId,varAgentName,varAmt,varRank,varUpdateTime);
END while;
#关闭游标 CLOSE cursor_2; END;
#调用存储函数add_test CALL add_test() 代码如上,我想写的存储过程,能够将查询出的结果插入rank表中,代码中只写了2个游标,请大神提示下应该怎么改,现在只能插入rankType为COMM的数据,如何插入其他的?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货