MySQl的视图、触发器和存储过程
3.存储过程
储蓄过程相当于编程语言调用函数存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql语句。
| 优点 | 1.用于替代程序写的SQL语句，实现程序与sql解耦 2.基于网络传输，传别名的数据量小，而直接传sql数据量大 |
|---|---|
| 缺点 | 程序员扩展功能不方便 |
PS:程序与数据库结合使用的三种方式比较
| 方式 | python | MySQL | 优点 | 缺点 |
|---|---|---|---|---|
| 方式一 | 调用存储过程 | 编写存储过程 | 解耦合 | 但是人为因素导致扩展性低 |
| 方式二 | 编写纯生SQL | do not care | 综合扩展性高 | 效率低于方式一，占用网络 |
| 方式三 | ORM->纯生SQL | do not care | 开发效率高，可维护性好 可扩展性好，最常用 |
效率最低,占网络 |
创建简单的储存过程
准备表
#假设当前所在的数据库为myschool
create table teacher(
tid int primary key auto_increment,
tname char(10)
);
insert into teacher values
(1,"张磊老师"),
(2,"李平老师"),
(3,"邓晓庆老师"),
(4,"朱云海老师"),
(5,"李杰老师")
;
无参数
#在shlle的MySQL的myschool库下执行以下创建代码
delimiter // #delimiter暂时修改结束符号;
create procedure p1() #形式和函数类似，过程名就叫p1
begin
select * from db7.teacher; #相关sql命令
end //
delimiter ; #将结束符号修改回来
# MySQL中调用，直接触发相关储存过程的执行
call p1();
+-----+-----------------+
| tid | tname |
+-----+-----------------+
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 邓晓庆老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
+-----+-----------------+
5 rows in set (0.08 sec)
# Python中调用方式
# cursor.callproc('p1')
# cursor.fetchall()
import pymysql
#建立链接
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="abc123",
db="myschool",
charset="utf8"
)
#拿游标
cursor = conn.cursor(pymysql.cursors.DictCursor)#基于字典的游标
cursor.callproc("p1") #调用存储过程
print(cursor.fetchall()) #全取出
#关闭游标和链接
cursor.close()
conn.close()
#运行结果
[{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '邓晓庆老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
补充：对于存储过程，可以接收参数，其参数有三类
- in 仅用于传入参数用
- out 仅用于返回值用
- inout 既可以传入又可以当作返回值(inout的使用就是in 和out 的结合，很简单)
有参数
###in 代表传入的参数，out为返回值
delimiter //
create procedure p2(in n1 int,in n2 int,out res int)
begin
select * from teacher where tid > n1 and tid < n2;
set res = 1;
end //
delimiter ;
# MySQL中调用
set @x=0; #用于接受返回值
call p2(2,4,@x);
+-----+-----------------+
| tid | tname |
+-----+-----------------+
| 3 | 邓晓庆老师 |
+-----+-----------------+
1 row in set (0.04 sec)
select @x; #查看结果
+------+
| @x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
# Python中调用
import pymysql
#建立链接
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="abc123",
db="myschool",
charset="utf8"
)
#拿游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#基于字典的游标,cursor=可以不写
cursor.callproc("p2",(2,4,0)) #调用存储过程，0代表初需要传出的值设定初始值！
# 这里，会自动产生参数，对应的值为@_p2_0=2,@_p2_1=4,@_p2_2=0 0相当于set @res=0
print(cursor.fetchall())
cursor.execute('select @_p2_2') #相当于shell里面执行select @x
print(cursor.fetchall()) #全取出
#关闭游标和链接
cursor.close()
conn.close()
#运行结果
[{'tid': 3, 'tname': '邓晓庆老师'}]
[{'@_p2_2': 1}]
### inout表示由可传入又可传出
delimiter //
create procedure p3(inout n1 int)
begin
select * from teacher where cid > n1;
set n1 = 1;
end //
delimiter ;
#在mysql中调用
set @x=0;
call p3(@x);
+-----+-----------------+
| tid | tname |
+-----+-----------------+
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 邓晓庆老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
+-----+-----------------+
5 rows in set (0.00 sec)
select @x;
+------+
| @x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
#在python中基于pymysql调用
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "abc123",
db = "myschool",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p3',(0,)) #必须写成元组的形式，在后面加个逗号，与(0)这种形式区分开！
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p3_0;') #查看返回值
print(cursor.fetchall())
cursor.close()
conn.close()
#执行结果
[{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '邓晓庆老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
[{'@_p3_0': 1}]
总结：执行储存过程
shell下进入MySQL
call proc_name()-- 无参数
call proc_name(1,2)-- 有参数，全in
-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
删除储存过程
drop procedure p1;
drop procedure p2;
drop procedure p3;
