存储过程
概念
保存在MySQL数据库上的 -> 一坨SQL语句
方式一:
mysql:存储过程
程序员:调用存储过程名
方式二:
mysql:.....
程序员:自己写代码实现(SQL语句)
方式三:
mysql:.....
程序员:创建类和对象(SQL语句)
1. 简单
create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("程序猿");
END
在pymysql中
call p1()
cursor.callproc('p1')
2. 传参数(in,out,inout)
参数in
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from student where sid > n1;
END //
delimiter ;
call p2(10,2)
在pymysql中
cursor.callproc('p2',(10,2))
参数 out
delimiter //
create procedure p3(
in n1 int,
out n2 int
)
BEGIN
set n2 = 10086;
select * from student where sid > n1;
END //
delimiter ;
set @v1 = 10; 相当于session 传进来的值10拿不到,拿到的是存储过程中的,但是在pymysql中调用callproc()时,可以将两个数值(10,10086)都可以拿到,用来验证存储过程是否正确;
call p3(12,@v1)
select @v1;
在pymysql中
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1') 传进来的值@_p3_1拿不到,拿到的是存储过程中的10086
r2 = cursor.fetchall()
print(r2)
=======> 特殊
a. 可传参:in out inout
b.无返回值, 通过out伪造返回值
c. pymysql
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
为什么有结果集又有out伪造的返回值?
delimiter //
create procedure p3(
in n1 int,
out n2 int 用于标识存储过程的执行结果 1,2
)
BEGIN
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
END //
delimiter ;
3.事务 -> 原子性操作
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;0
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2;
END //
delimiter ;
===============================
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END\\
delimiter ;
4.游标(cursor) -> 如果对单独的表每行进行分类时,一般优先选择
先声明在调用
新建表A和表B
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
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 my_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 ;
5.动态执行SQL
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句
set @xo = arg;
PREPARE xxx FROM 'select * from student where sid > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimiter ;
call p7("select * from tb where id > ?",9)
===>
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 ;
6.数据库相关操作:
1. SQL语句 *****
- select xx() from xx ;
如果性能要求高,则不建议用函数
2. 利用MySQL内部提供的功能