The MySQL stored procedure of the 6Python All-Stack Road series

The MySQL stored procedure of the Python full stack Road series

A stored procedure is a collection of SQL statements in which the internal SQL statements are executed logically when the stored procedure is actively invoked.

Stored procedure over-received parameters

Parameters	Description
in	Used only for incoming parameters
out	Used only for return values
inout	Can be passed in and can be used as a return value

Create a stored procedure

Create a simple stored procedure

--  modify the SQL statement terminator create this stored procedure for%delimiter %--  first to delete the DROP PROCEDURE IF EXISTS PROC_P1  %CREATE PROCEDURE PROC_P1 ()--  start BEGIN    -- SQL statement block      select * from color;--  End end %--  Change the Terminator of the SQL statement to; delimiter ; 

By call calling a stored procedure

Call PROC_P1 ();

Output to

+-----+--------+| Nid |   Title |+-----+--------+| 1 |   Red | | 2 | Yellow |+-----+--------+2 rows in Set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

To delete a stored procedure

DROP PROCEDURE Proc_p1;

Instance

Create a stored procedure, receive a parameter, the passed parameter is the number of display data,

Delimiter%drop PROCEDURE IF EXISTS proc_p1%create PROCEDURE proc_p1 (--I1 is the parameter passed in, the data type must be int type in I1 int) BEGIN    --Define two local variables D1 and D2, the data type is int,d1 default value is empty, d2 default value is 1 DECLARE d1 int;    DECLARE D2 int DEFAULT 1;    --The value of D1 equals the value passed in I1 plus the defined local variable d2 SET d1 = I1 + d2; --Find the data in the Person_info table with nid greater than D1 SELECT * from Person_info WHERE nid > D1; END%delimiter;

Query, enter the defined parameters in parentheses

Call PROC_P1 (4);

Show results

+-----+------+------------------+-------------+----------+----------+---------+-----------+| nid |  name | email            |  phone       | part_nid | position | caption |  color_nid |+-----+------+------------------+-------------+----------+----------+---------+--------- --+|   6 | w    | [email protected] |  13800138000 |        5 | python   |  null    |      null | |    9 | aa   | [email protected]      | 13800138000 |        3 | DBA       | null    |         2 | |   10 | b    | b.ansheng.me     |  13800138000 |        3 | dba       | null    |         1 | +-----+------+------------------+-------------+----------+----------+---------+-----------+3 rows in  set  (0.00 sec) query ok, 0 rows affected  (0.01 sec)

This time tonidGreater than5The data is all output, and the value passed in is4, we are internally letting4+1, so it's more than5The data.

DELIMITER %DROP PROCEDURE IF EXISTS PROC_P1 %CREATE PROCEDURE PROC_P1 (     --  received three parameters, the type is int    in i1 int,     inout ii int,    out i2 int) begin    --   defines a local variable d2, the default value is 3, and the data type is int    declare d2 int default 3;     -- ii = ii + 1    set ii = ii  + 1;    --  if the incoming i1 equals 1    if i1 = 1  THEN        -- i2 = 100 + d2         set i2 = 100 + d2;    - -  if the incoming i1 equals 2    elseif i1 = 2 then         -- i2 = 200 + d2        set i2 = 200 +  d2;    --  otherwise     ELSE         -- i2 = 1000 + d2        set  i2 = 1000 + d2;    END IF; end %delimiter ;

View data

Set @o = 5; Call PROC_P1 (1,@o,@u); SELECT @o,@u;

The results displayed

+------+------+| @o |    @u |+------+------+|  6 | 103 |+------+------+1 row in Set (0.00 sec)

Manipulating stored procedures using the Pymysql module

The Python code is:

Import Pymysqlconn = Pymysql.connect (host= "127.0.0.1", port=3306, user= ' root ', passwd= ' as ', db= "dbname") cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) # Execute stored Procedure row = Cursor.callproc ("Proc_p2", (#) # stored procedure query Result SELC = Cursor.fetchall () print (SELC) # Get stored procedure return Effect_row = Cursor.execute (' Select @_proc_p2_0, @_proc_p2_1, @_proc_p2_2 ') # Fetch stored procedure return value result = Cursor.fetchone () print (result) Conn.commit () Cursor.close () Conn.close ()

The results displayed

C:\Python\Python35\python.exe D:/pycharmprojects/pymysql_ stored procedure. py[{' nid ': 1, ' name ': ' Man1 '}, {' Nid ': 2, ' name ': ' Man2 ' }, {' Nid ': 3, ' name ': ' man3 '}]{' @_proc_p2_1 ': 3, ' @_proc_p2_0 ': 1, ' @_proc_p2_2 ': 103}process finished with exit code 0

Stored procedures using into

into is actually putting a select 's execution results as another select , such as the following instance:

delimiter %drop procedure if exists proc_p2 %create procedure proc_p2 () begin    --  defines a local variable n, which is of type int    declare n int;     --  get COLOR_NID = 2 data and assign value to n    select color_nid  into n from person_info where color_nid = 2;    --   Output Nid = n Data     select * from color where nid  = n; end %delimiter ; 

Perform

Call PROC_P2 ();

Results

+-----+--------+| Nid |   Title |+-----+--------+| 2 | Yellow |+-----+--------+1 row in Set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

#Python全栈之路

The MySQL stored procedure of the 6Python All-Stack Road series