mysql存储过程示例

CREATE PROCEDURE `pro_auto_subject`() 
	COMMENT '数据迁移' 
BEGIN
	/**procedure body**/
	DECLARE autoSubjectID INT  DEFAULT 0;
	DECLARE professionID INT  DEFAULT 0;
	DECLARE _done INT DEFAULT 0;
	
	DECLARE cur_1 CURSOR FOR 
	SELECT s.`id` AS autoSubjectID,p.`id` AS professionID 
	FROM `auto_subject` s join `profession` p on(s.`profession_new_id`=p.`new_id` ); 

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;
	OPEN cur_1;
		REPEAT 
		FETCH cur_1 INTO autoSubjectID,professionID;
		IF NOT _done THEN
		UPDATE auto_subject SET `profession` = professionID WHERE `id`  =autoSubjectID;
		END IF;
		UNTIL _done 
		END REPEAT;
		
	CLOSE cur_1;
END 

 

1、mysql以下直接在sql窗口可以创建存储过程，格式如下：

/*----套餐表迁移---- */
DELIMITER ;;
DROP PROCEDURE IF EXISTS `pro_move_meal` $$
CREATE PROCEDURE `pro_move_meal`()  
    COMMENT '套餐表迁移(meal表->benefit_package表)'
BEGIN 
 /**procedure body**/  
 	DECLARE p_id VARCHAR(50); 
 	DECLARE p_createTime VARCHAR(100); -- 创建时间
 	DECLARE p_updateTime VARCHAR(100);-- 更新时间
 	DECLARE p_createUser VARCHAR(100);-- 创建人
 	DECLARE p_updateUser VARCHAR(100);-- 更新人
 	DECLARE p_isDeleted VARCHAR(100);-- 是否删除 0否1是
 	-- DECLARE p_type INT  DEFAULT NULL;-- 类型 1 次数套餐 2 优惠券套餐
    DECLARE p_name VARCHAR(100); --  套餐名称
    DECLARE p_des VARCHAR(500);  -- 套餐说明
    DECLARE _done INT DEFAULT 0; 
    DECLARE cur_1 CURSOR FOR   
    /* 套餐表 */
    select id,create_time,update_time,create_user,update_user,is_deleted,name,des 
    from `airport_cloud`.meal;  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;  
    OPEN cur_1;  
           
         REPEAT   
        FETCH cur_1 INTO p_id,p_createTime,p_updateTime,p_createUser,p_updateUser,p_isDeleted,p_name,p_des;  
        IF NOT _done THEN  
        /** 新表-benefit_package*/
        insert into benefit_package(old_id,old_table,create_time,update_time,create_userid,update_userid,is_deleted,package_name,description) 
      values(p_id,'meal',p_createTime,p_updateTime,p_createUser,p_updateUser,p_isDeleted,p_name,p_des);  
        END IF;  
        UNTIL _done   
        END REPEAT;  
        
    CLOSE cur_1;
END;;
DELIMITER ;

 

2、清空mysql数据库中所有表数据

/*----清空account_service数据库中所有表数据---- */
DELIMITER ;;
CREATE PROCEDURE `pro_clear_database`()  
    BEGIN  
         DECLARE count INT;  
         DECLARE tb VARCHAR(200);  
         DECLARE dbname VARCHAR(200) DEFAULT 'account_service';  -- 清理数据库名
         DECLARE tbnames cursor FOR SELECT CONCAT('TRUNCATE TABLE `',dbname,'`.`',table_name,'`') FROM information_schema.tables WHERE table_schema = dbname;  
         SELECT count(*) INTO count FROM information_schema.tables WHERE table_schema = dbname;  
         OPEN tbnames;  
         loop_i:LOOP  
             IF count = 0 THEN   
                 LEAVE loop_i;  
             END IF;  
             FETCH tbnames INTO tb;  
             SET @tb = tb;  
             PREPARE stmt FROM @tb;    
             EXECUTE stmt;    
             DEALLOCATE PREPARE stmt;  
             SET count = count - 1;  
         END LOOP;  
         CLOSE tbnames;  
     END;;
DELIMITER ;

 

3、防止select into游标不循环

/*----用户休息室权益使用记录表数据迁移---- */
DELIMITER ;;
CREATE PROCEDURE `pro_move_benefit_use_record`()  
    COMMENT '用户休息室权益使用记录表数据迁移(benefit_use_record表->user_lounge_benefit_records表)'
BEGIN 
 /**procedure body**/  
 	DECLARE p_id VARCHAR(50); 
 	DECLARE p_orderNo VARCHAR(100); --  订单
 	DECLARE p_openId VARCHAR(500);  -- openid
 	DECLARE p_benefitId VARCHAR(500); -- 权益id 
 	DECLARE p_count VARCHAR(500); -- 次数
 	DECLARE p_createTime VARCHAR(100); -- 创建时间
 	DECLARE p_createUser VARCHAR(100);-- 创建人
 	DECLARE p_isDeleted VARCHAR(100);-- 是否删除 0否1是
    
    DECLARE p_loungeBenefitId bigint(20) default 0; -- 新表权益id
    DECLARE _done INT DEFAULT 0; 
    DECLARE cur_1 CURSOR FOR   
    /* 套餐表 */
    select id,order_no,open_id,benefit_id,count,create_time,create_user,is_deleted 
    from `airport_cloud`.benefit_use_record;  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;  
    OPEN cur_1;  
           
         REPEAT   
        FETCH cur_1 INTO p_id,p_orderNo,p_openId,p_benefitId,p_count,p_createTime,p_createUser,p_isDeleted;  
        IF NOT _done THEN  
		-- 查新id
		select id from lounge_benefit where old_id=p_benefitId into p_loungeBenefitId;
		
        insert into user_lounge_benefit_records
        (is_move,old_benefit_id,old_table,create_userid,create_time,is_deleted,open_id,lounge_benefit_id,
        consume_count,order_no) 
		values('1',p_benefitId,'benefit_use_record',p_createUser,p_createTime,p_isDeleted,p_openId,
		p_loungeBenefitId,p_count,p_orderNo);  
      
      	set _done = 0; -- 防止select into 游标不循环
        END IF;  
        UNTIL _done   END REPEAT;  
        
    CLOSE cur_1;
END;;
DELIMITER ;

 

4、大量数据插入性能问题，目前有80w多数据要迁移，按正常一秒只能插入5条左右数据，这样算需要迁移1天左右。现用批量commit性能大大提升。见以下代码：

DELIMITER ;;
CREATE  PROCEDURE `pro_move_user_third_party_benefit_one`()
    COMMENT '第三方权益数据迁移-韩国免税金卡(third_party_benefit表->user_third_party_benefit表)'
BEGIN 
 /**procedure body**/  
 	DECLARE p_id VARCHAR(50); 
 	DECLARE p_userId VARCHAR(100); --  用户id
 	DECLARE p_openId VARCHAR(100);  -- openid
 	DECLARE p_mobile VARCHAR(100); -- 手机号 
 	DECLARE p_gradeId VARCHAR(100); -- 等级id
 	DECLARE p_gradeVersion VARCHAR(100); -- 等级版本
 	DECLARE p_thirdPartyBenefitId VARCHAR(100); -- 第三方权益id
 	DECLARE p_benefitVersion VARCHAR(100); -- 第三方权益版本号
 	DECLARE p_benefitPackageId VARCHAR(100);-- 套餐id
 	
 	DECLARE p_effectiveStartDate VARCHAR(100) default null;-- 开始日期
	DECLARE p_effectiveEndDate VARCHAR(100) default null; -- 结束日期
 	
    DECLARE _done INT DEFAULT 0; 
    DECLARE cur_1 CURSOR FOR   
    /* 表 */
    select a.wx_client_id,a.open_id 
    from `airport_cloud_test`.client_info a where a.client_phone_verified=1; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;  
    OPEN cur_1;  
         SET AUTOCOMMIT=0;  -- 只提交一次
         REPEAT   
        FETCH cur_1 INTO p_userId,p_openId;  
        IF NOT _done THEN  
		-- 查第三方权益
		select id,benefit_version 
    	from `account_service`.third_party_benefit where benefit_name='韩国免税金卡' 
    	into p_thirdPartyBenefitId,p_benefitVersion;
    	-- 套餐id
    	select benefit_package_id from benefit_package_relation 
    	where is_deleted=0 and benefit_id=p_thirdPartyBenefitId 
    	limit 0,1 into p_benefitPackageId;
    	-- 查手机、等级、有效期等信息
    	select mobile,grade_id,grade_version,effective_start_date,effective_end_date from user_grade  
    	where is_deleted=0 and open_id=p_openId limit 0,1 into p_mobile,p_gradeId,p_gradeVersion,
    	p_effectiveStartDate,p_effectiveEndDate;
        	
		-- 插入第三方权益
        insert into user_third_party_benefit 
        (is_move,is_deleted,user_id,open_id,mobile,source_type,source,third_party_benefit_id,
       third_party_benefit_version,grade_id,grade_version,send_type,create_time,
       effective_start_date,effective_end_date) 
		values('1',0,p_userId,p_openId,p_mobile,2,p_benefitPackageId,p_thirdPartyBenefitId,
		p_benefitVersion,p_gradeId,p_gradeVersion,1,now(),p_effectiveStartDate,p_effectiveEndDate);
      
      	set _done = 0; -- 防止select into 游标不循环
        END IF;  
        UNTIL _done   END REPEAT;  
       	commit;
    CLOSE cur_1;
END;;
DELIMITER ;