MySQL storage process and pythoncallproc call

The stored procedures pre-compile a series of SQL statements, and store the execution in the database. If you need to use SQL statements to access this set of SQL statements, you can directly extract (

The stored procedures pre-compile a series of SQL statements, and store the execution in the database. If you need to use SQL statements to access this set of SQL statements, you can directly extract (

I. stored procedure)

Stored Procedures pre-compile and execute a series of SQL statements stored in the database, if you need to use SQL statements to access this group of SQL statements, you can directly extract them (a good understanding is that the Stored Procedure stores the SQL Execution Process in the database for extraction convenience ).

Advantages: 1. multiple extraction to reduce Compilation Time. the SQL statement must be input for each extraction. If the stored procedure name is used for calling, the access traffic is reduced by 3. added reuse (comparable (function impact on programming ))

Disadvantages: 1. stored Procedures will occupy memory space, and complex process operations require a certain amount of cpu 2. the stored procedure is difficult to debug. If the stored procedure is too complex, it is not conducive to business logic 3. the storage process is advanced and difficult to maintain.

DELIMITER $ // set comments
Create procedure CountOrderByStatus (
IN orderStatus VARCHAR (25), // you need to specify the parameters used for storage.
OUT total INT) // specify the output parameter.
BEGIN
SELECT count (orderNumber)
INTO total
FROM orders
WHERE status = orderStatus;
END $
DELIMITER;

This stored procedure accepts two process parameters: the input parameter orderStatus, and the output parameter total. Finally, Call CountOrderByStatus ('23', @ title, add @.

Parameter type

IN parameter name parameter type: indicates that this parameter needs to be provided when the storage type is created, so that it can be used IN the following statement

Official example:

DELIMITER //
Create procedure GetOfficeByCountry (IN countryName VARCHAR (255 ))
BEGIN
SELECT *
FROM offices
WHERE country = countryName;
END //
DELIMITER;

CALL GetOfficeByCountry ('usa') // CALL

OUT parameter name parameter Type: this parameter can be returned after all parameter statements are executed.

INOUT parameter name parameter type: the difference between this parameter is that the input parameter needs to be assigned a value, and after callprogram, its parameter value will be modified and returned to the variable value.

DELIMITER $
Create procedure set_counter (INOUT count INT (4), IN inc INT (4) // input an Inout parameter and an in Parameter
BEGIN
SET count = count + inc;
END $
DELIMITER;

Now try to call this stored procedure

SET @ counter = 1;
CALL set_counter (@ counter, 1); -- 2
CALL set_counter (@ counter, 1); -- 3 // The value of @ count has been modified.
CALL set_counter (@ counter, 5); -- 8
SELECT @ counter; -- 8

Predicate Logic:

IF (basic)

IF if_expression THEN commands
[ELSEIF elseif_expression THEN commands]
[ELSE commands]
End if;

While loop

WHILE expression DO
Statements
END WHILE

REPEAT
Statements;
UNTIL expression
END REPEAT

Case

CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
End case;

Note: Use Case to simplify Case

Key Points

How to Use python to call callproc to call the Stored Procedure

1. Create a complete Mysql database connection

2. Use cursor () to initialize the database cursor

3. Use a cursor to call the callproc function to add variables to be passed in, such as callproc (name, args), args = ['21', syh];

3. cursor can transmit a series of result sets and use storeresult to obtain a series of iterator pointing to the result set.

4. Use the fetchall method to obtain the result.

Callproc cannot directly obtain the out and INOUT variables, but the variables exist in the server. You can use @ _ procname_n to obtain the variable value. You can obtain the value based on the input parameter location, for example, 1st SELECT @ _ procname_0.

From mysql. connector import MySQLConnection, Error
From python_mysql_dbconfig import read_db_config

Def call_find_all_sp ():
Try:
Db_config = read_db_config ()
Conn = MySQLConnection (** db_config)
Cursor = conn. cursor ()

Cursor. callproc ('Find _ all ')

# Print out the result
For result in cursor. stored_results ():
Print (result. fetchall ())

Failed T Error as e:
Print (e)

Finally:
Cursor. close ()
Conn. close ()

If _ name _ = '_ main __':
Call_find_all_sp ()

This article permanently updates the link address: