Database: MySQL built-in features-stored procedures

description of a stored procedure

The stored procedure contains a series of executable SQL statements stored in MySQL, which can be executed by calling its name to execute a heap of SQL inside it.

Advantages of using Stored procedures:

#1. Used to replace the SQL statements written by the program, the implementation program and SQL decoupling # #. Based on the network transmission, the data volume of the alias is small, and the amount of direct SQL data is large.

Disadvantages of using Stored procedures:

#1. Programmer extension is not a convenient feature

　

Supplement: Three Ways to use the program in conjunction with the database

　

#方式一:    mysql: Stored procedure    Program: Call stored Procedure # Way two:    MySQL:    program: Pure SQL Statement # Way three:    MySQL:    Program: Class and Object, i.e. ORM (essentially or pure SQL statement)

　　

two creating a simple stored procedure (no parameters)

First CREATE table:mysql> creation table blog (ID int primary key auto_increment,    name varchar, and    sub_time date ); Create stored procedure delimiter//create procedure P1 () Beginselect * from Blog;insert into blog (name,sub_time) VALUES ("Test", now ()); END//delimiter;

　

#在mysql中调用call p1 () #在python中基于pymysql调用cursor. Callproc (' P1 ') print (Cursor.fetchall ())

　　

three create stored procedures (with parameters)

For stored procedures, you can receive parameters, whose parameters have three types of #        in only for incoming parameters with # out        only for return parameters with # InOut        can both be passed in and can be used as return values

　　

Delimiter//create procedure P2 (    in N1 int, in    n2 int) BEGIN    select * from blog where ID > n1; END//delimiter; #在mysql中调用call p2 (3,2) #在python中基于pymysql调用cursor. Callproc (' P2 ', (3,2)) print (Cursor.fetchall ())

　　

Delimiter//create procedure P3 (    in N1 int, out    res int.) BEGIN    SELECT * from blog where ID > n1;    Set res = 1; END//delimiter; #在mysql中调用set @res = 0; #0代表假 (Execution failed), 1 represents true (execution succeeded) call P3 (3, @res); select @res; #在python中基于pymysql调用cursor. Callproc (' P3 ', (3,0)) #0相当于set @res = 0print (Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' select @_p3_0,@_p3_1; ') # @p3_0 represents the first argument, @p3_1 represents the second argument, 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; #在mysql中调用set @x=3;call P4 (@x); select @x; #在python中基于pymysql调用cursor. Callproc (' P4 ', (3,)) print ( Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' select @_p4_0; ') print (Cursor.fetchall ())

　　

four executing stored procedures

　

--Parametric call Proc_name ()-parameter, full incall proc_name-parameters, In,out,inoutset @t1 =0;set @t2 =3;call proc_name (@t1, @t2) Execute stored procedure execute stored procedure in MySQL--parameterless call Proc_name ()--parameter, full incall proc_name--parameters, In,out,inoutset @t1 =0;set @t2 =3;call Proc_name (@t1, @t2)

　　

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 Stored procedure cursor.callproc (' P1 ', args= (1, 22, 3, 4)) # Gets the stored parameter cursor.execute ("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = Cursor.fetchall () conn.commit () cursor.close () Conn.close () print (result)

　　

Five Delete stored procedures

drop procedure Proc_name

　　

Database: MySQL built-in features-stored procedures