mysql存储过程示例

程序员文章站 2022-06-06 19:56:41

...

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 ;

上一篇： yii 之小组件

下一篇： Flutter组件之一行多种颜色文字组件（RichText）

mysql存储过程示例

Mysql存储引擎详解

mysql 存储过程判断重复的不插入数据

Mysql单文件存储删除数据文件容量不会减少的bug与解决方法

Win7下安装MySQL5.7.16过程记录

根据表名和索引获取需要的列名的存储过程

自己收集比较强大的分页存储过程推荐

sql 存储过程分页代码支持亿万庞大数据量

sql存储过程获取汉字拼音头字母函数

SQLserver 数据库危险存储过程删除与恢复方法

nodejs进阶(6)—连接MySQL数据库示例

mysql存储过程示例

Mysql存储引擎详解

mysql 存储过程判断重复的不插入数据

Mysql单文件存储删除数据文件容量不会减少的bug与解决方法

Win7下安装MySQL5.7.16过程记录

根据表名和索引获取需要的列名的存储过程

自己收集比较强大的分页存储过程 推荐

sql 存储过程分页代码 支持亿万庞大数据量

sql存储过程获取汉字拼音头字母函数

SQLserver 数据库危险存储过程删除与恢复方法

nodejs进阶(6)—连接MySQL数据库示例

自己收集比较强大的分页存储过程推荐

sql 存储过程分页代码支持亿万庞大数据量