mysql 存储过程编写中踩的坑-----工作项目实战二

如要转载请注明：https://blog.youkuaiyun.com/qwert789p/article/details/103627980

创作不易 且行且珍惜

---

存储过程 while循环  和 游标

第一次 用while写的割接商品数据

CREATE DEFINER=`wlwapp`@`%` PROCEDURE `pro_offer_cutover`()
    SQL SECURITY INVOKER
BEGIN
  #Author:  
  #Create: 2019-12-17
  #存储过程示例
  #日志记录定义模块
  DECLARE sys_StartTime datetime;
  DECLARE sys_ErrCode varchar(5) DEFAULT '00000';
  DECLARE sys_ErrMessage varchar(200);
  DECLARE IsSuc tinyint(1);
  DECLARE v_i int unsigned DEFAULT 1;
  DECLARE c_offer_id decimal(16,0);
  DECLARE j_offer_id decimal(16,0);
  DECLARE j_offer_nbr varchar(30) ;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION	
	
  BEGIN
    GET DIAGNOSTICS CONDITION 1
    sys_ErrCode = RETURNED_SQLSTATE, sys_ErrMessage = MESSAGE_TEXT;
  END;
  SET sys_StartTime = NOW();

  #开启事务
  START TRANSACTION;

    #修改隔离级别,防止对业务表过长的共享锁占用
     SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    #处理数据
		while v_i<1000 do 				 
		-- SELECT a.j_offer_id,a.j_offer_code,a.c_offer_id from sheet1 a left join offer b on a.c_offer_id=b.offer_id where v_i=v_id;
		set c_offer_id=(SELECT a.c_offer_id from sheet1 a left join offer b on a.c_offer_id=b.offer_id where v_i=v_id);
		set j_offer_id=(SELECT a.c_offer_id from sheet1 a left join offer b on a.c_offer_id=b.offer_id where v_i=v_id);
		set j_offer_nbr=(SELECT a.j_offer_code from sheet1 a left join offer b on a.c_offer_id=b.offer_id where v_i=v_id);
		
			CALL  `proc_offer1`(c_offer_id ,j_offer_id, j_offer_nbr);	
	set v_i=v_i=1;		
 end while;
	
	
		#恢复隔离级别
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
    #事务提交/回滚模块
    IF sys_ErrCode <> '00000' THEN
      SET IsSuc = 0;
      ROLLBACK;
    ELSE
      SET IsSuc = 1;
			
    COMMIT;
  END IF;

  SELECT
    IsSuc;
  #日志记录生成模块
  INSERT INTO sys_procedurelog (IsSuccess, Logger, ProcedureName, TimeSpan, ErrCode, ErrMessage, LogTime)
    SELECT
      IsSuc AS IsSuccess,
      '商品数据批量更新' AS Logger,
      'iotcmp.pro_offer_cutover' AS ProcedureName,
      TIMESTAMPDIFF(SECOND, sys_StartTime, NOW()) AS TimeSpan,
      IF(sys_ErrCode = '00000', NULL, sys_ErrCode) AS ErrCode,
      IF(sys_ErrCode = '00000', NULL, sys_ErrMessage) AS ErrMessage,
      NOW() AS LogTime;
			

END

 

 

发现把自己电脑跑死了，还有一个原因当我的商品数据大于1000条后无法执行

 

然后

CREATE DEFINER=`wlwapp`@`%` PROCEDURE `pro_offer_cutover`()
    SQL SECURITY INVOKER
BEGIN
  #Author: 秦艳红
  #Create: 2019-12-17
  #存储过程示例
  #日志记录定义模块
  DECLARE sys_StartTime datetime;
  DECLARE sys_ErrCode varchar(5) DEFAULT '00000';
  DECLARE sys_ErrMessage varchar(200);
  DECLARE IsSuc tinyint(1);
  DECLARE c_offer_id decimal(16,0);
  DECLARE j_offer_id decimal(16,0);
  DECLARE j_offer_nbr varchar(30);
	
  declare stops int default 0;
  declare mc cursor for 
		SELECT a.j_offer_id,a.j_offer_code,a.c_offer_id from sheet1 a 
		left join offer b on a.c_offer_id=b.offer_id;
		
	declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stops = null;  
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	
	
  BEGIN
    GET DIAGNOSTICS CONDITION 1
    sys_ErrCode = RETURNED_SQLSTATE, sys_ErrMessage = MESSAGE_TEXT;
  END;
  SET sys_StartTime = NOW();

  #开启事务
  START TRANSACTION;

    #修改隔离级别,防止对业务表过长的共享锁占用
     SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    #处理数据
		-- 第一个游标循环，循环分表
	
	OPEN mc;  
			fetch mc into j_offer_id,j_offer_nbr,c_offer_id;					 
			CALL  `proc_offer1`(c_offer_id ,j_offer_id, j_offer_nbr);		 
	close mc;
	
	
		#恢复隔离级别
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
    #事务提交/回滚模块
    IF sys_ErrCode <> '00000' THEN
      SET IsSuc = 0;
      ROLLBACK;
    ELSE
      SET IsSuc = 1;
			
    COMMIT;
  END IF;

  SELECT
    IsSuc;
 
  #日志记录生成模块
  INSERT INTO sys_procedurelog (IsSuccess, Logger, ProcedureName, TimeSpan, ErrCode, ErrMessage, LogTime)
    SELECT
      IsSuc AS IsSuccess,
      '商品数据批量更新' AS Logger,
      'iotcmp.pro_offer_cutover' AS ProcedureName,
      TIMESTAMPDIFF(SECOND, sys_StartTime, NOW()) AS TimeSpan,
      IF(sys_ErrCode = '00000', NULL, sys_ErrCode) AS ErrCode,
      IF(sys_ErrCode = '00000', NULL, sys_ErrMessage) AS ErrMessage,
      NOW() AS LogTime;
			

END