MySQL存储过程实践-优快云博客

本文介绍了一个MySQL存储过程的实际应用案例，该过程根据不同的时间段计算收费率。文章详细展示了如何通过前台参数调用存储过程，以及如何处理不同条件下的SQL查询。

今天，使用mysql，使用知识点有：

1.批量导入测试数据，即同一条数据重复插入若干遍，写了一个存储过程。

2.使用函数，自动创建不重复的主键id

遇到的问题，

1.写存储过程的时候，遇到了mysql的保留字，name,code，需要进行转义，使用反引号，位于键盘esc的下面`````这类符号

2.使用uuid()函数生成主键时，需要方法来去掉中间连接符-，使用replace()函数

3.使用spring mvc进行前台数据交互，使用百度API解析经纬度，无法进入action，2处地方修改，第一为web.xml的过滤器问题，第二是需要在xml文件中配置action文件。

今天使用mysql的存储过程，因为需要前台传参数（开始月份和截止月份），确定所计算的收费率，并且不同的参数所执行的sql是不一样的，所以前台使用拼接sql的方式调用存储过程：

String timeType=(String) parameter.get("timeType");
		String beginMonth=(String) parameter.get("beginMonth");
		String endMonth=(String) parameter.get("endMonth");
		String companyId=(String) parameter.get("companyId");
		String treeCode=(String) parameter.get("treeCode");
		String dictItemName=(String) parameter.get("dictItemName");
		String sql="call pro_month_rate ('"+timeType+"','"+beginMonth+"','"+endMonth+"','"+companyId+"','"+treeCode+"','"+dictItemName+"')";

使用一个list的object[]数组接收

List<Object[]> list=viewReportRateMonthDao.findListBySql(sql);

遍历集合，存入entity

for (Object[] objects : list) {
				ViewReportRateMonth entity=new ViewReportRateMonth();
				entity.setReceivableDetailId(objects[0].toString());
				entity.setCompanyId(objects[1].toString());
				entity.setCode(objects[2].toString());
				entity.setLevel2Name(objects[3].toString());
				entity.setThisMouth(objects[6].toString());
				entity.setDictItemName(objects[7].toString());
				entity.setDictItemCode(objects[8].toString());
				entity.setAmountReceivable(new BigDecimal(objects[9].toString()));
				entity.setActuallyAmount(new BigDecimal(objects[10].toString()));
				entity.setReceivableRate(objects[11].toString());
				entity.setTreeCode(objects[13].toString());
				listEntity.add(entity);
			}

上面是后台代码，

接下来是创建存储过程代码：

demo是

create procedure pro_month_rate(in timeType varchar(32), in beginMonth varchar(32),in endMonth varchar(32),in companyId varchar(32) ,in treeCode varchar(32),in dictItemName varchar(32))
begin 
if timeType='month' THEN 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth= beginMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
ELSEIF timeType='year' THEN 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth like CONCAT(beginMonth,'%') and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
else 
SELECT
	`vrr`.`receivable_detail_id` AS `receivable_detail_id`,
	`vrr`.`company_id` AS `company_id`,
vrr.house_name as `code`,
	`vrr`.`level2_name` AS `level2_name`,
vrr.level3_name,
vrr.level4_name,
	`vrr`.`this_mouth` AS `this_mouth`,
	`vrr`.`dict_item_name` AS `dict_item_name`,
	`vrr`.`dict_item_code` AS `dict_item_code`,
	sum(`vrr`.`fee_amount`) AS `amount_receivable`,
	sum(

		IF (
			(`vrr`.`state` = '1'),
			(
				`vrr`.`fee_amount` + `vrr`.`pref_amount`
			),
			0
		)
	) AS `actually_amount`,
	concat(
		format(
			(
				(
					sum(

						IF (
							(`vrr`.`state` = '1'),
							(
								`vrr`.`fee_amount` + `vrr`.`pref_amount`
							),
							0
						)
					) / sum(
						(
							`vrr`.`fee_amount` + `vrr`.`payment_amount`
						)
					)
				) * 100
			),
			0
		),
		'%'
	) AS `receivable_rate`,
	`vrr`.`collect_date` AS `collect_date`,
vrr.tree_code
FROM
	`view_report_base_rate` `vrr`
where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth >= beginMonth and vrr.this_mouth<= endMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)
GROUP BY
	`vrr`.`dict_item_name` ,
vrr.company_id ,
vrr.this_mouth ,
vrr.level3_name ;
end if;
end;

看着复杂，其实就是一个创建过程以及简单的if ,else逻辑

create procedure pro_month_rate()

begin
if timeType='month' THEN
select * from...

ELSEIF timeType='year' THEN
select * from .. ; //另一段sql

else
select * from ... ; //最后sql

end if;

end;

使用了一个函数FIND_IN_SET(table.field,'数组')，多个参数适用in和都好分开。

根据b表数据更新a表某个字段

update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;

mysql使用小整理