mysql存储过程

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

    --declare用于定义局部变量 也可以使用DEFAULT 初始化给值
    declare minV BIGINT ;
    declare minV BIGINT DEFAULT 72640014;
    -- 给局部变量改值1W
    set minV = minV + 10000;
    -- 使用查询的值给定义的变量赋值使用关键字into
    select id into minV from  tablename  where INVD_FLAG = 0 and is_clean='0'  limit 1;

	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)

4、查询表用到的存储过程

select * from mysql.proc
where db = 'sz_supplies_zs' and type = 'PROCEDURE'
and body like '%suppur_win_reported_hospital%'