Chapter5 MySQL的存储过程、事务、游标、动态执行SQL
文章目录
Chapter5 MySQL的存储过程、事务、游标、动态执行SQL一、存储过程二、事务三、游标四、动态执行SQL
一、存储过程
1.简单
delimiter
create procedure p1
()
BEGIN
select * from student
;
insert into teacher
(tname
) values("ct");
END
delimiter;
使用：
call p1
()
2.传参数
(in,out,inout)
a
.in
delimiter
create procedure p2
(
in n1
int,
in n2
int
)
BEGIN
select * from student
where sid
>n1
;
END
delimiter ;
使用：
call p2
(12,2)
b
.inout
delimiter
create procedure p3
(
in n1
int,
inout n2
int
)
BEGIN
set n2
=123123;
select * from student
where sid
>n1
;
END
delimiter;
set @v1=10;
call p2
(12,@v1)
select @v1;
c
.out
delimiter
create procedure p3
(
in n1
int,
out n2
int
)
BEGIN
insert into vv
(..)
insert into vv
(..)
insert into vv
(..)
insert into vv
(..)
insert into vv
(..)
insert into vv
(..)
set n2
=1
END
delimiter;
二、事务
delimiter
create procedure p5
(
out p_return_code
tinyint
)
BEGIN
declare exit handler for sqlexception
BEGIN
set p_return_code
=1;
rollback;
END;
START TRANSACTION
delete from tb1
;
insert into tb2
(name
) values('seven');
COMMIT;
set p_return_code
=2;
END
delimiter;
三、游标
(A
) id num
(B
) id num
1 9 1 9+1
2 8 2 8+2
delimiter
create procedure p6
()
BEGIN
declare row_id
int;
declare row_num
int;
declare done
int default false;
declare temp int;
declare my_cursor
CURSOR for select id
,num
from A
;
declare continue handler for not found
set done
=true;
open my_cursor
;
xxoo:
LOOP
fetch mu_cursor
into row_id
,row_num
;
if done
then
leave xxoo
;
end if;
set temp = row_id
+ row_num
;
insert into B
(number
) values(temp);
end loop xxoo
;
close my_cursor
;
END
delimiter;
四、动态执行SQL
delimiter
create procedure p8
(
in nid
int
)
BEGIN
set @nid=nid
;
prepare prod
from 'select * from student where sid > ?';
execute prod
using @nid;
deallocate prepare prod
;
END
delimiter;
