1、新建存储过程
DROP PROCEDURE IF EXISTS `trns_mcs_prod_shp_c_hospd_amount`;
delimiter $$
CREATE PROCEDURE trns_mcs_prod_shp_c_hospd_amount(IN order_date DATE)
begin
insert into trns_mcs_prod_shp_c_hospd(MEDINS_CODE,
SHPP_TIME,
SHPP_AMT,
I_AMT,
all_count)
select MEDINS_CODE AS MEDINSCODE,
DATE_FORMAT(SHPP_TIME, '%Y-%m-%d') AS SHPPTIME,
sum(SHPP_AMT),
sum(case when SHP_ID like 'I_%' THEN SHPP_AMT else 0 end),
count(1)
from trns_mcs_prod_shp_c
where SHP_STAS='3'
and PROD_TYPE='2'
and DATE_FORMAT(SHPP_TIME, '%Y-%m-%d')= order_date
GROUP BY MEDINS_CODE,
DATE_FORMAT(SHPP_TIME, '%Y-%m-%d')
ON DUPLICATE KEY UPDATE MEDINS_CODE=values(MEDINS_CODE),
SHPP_TIME=values(SHPP_TIME),
SHPP_AMT=values(SHPP_AMT);
end $$
DELIMITER ;
2、存储过程参数问题
// 多个参数,IN表示入参,OUT表示出参
// DATE --日期
// VARCHAR(255) --字符
// INT --数字
CREATE PROCEDURE `mon_procedure`(IN startDate DATE,
IN endDate DATE,
IN character_data VARCHAR(255),
OUT out_param INT)
3、查询表用到的存储过程
select * from mysql.proc
where db = 'sz_supplies_zs' and type = 'PROCEDURE'
and body like '%suppur_win_reported_hospital%'