数据库之存储过程

存储过程

1.什么是存储过程?

存储过程就类似于python中的自定义的函数,它的内部包含了一系列可以执行的sql语句,存储过程存放于mysql服务端中,你可以直接通过调用存储过程触发内部sql语句的执行

2.存储过程的基本使用

procedure /prəˈsiːdʒə(r) 程序, 过程

-- 创建语法
delimiter //
create procedure 存储过程的名字([形参1,形参2,...])
begin
	sql代码
end //
delimiter ;

-- mysql 中调用存储过程语法
call 存储过程的名字([实参1,实参2,...]);

-- python中基于pymysql调用
cursor.callproc("存储过程的名字")
print(cursor.fetchall())

3.三种开发模式: 程序与数据库结合开发

# 第一种
"""  
应用程序: 程序员写业务代码开发
mysql: dba提前编写好存储过程,供应用程序调用
好处: 开发效率提升了,执行效率也上去了
缺点: 考虑到人为因素,需要跨部门沟通问题,后续的存储过程的扩展性差
"""

# 第二种
"""
应用程序: 程序员写代码开发之外 涉及到数据库操作也要自己手写
优点: 解决了跨部门不方便扩展的问题
缺点: 开发效率低,都是程序员干活.程序员不仅要会多种编程语言,还要考虑sql语句的执行效率问题
"""

# 第三种
"""
应用程序: 程序员只写程序代码 不写sql语句了.而是用别人写好的操作sql的框架即可
比如: 后面要讲的ORM框架对象关系映射
优点: 开发效率比前俩种开发效率都要高
缺点: 语句的扩展性差,可能会出现效率低下的问题.
"""
# 第一种基本不用,一般都是第三种方法,当出现效率低下的情况,再去手动的写一些sql语句

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

创建库并准备表和数据

create database procedure_test charset utf8;
use procedure_test;

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

mysql种创建及调用

delimiter //
create procedure p1()
begin
	select * from blog;
	insert into blog(name,sub_time) values('ldsb',now());
end //
delimiter ;

# 调用
call p1();

python中基于pymysql调用

import pymysql

conn = pymysql.connect(
	host = '127.0.0.1',
    port=3306,
    user='root',
    password='jzd123',
    database='procedure_test',
    charset='utf8'
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 在python中 通过游标下的callproc方法调用存储过程p1
cursor.callproc('p1')
print(cursor.fetchall())

cursor.close()
conn.close()

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

对于存储过程, 可以接受参数,其参数有三类

in , out 和 inout

-- n n int      ---> 声明变量n为外部传入的值, 类型为int. 可以为存储过程定义的形参in直接传值,
-- out res int  ---> 声明变量res为返回值, 类型为int. 为存储过程定义的形参out传值, 需要实现使用set声明才能传.
-- inout x int  --->  声明变量x既能收值又能返回值. 虽然inout既能接收又能充当返回值, 但是为了保证int和out的传值的统一性, 因此也需要事先使用set声明才能传.

• in: 传入参数

Copydelimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
    select * from blog where id > n1;
END //
delimiter ;


# 在mysql中调用
call p2(3,2)

# 在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

• out：返回值

Copydelimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id > n1;
    set res = 1;
END //
delimiter ;

# 在mysql中调用
set @res=0;       # 定义全局变量用于给out定义的res形参. 0代表假（执行失败），1代表真（执行成功）.
call p3(3,@res);  # 调用存储过程
select @res;      # 查看存储过程

# 在python中基于pymysql调用
cursor.callproc('p3',(3,0)) # 0相当于set @res=0
print(cursor.fetchall())    #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') # @p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall()

• inout: 既可以传入又可以返回

Copydelimiter //
create procedure p4(
    inout n1 int
)
BEGIN
    select * from blog where id > n1;
    set n1 = 1;
END //
delimiter ;

#在mysql中调用
set @x=3;
call p4(@x);
select @x;


# 在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

6. 执行存储过程: int+out

• 创建库并准备表和数据

Copydrop database procedure_test;  # 小心
create database procedure_test charset utf8;
use procedure_test;

create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

• mysql中创建及调用

Copydrop procedure p1;

# 定义存储过程
delimiter $$
create procedure p1(
    in m int,  # 该形参m只负责接收值， m不能当作参数返回。
    in n int,  
    out res int  # 该形参res只负责返回值，不能直接接收参数，需要使用set先定义变量。 针对out存储过程的返回值参数一定要设置一个变量, 通常用返回值判断你这天sql语句运行成功与否, 返回值为0通常代表成功
)
begin
    select tname from teacher where tid>m and tid<n;
    set res=0;  # 将res变量修改， 用来标识当前的存储过程代码确实执行了。(修改成啥都行)
end  $$
delimiter ;

# 定义变量
set @xxx=10;  # 争对形参res不能直接传数据，因该传一个变量命。

# 查看变量对应得值
set @xxx;

# 调用存储过程
"""
第一个第二个参数: 为存储过程in定义的参数传值
第二个参数: 为存储过程out定义的返回值传值, out需要使用set声明才能传
"""
call p1(1, 10, @xxx);

# 删除存储过程
drop procedure p1;

• pymysql模块使用存储过程

Copyimport pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='jzd123',
    database='procedure_test',
    charset='utf8',
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 调用存储过程
cursor.callproc('p1', (1, 5, 10))
"""
pymysql底层实现. 
set @_p1_0 = 1
set @_p1_1 = 5
set @_p1_2 = 10
"""
print(cursor.fetchall())  

# 执行select语句验证
cursor.execute('select @_p1_0')  
print(cursor.fetchall())  # [{'@_p1_0:1'}]

cursor.close()
conn.close()

7. 删除存储过程

Copydrop procedure 存储过程的名字;