Shulou(Shulou.com)05/31 Report--

How to use stored procedures in mysql? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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

Advantages of stored procedures

# 1. It is used to replace the SQL statement written by the program to decouple the program from sql.

# 2. The business logic (or bug) can be modified by directly modifying the stored procedure without restarting the server

# 3. The execution speed is fast, and the compiled stored procedure is faster than a single one.

# 4. It has a great advantage to reduce network transmission, especially in the case of high concurrency, the stored procedure runs directly on the database server, all data access is carried out inside the server, and there is no need to transfer 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 and is procedural in nature. In the face of complex business logic, procedural processing will be very difficult. This is fatal, that is, it can only be applied to businesses with simple logic.

two。 It is not easy to debug. There are basically no good debuggers, and most of the time you use print to debug, but debugging hundreds of lines of stored procedures in this way is a nightmare. Well, that's not a big deal. C#/java can also write nightmarish code.

3. There is no way to apply cache. Although there are methods such as global temporary tables for caching, it also adds to the burden on the database. If the 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 the database is cut, the stored procedure does not know 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 call p1 () in mysql # call cursor.callproc ('p1') print (cursor.fetchall ()) in python based on pymysql

Stored procedure with parameters

For stored procedures, you can receive parameters, which have three types of parameters:

# in is only used for incoming parameters

# out is only used for return values

# inout can be passed in or 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 end / / Query OK, 0 rows affected (0.28 sec) mysql > delimiter Mysql > call p2 (1p3)-> +-+ | 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 ('p2mom, (1p2pm)) print (cursor.fetchall ()) based on pymysql in python

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 +-+ | 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 based on pymysql in python ('p3movie, (3Power0)) # 0 is equivalent to set @ res=0print (cursor.fetchall ()) # query the query result of select cursor.execute (' select @ _ p3room0) ') # @ p3room0 represents the first parameter, and @ p3room1 represents the second parameter, which returns the value print (cursor.fetchall ())

Example with inout

Delimiter / / create procedure p4 (inout N1 int) BEGIN select * from blog where id > N1; set N1 = 1position end / / delimiter; # call set @ xint3scape call p4 (@ x) in mysql; select @ XTX # call cursor.callproc ('p4neighbors, (3,) print (cursor.fetchall ()) # query select query result cursor.execute (' select @ _ p4room0politics') print (cursor.fetchall ()) in python based on pymysql

Business

# introduce delimiter / / create procedure p4 (out status int) BEGIN 1. Declare that if there is an exception, execute {set status = 1; rollback;} to start the transaction-from Qin Bing account minus 100-Fang Shaowei account plus 90-Zhang Gen 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; # failed to execute insert into blog (name,sub_time) values ('yyy',now ()); COMMIT -- SUCCESS set p_return_code = 0; # 0 indicates successful execution of END / / delimiter; # call stored procedure set @ res=123;call p5 (@ res) in mysql; select @ res;# calls stored procedure cursor.callproc ('p5neighbors, (123,)) print (cursor.fetchall ()) in python based on pymysql # query select query result cursor.execute (' select @ _ p5room0position') print (cursor.fetchall ())

Execution of stored procedure

Execute in mysql

-- No parameter call proc_name ()-- with parameters, full incall proc_name (1d2)-- with parameters, there are in,out,inoutset @ t _ 1pm _ 0 _ set @ t _ 2cm _ 3 _ switch _ call proc_name (1meme _ 2pm _ 1Q _ T2)

Execute in pymsql

#! / usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = 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 stored parameter cursor.execute ("select @ _ p1 recording 0, drawing 1, 1, 2, 2) @ _ p1room3 ") result = cursor.fetchall () conn.commit () cursor.close () conn.close () print (result)

Delete stored procedure

Drop procedure proc_name;, after reading the above, have you mastered how to use stored procedures in mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.