mysql存储过程 返回 list结果集
思路
直接链接多个表返回结果集即可，先写成普通的sql调整好,不要输入参数，再写成存储过程,
不要用游标，否则会把你慢哭的
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
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) TTALEFT 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) )) TTBON ( 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_numFROM( 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_numFROM(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 WorkSumFROM(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) |
mysql存储过程和存储函数练习
存储过程和存储函数语法
- 创建存储过程
|
1
2
3
4
5
6
7
8
9
10
|
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 模式 |
- 删除存储过程
|
1
|
drop procedure p1 （并且只能一次删除一个存储过程） |
- 更改存储过程
不能直接修改存储过程，只能先删除该过程，在重新创建该过程
- 调用存储过程
|
1
|
call p1('s1') |
注意：因为是in 模式所有可以传入常量，如果是inout模式
不可以传入常量，而必须是变量。
- 查看存储过程
|
1
|
show create procedure p1 |
存储函数
如果是第一次在mysql上创建存储函数，可能会发生SQL错误[1418][HY000]:
This function has none of DETERMINISTIC ,NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you “might” want to use the less safe_log_bin_trust_function_creators variable)。
此时的mysql 服务器开启了二进制日志选项，这种模式是默认禁止创建存储函数的。
使用下列命令选择开启就可以了。
- 创建存储函数
|
1
2
3
4
5
6
7
|
create function 函数名(参数列表) returns 返回类型begin 函数体 end1、参数列表 （参数名 参数类型）2、 函数体肯定有return 语句 |
- 调用存储函数
|
1
|
select 函数名（参数列表） |
案例演示
有一简易电子商务网站，其平台数据库中部分表及其结构如下：
（1）用户表：tUser（用户账号varchar(10)，用户名称varchar(50)，登陆密码varchar(20)，联系电话varchar(20)，邮件地址varchar(100)，已购商品总额numeric(10,2)，送货地址varchar(100)）
（2）用户登录历史记录表：tUserHisRec（登录序号int identity(1,1)，用户账号varchar(10)，登录时间datetime）
（3）商品列表：productsList（商品编号varchar(20)，商品名称varchar(100)，商品类别varchar(100)，商品价格numeric(10,2)，出厂日期datetiem，生产商varchar(200)）
（4）用户购买商品记录：tUserOrder（登陆序号int identity(1,1)，用户账号varchar(10)，商品编号varchar(20)，购买时间datetime，送货地址varchar(100)）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
-- 用户表：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)) |
1 创建商品检索存储过程procBrowProduct (关联子查询)
要求：输入商品名称或商品类别（要求模糊查询），输出商品编号，商品名称，商品价格，出厂日期，生产商，已购用户数量，最近一次购买用户姓名，最近一次购买时间
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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 |
分析：
输入的商品名称和商品类别是为了找到对应商品编号，有了商品编号就可以输出一系列信息，对应已购用户数量和最近一次购买用户姓名，和最近一次购买时间，分别使用三个子查询，而子查询与外层查询联系就是a.productsListnum作为限制条件，及关联子查询
关联子查询
给出一个简单例子引出关联子查询：查询低于相同职位平均工资的员工信息
table staff(staff_num,staff_depart,staff_salary)
|
1
2
3
|
select staff_num from staff a where staff_salary<(select avg(staff_salary from staff where staff_depart=a.staff_deapart) |
查询首先会从最外层select * from staff
在将每一行结果传递给子查询，传入第一行结果就是select staff_num from staff where staff_salary<(select avg(staff_salary from staff where staff_depart=‘经理’))，然后子查询的结果又返回给外层查询select staff_num from staff where staff_salary<9666。逐行逐行，直到结束。
关联子查询，外层与内层查询是信息是双向传递的。
2 创建商品检索存储过程t1
要求：输入商品名称或商品类别（要求模糊查询），输出商品编号，商品名称，商品价格，出厂日期，生产商，已购用户数量，最近一次购买用户姓名，最近一次购买时间
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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 |
这个比较简单。
3 创建用户自定义函数：varchar fGetUserMaxProduct(用户账号)（在查询结果子表中在进行查询）
要求：输入用户账号，返回该用户购买最多的商品编号。
|
1
2
3
4
5
6
7
8
9
|
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 |
分析：
将查询结果设成别名为t1 的表，再重t1的表中，在继续查询。
查询结果子表中，再查询
例如：
|
1
|
select a from (select a,b,c from table1 where…)as t1 |
从t1结果表中查询a
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/zhaofuqiangmycomm/article/details/89706306
