mysql 使用临时表以及游标返回结果集

mysql里面不可以返回游标,很让人蛋疼,没oracle好,而且,mysql没有什么好的调试工具,也比较纠结.

下面是我写的一个mysql存储过程,里面使用到了游标,临时表,最终返回的是结果接,希望对大家有帮助:

DELIMITER $$

USE `realwar`$$

DROP PROCEDURE IF EXISTS `queryRemainsPoints`$$

CREATE DEFINER=`root`@`%` PROCEDURE `queryRemainsPoints`(
    /*in startSize int,
    in pageSize int,startSize,分页的起始位置,pageSize 分页的大小,
    in queryUserName varchar(100),/*queryUserName根据用户名字来查找,
    in remainP int,
    in remainP1 INT,/*remainP 用户剩余军饷小,remainP1剩余军饷大
    in sumP int,
    IN sumP1 int, payP 用户充值总额小,payP 总金额大
    in rats int ,
    in rats1 int滞留率查询*/
    )
BEGIN
 DECLARE userNames VARCHAR(50);
 DECLARE nickName VARCHAR(50);
 DECLARE sumPoints INT;
 DECLARE remainPoint INT;
 DECLARE dayPay INT DEFAULT 0;
 DECLARE dayCost INT;
 DECLARE sumCost INT;
 DECLARE dayRemainRats VARCHAR(50);
 DECLARE sumRemainRats VARCHAR(50);
 DECLARE    over INT DEFAULT 0;
 
 /*定义一个游标,获得所有的用户*/
 /*if(queryUserName is not null){
  DECLARE usernameCur FOR SELECT rp.username AS userName FROM realwar_tool.play_money_pay_log rp where rp.username=queryUserName GROUP BY (rp.username);
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET userName = NULL;
 }else{
  DECLARE usernameCur CURSOR FOR SELECT rp.username AS userName FROM realwar_tool.play_money_pay_log rp GROUP BY (rp.username);
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET userName = NULL;*/
 
 DECLARE usernameCur CURSOR FOR SELECT rp.username  FROM realwar_tool.play_money_pay_log rp GROUP BY (rp.username);
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET userNames = NULL;
 
 /*创建一个临时表,用来存放数据*/
 DROP  TABLE IF EXISTS realwar.temp_remainsPoints;
 CREATE TEMPORARY TABLE realwar.temp_remainsPoints(
     sid INT(10) PRIMARY KEY AUTO_INCREMENT,
     userName1 VARCHAR(100),
     nickName1 VARCHAR(100),
     sumPoints1 INT(10),
     remainPoints1 INT(10),
     dayPay1  INT(10),
     dayCost1 INT(10),
     dayRemainRats1 VARCHAR(10),
     sumRemainRats1 VARCHAR(10)
 );
 OPEN usernameCur;/*打开游标*/
 /*usernameCur:loop */
  FETCH usernameCur INTO userNames;/*循环游标,给userName赋值*/
  WHILE(userNames IS NOT NULL) DO
    /*玩家昵称*/
    SELECT (rp.nickName) INTO nickName  FROM realwar.playbaseinfo rp WHERE rp.UserName = userNames;
    /*今日充值军饷*/
    SELECT SUM(r1.change_money)*10 INTO dayPay FROM realwar_tool.play_money_pay_log r1 WHERE r1.username=userNames AND YEAR(FROM_UNIXTIME(r1.dateline)) = YEAR(SYSDATE())
    AND MONTH(FROM_UNIXTIME(r1.dateline)) = MONTH(SYSDATE()) AND DATE(FROM_UNIXTIME(r1.dateline)) = DATE(SYSDATE());
    IF(dayPay  IS NULL) THEN
     SELECT 0 INTO dayPay FROM DUAL;
    END IF;
       
    /*用户总充值军饷或消费券*/
    SELECT SUM(r1.change_money*10) INTO sumPoints FROM realwar_tool.play_money_pay_log  r1 WHERE r1.username = userNames;
    IF (sumPoints IS NULL) THEN
     SELECT 0 INTO sumPoints FROM DUAL;
    END IF;
    
    /*今日花费军饷或消费券*/
    SELECT SUM(t1.price) INTO dayCost  FROM realwar.t_consumption t1 WHERE t1.userName = userNames  AND YEAR(STR_TO_DATE(t1.consumDate,'%Y-%m-%d %H:%i:%s')) = YEAR(SYSDATE())
    AND MONTH(STR_TO_DATE(t1.consumDate,'%Y-%m-%d %H:%i:%s')) = MONTH(SYSDATE()) AND DATE(STR_TO_DATE(t1.consumDate,'%Y-%m-%d %H:%i:%s')) = DATE(SYSDATE());
    IF (dayCost IS NULL) THEN
     SELECT 0 INTO dayCost FROM DUAL;
    END IF;
    
    
    /*用户总花费军饷或消费券*/
    SELECT SUM(t2.price) INTO sumCost FROM realwar.t_consumption t2 WHERE t2.userName = userNames;
    IF (sumCost IS NULL) THEN
     SELECT 0 INTO sumCost FROM DUAL;
    END IF;
    
    /*今日滞留*/
    IF(dayPay!=0) THEN
     SELECT FLOOR((dayPay-dayCost)/dayPay*100) INTO dayRemainRats FROM DUAL;
    ELSE
     SELECT 0 INTO dayRemainRats FROM DUAL;
    END IF;
    
    /*总滞留*/
    
    IF(sumPoints!=0)THEN
     SELECT FLOOR((sumPoints-sumCost)/sumPoints*100) INTO sumRemainRats FROM DUAL;
    ELSE
     SELECT 0 INTO sumRemainRats FROM DUAL;
    END IF;
    
    /*将得到的值,循环的插入到临时表中*/
    INSERT INTO temp_remainsPoints SET userName1=userNames,nickName1= nickName,sumPoints1= sumPoints,
    remainPoints1= (sumPoints-sumCost),dayPay1 = dayPay,dayCost1= dayCost,dayRemainRats1 = dayRemainRats,sumRemainRats1= sumRemainRats;
    FETCH usernameCur INTO userNames;/*循环游标,给userName赋值*/
 
 END WHILE;
 /*end loop;*/
 CLOSE usernameCur;
 
 /*返回的结果集*/
 SELECT userName1,nickName1,sumPoints1,remainPoints1,dayPay1,dayCost1,dayRemainRats1,sumRemainRats1
 FROM temp_remainsPoints;
    END$$

DELIMITER ;

评论

1 楼 lobtao 2014-05-05  

返回游标，游标定位的是谁，返回谁就好了，为什么返回游标。