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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
| USE school;
DELIMITER $$
CREATE PROCEDURE insert_student(
p_sno CHAR(10),
p_sname VARCHAR(50),
p_sex VARCHAR(1),
p_age INT,
p_dept VARCHAR(20)
)
BEGIN
# 声明变量info，以表明插入是否成功。
DECLARE info VARCHAR(20) DEFAULT '插入成功';
# 异常处理
DECLARE CONTINUE HANDLER FOR 1062 SET info='插入失败，不能插入重复的数据';
INSERT INTO student VALUES(p_sno,p_sname,p_sex,p_age,p_dept);
SELECT info;
END $$
DELIMITER ;
SET @sno='202006012';
SET @sname='魏延';
SET @ssex='男';
SET @sage=25;
SET @sdept='计算机系';
SET @sno='202006012';
SET @sname='甄宓';
SET @ssex='女';
SET @sage=32;
SET @sdept='汉语言系';
CALL insert_student(@sno,@sname,@ssex,@sage,@sdept);
SELECT * FROM student;
#系统变量，用户自定义变量，局部变量
select @@version;
#delimiter双$符号的作用
#插入一个相同学号的同学
#插入一个超出字符限制的数据
#区别和联系
#存储过程使用call,可返回好多输出变量，作为一句话调用
#函数不用call调用，写上就可以得到值，可以作为表达式一部分
#触发器在满足特定条件下才能触发
#游标
#创建存储过程
delimiter $$
create procedure proc01()
begin
select sname,sage from student;
end $$
delimiter ;
call proc01;
#定义局部变量: 用户自定义，在begin/end块中有效
#declare var_name type [default var_value];
delimiter $$
create procedure proc02()
begin
declare tname varchar(20) default "student's name";
-- declare tname varchar(5) default "student's name";
set tname = 'Yangyang';
select tname;
end $$
delimiter ;
call proc02();
#Error Code: 1406. Data too long for column 'tname' at row 1
drop procedure proc02;
#用户变量：用户自定义，不需要提前声明，使用及声明；可在begin/end块外有效(外部也可以使用用户变量)
# @var_name
delimiter $$
create procedure proc03()
begin
set @tname = 'potato';
select @tname;
end $$
delimiter ;
call proc03();
select @tname;
#系统变量分为全局变量和会话变量
# @@global.var_name
#全局变量在MYSQL启动的时候由服务器自动将他的初始化为默认值
#会话变量在每次建立一个新的连接的时候,由MYSQL来初始化,MYSQL会将当前所有全局变量的值复制一份,来作为会话变量
#全局变量的修改会影响整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)
#可以更改的系统变量,我们可以利用set语句进行更改
show global variables;-- 查看全局变量
select @@sort_buffer_size;-- 查看某全局变量
set @@sort_buffer_size = 50000;-- 修改全局变量的值
set global sort_buffer_size = 100000;
select @@sort_buffer_size;
#存储过程传参
#in表示传入的参数,可以传入数值或者变量,即使传入变量,并不会改变变量的值,可以内部更改,
#仅仅作用在函数范围内
delimiter $$
create procedure proc04(in miniage int,in maxage int)
begin
select * from student where sage>miniage and sage<maxage;
end $$
delimiter ;
drop procedure proc04;
call proc04(22,30);
#out表示从存储过程内部传值给调用者
delimiter $$
create procedure proc05(in in_sage int, out out_sname varchar(45))
begin
select sname into out_sname from student where sage=in_sage;
end $$
delimiter ;
call proc05(22,@out_res);
select @out_res;
#inout表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入变量的值
#也可以修改变量的值(即使函数执行完)
delimiter $$
create procedure proc06(inout num int)
begin
set num = num*10;
end $$
delimiter ;
set @inout_num = 2;
call proc06(@inout_num);
select @inout_num;
#流程控制-判断
#if...then...;
#elseif...then...;
#end if;
#判断IF语句包含多个条件判断,根据结果为TURE和FALSE执行语句,与编程语言中的if、else if、
#else语法类似
delimiter $$
create procedure proc07(in score int)
begin
if score<60 then select '不及格';
elseif score>=60 and score<80 then select '及格';
elseif score>=80 and score<90 then select '良好';
elseif score>=90 and score<=100 then select '优秀';
else select '成绩错误！';
end if;
end $$
delimiter ;
set @score = 79;
call proc07(@score);
delimiter $$
create procedure proc08(in in_name varchar(45))
begin
declare var_float decimal(7,2); -- 定义局部变量，7个长度，2个小数
declare res varchar(20);
select sage into var_float from student where sname=in_name;
if var_float>25 then set res='毕业了';
else set res='在上学';
end if;
select res;
end $$
delimiter ;
call proc08('刘备');
delimiter $$
create procedure proc07(in score int)
begin
declare res varchar(45);
if score<60 then set res= '不及格';
elseif score>=60 and score<=100 then set res= '及格';
else set res= '成绩错误';
end if;
select res as '成绩结果';
end $$
delimiter ;
drop procedure proc07;
set @score=55;
call proc07(@score);
#流程控制-case
#case...
#when...then...;
#else...;
#end case;
#CASE是另一个条件判断语句,类似于编程语言中的switch语法
#语法一:当case后面的语句与when后面的语句相等时,则会执行then后面的语句,如果均不匹配则执行else
#语法二:直接写case 当when后面的条件成立则执行then后面的语句,如果都不成立,则执行else
delimiter $$
create procedure proc08(in food varchar(45),in pay_type int)
begin
case '面包'
when 1 then select '微信支付';
when 2 then select '支付宝支付';
else select '其他支付方式';
end case;
case '饮料'
when 1 then select '微信支付';
when 2 then select '支付宝支付';
else select '其他支付方式';
end case;
end $$
delimiter ;
call proc08('饮料',2);
#控制流程-循环
#循环分类：while、repeate、loop
#循环控制：leave 类似于break,跳出,结束当前所在的循环
# iteater 类似于continue,继续,结束本次循环,继续下一次
#label:while...do
#...
#if...then leave label;
#end while lable;
delimiter $$
create procedure proc09(in insertCount int)
begin
declare i int default 1;
label:while i<=insertCount do
insert into student(sno,sname) values(i,concat('user-',i));
set i=i+1;
end while label;
end $$
delimiter ;
drop procedure proc09;
call proc09(10);
delimiter $$
create procedure proc10(in delCount int)
begin
declare i int default 1;
label:while i<=delCount do
delete from student where sno=i;
if i=5 then leave label;
end if;
set i = i+1;
end while label;
end $$
delimiter ;
drop procedure proc10;
call proc10(10);
#Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'sno' To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
#因为MySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令。可以通过以下SQL进行状态查询
show variables like 'SQL_SAFE_UPDATES';
SET SQL_SAFE_UPDATES = 0;
#label:repeat
#...;
#until 条件表达式;
#end repeat label;
delimiter $$
create procedure proc11(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into student(sno,sname) values(i,concat('user-',i));
set i=i+1;
until i>insertCount
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc11(10);
#label:loop
# ...
# if 条件表达式 then leave label;
# end if;
# end loop label;
delimiter $$
create procedure proc12(in delCount int)
begin
declare i int default 1;
label:loop
delete from student where sno=i;
set i=i+1;
if i>delCount then leave label;
end if;
end loop label;
end $$
delimiter ;
drop procedure proc12;
call proc12(10);
#调用储存过程显示Error Code: 1305. PROCEDURE school.proc12 does not exist
#1.首先考虑是否是此储存过程当真不在，查看当前存储过程，发现存储过程存在：
#show procedure status;
# 2.此时想到是用户没有当前存储过程的调用权限，赋予存储过程权限给当前用户，此时又出现了一个错误
#GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
#ERROR 1133 (42000): Can't find any matching row in the user table
#通过查询资料发现，需要先将当前的存储过程刷新，再执行授权语句：
#FLUSH PRIVILEGES;
#GRANT ALL PRIVILEGES ON test.* TO 'root'@localhost;
#3.再执行调用此存储过程，显示成功调用
|
