小达摩笔记，记录生活点滴，总有一些美好的事物值得回忆。网站首页数据库

Mysql存储过程中事务相关问题

发布时间：2017-06-07编辑：xiaodamo阅读：(796)字号： 大 中 小

DROP PROCEDURE IF EXISTS UP_QueryNoticeInfo;
DELIMITER &&
CREATE PROCEDURE UP_QueryNoticeInfo
(
    IN t_UserId   VARCHAR(20)  CHARACTER SET utf8---设置带有中文的字段为utf8,否则遇到中文时会出错     
)

BEGIN
 
 DECLARE t_Provinces             VARCHAR(20) CHARACTER SET utf8;
 DECLARE t_City                  VARCHAR(20) CHARACTER SET utf8;
 DECLARE t_fk_PlanID             INT;     
 DECLARE t_Num                   INT;     
 DECLARE t_TypeNum               INT;             
 DECLARE t_StartTime             DATETIME;        
 DECLARE t_EndTime               DATETIME;        
 DECLARE t_LotteryType           CHAR(1) CHARACTER SET utf8;         
 DECLARE t_error INT DEFAULT 0;------一定要在前面变量的后面定义
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
 
  CREATE TEMPORARY TABLE IF NOT EXISTS Table_TempTable (
      pk_Notice INT(10),
      Publisher VARCHAR(20),
      PublisheTime DATETIME,
      EntryTime DATETIME,
      Provinces VARCHAR(20),
      Title VARCHAR(100),
      Content VARCHAR(1000),
      StartTime DATETIME,
      StopTime DATETIME,
      State VARCHAR(10),
      City VARCHAR(20),
      LotteryType VARCHAR(1)
     )ENGINE=INNODB DEFAULT CHARSET=utf8;
  TRUNCATE TABLE Table_TempTable;  
 
 START TRANSACTION;----开启事务
  SELECT Provinces,City INTO t_Provinces,t_City FROM vipinfo WHERE UserId=t_UserId LIMIT 1;

  SELECT COUNT(1) INTO t_Num FROM vipplan WHERE UserId=t_UserId AND StartTime<NOW() AND EndTime > NOW();

  WHILE t_Num>0 DO
   SELECT a.fk_PlanID,a.StartTime,a.EndTime INTO t_fk_PlanID,t_StartTime,t_EndTime
      FROM (
      SELECT @x:=IFNULL(@x,0)+1 AS RowNum,fk_PlanID,StartTime,EndTime FROM vipplan
   WHERE UserId=t_UserId AND StartTime<NOW() AND EndTime > NOW() ORDER BY pk_VIPPlanID
      ) AS a
     WHERE a.RowNum=t_Num  LIMIT 1;
    
   SELECT COUNT(1) INTO t_TypeNum
     FROM pricetype
    WHERE fk_PlanID=t_fk_PlanID;
   
   WHILE t_TypeNum>0 DO
    SELECT a.LotteryType INTO t_LotteryType
      FROM (
      SELECT @x:=IFNULL(@x,0)+1 AS RowNum,LotteryType FROM pricetype
      WHERE fk_PlanID=t_fk_PlanID ORDER BY pk_PriceTypeId
      ) AS a
     WHERE a.RowNum=t_TypeNum;
    
     INSERT INTO Table_TempTable
     SELECT pk_Notice, Publisher, PublisheTime, EntryTime, Provinces,
            Title, Content, StartTime, StopTime, State, City, LotteryType
       FROM notice
      WHERE Provinces=t_Provinces AND City=t_City AND
            LotteryType=t_LotteryType AND StartTime>=t_StartTime AND
            StopTime <= t_EndTime AND State='审批通过';
    
    
     SET t_TypeNum = t_TypeNum - 1;
   END WHILE;
   
   
   SET t_Num = t_Num - 1;
   
  END WHILE;  
        
  SELECT pk_Notice, Publisher, PublisheTime, EntryTime, a.Provinces,
      Title, Content, StartTime, StopTime, State, a.City, LotteryType, b.ProvinceName,c.CityName
    FROM Table_TempTable AS a
    LEFT JOIN province AS b ON a.Provinces = b.ProvinceCode
    LEFT JOIN city AS c ON b.ProvinceCode = c.ProvinceCode AND a.City = c.CityCode;
  
  
  
  IF t_error = 1 THEN   ---判断sql是否有问题，有问题回滚，无问题提交
   ROLLBACK;  
  ELSE  
   COMMIT;
  END IF;
 END &&