Detailed explanation of the principles and usage of MySQL stored procedures

delimiter //
create procedure p1()
BEGIN
  select * from blog;
  INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

#Call p1() in mysql

#Call cursor.callproc('p1') based on pymysql in python
print(cursor.fetchall())

mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | egon | 20 | 2 |
| 5 | alex | 18 | 2 |
+----+----------+-----+--------+
4 rows in set (0.30 sec)
mysql> delimiter //
mysql> create procedure p2(in n1 int, in n2 int)
  -> begin
  -> select * from emp where id >n1 and id <n2;
  -> end //
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter;
mysql> call p2(1,3)
  -> ;
+----+------+-----+--------+
| id | name | age | dep_id |
+----+------+-----+--------+
| 2 | lisi | 19 | 1 |
+----+------+-----+--------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.07 sec)

#Call cursor.callproc('p2',(1,3)) based on pymysql in python
print(cursor.fetchall())

mysql> delimiter //
mysql> create procedure p3( in n1 int, out res int)
  -> begin
  -> select * from emp where id >n1;
  ->set res=1;
  -> end //
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter;
mysql> set @res=0;
Query OK, 0 rows affected (0.00 sec)
mysql> call p3(3,@res);
+----+------+-----+--------+
| id | name | age | dep_id |
+----+------+-----+--------+
| 5 | alex | 18 | 2 |
+----+------+-----+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select @res;
+------+
| @res |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

#Call cursor.callproc('p3',(3,0)) based on pymysql in python #0 is equivalent to set @res=0
print(cursor.fetchall()) #Query the query results of select cursor.execute('select @_p3_0,@_p3_1;') #@p3_0 represents the first parameter, @p3_1 represents the second parameter, that is, the return value print(cursor.fetchall())

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

#Call set @x=3 in mysql;
call p4(@x);
select @x;

#Call cursor.callproc('p4',(3,)) based on pymysql in python
print(cursor.fetchall()) #Query the query results of select cursor.execute('select @_p4_0;')
print(cursor.fetchall())

#Introduction delimiter //
      create procedure p4(
        out status int
      )
      BEGIN
        1. Declare that if an exception occurs, execute {
          set status = 1;
          rollback;
        }
        Start transaction -- subtract 100 from Qin Bing's account
          -- Add 90 to Fang Shaowei's account
          -- Zhang Gen's account plus 10
          commit;
        end set status = 2;
      END //
      delimiter ;
#Implement 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; #Execution failed insert into blog(name,sub_time) values('yyy',now());
  COMMIT; 
  --SUCCESS 
  set p_return_code = 0; #0 represents successful execution END //
delimiter ;

#Call the stored procedure in mysql set @res=123;
call p5(@res);
select @res;

#Calling stored procedures based on pymysql in python cursor.callproc('p5',(123,))
print(cursor.fetchall()) #Query the query results of select cursor.execute('select @_p5_0;')
print(cursor.fetchall())

-- call proc_name() without parameters
-- With parameters, all in
call proc_name(1,2)
-- There are parameters, in, out, inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

#!/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(cursor=pymysql.cursors.DictCursor)
# Execute the stored procedure cursor.callproc('p1', args=(1, 22, 3, 4))
# Get the parameters after execution and storage 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;