Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use PREPARE, EXECUTE and DEALLOCATE sentences in MySQL 5.7. I believe most people don't know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

The PREPARE statement prepares a SQL statement and assigns a name to the SQL statement to be called later. The prepared SQL statements are executed by the EXECUTE command and released by the DEALLOCATE PREPARE command.

The name of the statement is not case-sensitive. The name of the prepared SQL statement can be a string or a user-specified variable that contains SQL text. The SQL text in PREPARE must represent a single SQL statement, not multiple SQL statements. In the SQL statement,? The character is used as a parameter for later execution of the query. ? You can't put quotation marks, and you can't bind them to character variables in time.

If the prepared SQL statement name already exists, it will be released before the new statement is ready. This means that if a new statement contains errors and cannot be prepared, an error will be returned and the prepared SQL statement will no longer exist.

The prepared statement scope is the session in which it was created, with the following characteristics:

The prepared statement is not valid in another session

When the session ends, regardless of whether the session ends normally or abnormally, the SQL statements prepared in the session will no longer exist. If the automatic connection function is turned on, the client will not be notified that the connection is lost.

Prepared statements in a stored procedure or function continue to exist after the execution of the stored procedure or function and can continue to be executed outside the stored procedure or package.

Example:

Mysql > SET @ axi10

Query OK, 0 rows affected (0.00 sec)

Mysql > PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT?'

Query OK, 0 rows affected (0.08 sec)

Statement prepared

Mysql > EXECUTE STMT USING @ a

+-+ +

| | deptno | dname |

+-+ +

| | 10 | A |

| | 20 | B |

| | 30 | C |

| | 40 | D | |

| | 50 | E |

| | 60 | F | |

| | 70 | G |

| | 80 | H |

| | 90 | I |

| | 100 | J |

+-+ +

10 rows in set (0.02 sec)

Mysql > SET @ skip=1; SET @ numrows=5

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT?

Query OK, 0 rows affected (0.00 sec)

Statement prepared

Mysql > EXECUTE STMT USING @ skip, @ numrows

+-+ +

| | deptno | dname |

+-+ +

| | 20 | B |

| | 30 | C |

| | 40 | D | |

| | 50 | E |

| | 60 | F | |

+-+ +

5 rows in set (0.00 sec)

Mysql > DEALLOCATE PREPARE STMT

Query OK, 0 rows affected (0.00 sec)

Mysql > EXECUTE STMT USING @ skip, @ numrows

ERROR 1243 (HY000): Unknown prepared statement handler (STMT) given to EXECUTE

Mysql > PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT?

Query OK, 0 rows affected (0.00 sec)

Statement prepared

Mysql > EXECUTE STMT USING @ skip, @ numrows

+-+ +

| | deptno | dname |

+-+ +

| | 20 | B |

| | 30 | C |

| | 40 | D | |

| | 50 | E |

| | 60 | F | |

+-+ +

5 rows in set (0.00 sec)

Sample stored procedure

Delimiter $$

Create procedure registerSQLReference (IN the_table_name VARCHAR (80), IN the_sql_script_version VARCHAR (80), IN the_sql_script_name VARCHAR (80)

IN the_install_version VARCHAR (80), IN the_execution_duration VARCHAR (80), IN the_sql_script_description VARCHAR

BEGIN

Declare column_exist INT DEFAULT 0

Declare the_query VARCHAR (500)

Select'[INFO] Check if the column C_CHANGE_DESCRIPTION exists in the table @ VERSION_LEVEL_TABLE_NAME'

Set column_exist = is_ChangeDescColumnExist ('@ VERSION_LEVEL_TABLE_NAME')

IF column_exist = 0

THEN

Select'[INFO] VERSION LEVEL TABLE does not contain C_CHANGE_DESCRIPTION column.'

Set @ v_the_table_name=the_table_name

Select concat ('INSERT INTO', @ v_the_table_name, 'VALUES (?)') into the_query

SET @ stmt=the_query

PREPARE STMT FROM @ stmt

Select concat ('[INFO] the_query=', the_query)

Set @ v_the_sql_script_version=the_sql_script_version

Set @ v_the_sql_script_name=the_sql_script_name

Set @ v_date=now ()

Set @ v_the_install_version=the_install_version

Set @ v_the_execution_duration=the_execution_duration

EXECUTE STMT using @ v_the_sql_script_version, @ v_the_sql_script_name, @ v_date, @ v_the_install_version, @ v_the_execution_duration

ELSE

Select'[INFO] VERSION LEVEL TABLE contains C_CHANGE_DESCRIPTION column.'

Select concat ('INSERT INTO', the_table_name, 'VALUES') into the_query

SET @ stmt=the_query

PREPARE STMT FROM @ stmt

Select concat ('[INFO] the_query=', the_query)

Set @ v_the_sql_script_version=the_sql_script_version

Set @ v_the_sql_script_name=the_sql_script_name

Set @ v_date=now ()

Set @ v_the_install_version=the_install_version

Set @ v_the_execution_duration=the_execution_duration

Set @ v_the_sql_script_description=the_sql_script_description

Select concat ('[INFO] the_query=', the_query)

EXECUTE the_query using @ v_the_sql_script_version, @ v_the_sql_script_name, @ v_date, @ v_the_install_version, @ v_the_execution_duration, @ v_the_sql_script_description

END IF

DEALLOCATE PREPARE STMT

END$$

Delimiter

These are all the contents of this article entitled "how to use PREPARE, EXECUTE and DEALLOCATE sentences in MySQL 5.7.Thank you for reading!" I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.