存储过程

一、什么是存储过程

简单来说，存储过程就是一堆sql的组合， 夹杂着一些控制逻辑。

二、存储过程的应用场景

一般用于处理复杂的数据业务场景，如果涉及到多个数据表操作，会有多次数据库连接， 用存储过程处理，只会有一次数据库连接

三、存储过程的优点

• 创建时进行一次编译，后续直接使用，执行速度快，效率高
• 可用于处理复杂的数据操作
• 存储过程可复用，只需编写一次
• 安全性高，可指定用户权限

四、存储过程的缺点

• 相比于代码，存储过程的开发、调试困难
• 维护成本较高
• 移植性差（跟数据库强绑定）

五、存储过程事例

存储过程自动创建月表

-- 存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `P_AUTO_CREATE_T_RECHARE_ORDER201908`;
CREATE PROCEDURE `P_AUTO_CREATE_T_RECHARE_ORDER201908`()
  BEGIN
    SET @sqlPrefix = "CREATE TABLE IF NOT EXISTS `t_recharge_success_";
    SET @sqlSuffix = "` (
       `rechargeOrderNum` varchar(50) NOT NULL COMMENT '充值订单号',
       `userId` bigint(20) NOT NULL COMMENT '用户id',
       `amount` decimal(32,2) unsigned NOT NULL COMMENT '金额',
       `rechargeTime` bigint(20) NOT NULL COMMENT '充值到账时间',
       `createTime` int(11) NOT NULL COMMENT '入库时间',
       `refer` varchar(128) NOT NULL COMMENT '充值来源refer',
       `payTypeId` int(11) NOT NULL COMMENT '充值渠道',
       `extraJsonData` tinytext NOT NULL COMMENT '充值扩展字段',
        PRIMARY KEY (`rechargeOrderNum`),
        KEY `idx_userId` (`userId`),
        KEY `idx_createTime` (`createTime`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='充值成功列表';";


    -- 本月
    SET @thisDateTime = NOW();
    SET @thisYearMonth = DATE_FORMAT(@thisDateTime, '%Y%m');
    SET @tmpSql = CONCAT(@sqlPrefix, @thisYearMonth, @sqlSuffix);
    PREPARE tmpStmt FROM @tmpSql;
    EXECUTE tmpStmt;
 
    -- 下个月
    SET @nextDateTime = DATE_ADD(NOW(), INTERVAL 1 MONTH);
    SET @nextYearMonth = DATE_FORMAT(@nextDateTime, '%Y%m');
    SET @tmpSql = CONCAT(@sqlPrefix, @nextYearMonth, @sqlSuffix);
    PREPARE tmpStmt FROM @tmpSql;
    EXECUTE tmpStmt;
  END;
$$
DELIMITER ;

-- 事件触发器
DELIMITER $$
DROP EVENT IF EXISTS `E_CREATE_T_RECHARE_ORDER201908`;
CREATE EVENT `E_CREATE_T_RECHARE_ORDER201908`
  ON SCHEDULE EVERY 1 DAY
  ON COMPLETION NOT PRESERVE
  ENABLE DO BEGIN
  CALL P_AUTO_CREATE_T_RECHARE_ORDER201908;
END;
$$
DELIMITER ;