写这边文章的目的，是想结合mysql 存储过程+函数完成一个批量删除的功能吧…正好也好加深下对procedure和function的熟练操作吧…
表结构：
create table dept(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default “”,
loc varchar(13) not null default “”
)engine=innodb default charset=utf8;
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,/编号/
ename varchar(20) not null default “”,/姓名/
job varchar(9) not null default “”,/工作/
mgr mediumint unsigned not null default 0,/上级编号/
hiredate date not null,/入职时间/
sal decimal(7,2) not null, /薪水/
comm decimal(7,2) not null,/红利/
deptno mediumint unsigned not null default 0/部门编号/
)engine=innodb default charset=utf8;
执行以下命令查看mysql版本是否开启了函数功能
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;
创建函数(作用：生产随机字符串)：
delimiter
c
r
e
a
t
e
f
u
n
c
t
i
o
n
r
a
n
d
s
t
r
i
n
g
(
n
i
n
t
)
r
e
t
u
r
n
s
v
a
r
c
h
a
r
(
255
)
b
e
g
i
n
d
e
c
l
a
r
e
c
h
a
r
s
s
t
r
v
a
r
c
h
a
r
(
100
)
d
e
f
a
u
l
t
′
q
w
e
r
t
y
u
i
o
p
a
s
d
f
g
h
j
k
l
z
x
c
v
b
n
m
′
;
d
e
c
l
a
r
e
r
e
t
u
r
n
s
t
r
v
a
r
c
h
a
r
(
255
)
d
e
f
a
u
l
t
′
′
;
d
e
c
l
a
r
e
i
i
n
t
d
e
f
a
u
l
t
0
;
w
h
i
l
e
i
<
n
d
o
s
e
t
r
e
t
u
r
n
s
t
r
=
c
o
n
c
a
t
(
r
e
t
u
r
n
s
t
r
,
s
u
b
s
t
r
i
n
g
(
c
h
a
r
s
s
t
r
,
f
l
o
o
r
(
1
+
r
a
n
d
(
)
∗
52
)
,
1
)
)
;
s
e
t
i
=
i
+
1
;
e
n
d
w
h
i
l
e
;
r
e
t
u
r
n
r
e
t
u
r
n
s
t
r
;
e
n
d
create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm'; 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
createfunctionrandstring(nint)returnsvarchar(255)begindeclarecharsstrvarchar(100)default′qwertyuiopasdfghjklzxcvbnm′;declarereturnstrvarchar(255)default′′;declareiintdefault0;whilei<ndosetreturnstr=concat(returnstr,substring(charsstr,floor(1+rand()∗52),1));seti=i+1;endwhile;returnreturnstr;end
创建函数(作用：随机生产部门编号)：
delimiter
c
r
e
a
t
e
f
u
n
c
t
i
o
n
r
a
n
d
n
u
m
(
)
r
e
t
u
r
n
s
i
n
t
(
5
)
b
e
g
i
n
d
e
c
l
a
r
e
i
i
n
t
d
e
f
a
u
l
t
0
;
s
e
t
i
=
f
l
o
o
r
(
100
+
r
a
n
d
(
)
∗
10
)
;
r
e
t
u
r
n
i
;
e
n
d
create function rand_num() returns int(5) begin declare i int default 0; set i=floor(100+rand()*10); return i; end
createfunctionrandnum()returnsint(5)begindeclareiintdefault0;seti=floor(100+rand()∗10);returni;end
接着我们来创建存储过程：
1：创建往emp表中插入数据的存储过程
delimiter c r e a t e p r o c e d u r e i n s e r t e m p ( i n s t a r t i n t ( 10 ) , i n m a x n u m i n t ( 10 ) ) b e g i n d e c l a r e i i n t d e f a u l t 0 ; / ∗ 把 a u t o c o m m i t 设 置 成 0 ∗ / s e t a u t o c o m m i t = 0 ; r e p e a t s e t i = i + 1 ; i n s e r t i n t o e m p ( e m p n o , e n a m e , j o b , m g r , h i r e d a t e , s a l , c o m m , d e p t n o ) v a l u e s ( ( s t a r t + i ) , r a n d s t r i n g ( 6 ) , ′ S A L E S M A N ′ , 0001 , c u r d a t e ( ) , 2000 , 400 , r a n d n u m ( ) ) ; u n t i l i = m a x n u m e n d r e p e a t ; c o m m i t ; e n d create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 0; /*把autocommit设置成0*/ set autocommit= 0; repeat set i=i+1; insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); until i=max_num end repeat; commit; end createprocedureinsertemp(instartint(10),inmaxnumint(10))begindeclareiintdefault0;/∗把autocommit设置成0∗/setautocommit=0;repeatseti=i+1;insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values((start+i),randstring(6),′SALESMAN′,0001,curdate(),2000,400,randnum());untili=maxnumendrepeat;commit;end
2：创建往dept表中插入数据的存储过程：
delimiter
c
r
e
a
t
e
p
r
o
c
e
d
u
r
e
i
n
s
e
r
t
d
e
p
t
(
i
n
s
t
a
r
t
i
n
t
(
10
)
,
i
n
m
a
x
n
u
m
i
n
t
(
10
)
)
b
e
g
i
n
d
e
c
l
a
r
e
i
i
n
t
d
e
f
a
u
l
t
0
;
s
e
t
a
u
t
o
c
o
m
m
i
t
=
0
;
r
e
p
e
a
t
s
e
t
i
=
i
+
1
;
i
n
s
e
r
t
i
n
t
o
d
e
p
t
(
d
e
p
t
n
o
,
d
n
a
m
e
,
l
o
c
)
v
a
l
u
e
s
(
(
s
t
a
r
t
+
i
)
,
r
a
n
d
s
t
r
i
n
g
(
10
)
,
r
a
n
d
s
t
r
i
n
g
(
8
)
)
;
u
n
t
i
l
i
=
m
a
x
n
u
m
e
n
d
r
e
p
e
a
t
;
c
o
m
m
i
t
;
e
n
d
create procedure insert_dept(in start int(10),in max_num int(10)) begin declare i int default 0; set autocommit=0; repeat set i=i+1; insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8)); until i=max_num end repeat; commit; end
createprocedureinsertdept(instartint(10),inmaxnumint(10))begindeclareiintdefault0;setautocommit=0;repeatseti=i+1;insertintodept(deptno,dname,loc)values((start+i),randstring(10),randstring(8));untili=maxnumendrepeat;commit;end
开始执行：
call insert_dept(1,100);//从deptno为1起插入100条随机生成数据
call insert_emp(1001,50000);//从empno为1001起插入50000条随机生成数据
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
