MySQL-存储过程procedure

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

1.创建存储过程:

-- 创建存储过程
delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

-- 执行存储过程
call p1()

存储过程参数:

in:仅用于传入参数用

out:仅用于返回值用

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

 1 -- 创建存储过程
 2 delimiter \\
 3 create procedure p1(
 4     in i1 int,
 5     in i2 int,
 6     inout i3 int,
 7     out r1 int
 8 )
 9 BEGIN
10     DECLARE temp1 int;
11     DECLARE temp2 int default 0;
12     
13     set temp1 = 1;
14 
15     set r1 = i1 + i2 + temp1 + temp2;
16     
17     set i3 = i3 + 100;
18 
19 end\\
20 delimiter ;
21 
22 -- 执行存储过程
23 set @t1 =4;
24 set @t2 = 0;
25 CALL p1 (1, 2 ,@t1, @t2);
26 SELECT @t1,@t2;

1 delimiter //
2 create procedure p1()
3 begin
4 select * from v1;
5 end //
6 delimiter ;

 1 delimiter //
 2 create procedure p2(
 3 in n1  int,
 4 inout n3 int,
 5 out n2 int,
 6 )
 7 begin
 8     declare temp1 int;
 9     declare temp2 int default 0;
10     select * from v1;
11     set n2 = n1 + 100;
12     set n3 = n3 + n1 + 100;
13 end //
14 delimiter;

 1 delimiter \ \
 2 create PROCEDURE p1(OUT p_return_code tinyint)
 3 BEGIN
 4     DECLARE exit handler for sqlexception
 5     BEGIN
 6         --执行失败
 7         set p_return_code = 1;
 8         rollback;
 9     END;
10     DECLARE exit handler for sqlwarning
11     BEGIN
12         --警示
13         set p_return_code = 2;
14         rollback;
15     END;
16 
17     START TRANSACTION;
18     DELETE from tb1;
19     insert into tb2(name) values('seven');
20     COMMIT;
21     -- SUCCESS
22     set p_return_code = 0;
23 END\ \
24 delimiter;

 1                     delimiter //
 2                     create procedure p3()
 3                     begin 
 4                         declare ssid int; -- 自定义变量1  
 5                         declare ssname varchar(50); -- 自定义变量2  
 6                         DECLARE done INT DEFAULT FALSE;
 7 
 8 
 9                         DECLARE my_cursor CURSOR FOR select sid,sname from student;
10                         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
11                         
12                         open my_cursor;
13                             xxoo: LOOP
14                                 fetch my_cursor into ssid,ssname;
15                                 if done then 
16                                     leave xxoo;
17                                 END IF;
18                                 insert into teacher(tname) values(ssname);
19                             end loop xxoo;
20                         close my_cursor;
21                     end  //
22                     delimter ;

 1                     delimiter \\
 2                     CREATE PROCEDURE p4 (
 3                         in nid int
 4                     )
 5                     BEGIN
 6                         PREPARE prod FROM 'select * from student where sid > ?';
 7                         EXECUTE prod USING @nid;
 8                         DEALLOCATE prepare prod; 
 9                     END\\
10                     delimiter ;

2.删除存储过程:

drop procedure proc_name;

3.执行存储过程

-- 无参数
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)

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 5 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 6 # 执行存储过程
 7 cursor.callproc('p1', args=(1, 22, 3, 4))
 8 # 获取执行完存储的参数
 9 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
10 result = cursor.fetchall()
11 conn.commit()
12 cursor.close()
13 conn.close()
14 print(result)