mysql存储过程 返回 list结果集

 
BEGIN
	DECLARE In_StartTime  VARCHAR(64);
	DECLARE In_StopTime   VARCHAR(64);
	DECLARE IN_User_id    VARCHAR(64);
	DECLARE IN_work_no    VARCHAR(20);
	DECLARE IN_Office_id  VARCHAR(64);
	DECLARE IN_Cus_source VARCHAR(100);
 
 
if in_begindate is NULL or in_begindate ='' THEN
	#set In_StartTime = timestamp(date_add(curdate(), interval - day(curdate()) + 1 day));##默认查询开始时间为当月1日
set In_StartTime = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00');##默认查询开始时间为昨天开始
ELSE
	SET In_StartTime = in_begindate;
END IF;
 
if in_enddate is NULL or in_enddate ='' THEN
	set In_StopTime = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59');##默认查询结束时间为昨天时间
ELSE
	SET In_StopTime = in_enddate;
END IF;
 
 
 
SET IN_User_id = in_userid;
SET IN_work_no =in_user_work_no;
SET IN_Office_id = in_offid;
SET IN_Cus_source = in_custsouce;
 
 
	select DTA.*,
(case when DTB.callcount is  NULL then 0 ELSE DTB.callcount  END) as all_call_num,	##总呼出量
(case when DTB.calllens is  NULL then 0 ELSE
	concat(concat(DTB.calllens div 3600,'时'),concat(DTB.calllens mod 3600 div 60,'分'),concat(DTB.calllens mod 3600 mod 60 mod 60,'秒'))
 END) as all_call_time,		##总呼出时长
 
(case when DTB.effcount is  NULL then 0 ELSE DTB.effcount END) as eff_call_num,			##有效呼出量
(case when DTB.effcountlens is  NULL then 0 ELSE 
	concat(concat(DTB.effcountlens div 3600,'时'),concat(DTB.effcountlens mod 3600 div 60,'分'),concat(DTB.effcountlens mod 3600 mod 60 mod 60,'秒'))
END) as eff_call_time,## 有效呼出时长
 
(case when DTC.exchange_num is  NULL then 0 ELSE DTC.exchange_num END) as exchange_num,## 交流次数
(case when DTD.summary_num is  NULL then 0 ELSE DTD.summary_num END) as summary_num, ## 总结次数 
(case when DTE.WorkCount is  NULL then 0 ELSE DTE.WorkCount END) as worksheet_num, ## 总机会点数
(case when DTE.WorkQDCount is  NULL then 0 ELSE DTE.WorkQDCount END) as sign_worksheet_num,##签单机会点数
 
 
(CASE WHEN DTE.WorkQDCount = 0 	then 0
			WHEN DTE.WorkCount   = 0 	then 0
			when   DTE.WorkCount  is NULL  then 0
			when  DTE.WorkQDCount is NULL  then 0
 
 ELSE  concat((ROUND( (IFNULL(DTE.WorkQDCount,0)/DTE.WorkCount),2)  *100 ),'%')
 END ) as WorkRate, ## 成功率
 
(case when DTE.WorkSum  is  NULL then 0 ELSE DTE.WorkSum  END) as sales_volume ##成交总金额
 
FROM
(
 
 
## 公司相关信息和人员账号
SELECT TTA.office_id,TTA.company,user_id,work_no,user_name from 
(SELECT id as office_id, name as company FROM sys_office WHERE del_flag=0) TTA
LEFT OUTER JOIN 
(SELECT id as user_id ,work_no, name as user_name,office_id from sys_user where del_flag=0 and   
	(is_disabled ='1'  or is_disabled='' or ISNULL(is_disabled) )
) TTB
ON ( TTA.office_id =TTB.office_id) WHERE 
		( TTB.user_id=IN_User_id or  IN_User_id is null  or IN_User_id = '') 
and ( TTB.work_no=IN_work_no or  IN_work_no is null  or IN_work_no = '') 
 
and  ( TTB.office_id=IN_Office_id or  IN_Office_id is null  or IN_Office_id = '')
) DTA 
LEFT OUTER JOIN 
 
 
(##通话相关次数及时长，有效通话次数及时长
SELECT TA.agent_id ,
SUM(callcount) as callcount,
SUM(calllens) as calllens,
SUM(effcount) as effcount,
SUM(effcountlens) as effcountlens
 from 
(select agent_id,
		 (case when (agent_id is NOT NULL or times  is NOT NULL)  then 1 ELSE 0 END) as callcount,
		(case when (agent_id is NOT NULL  or times  is NOT NULL )then times else 0 end) as calllens,
		(case when  times >=30 then 1 else 0 end) as effcount,
		(case when  times >=30 then times else 0 end) as effcountlens
 
 
 from  crm_hw_call where call_begintime >=In_StartTime  and STR_TO_DATE(call_begintime,'%Y-%m-%d %H:%i:%s')<=In_StopTime
 
) TA GROUP BY TA.agent_id
 
 
 
 
) DTB on (DTA.work_no=DTB.agent_id)
LEFT OUTER JOIN 
(## 交流次数
SELECT TC.create_by,IFNULL(SUM(exchange_num),0) as exchange_num
FROM
(
 
SELECT wk.create_by, wk.user_type,
 (	CASE WHEN ex.create_by is not NULL then 1 else 0 end) as  exchange_num 
 from crm_worksheet wk  LEFT OUTER JOIN  crm_wkst_exchange_record ex on wk.worksheet_no=ex.worksheet_no
	where  ex.create_by is not NULL and ex.del_flag=0 and wk.del_flag=0
	and ( wk.user_type ='sys_basic_hua_wei' or  'sys_basic_hua_wei' is null  or 'sys_basic_hua_wei' = '')
	and ex.create_date >=In_StartTime  and ex.create_date<=In_StopTime
 ) TC
			GROUP BY TC.create_by
		) DTC on (DTA.user_id = DTC.create_by)
LEFT OUTER JOIN
 
 
(## 总结次数
SELECT TD.create_by,SUM(TD.summary_num) as summary_num
FROM
(SELECT create_by, 
(CASE WHEN create_by is not NULL then 1 else 0 end) as summary_num 
FROM crm_day_report  where del_flag=0 
	and create_date >=In_StartTime  and create_date<=In_StopTime
) TD GROUP BY TD.create_by
) DTD on (DTA.user_id = DTD.create_by)
LEFT OUTER JOIN
 
 
 
## 签单次数，工单总数，签单总额
(SELECT 
	TE.create_by,
	SUM(WorkCount) AS WorkCount,
	SUM(WorkQDCount) AS WorkQDCount,
	SUM(WorkSum) AS WorkSum
FROM
(SELECT create_by,
(CASE WHEN create_by is not null THEN 1 else 0 end ) as WorkCount,
(CASE WHEN create_by is not null and important_degree='sys_basic_qian_shu_he_tong' THEN 1 else 0 end ) as WorkQDCount,
(CASE WHEN create_by is not null and important_degree='sys_basic_qian_shu_he_tong' THEN IFNULL(solution,0) else 0 end ) as WorkSum
 from crm_worksheet where del_flag=0  
	and ( user_type =IN_Cus_source or  IN_Cus_source is null  or IN_Cus_source = '')
	and create_date >=In_StartTime  and create_date<=In_StopTime
 
) TE  GROUP BY TE.create_by
) DTE ON (DTA.user_id = DTE.create_by);
  SELECT  IN_work_no,in_user_work_no;
 
END
##输入参数
in_begindate varchar(64),in_enddate varchar(64),in_userid varchar(64),in_offid varchar(64), in_custsouce  varchar(100),in_user_work_no varchar(20)
 

create procedure p1(存储名)（in suppiler_num varchar(10)）
begin 
   		declare results int default 0;  #声明变量并初始化
   		select count(*) into results    #赋值
   		from factory.suppiler
    	where factory.suppiler.suppiler_num=suppiler_num;
   	    select if (results>0,'成功','失败' );  #使用
end
（参数列表: 模式  参数名  参数类型）
     模式 in(默认)、out、 inout：in 模式；out 模式；inout 模式

drop procedure p1 （并且只能一次删除一个存储过程）

call p1('s1')

  show create procedure p1

create function 函数名(参数列表) returns 返回类型
begin
   	函数体
   	
end
1、参数列表 （参数名 参数类型）
2、 函数体肯定有return 语句

select 函数名（参数列表）

-- 用户表：
Create table  tUser  
(      /*用户账号*/ tuser_num varchar(10), 
     /* 用户名称 */tuser_name  varchar(50),
      /*登陆密码 */ tuser_password  varchar(20),
     /* 联系电话 */ tuser_tel  varchar(20),
     /* 邮箱地址 */tuser_maill  varchar(100),
      /*已购商品总额 */ tuser_sum_expence  numeric(10,2),
      /*送货地址 */ tuser_addr   varchar(100)
)
--用户登录历史记录表：
Create table tUserHisRec (
        /*登录序号*/ tuserhisrec_row int auto_increment,
        /*用户账号*/tuserhisrec_num varchar(10),
        /*登录时间*/tuserhisrec_time datetime,
         primary key (tuserhisrec_row) #自增必须要设键
)
--商品列表：
Create table productsList(
        /*商品编号*/ productsList_num varchar(20),
        /*商品名称*/ productsList_name  varchar(100),
        /*商品类别 */productsList_kind varchar(100),
        /*商品价格 */productsList_price numeric(10,2),
        /*出厂日期 */productsList_date datetime, 
        /*生产商 */productsList_suppiler varchar(200)
)
--用户购买商品记录：
Create table tUserOrder(
   	/*登陆序号*/ tuserorder_row int auto_increment,
   	/*用户账号 */tuser_num varchar(10),
   	/*商品编号 */productsList_num varchar(20),
   	/*购买时间 */tuserorder_time datetime,
   	/*送货地址 */tuser_addr varchar(100),
   	primary key (tuserorder_row)
)

create procedure t2(in products_name varchar(100),in products_kind varchar(100))
begin
   select *,(select count(distinct tuser_num) from tUserOrder
    where `productsList_num`=a.productsList_num) as '已购用户数量',
   (select tuser_num  from factory.tuserorder 
   where tuserorder_time=(select max(tuserorder_time) 
   from factory.tuserorder 
   where `productsList_num`=a.productsList_num
    group by a.productsList_num)) as '最近一次购买的用户',
   (select max(tuserorder_time) from factory.tuserorder 
   where `productsList_num`=a.productsList_num
    group by a.productsList_num)as '最近一次购买的时间' 
   from factory.productslist  a where `productsList_num` in 
   (select productsList_num  from factory.productslist 
   where `productsList_name` like concat('%',products_name,'%')
   and `productsList_kind` like concat('%',products_kind,'%'));
end

		select staff_num from staff a where staff_salary<(
		select avg(staff_salary from staff 
		where staff_depart=a.staff_deapart)

create procedure t1(in use_num varcharacter(10),in product_num varcharacter(20))
begin
   declare tuser_addr1 varchar(100) ; 
   declare productsList_price1 numeric(10,2);
   select  tuser_addr into tuser_addr1
   from tUser 
   where tUser.tuser_num=use_num;
   select productsList_price into productsList_price1
   from factory.productsList
   where productsList_num=product_num;
   update tUser
  set tuser_sum_expence=tuser_sum_expence+productsList_price1 
   where tuser_num=use_num;
   insert into factory.tuserOrde(tuser_num,productsList_num,tuserorder_time,tuser_addr)
  values(use_num,product_num,now(),tuser_addr1);
end

create function t3(user_num varchar(10)) returns varchar(20) 
begin
   declare products_num varchar(20);
   select `productsList_num` into products_num from  
   (select count(`productsList_num`)as c1,`productsList_num` 
    from factory.tuserorder   where tuser_num=user_num
    group by `productsList_num`)as t1  having max(c1);
   return products_num;
end

select a from (select a,b,c from table1 where…)as t1