存储过程

一、介绍

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

使用存储过程的优点：

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

使用存储过程的缺点：

程序员扩展功能不方便

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

方式一：

MySQL：存储过程

程序：调用存储过程

方式二：

MySQL：

程序：纯SQL语句

方式三：

MySQL:

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

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

无参的例子

delimiter //         #定义sql的结束语句为//

create procedure p1()

BEGIN

    select * from blog;

    INSERT into blog(name,sub_time) values("xxx",now());

END //

delimiter ;           #定义sql的结束语句为;

#在mysql中调用

call p1() 

#在python中基于pymysql调用

cursor.callproc('p1')

print(cursor.fetchall())

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

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

in 仅用于传入参数用

out 仅用于返回值用

inout 既可以传入又可以当作返回值

in 的运用实例:

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

out 的运用实例:

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; #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 的运用实例：

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()) #查询select的查询结果

cursor.execute('select @_p4_0;')

print(cursor.fetchall())

事务和存储过程的实例：

#介绍

delimiter //

            create procedure p4(

                out status int

            )

            BEGIN

                1. 声明如果出现异常则执行{

                    set status = 1;

                    rollback;

                }

                开始事务

                    -- 大木木账户减去100

                    -- 二木木账户加90

                    -- 三木木账户加10

                    commit;

                结束

                set status = 2;

            END //

            delimiter ;

#实现

delimiter //

create PROCEDURE p5(

    OUT p_return_code tinyint

)

BEGIN

    DECLARE exit handler for sqlexception

    BEGIN

        -- ERROR

        set p_return_code = 1;

        rollback;

    END; 

    DECLARE exit handler for sqlwarning

    BEGIN

        -- WARNING

        set p_return_code = 2;

        rollback;

    END; 

    START TRANSACTION;

        DELETE from tb1; #执行失败

        insert into blog(name,sub_time) values('yyy',now());

    COMMIT; 

    -- SUCCESS

    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()) #查询select的查询结果

cursor.execute('select @_p5_0;')

print(cursor.fetchall())

四、执行存储过程

在mysql 中执行存储过程:

-- 无参数

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 中基于pymysql 执行存储过程：

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='t1')

cursor = conn.cursor(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)