mysql 存储过程示例,有参有返回值,使用到了判断,循环,游标,临时表,事务

CREATE PROCEDURE `NewProc`(IN beforeStatus VARCHAR(31),IN afterStatus VARCHAR(31),IN retryTime INT,IN xlimit INT)
BEGIN
/*定义变量,不可与列名重名*/
DECLARE xid INT;
/*影响的行数*/
DECLARE cnt INT DEFAULT 0;
  /* 针对NOT FOUND的条件*/
DECLARE done INT DEFAULT FALSE;
/* 声明游标 */
DECLARE rs CURSOR FOR 
select t.id from ins_transfertask_collect t
where t.collectStatus = beforeStatus  and t.retryTime < retryTime and t.planExecTime < NOW()
    LIMIT xlimit;
/* NOT FOUND 标志*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/* 创建个临时表 */
drop table if exists tmpidtable;
CREATE TEMPORARY TABLE tmpidtable (  
Id int(11) NOT NULL  
);
/*打开事务*/
START TRANSACTION;  
/* 打开游标 */
OPEN rs;
/*开始循环*/
read_loop: LOOP
/* 抓取游标中的每个值 */
FETCH rs INTO xid;
  /* 结束的时候 */
    IF done THEN
      LEAVE read_loop;
    END IF;
/* 具体业务*/
UPDATE ins_transfertask_collect t set t.collectStatus=afterStatus,t.updateTime=NOW()
where t.collectStatus=beforeStatus and t.id=xid;
set cnt = ROW_COUNT(); /*返回被前面语句更新的、插入的或删除的行数*/
/*更新成功则插入临时表*/
if cnt>0 then 
INSERT INTO tmpidtable(id) VALUES(xid);
end if;
/*结束循环*/
END LOOP;
/* 关闭游标 */
CLOSE rs; 
/*提交事务*/
commit;
/* 返回临时表中的值 */
SELECT * FROM tmpidtable;  
/* 删掉临时表 */
  DROP TABLE  tmpidtable;  

END;

//不使用游标的例子

CREATE PROCEDURE `NewProc`(IN beforeStatus VARCHAR(31),IN afterStatus VARCHAR(31),IN retryTime INT,IN xlimit INT)
BEGIN
/*定义变量,不可与列名重名*/
DECLARE xid BIGINT;
/*影响的行数*/
DECLARE cnt INT DEFAULT 0;
/*定义循环次数*/
DECLARE steps INT DEFAULT 500;
/* 创建个临时表 */
CREATE TEMPORARY TABLE if not exists tmpidtable (  Id BIGINT NOT NULL  ) ;
/*清空临时表*/
truncate TABLE tmpidtable;
if xlimit >0 THEN
set steps=xlimit;
end IF;
/*开始循环*/
read_loop: LOOP


  /* 结束的时候 */
IF steps < 1 THEN
LEAVE read_loop;
END IF;
/* 具体业务*/
select t.id INTO xid from ins_transfertask_collect t
where t.collectStatus = beforeStatus  and t.retryTime < retryTime and t.planExecTime < NOW()
LIMIT 1;
UPDATE ins_transfertask_collect t set t.collectStatus=afterStatus,t.updateTime=NOW()
where t.collectStatus=beforeStatus and t.id=xid;
set cnt = ROW_COUNT(); /*返回被前面语句更新的、插入的或删除的行数*/
/*更新成功则插入临时表*/
if cnt>0 then 
INSERT INTO tmpidtable(id) VALUES(xid);
end if;
set steps=steps-1;
/*结束循环*/
END LOOP;


/* 返回临时表中的值 */
SELECT * FROM tmpidtable;  
/*清空临时表*/
truncate TABLE tmpidtable;  
END;