mysql存储过程如何利用临时表返回结果集

and  ( TTB.office_id=输入参数 or  输入参数 is null  or 输入参数 = ”)
and  ( TTB.office_id=IN_Office_id or  IN_Office_id is null  or IN_Office_id = ”)

BEGIN
#创建一个临时表
DROP TABLE if exists user_temporary;
create temporary table if not exists user_temporary
(
id VARCHAR(64) primary key,#id
user_name VARCHAR(20) #姓名

) ;
begin

#定义 变量 接收id和姓名
declare a VARCHAR(64);
declare b VARCHAR(20);

#这个用于处理游标到达最后一行的情况

DECLARE s int default 0;

#声明游标cursor_name（cursor_name是个多行结果集）

DECLARE cursor_name CURSOR FOR select id ,name from user ;

#设置一个终止标记

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET s=1;

#打开游标

OPEN cursor_name;

#获取游标当前指针的记录，读取一行数据并传给变量a,b

fetch cursor_name into a,b;
#开始循环，判断是否游标已经到达了最后作为循环条件

while s <> 1 do

insert into user_temporary(id,user_name) values(a,b);
#读取下一行的数据

fetch cursor_name into a,b;

end while;

#关闭游标

CLOSE cursor_name ;

#从临时表中拿到结果集
SELECT * from user_temporary;

#语句执行结束

end;

END

BEGIN
DROP TABLE if exists user_temporary;
create temporary table if not exists user_temporary
(
id VARCHAR(64) primary key,id
user_name VARCHAR(20) ,#姓名
company_name VARCHAR(20) ,#所属公司
worksheet_num INTEGER, #机会点总数
sign_worksheet_num INTEGER ,#签单数量
exchange_num INTEGER ,#填写交流记录次数
sales_volume double(20,2) #销售额
) ;
begin

#定义 变量
declare a_id VARCHAR(64);
declare b_user_name VARCHAR(20);
declare c_company_name VARCHAR(20);
declare d_worksheet_num int ;
declare e_sign_worksheet_num int ;
declare f_exchange_num int ;
declare g_sales_volume double(20,2) ;

#这个用于处理游标到达最后一行的情况

DECLARE s int default 0;

#声明游标cursor_name（cursor_name是个多行结果集）

DECLARE cursor_name CURSOR FOR
select a.id ,a.name ,o.name AS company_name from sys_user a LEFT JOIN sys_office o on a.company_id =o.id;

#设置一个终止标记

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET s=1;

#打开游标

OPEN cursor_name;

#获取游标当前指针的记录，读取一行数据并传给变量a,b

fetch cursor_name into a_id,b_user_name ,c_company_name;
#开始循环，判断是否游标已经到达了最后作为循环条件

while s <> 1 do

#读取下一行的数据

#声明输入变量，以便在sql串中拼接

set @userId=a_id;
set @beginDate=begin_date;
set @endDate=end_date;

#收集总机会点 有if判断用拼接sql,然后再解析执行sql,
set @exesqlAll =CONCAT(‘SELECT count(1) into @handle_num FROM crm_worksheet

where create_by=@userId and del_flag=0 ‘);

IF begin_date is not null and begin_date !=” THEN
set @exesqlAll =CONCAT(@exesqlAll,’ AND TO_DAYS(create_date) >=TO_DAYS(@beginDate)’);
END IF;

IF end_date is not null and end_date !=” THEN
set @exesqlAll =CONCAT(@exesqlAll,’ AND TO_DAYS(create_date) <=TO_DAYS(@endDate)’);
END IF;

prepare allWorksheet from @exesqlAll; #解析sql
execute allWorksheet; #执行sql
DEALLOCATE prepare allWorksheet; #释放sql
SET d_worksheet_num = @handle_num; #变量赋值

IF d_worksheet_num > 0 THEN

set @exesqlSign =CONCAT(‘SELECT count(1) into @sign_num FROM crm_worksheet

where create_by=@userId and del_flag=0 and important_degree=”sys_basic_qian_shu_he_tong” ‘);

IF begin_date is not null and begin_date !=” THEN
set @exesqlSign =CONCAT(@exesqlSign,’ AND TO_DAYS(create_date) >=TO_DAYS(@beginDate)’);
END IF;

IF end_date is not null and end_date !=” THEN
set @exesqlSign =CONCAT(@exesqlSign,’ AND TO_DAYS(create_date) <=TO_DAYS(@endDate)’);
END IF;

prepare signWorksheet from @exesqlSign;
execute signWorksheet;
DEALLOCATE prepare signWorksheet;
SET e_sign_worksheet_num = @sign_num;

#收集交流次数
set @exesqlExchange =CONCAT(‘SELECT COUNT(1) into @exchange_num from crm_wkst_exchange_record e LEFT JOIN
crm_worksheet w on e.worksheet_no= w.worksheet_no where w.create_by=@userId and w.del_flag=0′);

IF begin_date is not null and begin_date !=” THEN
set @exesqlExchange =CONCAT(@exesqlExchange,’ AND TO_DAYS(w.create_date) >=TO_DAYS(@beginDate)’);
END IF;

IF end_date is not null and end_date !=” THEN
set @exesqlExchange =CONCAT(@exesqlExchange,’ AND TO_DAYS(w.create_date) <=TO_DAYS(@endDate)’);
END IF;

prepare exchangeWorksheet from @exesqlExchange;
execute exchangeWorksheet;
DEALLOCATE prepare exchangeWorksheet;
SET f_exchange_num = @exchange_num;

#收集销售额
set @exesqlSales =CONCAT(‘SELECT (@sumSalary := @sumSalary + solution) AS count into @sales_num FROM crm_worksheet cw , (SELECT @sumSalary := 0) b
WHERE cw.create_by=@userId and important_degree=”sys_basic_qian_shu_he_tong” and cw.del_flag=0 ORDER BY count desc limit 1 ‘);

IF begin_date is not null and begin_date !=” THEN
set @exesqlExchange =CONCAT(@exesqlExchange,’ AND TO_DAYS(cw.create_date) >=TO_DAYS(@beginDate)’);
END IF;

IF end_date is not null and end_date !=” THEN
set @exesqlExchange =CONCAT(@exesqlExchange,’ AND TO_DAYS(cw.create_date) <=TO_DAYS(@endDate)’);
END IF;

prepare salesWorksheet from @exesqlSales;
execute salesWorksheet;
DEALLOCATE prepare salesWorksheet;
SET g_sales_volume = @sales_num;

ELSE
SET e_sign_worksheet_num=0; SET f_exchange_num=0; SET g_sales_volume=0;

END IF;

insert into user_temporary(id,user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume)
values(a_id,b_user_name,c_company_name,e_sign_worksheet_num,f_exchange_num,d_worksheet_num,g_sales_volume); #插入临时表

fetch cursor_name into a_id,b_user_name ,c_company_name;

end while;

#关闭游标

CLOSE cursor_name ;

#从临时表中查出结果集
set @userIdInput=user_id;
set @exesqlResult =CONCAT(‘SELECT user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume from user_temporary’);

IF user_id is not null and user_id !=” THEN
set @exesqlResult =CONCAT(@exesqlResult,’ where id = @userIdInput’);
END IF;

prepare resultUser from @exesqlResult;
execute resultUser;
DEALLOCATE prepare resultUser;

#语句执行结束

end;

END