【mysql 存储过程】数据库从菜鸟走向大神系列3

最新推荐文章于 2022-08-25 10:08:05 发布

置顶东华果汁哥

最新推荐文章于 2022-08-25 10:08:05 发布

阅读量354

点赞数 1

分类专栏：数据开发-mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/u013421629/article/details/78622092

版权

数据开发-mysql 专栏收录该内容

88 篇文章 3 订阅

订阅专栏

主要内容：
1、建表
2、写存储过程
3、写事件（自动化）

mysql 建表语句：

CREATE TABLE `nld_op_data_statistics_day` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel` varchar(16) NOT NULL COMMENT '渠道',
  `count_date` varchar(16) NOT NULL COMMENT '日期',
  `product` varchar(16) NOT NULL COMMENT '产品',
  `apply_num` int(8) DEFAULT '0' COMMENT '当日申请数量',
  `pass_num` int(8) DEFAULT '0' COMMENT '当日通过数量',
  `pass_amount` decimal(20,2) DEFAULT '0.00' COMMENT '当日审批金额',
  `loan_amount` decimal(20,2) DEFAULT '0.00' COMMENT '当日放款金额',
  `loan_num` int(8) DEFAULT '0' COMMENT '当日放款数量',
  `service_fail_num` int(8) DEFAULT '0' COMMENT '当日服务费代扣失败数量',
  PRIMARY KEY (`id`),
  KEY `Index 2` (`count_date`),
  KEY `Index 3` (`product`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COMMENT='运营部农粒贷每日数据统计日报';

mysql 存储过程（无参的）

CREATE DEFINER=`ldf`@`%` PROCEDURE `nld_op_data_statistics_day`()
    SQL SECURITY INVOKER
BEGIN


-- 农粒贷运营部每日数据统计日报存储过程
declare done1 int default 0;
declare m_channel varchar(16);
declare m_count_date varchar(16);
DECLARE m_product varchar(16);
DECLARE m_apply_num int(8);
DECLARE m_pass_num int(8);
DECLARE m_pass_amount decimal(20,2);
DECLARE m_loan_amount decimal(20,2);
DECLARE m_loan_num int(8);
DECLARE m_service_fail_num int(8);


/************定义游标**************/
declare cur1 cursor for 



SELECT
a.`渠道`,
a.`日期`,
a.`产品`,
a.`当日申请数量`,
a.`当日通过数量`,
a.`当日审批金额`,
a.`当日放款金额`,
a.`当日放款数量`,
b.`当日服务费代扣失败数量`
from(
SELECT
"二维码" as '渠道',
DATE_FORMAT(@mytime, '%Y-%m-%d') as "日期",
"农粒贷" as '产品',
sum(IF(DATE_FORMAT(t.apply_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d'),1,0)) as "当日申请数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),1,0)) as "当日通过数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),t.borrow_amount,0)) as '当日审批金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),t.borrow_amount,0)) as '当日放款金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),1,0)) as '当日放款数量'
FROM t_order_info as t,(SELECT @mytime:=CURRENT_DATE()-1) t_order_info
 WHERE model_id=12
and user_id in(
SELECT DISTINCT user_id from t_user_info WHERE from_two_dimension_code is not null
)
UNION ALL
SELECT
"互联网" as '渠道',
DATE_FORMAT(@mytime, '%Y-%m-%d') as "日期",
"农粒贷" as '产品',
sum(IF(DATE_FORMAT(t.apply_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d'),1,0)) as "当日申请数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),1,0)) as "当日通过数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),t.borrow_amount,0)) as '当日审批金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),t.borrow_amount,0)) as '当日放款金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),1,0)) as '当日放款数量'
FROM t_order_info as t,(SELECT @mytime:=CURRENT_DATE()-1) t_order_info
 WHERE model_id=12
and user_id not  in(
SELECT DISTINCT user_id from t_user_info WHERE from_two_dimension_code is not null
)
UNION ALL
SELECT
"汇总" as '渠道',
DATE_FORMAT(@mytime, '%Y-%m-%d') as "日期",
"农粒贷" as '产品',
sum(IF(DATE_FORMAT(t.apply_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d'),1,0)) as "当日申请数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),1,0)) as "当日通过数量",
sum(IF(DATE_FORMAT(t.audit_pass_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (111,101,202,108,105,106,107),t.borrow_amount,0)) as '当日审批金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),t.borrow_amount,0)) as '当日放款金额',
sum(IF(DATE_FORMAT(t.update_time, '%Y-%m-%d') = DATE_FORMAT(@mytime, '%Y-%m-%d') and t.STATUS in (105),1,0)) as '当日放款数量'
FROM t_order_info as t,(SELECT @mytime:=CURRENT_DATE()-1) t_order_info
WHERE model_id=12
)a

LEFT JOIN(
SELECT
"二维码" as "渠道",
SUBDATE(CURDATE(),INTERVAL 1 day) as "日期",
"农粒贷" as "产品",
count(order_no) as '当日服务费代扣失败数量' from t_user_recharge_log 
WHERE 
order_no in(
SELECT order_no from t_order_info WHERE model_id=12 and substr(loan_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
and user_id in(
SELECT DISTINCT user_id from t_user_info WHERE from_two_dimension_code is not null
)
)
and  substr(create_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
and purpose=4
and `status`=2
union all
SELECT
"互联网" as "渠道",
SUBDATE(CURDATE(),INTERVAL 1 day) as "日期",
"农粒贷" as "产品",
count(order_no) as '当日服务费代扣失败数量' from t_user_recharge_log 
WHERE 
order_no in(
SELECT order_no from t_order_info WHERE model_id=12 and substr(loan_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
and user_id not in(
SELECT DISTINCT user_id from t_user_info WHERE from_two_dimension_code is not null
)
)
and  substr(create_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
and purpose=4
and `status`=2
union all
SELECT
"汇总" as "渠道",
SUBDATE(CURDATE(),INTERVAL 1 day) as "日期",
"农粒贷" as "产品",
count(order_no) as '当日服务费代扣失败数量' from t_user_recharge_log 
WHERE 
order_no in(
SELECT order_no from t_order_info WHERE model_id=12 and substr(loan_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
)
and  substr(create_time,1,10)=SUBDATE(CURDATE(),INTERVAL 1 day)
and purpose=4
and `status`=2
)b
on a.`渠道`=b.`渠道` and a.`产品`=b.`产品` and a.`日期`=b.`日期`;








declare continue handler for not found set done1 = 1;



open cur1;
fetch cur1 into m_channel,m_count_date,m_product,m_apply_num,m_pass_num,m_pass_amount,m_loan_amount,m_loan_num,m_service_fail_num;

while done1<>1 do


/*******************************执行插入操作 **************************************/

INSERT into nld_op_data_statistics_day(channel,count_date,product,apply_num,pass_num,pass_amount,loan_amount,loan_num,service_fail_num)

VALUES(m_channel,m_count_date,m_product,m_apply_num,m_pass_num,m_pass_amount,m_loan_amount,m_loan_num,m_service_fail_num);

       commit;

     set done1 = 0;

     fetch cur1 into m_channel,m_count_date,m_product,m_apply_num,m_pass_num,m_pass_amount,m_loan_amount,m_loan_num,m_service_fail_num;
  end while ; 

close cur1;            

END

mysql 定义事件（调用存储过程，实现自动化插入数据到上面建立的表）

CREATE DEFINER=`ldf`@`%` EVENT `nld_op_data_statistics_day` ON SCHEDULE EVERY 1 DAY STARTS '2017-11-16 06:00:00' ON COMPLETION PRESERVE ENABLE DO CALL nld_op_data_statistics_day()