8.7.4 MySQL built-in features-stored procedures

A stored procedure describes 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. SQL statement to replace program write, implement program with 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:

#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 create a simple stored procedure (no parameter)

// CREATE PROCEDURE p1 () BEGIN     Select  from  //delimiter; #在mysql中调用call p1 () #在python中基于pymysql调用cursor. Callproc ('p1  ') print (Cursor.fetchall ())

Mysql>Use cmz;database changedmysql>show tables;+---------------+| TABLES_IN_CMZ |+---------------+|class|| Class_grade | | Course | | Score | | Student | | Teacher | | Teacher2cls |+---------------+7Rowsinch Set(0.00sec) MySQL> Delimiter//# indicates that the end of the SQL statement is//and not;Mysql>CREATE PROCEDURE P1 ()-BEGIN-Select* fromscore; -END//Query OK,0Rows Affected (0.00sec) MySQL>delimiter; # Restore the end flag bit of the SQL statement; MySQL>Call P1 (); # Call a stored procedure+-----+------------+-----------+-------+| Sid | student_id | course_id | Score |+-----+------------+-----------+-------+|1|1|1| -||2|1|2| -||3|2|2| -||4|3|2| the|+-----+------------+-----------+-------+4Rowsinch Set(0.00sec) Query OK,0Rows Affected (0.01Sec

Operation Process

Pycharm reduced-use stored procedures

#!/usr/bin/Env python# _*_ coding:utf-8_*_Import pymysql# Establish connection conn=Pymysql.connect (Host="127.0.0.1", Port=3306, the user="CMZ", passwd="CMZ", DB="CMZ", # Library with stored procedure charset="UTF8") # Get cursors cursor=conn.cursor () Cursor.callproc ("P1"# Call stored procedure, p1 bit stored procedure name print (Cursor.fetchall ()) # Get Data Cursor.close () Conn.close ()

The result is

C:\Python35\python.exe The stored procedure of the D:mysql module. PY ((111), (21  2), (322), (432  ))

Result and consistent on the terminal

Three create stored procedures (with parameters)

for stored procedures, you can receive parameters with three types of parameters: # inch           only used for incoming parameters with #out          only for return values with #inout       can both be passed in and can be used as return values

Mysql> delimiter//Mysql> CREATE PROCEDURE P2 (inchN1int,inchN2int, outResint)    -BEGIN-Select* fromScorewhereCourse_id=n1 and score >N2; -Setres =1; -END//Query OK,0Rows Affected (0.00sec) MySQL>delimiter;MySQL>Set@x=0; Query OK,0Rows Affected (0.00sec) MySQL> Call P2 (2, -, @x); # called in MySQL+-----+------------+-----------+-------+| Sid | student_id | course_id | Score |+-----+------------+-----------+-------+|4|3|2| the|+-----+------------+-----------+-------+1Rowinch Set(0.00sec) Query OK,0Rows Affected (0.01sec) MySQL>Select@x; # View results after execution+------+| @x |+------+|1|+------+1Rowinch Set(0.00sec)

In the Pycharm

Import pymysql# Establish connection conn=Pymysql.connect (Host="127.0.0.1", Port=3306, the user="CMZ", passwd="CMZ", DB="CMZ", CharSet="UTF8") # Get cursors cursor=conn.cursor () Cursor.callproc ('P2',(2, -,0) # in Python based on the Pymysql call, 0 corresponds to set @x=0print (Cursor.fetchall ()) Cursor.execute ("Select @_p2_2"# @_p2_0=2 represents the first argument, @_p2_1=60 represents the second argument, @_p2_2=0 represents the third parameter, print (Cursor.fetchall ()) # query Select query result Cursor.close () CO Nn.close ()

Results

C:\Python35\python.exe d:mysql/Thestored procedure of the MySQL module. PY ((432), ((  1,),)

Application combined with Database
Mode 1:
Python: Calling a stored procedure
MySQL: Writing Stored procedures

Mode 2:
Python writes native SQL
Mysql

Mode 3:
Python orm-> Native SQL
Mysql

8.7.4 MySQL built-in features-stored procedures