Mysql之存储过程(无参、有参(in、out、inout)))

一.存储过程

1.什么是存储过程

• 存储过程中包含了一系列的 SQL 语句, 就类似于 Python 中定义的函数, 通过调用存储过程名(函数名)来执行其内部的一堆 sql 语句(Python代码)

2.使用存储过程的优缺点

• 优点 :
  • 用于替代程序写的 SQL 语句, 实现程序与 SQL 语句的解耦
  • 基于网络的传输, 只传"存储过程名"比传一堆的 SQL 语句的数据量小的多
• 缺点 :
  • 程序的可扩展性非常低

3.应用程序与数据库结合使用的三种开发模式

• 第一种

"应用程序" : 程序员自己写代码开发
"mysql" : 提前写好的存储过程, 提供给程序来调用

🔰优点 : 提升了开发效率, 执行效率提升(只传输'存储过程名')
🔰缺点 : 可扩展性查, 可能使用者的一些变动就需要修改整个存储过程

• 第二种

程序员在开发应用程序的过程中涉及到的数据库操作也是自己手动编写

🔰优点 : 扩展性很高
🔰缺点 : 开发效率低, 编写SQL语句太过繁琐, 且有些重复, 后期维护也不方便

• 第三种

程序员开发程序时只写程序代码, 而sql语句是基于别人写好的框架直接拿过来使用 (例如使用ORM框架)

🔰优点 : 开发效率比第一种和第二种都要高
🔰缺点 : 语句的可扩展性差, 并可能出现效率低下的问题

4.创建存储过程 (无参)

• 语法

delimiter %%  # 更换默认结束符
create procedure [存储过程名]()
begin
    [一堆SQL语句]
end %%
delimiter ;   # 将默认结束符改回来

• 简单示例

🍅先创建一个表, 在插入几条子记录
create table text(
    id int primary key auto_increment,
    name varchar(100) not null,
    sub_time datetime);
insert text(name,sub_time) value
    ("python详解",now()),
    ("Java入门",now()),
    ("派大星的故事",now()),
    ("小江放牛羊",now());
    
🍅创建无参存储过程,对"text"表进行一些操作
delimiter %%
create procedure p01()
begin
    select * from text;
    insert text(name,sub_time) value("杀牛羊手法2",now());
end %% 
delimiter ;
	
🔰在"mysql"中调用
call p01();

🔰在"Python"中的"pymysql"中调用
# 首先导入模块并连接数据库
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
# 得到游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p01")    # 调用存储过程
print(cursor.fetchall())  # 拿到结果并输出

cursor.close()  # 关闭游标
conn.close()    # 关闭连接 

• mysql 中调用

5.创建存储过程 (有参)

• 三种参数类型

"in" : 仅用于传入参数 (声明后面的变量为外部参数)
"out" : 仅用于返回值 (声明后面的变量是一个返回值,需要使用"set"声明)
"inout" : 既可以传入时使用有可以返回值时使用 (声明后面的变量可以但参数也可以当返回值,需要"set"声明)

• 语法

delimiter %%%  # 修改默认结束符
create procedure [存储过程名](
    in|out|inout [参数名] [类型],
    .....(可以多个参数),
    ......
)
begin
    [sql语句]
end %%%
delimiter ;  # 将默认结束符改回来

• in : 传入参数示例

🍅创建无参存储过程
delimiter %%%
create procedure p02(
    in num01 int
)
begin
    select * from text where id=num01;
end %%%
delimiter ;

🍅在"mysql"中直接调用
call p02(4);

🍅"Python"中使用"pymysql"模块调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc("p02",(4,))  # 参数传入的是一个元组
print(cursor.fetchall())

cursor.close()
conn.close()

• out : 返回值示例 (in+out)

🍅创建有参存储过程
delimiter %%%
create procedure p03(
    in num01 int,
    out res01 int
)
begin
    select * from text where id=num01;
    set res01=num01+1;
end %%%
delimiter ;

🍅在"mysql"中调用
call p03(4,@res01);  # @res01 表示定义一个全局变量来接收返回值
select @res01;       # 查看这个返回值

🍅在"python"中使用"pymysql"调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = ""
cursor.callproc("p03", (4, res))  # 第二个参数是用来接收返回值的
print(cursor.fetchall())

cursor.execute("select @_p03_0,@_p03_1;")  # "@_p03_0"代表的是传入的第一个参数,"@_p03_1"代表的是第二的参数,也就是返回值
print(cursor.fetchall())

cursor.close()
conn.close()

• inout : 能传能返回示例

🍅创建存储过程
delimiter %%%
create procedure p04(
    inout num01 int
)
begin
    select * from text where id>num01;
    set num01=num01+num01;
end %%%
delimiter ;

🍅在"mysql"中调用
set @res=2;     # 先定义一个全局变量
call p04(@res); # 然后传进去,一方面当参数, 一方面接收返回值
select @res     # 查看这个返回值

🍅在"Python"使用"pymysql"调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = 2  # 定义一个变量
cursor.callproc("p04", (res,))     # 传入这个变量,可当参数,也可接收返回值 
print(cursor.fetchall())

cursor.execute("select @_p04_0;")  # 查看返回值
print(cursor.fetchall())

cursor.close()
conn.close()

6.删除存储过程

• 语法

drop procedure [存储过程名];

• 示例

drop procedure p01;