大家帮我分析下Mysql的存储过程的一个问题,返回结果非常缓慢?

CapJes 发布于 2016/04/30 00:37
阅读 289
收藏 0

下面的是一个存储过程,调用函数。执行下面的存储过程,返回结果非常缓慢,会出现“内存溢出”的 错误提示?主意:这里的内存溢出,不是Java代码里,而是数据库工具内存溢出(很有可能数据库内存溢出)。


存储过程语句:


BEGIN
	declare deleteDepartmentResult int(2);
	declare p_userId bigint(11);
	declare stopFlag int(11);
  declare done int(1);  
	declare user_cursor CURSOR for select userId from t_user where departmentId=p_departmentId; 
	declare continue handler FOR not found SET done = 1;  
	delete from t_department where departmentId=p_departmentId;
	select ROW_COUNT() into deleteDepartmentResult;
	open user_cursor;
	REPEAT
	FETCH user_cursor into p_userId;
	BEGIN
		select fn_delete_user(p_userId);
	END;
	UNTIL stopFlag=1
	END REPEAT;
	CLOSE user_cursor;
	select deleteDepartmentResult;
END



下面是调用的函数:
fn_delete_user(userId)//被上面存储过程调用



BEGIN
	declare result varchar(200);
	declare userResult int(2);
	declare scoreResult int(2);	
	declare commentResult int(2);
	declare exameItemResult int(2);
	declare limitsResult int(2);
	#删除用户表
	delete from t_user where userId=p_userId;
	select ROW_COUNT() into userResult;
	#删除积分表
	delete from t_score where userId=p_userId;
	select ROW_COUNT() into scoreResult;
	#删除评论表
	delete from t_comment where userId=p_userId;
	select ROW_COUNT() into commentResult;
	#删除答题表
	delete from t_user_exam_item where userId=p_userId;
	select ROW_COUNT() into exameItemResult;
	#删除权限表
	delete from t_limits where userId=p_userId;
	select ROW_COUNT() into limitsResult;

	set result = CONCAT(userResult,"_",scoreResult,"_",commentResult,"_",exameItemResult,"_",limitsResult);

	return result;
END


怎么修改和优化?有何建议。



加载中
0
温习江湖
温习江湖
建议,别用存储过程
0
a
astars
存储过程  到后面坑的你想哭
返回顶部
顶部