|
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
|
#开山函数 hello world
delimiter $$
create
function
test()
returns
varchar
(20)
begin
return
'hello world!'
;
end
$$
delimiter ;
#小测试
delimiter $$
create
function
func1()
returns
varchar
(20)
begin
if
hour
(now())>=18
then
return
'late'
;
else
return
'zao'
;
end
if;
end
$$
delimiter ;
#全局变量
delimiter $$
create
function
func2()
returns
int
begin
set
@i = 1;
set
@
sum
= 0;
while @i<=10 do
set
@
sum
= @
sum
+ @i;
set
@i = @i + 1;
end
while;
return
@
sum
;
end
$$
delimiter ;
delimiter $$
create
function
func3()
returns
int
begin
set
@i = 1;
set
@
sum
= 0;
w:while @i<=10 do
if @i = 5
then
leave w;
end
if;
set
@
sum
= @
sum
+ @i;
set
@i = @i + 1;
end
while w;
return
@
sum
;
end
$$
delimiter ;
delimiter $$
create
function
func6()
returns
int
begin
set
@i = 0;
set
@
sum
= 0;
w:while @i<10 do
set
@i = @i + 1;
if @i = 5
then
iterate w;
end
if;
set
@
sum
= @
sum
+ @i;
end
while w;
return
@
sum
;
end
$$
delimiter ;
#参数方式，局部变量
delimiter $$
create
function
hello(
name
varchar
(10))
returns
varchar
(20)
begin
return
concat(
'hello'
,
name
);
end
$$
delimiter ;
#定义局部变量，注意mysql的跳出是需要指定循环的。
delimiter $$
create
function
func8()
returns
int
begin
declare
i
int
default
0;
declare
total
int
default
0;
w:while i<10 do
set
i = i + 1;
if i = 5
then
iterate w;
end
if;
set
total = total + i;
end
while w;
return
total;
end
$$
delimiter ;
#以下写一个存储过程，生成一张任意条记录的表。
#生成随机字符串
delimiter $$
create
function
rand_string(n
int
)
returns
varchar
(255)
begin
declare
chars_str
varchar
(100)
default
'abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
;
declare
return_str
varchar
(255)
default
''
;
declare
i
int
default
0;
while i<n do
set
return_str = concat(return_str,
substring
(chars_str,floor(1+rand()*52),1));
set
i = i + 1;
end
while;
return
return_str;
end
delimiter ;
#生成随机数字
delimiter $$
create
function
rand_num()
returns
int
(5)
begin
declare
i
int
default
0;
set
i = floor(10+rand()*500);
return
i;
end
$$
delimiter ;
#定义存储过程
delimiter $$
create
procedure
insert_emp(start
int
(10),max_num
int
(10))
begin
declare
i
int
default
0;
set
autocommit = 0;
repeat
set
i = i+1;
insert
into
emp
values
((start+i),rand_string(6),
'SALESMAN'
,0001,2000,400,rand_num(),curdate());
until i = max_num
end
repeat;
end
|
本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1288070，如需转载请自行联系原作者
