Detailed explanation of the principles and usage of MySQL stored procedures

This article uses examples to explain the principles and usage of MySQL stored procedures. Share with you for your reference, the details are as follows:

A stored procedure contains a series of executable SQL statements. The stored procedure is stored in MySQL and can be executed by calling its name.

Advantages of stored procedures

#1. Used to replace SQL statements written by programs to decouple programs from SQL
#2. You can modify business logic (or bugs) by directly modifying the stored procedure without restarting the server
#3. Fast execution speed. After the stored procedure is compiled, it will be faster than executing it one by one.
#4. Reduce network transmission, especially in high concurrency situations. This is a big advantage. The stored procedure runs directly on the database server, and all data access is performed within the server. There is no need to transmit data to other terminals.

Disadvantages of stored procedures

1. SQL itself is a structured query language with some controls (assignment, loops, exception handling, etc.), but it is not OO and is essentially procedural. Faced with complex business logic, procedural processing will be very difficult. This is a fatal flaw, that is, it can only be applied to businesses with simple logic.
2. Not easy to debug. There is basically no good debugger, and print is often used for debugging, but using this method to debug stored procedures that are hundreds of lines long is simply a nightmare. Well, that’s nothing, C#/Java can also write nightmare code.
3. There is no way to apply cache. Although there are methods such as global temporary tables for caching, they also increase the burden on the database. If cache concurrency is serious and locking is required frequently, the efficiency will be worrying.
4. Unable to adapt to database cutting (horizontal or vertical cutting). After the database is split, the stored procedure is not aware of which database the data is stored in.

Stored procedure without parameters

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

Stored procedures with parameters

For stored procedures, you can receive parameters, which are divided into three categories:

#in is only used to pass in parameters
#out is only used for return values
#inout can be passed in and used as a return value

Stored procedure with in

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

With out

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

Example with inout

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

Transactions

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

Execution of stored procedures

Execute in mysql

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

Execute in pymsql

#!/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)

Deleting a stored procedure

drop procedure proc_name;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Summary of if and case statements in MySQL
Detailed explanation of the principle and usage of cursor (DECLARE) in MySQL stored procedure
MySQL stored procedure method example of returning multiple values
How to create (CREATE PROCEDURE) and call (CALL) a MySQL stored procedure and how to create (DECLARE) and assign (SET) a variable
Example analysis of mysql stored procedures that trigger error conditions in stored procedures (SIGNAL and RESIGNAL statements)
Detailed explanation of error handling examples in MySQL stored procedures
Definition and assignment of variables in mysql stored procedures
Introduction to the use of MySQL stored procedure cursor loop
MySQL stored procedure example (including transactions, output parameters, nested calls)
Detailed explanation of MySql stored procedures and functions
Detailed example of using if statement in mysql stored procedure

<<: Detailed graphic tutorial on downloading and installing VirtualBox software and installing and deploying it in Linux environment

>>: Vue routing relative path jump method