mysql数据库的储存过程_数据库mysql储存过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

一：创建存储过程

MariaDB [test2]> delimiter //MariaDB [test2]>create procedure p1() #创建存储过程-> begin select * froma;-> end //Query OK, 0 rows affected (0.00sec)

MariaDB [test2]>call p1() #调用存储过程->;-> //

+------+

| name |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00sec)

Query OK, 0 rows affected (0.00sec)

MariaDB [test2]>

二：创建存储过程(带参数)

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

in          仅用于传入参数用

out        仅用于返回值用

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

delimiter $

MariaDB [test2]>create procedure p2(-> ini1 int,-> ini2 int,->inout i3 int,->out r1 int->)->begin->declare temp1 int;->declare temp2 int default 0;-> set temp1=1;-> set r1=i1+i2+temp1+temp2;-> set i3=i3+100;->end $

delimiter ;

set @t1=4;

set @t2=0;

call p2(1,2,@t1,@t2);

select @t1,@t2;

MariaDB [test2]> select @t1,@t2;

+------+------+

| @t1 | @t2 |

+------+------+

| 104 | 4 |

+------+------+

1 row in set (0.01 sec)

结果集

delimiter //create procedure p1()

begin

select* fromv1;

end//delimiter ;

结果集+out

delimiter \

create PROCEDURE p1(

OUT p_return_code tinyint

)

BEGIN

DECLARE exit handlerforsqlexception

BEGIN--ERROR

set p_return_code= 1;

rollback;

END;

DECLARE exit handlerforsqlwarning

BEGIN--WARNING

set p_return_code= 2;

rollback;

END;

START TRANSACTION;

DELETEfromtb1;

insert into tb2(name)values('seven');

COMMIT;--SUCCESS

set p_return_code=0;

END\

delimiter ;

游标

delimiter //create procedure p3()

begin

declare ssid int;--自定义变量1

declare ssname varchar(50); --自定义变量2

DECLARE done INT DEFAULT FALSE;

DECLARE my_cursor CURSOR FOR select sid,snamefromstudent;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

open my_cursor;

xxoo: LOOP

fetch my_cursor into ssid,ssname;ifdone then

leave xxoo;

END IF;

insert into teacher(tname) values(ssname);

end loop xxoo;

close my_cursor;

end//delimter ;

动态执行

delimiter \

CREATE PROCEDURE p4 (innid int

)

BEGIN

PREPARE prod FROM'select * from student where sid > ?';

EXECUTE prod USING @nid;

DEALLOCATE prepare prod;

END\

delimiter ;

三：删除存储过程

drop procedure proc_name;

四：执行存储过程

--无参数

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)

五：pymysql调用存储过程

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

importpymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='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)