首先要声明的是
1，游标的方法会很慢在mysql中，在oracle数据库中还可以，除非没有别的方法，一般不建议在mysql中使用游标，
2，不建议在mysql中拼接sql，会使存储过程显得很臃肿，可以使用or来动态判别传入的参数是否为空
|
1
2
|
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 = '') |
3，不建议使用临时表来存储多用户下经常查询的内容，比如报表
4，返回结果集更好的方法是直接链接多个表返回结果集即可，下面的示例虽然给以得到正确结果，
但代码臃肿，速度异常的慢，可以当个反面教材
5，优化后的存储过程：http://www.tuohang.net/article/254035.html
测试示例
|
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
|
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 |
注意类型 为存储过程 procedure 不是存储函数function
运行结果：
真实需求，查找出所有用建单情况
|
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
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 |
运行结果
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/zhaofuqiangmycomm/article/details/88651446
