存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点:
#1. 用于替代程序写的SQL语句,实现程序与sql解耦
#2. 基于网络传输,传别名的数据量小,而直接传sql数据量大
使用存储过程的缺点:
#1. 程序员扩展功能不方便
程序与数据库结合使用的三种方式
#方式一:
MySQL:存储过程
程序:调用存储过程
#方式二:
MySQL:
程序:纯SQL语句
#方式三:
MySQL:
程序:类和对象,即ORM(本质还是纯SQL语句)
# 创建无参存储过程
delimiter $$
create procedure p1()
begin
select * from emp;
end $$
delimiter ;
call p1();
# 创建有参存储过程
delimiter $$
create procedure p2(
in n int,
out res int
)
begin
select * from emp where id > n;
set res=1;
end $$
delimiter ;
==========================>在mysql里如何调用存储过程
mysql> set @x=1111;
Query OK, 0 rows affected (0.00 sec)
mysql> call p2(3,x);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine db4.p2 is not a variable or NEW pseudo-variable in BEFORE trigger
mysql> call p2(3,@x);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | lili | female | 48 | NULL |
+----+------------+--------+------+--------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select @x;
+------+
| @x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
==========================>在pymysql里如何调用存储过程
python 实现:
import pymysql # pip3 install pymysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123", db="db4", charset="utf8mb4")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p2',(3,0)) # @_p2_0=3,@_p2_1=0
'''
set @_p2_0=3
set @_p2_1=0
call p2(@_p2_0,@_p2_1);
'''
print(cursor.fetchall())
cursor.execute("select @_p2_1;")
print(cursor.fetchall())
cursor.execute("select @_p2_0;")
print(cursor.fetchall())
cursor.close()
conn.close()