一、介绍

存储过程包含了一系列可执行的SQL语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆SQL。

使用存储过程的优点：

1. 用于替代程序写的SQL语句，实现程序与SQL解耦
2. 基于网络传输，传别名的数据量小，而直接传SQL数据量大

使用存储过程的缺点：

1. 程序员扩展功能不方便

补充：程序与数据库结合使用的三种方式

1. 方式一：

• MySQL：存储过程
• 程序：调用存储过程

2. 方式二：

• MySQL：
• 程序：纯SQL语句

3. 方式三：

• MySQL:
• 程序：类和对象，即ORM（本质还是纯SQL语句）

二、创建简单存储过程（无参）

delimiter //
CREATE PROCEDURE p1()
BEGIN
    SELECT * FROM blog;
    INSERT INTO blog(name, sub_time) VALUES("xxx", NOW());
END //
delimiter ;

在MySQL中调用：

CALL p1();

在Python中基于pymysql调用：

cursor.callproc('p1')
print(cursor.fetchall())

三、创建存储过程（有参）

对于存储过程，可以接收参数，其参数有三类：

• IN    仅用于传入参数用
• OUT   仅用于返回值用
• INOUT 既可以传入又可以当作返回值

示例1：仅传入参数

delimiter //
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())

示例2：传入和返回参数

delimiter //
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;
CALL p3(3, @res);
SELECT @res;

在Python中基于pymysql调用：

cursor.callproc('p3', (3, 0))
print(cursor.fetchall())

cursor.execute('SELECT @_p3_0, @_p3_1;')
print(cursor.fetchall())

示例3：既传入又返回参数

delimiter //
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())

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

四、事务处理的存储过程

delimiter //
CREATE PROCEDURE p5(
    OUT p_return_code TINYINT
)
BEGIN 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN 
        SET p_return_code = 1; 
        ROLLBACK; 
    END; 

    DECLARE EXIT HANDLER FOR SQLWARNING 
    BEGIN 
        SET p_return_code = 2; 
        ROLLBACK; 
    END; 

    START TRANSACTION; 
        DELETE FROM tb1; #执行失败
        INSERT INTO blog(name, sub_time) VALUES('yyy', NOW());
    COMMIT; 

    SET p_return_code = 0; # 0代表执行成功

END //
delimiter ;

在MySQL中调用存储过程：

SET @res = 123;
CALL p5(@res);
SELECT @res;

在Python中基于pymysql调用存储过程：

cursor.callproc('p5', (123,))
print(cursor.fetchall())

cursor.execute('SELECT @_p5_0;')
print(cursor.fetchall())

五、执行存储过程

无参数

CALL proc_name();

有参数，全IN

CALL proc_name(1, 2);

有参数，有IN，OUT，INOUT

SET @t1 = 0;
SET @t2 = 3;
CALL proc_name(1, 2, @t1, @t2);

在Python中执行存储过程

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("SELECT @_p1_0, @_p1_1, @_p1_2, @_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

print(result)

六、删除存储过程

DROP PROCEDURE proc_name;