初次接触存储过程

最近有个需求需要查数据库中的数据，然后需要对一个结果集进行循环比对，使用sql语句已经无法满足需求了，所以大概看了一下存储过程的相关内容，这里需要特别感谢这篇文章，基本是以它为蓝本完成的。

创建存储过程的格式

create procedure name(param...)
begin
...
end

定义全局变量

declare name varchar(32);

创建临时表

CREATE TEMPORARY TABLE tmp_table (projectItemId VARCHAR(32), `name` VARCHAR(32), serial VARCHAR(32), projectId VARCHAR(32));

使用游标

DECLARE s int DEFAULT 0;
DECLARE user CURSOR FOR select name from user; // 从用户表中查询name放入user这个游标中
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; // 这句话的解释见https://zhidao.baidu.com/question/237450998.html

// 这里就是游标的具体使用
OPEN user
   FETCH user into name; // 将游标中的值赋给变量
   //在游标的处理中进行while循环
   while s <> 1 do //当s不等于1时进行循环遍历操作
   IF (name is not null) then // if条件判断name非空
   ... //相应的操作
   end IF;
   FETCH user into name; //相当于获取下一条数据并赋值
   end while
CLOSE user

如果不了解游标，可以看一下这篇文章。

实际案例

CREATE PROCEDURE XXOO1(in str varchar(40))
BEGIN
	DECLARE projectItemId VARCHAR(32);
	DECLARE projectItemPlanEndDate VARCHAR(32);
	DECLARE s int DEFAULT 0;
	DECLARE projectItem CURSOR FOR SELECT b.id as projectItemId, 
     DATE_ADD(a.start_date,INTERVAL b.plan_end_day DAY) as projectItemPlanEndDate 
     FROM project as a, project_item as b 
     WHERE a.id = b.project_id AND b.id in 
     (select id from project_item 
     where status not in ('0', '100') AND `name` = str);
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;

	drop table if EXISTS tmp_table;
	CREATE TEMPORARY TABLE tmp_table 
   (projectItemId VARCHAR(32), `name` VARCHAR(32), serial VARCHAR(32), projectId VARCHAR(32));

	OPEN projectItem;
		FETCH projectItem into projectItemId,projectItemPlanEndDate;

		WHILE s <> 1 DO
		IF (projectItemPlanEndDate is not null) then

	  INSERT INTO tmp_table select id, `name`, serial, project_id 
      FROM project_item WHERE CURDATE() > projectItemPlanEndDate AND id = projectItemId;
		end IF;

		FETCH projectItem into projectItemId,projectItemPlanEndDate;
		END WHILE;
	CLOSE projectItem;

	SELECT serial as '工程编号', `name` as '工程名称', create_datetime as '创建时间', 
   plan_start_date as '计划开始时间',plan_end_date as '计划结束时间', `status` as '状态' 
   from project 
	where status not in ('0', '100') AND id in (SELECT projectId from tmp_table);
END

// 调用存储过程
call XXOO1('中期验收');
// 删除存储过程
DROP PROCEDURE XXOO1;

其中使用到了mysql中的时间加减函数，可以参考这篇文章。

以及最后如何在navicat中创建存储过程。