Knowledge points: The stored procedure for basic Mysql usage

Stored procedure One, Introduction

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. SQL statement to replace program write, implement program and SQL decoupling
• 2. 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:

• Programmer extension is not a convenient feature

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

• Way One:

MySQL: Stored Procedure

Programs: Calling Stored Procedures

• Way two:

Mysql:

Program: Pure SQL statement

• Way three:

Mysql:

Programs: Classes and objects, i.e. ORM (essentially or purely SQL statements)

Ii. creating a simple stored procedure (no parameters)

Examples of non-reference

//          #定义sql的结束语句为 //CREATE PROCEDURE p1 () BEGIN    * from blog;    INSERT into blog (name,sub_time) VALUES ("xxx"//delimiter;           #定义sql的结束语句为 #在mysql中调用call p1 () #在python中基于pymysql调用cursor. Callproc (' p1 ') print (Cursor.fetchall ()) 

Iii. Creating stored procedures (with parameters)

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

In only for incoming parameters

Out is used only for return values

InOut can be passed in and can be used as a return value

In application examples:

// CREATE PROCEDURE P2 (    int,    int) BEGIN        * from blog where ID >//delimiter; #在mysql中调用call p2 (3,2) #在python中基于pymysql调用cursor. Callproc ( ' P2 ', (3,2)) print (Cursor.fetchall ())

Example of application of out:

// CREATE PROCEDURE P3 (    int,    int) BEGIN    * from blog where ID > N1;     = 1//delimiter; #在mysql中调用set @res=0; #0代表假 (execution failed), 1 is 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 parameter, which is the return value print (Cursor.fetchall ())

Examples of application of inout:

// CREATE PROCEDURE P4 (    int) BEGIN    * from blog where ID > n1;     = 1//delimiter; #在mysql中调用set @x=3; call P4 (@x); Select @x;# Call Cursor.callproc (' P4 ', (3,)) print (Cursor.fetchall ()) #查询select的查询结果cursor in Python based on Pymysql. Execute ( ' select @_p4_0; ' ) Print (Cursor.fetchall ())

Examples of transactions and stored procedures:

#介绍delimiter//CREATE PROCEDURE P4 (out statusint) BEGIN1declares that the {set status is executed if an exception occurs.= 1;                Rollback } Start Transaction--big mu mu account minus--two mu mu account plus--three mu mu account plus ten commits; End Set Status= 2; END//delimiter; #实现delimiter//Create PROCEDURE P5 (out P_return_code tinyint) BEGIN DECLARE exit Handler forSqlException BEGIN--ERROR Set P_return_code= 1;     Rollback     END; DECLARE Exit Handler forsqlwarning BEGIN--WARNING Set P_return_code= 2;     Rollback     END;         START TRANSACTION; DELETE from TB1; #执行失败 INSERT INTO blog (name,sub_time) VALUES (' YYY ', now ());     COMMIT; --SUCCESS Set P_return_code= 0; #0代表执行成功END//delimiter, #在mysql中调用存储过程set @res=123; call P5 (@res); select @res; #在python中基于pymysql调用存储过程cursor. Callproc (' P5 ', (123,)) Print (Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' Select @_p5_0; ') Print (Cursor.fetchall ())

Iv. execution of stored procedures

Execute the stored procedure in MySQL:

--Parameter call proc_name ()-- parameter, full incall proc_name-- parameters, In,out,inoutset @t1=0 ; set @t2=3; call Proc_name (@t1, @t2) Execute the stored procedure

Execute stored procedures based on Pymysql in Python:

Import= pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', password= ' 123 ', db= ' t1 '= Conn.cursor (cursor=pymysql.cursors.DictCursor) # Execute Stored procedure cursor.callproc (' P1 ', args= (1, 3, 4)) # Gets the stored parameter cursor.execute ("Select @_p1_0,@_p1_1,@_p1_2,@_p1_3"= cursor.fetchall () conn.commit () Cursor.close () Conn.close () print (result)

V. Delete a stored procedure

Delete syntax:

drop procedure Proc_name;

Attached: Mysql basic usage

First, "Mysql Basic usage view"

Second, "Mysql basic usage of the trigger"

Third, "Mysql basic usage of the business"

Iv. "Stored Procedures for Mysql basic usage"

V. "Functions of basic Mysql usage"

Vi. "Mysql Basic usage Process Control"

Knowledge points: The stored procedure for basic Mysql usage