1. 存储过程
存储过程也是一个别名,是保存在mysql中的一大堆sql语句所起的别名,日后可以直接不用在终端或者python中写sql语句 而是直接使用别名就可以拿到sql语句的执行结果;
对比视图来看,视图是用来保存查询结果的一张临时表,是虚拟的表,不可以插入,只可以查询操作;
而在存储过程中只需要写一个别名就可以自动去执行存储过程中的sql语句;
所以试图就是别名代指的临时表,而存储过程就是用别名代指很多sql语句的操作;
python中操作mysql数据库的三种方式:
1. mysql中什么也不做,程序中写sql语句;
2. mysql中什么也不做,程序来写类和对象(当然内部也会转化sql语句去执行);
3. mysql 中写存储过程,程序直接调用存储过程的名称来执行存储过程中的sql语句;
2. 简单存储过程
在mysql终端:
delimiter \\create procedurep1()begin
select * from student; --存储过程中可以写很多sql语句;
insert into teacher(name) values("李丹");end\\
delimiter ;
call p1();--在mysql的终端直接使用call p1() 调用存储过程,会自动执行存储过程中的sql语句;
运行结果:
在pymysql中执行存储过程:
importpymysql
conn=pymysql.connect(host="localhost",user="root",password="******",database="db666",charset="utf8") #charset="utf8" 是为了存储过程返回的结果集有中文时 不乱码
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #参数是为了当查询结果按照字典形式显示;
cursor.callproc("p1") #调用存储过程,执行存储过程中的sql语句(以前都是cursor.execute(sql,[arg1,arg2]))
conn.commit() #存储过程的sql语句有增删改操作时需要把修改操作提交
result=cursor.fetchall() #存储过程的sql语句有select查询语句 需要拿到查询结果集使用cursor.fetchall()获取;
print(result)
cursor.close()
conn.close()
运行结果:
3. 存储过程带参数--in(参数三种方式 in out inout)
delimiter \\create procedurep2(in n1 int,in n2 int)begin
select * from student where id>n1;insert into teacher(name) values("尺子");end\\
delimiter ;
call p2(4,2) --直接传参数,这里第二个参数n2没有用到,但是也是需要传;
运行结果:
importpymysql
conn=pymysql.connect(host="localhost",user="root",password="******",database="db666",charset="utf8")
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p2",(4,2)) #调用存储过程,p2有参数 在cursor.callproc("p2",[arg1,arg2])传入参数;
conn.commit() #存储过程中有增删改操作时,需要进行提交
result=cursor.fetchall() #存储过程中有查询操作 可以使用cursor.fetchall()获取结果集
print(result)
cursor.close()
conn.close()
运行结果:
4. 存储过程带参数---out
delimiter \\create procedurep3(in n1 int,
out n2int) --存储过程中的out类型的参数,在后续调用存储过程传参数时需要传一个变量
begin
set n2=666;select * from teacher where id>n1;end\\
delimiter ;set @v1=0; --设置存储过程中out类型的变量n1 初始值为0;
call p3(4,@v1);select @v1; --查看out类型的变量 在经过存储过程set n2=666之后修改过的值
drop procedure p3; --删除存储过程p3;
运行结果:
importpymysql
conn=pymysql.connect(host="localhost",user="root",password="123",database="db666",charset="utf8")
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p3",(4,12)) #调用存储过程,传参数 但是由于存储过程的参数n1是out类型,所以后续可以使用cursor.execute("select @_p3_0,@_p3_1")来获取参数的返回结果
conn.commit() #当存储过程中的sql语句含有增删改操作时,需要提交
result=cursor.fetchall() #存储过程的sql语句含有select查操作时 需要获取查询结果集,可以使用cursor.fetchall()
print(result)
cursor.execute("select @_p3_0,@_p3_1")
result=cursor.fetchall() #上面的cursor.execute("select @_p3_1") 可以获得存储过程中参数的值
print(result)
cursor.close()
conn.close()
运行结果:
自定义函数有return 但是不可以select ,存储过程可以select 但是不可以return 但是自定义函数和存储过程都可以传参~
其实out 可以用于表示存储过程的执行结果(比如存储过程中的sql语句没有select需要返回结果集的 而是一些insert update delete 操作 那在终端调用存储过程是没有任何反应的,所以可以传一个out类型的参数标志存储过程的执行过程~)