mysql存储过程 返回 list结果集方式_Mysql_

 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
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：使用MySQL实现select into临时表的功能_Mysql_
                                                                
下一篇：MySQL 中这么多索引该怎么选择_Mysql_