分享程序员开发的那些事...
更新时间：2022-09-15 20:25:17
|
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 worlddelimiter $$create function test() returns varchar(20)
beginreturn 'hello world!';
end$$delimiter ;#小测试delimiter $$create function func1() returns varchar(20)
beginif hour(now())>=18 then
return 'late';
elsereturn 'zao';
end if;
end$$delimiter ;#全局变量delimiter $$create function func2() returns int
beginset @i = 1;
set @sum = 0;
while @i<=10 doset @sum = @sum + @i;
set @i = @i + 1;
end while;
return @sum;
end$$delimiter ;delimiter $$create function func3() returns int
beginset @i = 1;
set @sum = 0;
w:while @i<=10 doif @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
beginset @i = 0;
set @sum = 0;
w:while @i<10 doset @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)
beginreturn concat('hello',name);
end$$delimiter ;#定义局部变量，注意mysql的跳出是需要指定循环的。delimiter $$create function func8() returns int
begindeclare i int default 0;
declare total int default 0;
w:while i<10 doset 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)
begindeclare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i<n doset return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
enddelimiter ;#生成随机数字delimiter $$create function rand_num() returns int(5)
begindeclare 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))
begindeclare i int default 0;
set autocommit = 0;
repeatset i = i+1;
insert into emp values((start+i),rand_string(6),'SALESMAN',0001,2000,400,rand_num(),curdate());
until i = max_numend repeat;
end |
本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1288070，如需转载请自行联系原作者