Java里sql过长影响性能，那就把sql搬到存储过程吧（本篇详细讲解Mysql存储过程的重要细节及含义）

本文链接：https://blog.csdn.net/qq_44856481/article/details/128955771

1.概念

MySQL5.0版本开始支持存储过程,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法,存储过就是数据库SQL与层层面的代码封装与重用

2.使用存储过程的优势

调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

重复使用：存储过程可以重复使用，从而可以减少数据库开发人员的工作量
减少网络流量：存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量
安全性：参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant（用户授权）、Deny（禁止授予用户权限）以及Revoke（撤销 MySQL 用户帐户的权限和角色）权限应用于存储过程

3.存储过程基础语法

一开始新建函数，如下：

CREATE DEFINER = CURRENT_USER PROCEDURE `sp_base_test`()
BEGIN
    #Routine body goes here...

END;

CREATE DEFINER = CURRENT_USER PROCEDURE `sp_base_test`()

definer后边一般写用户名，procedure后边会自动生成表名，例子：

CREATE DEFINER=`root`@`%` PROCEDURE `sp_base_sys_menu`()

如果sql是需要传参的，参数写在BEGIN前面，并且sql最后的where条件后面也要对应，例子：

CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_cq_project_declare`(
	in scoreType varchar(64),
	in pointAnnual varchar(64),
    in checkStatus varchar(64),
	in projectHead varchar(64),
	in headId varchar(64),
    in isDistribute varchar(64),
	in projectSecondaryAcademyId varchar(64),
	in current int ,
	in pageSize int 
)
BEGIN

# 声明变量
	DECLARE allCount int DEFAULT 0;

-- insert into temp
create TEMPORARY table temp_table (

select *  from 
(
SELECT
	c.* , pd5.process_inst_id AS processInstanceId , 0 as count 
FROM
	(
		SELECT
			pd2.project_name AS projectName,
			pd2.project_id AS projectId,
			pd2.id AS id,
			pd2.project_grade AS projectGrade,
			pd2.project_grade_name AS projectGradeName,
			pd2.user_college AS project_secondary_academy,
			pd2.user_college_id AS project_secondary_academy_id,
			pd2.project_head_id,
			pd2.project_head,
			pd1.cooperative_start_time,
			pd1.cooperative_end_time,
			pd2.point_annual,
			pd2.score_type ,
			pd2.is_distribute,
			pd2.check_status,
			pd2.declaration_type,
			pd2.integral_type AS integralType,
			pd2.integral_project_type AS integralProjectType,
			pd2.create_time,
			pd1.protocol_start_time,
			pd1.protocol_end_time,
			pd1.project_manager_type,
			pd2.bpm_status,
			pd2.project_child_type_id AS projectChildTypeId,
			'1' AS is_donation,
			pd2.integral_project_type			
		FROM
			cq_project_declaration pd2
			LEFT JOIN cq_project_declare pd1 ON pd1.id = pd2.project_id 
		WHERE
			pd2.project_name IS NOT NULL 
		) c
		LEFT JOIN ext_act_flow_data pd5 ON c.id = pd5.form_data_id ) t 
		
		where  
		score_type like CONCAT('%',scoreType,'%') 
		AND point_annual like CONCAT('%',pointAnnual,'%') 
	    AND check_status like CONCAT('%',checkStatus,'%') 
		AND project_head like CONCAT('%',projectHead,'%')
		AND project_head_id like CONCAT('%',headId,'%')
	    AND is_distribute like CONCAT('%',isDistribute,'%')
		AND project_secondary_academy_id like CONCAT('%',projectSecondaryAcademyId,'%')
    
		ORDER BY create_time DESC
	) 	;

        
        set allCount = (select count(1) from temp_table  );
		update temp_table set count = allCount ;
		select * from temp_table limit current,pageSize;

END

① create TEMPORARY table temp_table（）是做分页sql的话一定要的，含义是创建一张临时表

② DECLARE allCount int DEFAULT 是为了多出来的总数量字段给个初始值

③ set allCount = (select count(1) from temp_table );
update temp_table set count = allCount ;
select * from temp_table limit current,pageSize;

与sql含义一样，查到这张临时表的总数量，给这张临时表的数量字段插入查到的总数量，然后分页显示出所有的数据

如果只是想做分页的存储过程，但不想每次生产临时表，那么就在BEGIN下加上一句

drop TABLE if EXISTS temp_table; 例：

一个MySQL的存储过程就写好啦！

4.调用存储过程

CALL 名称 ([ 参数 ]);

例：Mapper接口调用如下，也可以在.xml文件里调用

    @Select({"<script> ",
            " call sp_get_cq_project_declare( #{scoreType},#{pointAnnual},#{checkStatus},#{projectHead},#{headId},#{isDistribute},#{projectSecondaryAcademyId},#{current},#{pageSize} ) ",
            " </script>"})
    List<CqPointDeclarationVo> getPointDeclarationByPage(  @Param("pageSize") Integer pageSize, @Param("current") Integer current,
                                                           @Param("headId") String headId,
                                                           @Param("scoreType") String scoreType, @Param("pointAnnual") String pointAnnual,
                                                           @Param("isDistribute") String isDistribute,
                                                           @Param("checkStatus") String checkStatus,@Param("projectHead") String projectHead,
                                                           @Param("processKey") String processKey,@Param("projectSecondaryAcademyId")String projectSecondaryAcademyId);

MySQL存储过程的各种语法这参考这篇：

MySQL的存储过程_吕维尧的博客-CSDN博客_存储过程

下面再发一些写的存储过程，希望给大家一些思路吧：

① 存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `sp_base_sys_menu`()
BEGIN
	# 声明变量
	DECLARE s int DEFAULT 0;
	# 企业id
	DECLARE permissionId varchar(32) ; 
	# 企业说书机构
	DECLARE permissionNodeLevel int DEFAULT 1 ;
	#声明游标 report
	DECLARE report  CURSOR FOR 
	select SEQ from kdbase_tree;
	-- 声明当游标遍历完后将标志变量置为某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
	OPEN report;
		-- 		将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
	FETCH report INTO permissionId ;
	-- 当s不等于1时，也就是未遍历完时，会一直循环
	WHILE s <> 1 DO
		set permissionNodeLevel = (
			select count(1) from (
			SELECT DISTINCT 
			@r AS _id,
			(SELECT @r := parent_id FROM sys_permission WHERE id = _id) AS parent_id FROM
			(SELECT @r := permissionId , @l := 0) vars, sys_permission AS h
			WHERE @r != '') a
		) ;

	update kdbase_tree set NODE_DEEP = permissionNodeLevel where SEQ = permissionId ;
	-- 当s等于1时代表遍历已完成，退出循环
	FETCH report INTO permissionId ;
	END WHILE;

	-- 关闭游标
	CLOSE report;
END

调用：

/**
	 * 调用菜单存储过程更新数据
	 */
	@Select("call sp_base_sys_menu_zc")
	void updateSysMenu();

②存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `sp_base_synchronous`()
BEGIN

	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kduser_users';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 1、用户基本信息同步
	delete from kduser_users ;
	insert into kduser_users
	(USER_CODE,USER_PASSWORD,USER_ROLE,IS_MEMBER,IS_ADMIN,CREATE_TIME,LAST_LOGIN_TIME,LAST_LOGIN_IP,SYS_ROLE,can_login,expired_date,user_state)
	select username , `password` , '' , 0 , 0 ,  create_time  , null , null , 'user' , '1' , '9999-12-21' , '1'  
	from sys_user where status = '1' and del_flag = '0';
	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kdbase_role';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 2、角色信息同步
	delete from kdbase_role ;
	insert into kdbase_role(ROLE_CODE,ROLE_NAME,ROLE_DESC,ORDER_NUM,DATA_RIGHT_TYPE)
	select role_code , role_name , '' , 1 , asset_permission
	from sys_role ;
	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kduser_user_role';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 3、同步用户角色
	delete from kduser_user_role ;
	insert into kduser_user_role(user_code,role_code)
	select s1.username , s2.role_code  from sys_user_role s , sys_user s1 , sys_role s2 
	where s.role_id = s2.id and s.user_id = s1.id and s1.status = '1' and s1.del_flag = '0' ;
	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kduser_frame_info';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 4、同步部门
	delete from kduser_frame_info ;
	insert into kduser_frame_info (frame_id,frame_code,frame_name,frame_type,para_id,	para_code)
	select id , org_code , depart_name , org_category , parent_id , (
	select org_code from sys_depart where id = a.parent_id 
) from sys_depart a where   del_flag = '0' ;
	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kduser_user_frame';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 5、同步用户部门
	delete from kduser_user_frame ;
	insert into kduser_user_frame(frame_id,frame_type,user_code,is_current_dept,sort_no)
	select s2.id , '1' , s1.username , '1' , '0' from sys_user_depart s , sys_user s1 , 		sys_depart s2
	where s.dep_id = s2.id and s.user_id = s1.id and s2.del_flag = '0' and s1.del_flag = '0' ;
	/**
	 按照周备份表
	*/
	set @weeks = DATE_FORMAT(NOW(),'%w') ;
	set @table_name = 'kduser_user_manage_dept';
	set @new_table_name  = CONCAT('bak_',@table_name,'_',@weeks);
	set @deletes = CONCAT('drop table if EXISTS  ' , @new_table_name  ) ;
	PREPARE stmt1 FROM @deletes;
	EXECUTE stmt1;
	SET @bak_name = CONCAT('create table ',@new_table_name, ' select * from ' , @table_name);
	PREPARE stmt1 FROM @bak_name;
	EXECUTE stmt1;
	-- 6、同步资产用户数据范围
	delete from kduser_user_manage_dept ;
	insert into kduser_user_manage_dept(user_code,frame_id)
	select username , depart_id  from sys_user_asset ;
	
END

调用：

	/**
	 * 数据库同步数据
	 */
	@Select("call sp_base_synchronous")
	void updateSqlSynchronize();

③ 存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_cq_project_declare`(
	in scoreType varchar(64),
	in pointAnnual varchar(64),
  in checkStatus varchar(64),
	in projectHead varchar(64),
	in headId varchar(64),
  in isDistribute varchar(64),
	in projectSecondaryAcademyId varchar(64),
	in current int ,
	in pageSize int 
)
BEGIN

# 声明变量
	DECLARE allCount int DEFAULT 0;

drop TABLE if EXISTS temp_table;

-- insert into temp
create TEMPORARY table temp_table (

select *  from 
(
SELECT
	c.* , pd5.process_inst_id AS processInstanceId , 0 as count 
FROM
	(
	SELECT
		pd1.project_name AS projectName,
		pd1.id AS projectId,
		NULL AS id,
		pd1.project_grade AS projectGrade,
		'' AS projectGradeName,
		pd1.project_secondary_academy,
		pd1.project_secondary_academy_id,
		pd1.project_head_id,
		pd1.project_head,
		pd1.cooperative_start_time,
		pd1.cooperative_end_time,
		IFNULL(pd1.point_annual,'') as point_annual ,
		'0' AS score_type,
		'0' AS is_distribute,
		'-9' AS check_status,
		'1' AS declaration_type,
		NULL AS integralType,
		NULL AS integralProjectType,
		pd1.create_time,
		pd1.protocol_start_time,
		pd1.protocol_end_time,
		pd1.project_manager_type,
		'' AS bpm_status,
		pd1.project_child_type_id,
		'1' AS is_donation,
		pd1.project_child_type
	FROM
		cq_project_declare pd1 
	WHERE
		pd1.project_manager_status IN ( 1, 3 ) 
		AND pd1.project_name IS NOT NULL 
		AND pd1.id NOT IN (
		SELECT
			pd2.project_id 
		FROM
			cq_project_declaration pd2 
		WHERE
			pd2.project_name IS NOT NULL 
			AND pd2.declaration_type = 1 
			AND pd2.point_annual = YEAR (
			CurDate())) 
		AND pd1.project_manager_type = 1 
		AND NOW() BETWEEN pd1.protocol_start_time 
		AND pd1.protocol_end_time UNION all 
	SELECT
		pd1.project_name AS projectName,
		pd1.id AS projectId,
		NULL AS id,
		pd1.project_grade AS projectGrade,
		NULL AS projectGradeName,
		pd1.project_secondary_academy,
		pd1.project_secondary_academy_id,
		pd1.project_head_id,
		pd1.project_head,
		pd1.cooperative_start_time,
		pd1.cooperative_end_time,
		IFNULL(pd1.point_annual,'') as point_annual ,
		'1' AS score_type,
		'0' AS is_distribute,
		'-9' AS check_status,
		'1' AS declaration_type,
		NULL AS integralType,
		NULL AS integralProjectType,
		pd1.create_time,
		pd1.protocol_start_time,
		pd1.protocol_end_time,
		pd1.project_manager_type,
		'' AS bpm_status,
		pd1.project_child_type_id,
		'1' AS is_donation,
		pd1.project_child_type
	FROM
		cq_project_declare pd1 
	WHERE
		pd1.project_manager_status IN ( 1, 3 ) 
		AND pd1.project_name IS NOT NULL 
		AND pd1.id NOT IN (
		SELECT
			pd2.project_id pd1 
		FROM
			cq_project_declaration pd2 
		WHERE
			pd2.project_name IS NOT NULL 
			AND pd2.declaration_type = 1 
			AND pd2.point_annual = YEAR (
			CurDate())) 
		AND pd1.project_manager_type = 3 
		AND NOW() BETWEEN pd1.cooperative_start_time 
		AND (
			IFNULL(
				pd1.cooperative_end_time,
				IFNULL((
					SELECT
						max( pd4.actual_donate_date ) 
					FROM
						cq_donate_situation pd4
						JOIN cq_project_declare v 
					WHERE
						pd4.project_id = v.id 
						AND v.project_manager_type = 3 
						),(
					SELECT
						max( pd4.plan_donate_date ) 
					FROM
						cq_donate_situation pd4,
						cq_project_declare v 
					WHERE
						pd4.project_id = v.id 
						AND v.project_manager_type = 3 
					)))) UNION all 
	SELECT
		* 
	FROM
		(
		SELECT
			pd1.project_name AS projectName,
			pd1.id AS projectId,
			NULL AS id,
			pd1.project_grade AS projectGrade,
			NULL AS projectGradeName,
			pd1.project_secondary_academy,
			pd1.project_secondary_academy_id,
			pd1.project_head_id,
			pd1.project_head,
			pd1.cooperative_start_time,
			pd1.cooperative_end_time,
			IFNULL(pd1.point_annual,'') as point_annual ,
			( CASE ( TIMESTAMPDIFF( YEAR, pd1.cooperative_start_time, NOW())) WHEN 0 THEN '0' ELSE '1' END ) AS score_type,
			'0' AS is_distribute,
			'-9' AS check_status,
			'1' AS declaration_type,
			NULL AS integralType,
			NULL AS integralProjectType,
			pd1.create_time,
			pd1.protocol_start_time,
			pd1.protocol_end_time,
			pd1.project_manager_type,
			'' AS bpm_status,
			pd1.project_child_type_id,
			'1' AS is_donation,
			pd1.project_child_type
		FROM
			cq_project_declare pd1 
		WHERE
			pd1.project_manager_status IN ( 1, 3 ) 
			AND pd1.project_name IS NOT NULL 
			AND pd1.id NOT IN (
			SELECT
				pd2.project_id 
			FROM
				cq_project_declaration pd2 
			WHERE
				pd2.project_name IS NOT NULL 
				AND pd2.declaration_type = 1 
				AND pd2.point_annual = YEAR (
				CurDate()) 
			) 
			AND pd1.project_manager_type = 2 
			AND NOW() BETWEEN pd1.cooperative_start_time AND DATE_ADD( pd1.cooperative_end_time, INTERVAL 6 MONTH )
			)t

			 UNION
		SELECT
			pd2.project_name AS projectName,
			pd2.project_id AS projectId,
			pd2.id AS id,
			pd2.project_grade AS projectGrade,
			pd2.project_grade_name AS projectGradeName,
			pd2.user_college AS project_secondary_academy,
			pd2.user_college_id AS project_secondary_academy_id,
			pd2.project_head_id,
			pd2.project_head,
			pd1.cooperative_start_time,
			pd1.cooperative_end_time,
			pd2.point_annual,
			pd2.score_type ,
			pd2.is_distribute,
			pd2.check_status,
			pd2.declaration_type,
			pd2.integral_type AS integralType,
			pd2.integral_project_type AS integralProjectType,
			pd2.create_time,
			pd1.protocol_start_time,
			pd1.protocol_end_time,
			pd1.project_manager_type,
			pd2.bpm_status,
			pd2.project_child_type_id AS projectChildTypeId,
			'1' AS is_donation,
			pd2.integral_project_type			
		FROM
			cq_project_declaration pd2
			LEFT JOIN cq_project_declare pd1 ON pd1.id = pd2.project_id 
		WHERE
			pd2.project_name IS NOT NULL 
		) c
		LEFT JOIN ext_act_flow_data pd5 ON c.id = pd5.form_data_id ) t 
		
		where  -- userId like CONCAT('%',userId,'%') 
		 score_type like CONCAT('%',scoreType,'%') 
		AND point_annual like CONCAT('%',pointAnnual,'%') 
	    AND check_status like CONCAT('%',checkStatus,'%') 
		AND project_head like CONCAT('%',projectHead,'%')
		AND project_head_id like CONCAT('%',headId,'%')
	    AND is_distribute like CONCAT('%',isDistribute,'%')
		AND project_secondary_academy_id like CONCAT('%',projectSecondaryAcademyId,'%')
    
		ORDER BY create_time DESC
	) 	;
		

		
		set allCount = (select count(1) from temp_table  );
		update temp_table set count = allCount ;
		select * from temp_table limit current,pageSize;

END

调用：上面有，传参一定要一一对应喔

最后注意：如果是SqlServer数据库的话，那么调用不用CALL，用EXEC喔！