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