mysql 存储过程

/* 2013-06-20 创建附件表存储过程  */
/* begin of added by zhugexh for prc_create_table_attachment 2013-07-11 */
DELIMITER $$
Drop PROCEDURE IF EXISTS `prc_create_table_attachment`$$
Create PROCEDURE `prc_create_table_attachment`(mdate varchar(6))
BEGIN
    declare attachment_table_name varchar(32);
    declare attachment_index_name varchar(64);
    declare db_name varchar(32);
    SET @db_name = DATABASE();
    SET @attachment_table_name = concat('base_attachment_info_', mdate);
    SET @tableSql = concat("create table if not exists ",@attachment_table_name,
                           " (`attach_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '附件编号:非空,主键',
                              `object_type` int(11) NOT NULL COMMENT '对象类型:非空,联合索引1,1-签约用户申请',
                              `object_id` bigint(20) NOT NULL COMMENT '对象编号:非空,联合索引1',
                              `file_name` varchar(128) NOT NULL COMMENT '文件名:非空',
                              `full_filename` varchar(128) NOT NULL COMMENT '保存文件:非空,绝对路径,由oper_serial + create_date生成md5作为磁盘文件名',
                              `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON Update CURRENT_TIMESTAMP COMMENT '创建时间:非空',
                              `oper_serial` bigint(20) NOT NULL COMMENT '操作流水号:非空',
                              `oper_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '操作时间:非空',
                              `oper_id` int(11) NOT NULL COMMENT '操作员:非空',
                              PRIMARY KEY (`attach_id`)
                             ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
    prepare create_table_stmt from @tableSql;
    execute create_table_stmt;

    SET @attachment_index_name = concat(@attachment_table_name, '_index1');
    SET @indexSql = concat("Alter TABLE ",@attachment_table_name, " ADD INDEX ", @attachment_index_name, "(`object_id`,`object_type`)");
    Select COUNT(*) INTO @CNT
    FROM information_schema.statistics
        Where TABLE_SCHEMA = @db_name
              AND TABLE_NAME = @attachment_table_name
              AND INDEX_NAME = @attachment_index_name;
        IF @CNT <=0 THEN
            prepare create_index_stmt from @indexSql;
            execute create_index_stmt;
        END IF;
END$$

DELIMITER ;
/* end of added by zhugexh for prc_create_table_attachment 2013-07-11 */

call prc_create_table_attachment('201308');
call prc_create_table_attachment('201309');
call prc_create_table_attachment('201310');
call prc_create_table_attachment('201311');
call prc_create_table_attachment('201312');


/* 创建下一年附件表存储过程 */
/* begin of added by zhugexh for prc_create_table_nextyear_attachment 2013-07-11 */
DELIMITER $$

Drop PROCEDURE IF EXISTS `prc_create_table_nextyear_attachment`$$

Create  PROCEDURE `prc_create_table_nextyear_attachment`()
BEGIN
  declare nextYear varchar(4);
  declare yearMonth varchar(6);
  declare i integer;
  set nextYear = DATE_FORMAT(now(),'%Y') + 1;
  set i=1;
  while i<=12 do
    begin    
      if i<10 then
        set yearMonth = concat(nextYear,'0',i);
      else
        set yearMonth = concat(nextYear,i);            
      end if;

      call prc_create_table_attachment(yearMonth);        
      set i=i+1;
    end;
  end while;
END$$

DELIMITER ;
/* end of added by zhugexh for prc_create_table_nextyear_attachment 2013-07-11 */

上一篇: mysql 事件
下一篇: java 学习英语
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志:
评论: 0 | 引用: 0 | 查看次数: -
发表评论
昵 称:
密 码: 游客发言不需要密码.
邮 箱: 支持Gravatar头像.
网 址: 输入网址便于回访.
内 容:
验证码:
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.