MYSQL存储过程实例

/* 2-------------创建租户升级存储过程 */
DROP PROCEDURE IF EXISTS `helpdesk_cater`.`upgradeTenantDB`;
DELIMITER $$
CREATE PROCEDURE `helpdesk_cater`.`upgradeTenantDB`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,c CHAR(64);
DECLARE e VARCHAR(100)DEFAULT '0';
/* 第一次升级使用下面第一行，如果有异常租户库，以后每次调用下面第二行并注解下面第一行*/
DECLARE curTenant CURSOR FOR SELECT ent_id, catalog_name FROM helpdesk_cater.tenant WHERE is_activated = 1;
/*DECLARE curTenant CURSOR FOR SELECT ent_id, catalog_name FROM helpdesk_g3.db_upgrade_error_log;*/

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET e ='表不存在|未知表';/*已测试*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S01' SET e ='表已存在';/*已测试*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S21' SET e ='重复列名';/*已测试*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET e ='列不存在';

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET e ='重复插入主键|外键约束|列不能为空|列不明确|其它情况出错';/*已测试*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET e ='库不存在|拒绝访问库|语句中有sum函数和相同语句中的列|重复键名称|查询为空|非唯一的表/别名|定义了多个主键|键列在表中不存在|不正确的数据库名|不正确的表名|不正确的列名|其它情况出错';


DECLARE CONTINUE HANDLER FOR SQLWARNING SET e='发生SQLWARNING';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e='发生SQLEXCEPTION';/*已测试*/

OPEN curTenant;
DELETE FROM `db_upgrade_error_log`;
DELETE FROM `db_upgrade_log`;
REPEAT
FETCH curTenant INTO a, c;

IF NOT done THEN
/* 记录操作开始日志 */
SET @LOG = CONCAT('INSERT INTO helpdesk_cater.db_upgrade_log (ent_id, catalog_name, start_time) values ("',a,'","',c,'",now());');
PREPARE stmt FROM @LOG;
EXECUTE stmt;

/*SET @mst = CONCAT('CREATE TABLE ',c,'.`t_marketingsms_template` (  `id` varchar(32) NOT NULL,  `title` varchar(10) DEFAULT NULL,  `content` varchar(512) DEFAULT NULL,  `creator_id` varchar(32) DEFAULT NULL,  `creator_agent_tel` varchar(32) DEFAULT NULL,  `create_time` datetime DEFAULT NULL,  `modify_time` datetime DEFAULT NULL, `is_deleted` tinyint(4) DEFAULT NULL,`version` bigint(20) DEFAULT NULL,  `reserve1` varchar(32) DEFAULT NULL,  `reserve2` varchar(32) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;');
prepare stmt92 from @prod92;
execute stmt92;*/

升级脚本开始 */
/*例子： 2010-09-09 lihf 创建录音文件索引表*/
/*SET @sClient1 = CONCAT('CREATE TABLE ',c,'.call_records (record_id varchar(255) NOT NULL,call_type varchar(32) NOT NULL,user_id bigint(20) NOT NULL,ani varchar(16) NOT NULL,dnis varchar(16) NOT NULL,start_time datetime default NULL,end_time datetime default NULL,file_name varchar(64) NOT NULL,PRIMARY KEY  (record_id)) ENGINE=InnoDB DEFAULT CHARSET=gbk;');*/
/*prepare stmtClient1 from @sClient1;*/
/*execute stmtClient1;*/

/*2014-05-04 zhangmx 修改表结构*/
SET @ma = CONCAT('ALTER TABLE ',c,'.`t_marketing_activity`  CHANGE `mlabel` `mlabel` TEXT(128) NULL  COMMENT "营销活动客户类型", CHANGE `members` `members` TEXT(128) NULL  COMMENT "营销活动客户会员类型";');
PREPARE stmtma FROM @ma;
EXECUTE stmtma;

 
/* 升级脚本结束 */

/* 记录日志，记录异常数据库 */
IF e!='0' THEN
INSERT INTO helpdesk_g3.db_upgrade_error_log VALUES(a,c);
UPDATE helpdesk_g3.db_upgrade_log SET error_time = NOW(),error_message= e ,end_time = NOW() WHERE ent_id = a;
ELSE
SET @LOG = CONCAT('update helpdesk_cater.db_upgrade_log set end_time = now() where ent_id = "',a,'";');
PREPARE stmt FROM @LOG;
EXECUTE stmt;
END IF;

SET e='0';
END IF;

UNTIL done END REPEAT;
CLOSE curTenant;

END$$
DELIMITER ;




/* 5-------------调用租户升级存储过程 */
#call `helpdesk_cater`.`upgradeTenantDB`();