Principle and Application of mysql Stored Procedures

This paper describes the principle and usage of mysql stored procedure with examples. Share it for your reference, as follows:

Stored procedures contain 1 series of executable sql statements. Stored procedures are stored in MySQL and can execute 1 heap of sql inside by calling its name

Advantages of stored procedures

# 1. Used to replace SQL statement written by the program to decouple the program from sql

# 2. You can modify the business logic (or bug) by directly modifying the stored procedure without restarting the server

# 3. Execute fast. Stored procedures will execute faster after compiling than one single article

# 4. Reduce network transmission, especially in the case of high concurrency. This advantage is great. Stored procedures run directly on the database server, and all data access is carried out inside the server without transmitting data to other terminals.

Disadvantages of stored procedures

1. SQL itself is a structured query language, with some controls (assignment, loop and exception handling, etc.), but it is not OO's, and it is essentially procedural. Facing complex business logic, procedural processing will be very difficult. This point is fatal, that is, it can only be applied to businesses with simple logic.

2. Not easy to debug. There are basically no good debuggers, and most of the time print is used for debugging, but debugging hundreds of lines of stored procedures in this way is a nightmare. Well, this is nothing, C #/java1 can write nightmare code.

3. There is no way to apply caching. Although there are methods such as global temporary tables that can be used for caching, it also increases the burden on the database. If cache concurrency is serious and locks are often needed, the efficiency is really worrying.

4. Unable to adapt to database cutting (horizontal or vertical cutting). After database cutting, the stored procedure does not know which database the data is stored in.

Parameterless stored procedure

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

# In mysql Call in 
call p1()

# In python Based on pymysql Call 
cursor.callproc('p1')
print(cursor.fetchall())

Stored procedure with parameters

For stored procedures, you can accept parameters, and there are three types of parameters:

# in is only used for passing in parameters
# out is only used for return value
# inout can be passed in or returned

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

# In python Based on pymysql Call 
cursor.callproc('p2',(1,3))
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)

# In python Based on pymysql Call 
cursor.callproc('p3',(3,0)) #0 Equivalent to set @res=0
print(cursor.fetchall()) # Query select Query results of 
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0 On behalf of 1 Parameters, @p3_1 On behalf of 2 Parameters, 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 ;

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

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

Affairs

# In mysql Call in 
call p1()

0

# In mysql Call in 
call p1()

1

# In mysql Call in 
call p1()

2

Execution of stored procedures

Executed in mysql

--  Parametric-free 
call proc_name()
--  Parametric, full in
call proc_name(1,2)
--  There are parameters, there are in , out , inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

Executed in pymsql

# In mysql Call in 
call p1()

4

Delete stored procedure

# In mysql Call in 
call p1()

5

More readers interested in MySQL can check out the topics on this site: "MySQL Stored Procedure Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.