mysql存储过程: 封装sql:
显示存储过程信息: \G (横向表格采用纵向表格输出)
delimiter $ 改变执行符号,直到mysql碰到$ 开始执行语句命令 set names 解决mysql乱码问题 但mysql重启后又还原到以前字符集状态
call 存储过程名字 () 调用存储过程
参数:
控制结构:
1
2
3
4
5
|
create
procedure
p1()
begin
select
*
from
t_news;
end
$ //mysql存储过程 简单实例
|
![](http://www.2cto.com/uploadfile/Collfiles/20140821/201408211031271.jpg)
delimiter $ 改变执行符号,直到mysql碰到$ 开始执行语句命令 set names 解决mysql乱码问题 但mysql重启后又还原到以前字符集状态
call 存储过程名字 () 调用存储过程
参数:
1
2
3
4
|
create
procedure
p2(n
int
) #含参
begin
select
*
from
t_category
where
cid > n;
end
$
|
![](http://www.2cto.com/uploadfile/Collfiles/20140821/201408211031284.jpg)
控制结构:
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
|
create
procedure
p3(n
int
, j
char
(1)) #含参
begin
if j=
'h'
then
#与其他语言不同 必须加
then
注意符号= 不是==
select
*
from
t_category
where
cid > n;
else
select
*
from
t_category
where
cid <n;
end
=
""
pre=
""
>
<img alt=
""
src=
"/uploadfile/Collfiles/20140821/201408211031296.jpg"
style=
"width: 462px; height: 365px;"
>
实例一
计算1....n的和:<pre class=
"brush:sql;"
>
create
procedure
p4(n
smallint
) #含参
begin
declare
i
int
;
declare
s
int
;
set
s = 0;
set
i = 1;
while i<=n do
set
s =s+i;
set
i=i+1;
end
while;
select
s;
end
$ </pre>
<img alt=
""
src=
"/uploadfile/Collfiles/20140821/201408211031297.jpg"
style=
"width: 492px; height: 373px;"
><br>
<br>
存储过程和函数的区别: 名称不同 :存储过程:
procedure
函数
function
存储过程没有返回值。<br>
实例二<br>
一个简单存储过程游标实例<br>
DELIMITER $$<br>
DROP
PROCEDURE
IF EXISTS getUserInfo $$<br>
CREATE
PROCEDURE
getUserInfo(
in
date_day datetime)<br>
--<br>
-- 实例<br>
-- 存储过程名为:getUserInfo<br>
-- 参数为:date_day日期格式:2008-03-08<br>
--<br>
BEGIN
<br>
declare
_userName
varchar
(12);
-- 用户名<br>
declare
_chinese
int
;
-- 语文<br>
declare
_math
int
;
-- 数学<br>
declare
done
int
;<br>
-- 定义游标<br>
DECLARE
rs_cursor
CURSOR
FOR
SELECT
username,chinese,math
from
userInfo
where
datediff(createDate, date_day)=0;<br>
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done=1;<br>
-- 获取昨天的日期<br>
if date_day
is
null
then
<br>
set
date_day = date_add(now(),interval -1
day
);<br>
end
if;<br>
open
rs_cursor;<br>
cursor_loop:loop<br>
FETCH
rs_cursor
into
_userName, _chinese, _math;
-- 取数据<br>
<br>
if done=1
then
<br>
leave cursor_loop;<br>
end
if;<br>
-- 更新表<br>
update
infoSum
set
total=_chinese+_math
where
UserName=_userName;<br>
end
loop cursor_loop;<br>
close
rs_cursor;
END
$$<br>
DELIMITER ;<br>
实例三<br>
存储过程游标循环跳出现<br>
在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.<br>
1.REPEAT<br>
REPEAT<br>
Statements;<br>
UNTIL expression<br>
END
REPEAT<br>
demo<br>
DECLARE
num
INT
;<br>
DECLARE
my_string
VARCHAR
(255);<br>
REPEAT<br>
SET
my_string =CONCAT(my_string,num,
','
);<br>
SET
num = num +1;<br>
UNTIL num <5<br>
END
REPEAT;<br>
2.WHILE<br>
WHILE expression DO<br>
Statements;<br>
END
WHILE<br>
demo<br>
DECLARE
num
INT
;<br>
DECLARE
my_string
VARCHAR
(255);<br>
SET
num =1;<br>
SET
str =
''
;<br>
WHILE num < span>10DO<br>
SET
my_string =CONCAT(my_string,num,
','
);<br>
SET
num = num +1;<br>
END
WHILE;<br>
3.LOOP(这里面有非常重要的ITERATE,LEAVE)<br>
代码如下 复制代码<br>
DECLARE
num
INT
;<br>
DECLARE
str
VARCHAR
(255);<br>
SET
num =1;<br>
SET
my_string =
''
;<br>
loop_label: LOOP<br>
IF num <10THEN<br>
LEAVE loop_label;<br>
ENDIF;<br>
SET
num = num +1;<br>
IF(num mod3)
THEN
<br>
ITERATE loop_label;<br>
ELSE
<br>
SET
my_string =CONCAT(my_string,num,
','
);<br>
ENDIF;<br>
END
LOOP;<br>
PS:可以这样理解ITERATE就是我们程序中常用的contiune,而ITERATE就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.<br>
实例四<br>
mysql 存储过程中使用多游标<br>
先创建一张表,插入一些测试数据:<br>
DROP
TABLE
IF EXISTS netingcn_proc_test;<br>
CREATE
TABLE
`netingcn_proc_test` (<br>
`id`
INTEGER
(11)
NOT
NULL
AUTO_INCREMENT,<br>
`
name
`
VARCHAR
(20),<br>
`
password
`
VARCHAR
(20),<br>
PRIMARY
KEY
(`id`)<br>
)ENGINE=InnoDB;<br>
insert
into
netingcn_proc_test(
name
,
password
)
values
<br>
(
'procedure1'
,
'pass1'
),<br>
(
'procedure2'
,
'pass2'
),<br>
(
'procedure3'
,
'pass3'
),<br>
(
'procedure4'
,
'pass4'
);下面就是一个简单存储过程的例子:<br>
drop
procedure
IF EXISTS test_proc;<br>
delimiter //<br>
create
procedure
test_proc()<br>
begin
<br>
-- 声明一个标志done, 用来判断游标是否遍历完成<br>
DECLARE
done
INT
DEFAULT
0;<br>
-- 声明一个变量,用来存放从游标中提取的数据<br>
-- 特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL<br>
DECLARE
tname
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
tpass
varchar
(50)
DEFAULT
NULL
;<br>
-- 声明游标对应的 SQL 语句<br>
DECLARE
cur
CURSOR
FOR
<br>
select
name
,
password
from
netingcn_proc_test;<br>
-- 在游标循环到最后会将 done 设置为 1<br>
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done = 1;<br>
-- 执行查询<br>
open
cur;<br>
-- 遍历游标每一行<br>
REPEAT<br>
-- 把一行的信息存放在对应的变量中<br>
FETCH
cur
INTO
tname, tpass;<br>
if
not
done
then
<br>
-- 这里就可以使用 tname, tpass 对应的信息了<br>
select
tname, tpass;<br>
end
if;<br>
UNTIL done
END
REPEAT;<br>
CLOSE
cur;<br>
end
<br>
//<br>
delimiter ;<br>
-- 执行存储过程<br>
call test_proc();<br>
需要注意的是变量的声明、游标的声明和HANDLER声明的顺序不能搞错,<br>
必须是先声明变量,再申明游标,最后声明HANDLER。<br>
上述存储过程的例子中 只使用了一个游标,那么如果要使用两个或者更多游标怎么办,其实很简单,<br>
可以这么说,一个怎么用两个就是怎么用的。例子如下:<br>
drop
procedure
IF EXISTS test_proc_1;<br>
delimiter //<br>
create
procedure
test_proc_1()<br>
begin
<br>
DECLARE
done
INT
DEFAULT
0;<br>
DECLARE
tid
int
(11)
DEFAULT
0;<br>
DECLARE
tname
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
tpass
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
cur_1
CURSOR
FOR
<br>
select
name
,
password
from
netingcn_proc_test;<br>
DECLARE
cur_2
CURSOR
FOR
<br>
select
id,
name
from
netingcn_proc_test;<br>
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done = 1;<br>
open
cur_1;<br>
REPEAT<br>
FETCH
cur_1
INTO
tname, tpass;<br>
if
not
done
then
<br>
select
tname, tpass;<br>
end
if;<br>
UNTIL done
END
REPEAT;<br>
CLOSE
cur_1;<br>
-- 注意这里,一定要重置done的值为 0<br>
set
done = 0;<br>
open
cur_2;<br>
REPEAT<br>
FETCH
cur_2
INTO
tid, tname;<br>
if
not
done
then
<br>
select
tid, tname;<br>
end
if;<br>
UNTIL done
END
REPEAT;<br>
CLOSE
cur_2;<br>
end
<br>
//<br>
delimiter ;<br>
call test_proc_1();<br>
上述代码和第一个例子中基本一样,就是多了一个游标声明和遍历游标。<br>
这里需要注意的是,在遍历第二个游标前使用了
set
done = 0,<br>
因为当第一个游标遍历玩后其值被handler设置为1了,如果不用
set
把它设置为 0 ,<br>
那么第二个游标就不会遍历了。当然好习惯是在每个打开游标的操作前都用该语句,确保游标能真正遍历。<br>
当然还可以使用
begin
语句块嵌套的方式来处理多个游标,<br>
例如:<br>
drop
procedure
IF EXISTS test_proc_2;<br>
delimiter //<br>
create
procedure
test_proc_2()<br>
begin
<br>
DECLARE
done
INT
DEFAULT
0;<br>
DECLARE
tname
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
tpass
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
cur_1
CURSOR
FOR
<br>
select
name
,
password
from
netingcn_proc_test;<br>
DECLARE
cur_2
CURSOR
FOR
<br>
select
id,
name
from
netingcn_proc_test;<br>
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done = 1;<br>
open
cur_1;<br>
REPEAT<br>
FETCH
cur_1
INTO
tname, tpass;<br>
if
not
done
then
<br>
select
tname, tpass;<br>
end
if;<br>
UNTIL done
END
REPEAT;<br>
CLOSE
cur_1;<br>
begin
<br>
DECLARE
done
INT
DEFAULT
0;<br>
DECLARE
tid
int
(11)
DEFAULT
0;<br>
DECLARE
tname
varchar
(50)
DEFAULT
NULL
;<br>
DECLARE
cur_2
CURSOR
FOR
<br>
select
id,
name
from
netingcn_proc_test;<br>
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done = 1;<br>
open
cur_2;<br>
REPEAT<br>
FETCH
cur_2
INTO
tid, tname;<br>
if
not
done
then
<br>
select
tid, tname;<br>
end
if;<br>
UNTIL done
END
REPEAT;<br>
CLOSE
cur_2;<br>
end
;<br>
end
<br>
//<br>
delimiter ;<br>
call test_proc_2();</n;><br>
<br>
<br>
|