Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the usage of various dynamic sql statements in mysql stored procedures. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, let's go straight to the topic. I hope this article on the usage of various dynamic sql statements in mysql stored procedures can bring you some practical help.

The usage of each keyword in a simple stored procedure:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc` (in _ xnb varchar (50)) BEGIN # # definition variable DECLARE _ num FLOAT (14meme 6) DEFAULT 0 # # @ indicates that global variables are equivalent to php $# # splicing assignment INTO must use global variables or the statement will report an error # # / / CONCAT will concatenate 'SELECT SUM (' and _ xnb and') INTO @ tnum FROM btc_user_coin', and the parameters of CONCAT will be separated by "," SET @ strsql = CONCAT ('SELECT SUM (', _ xnb,') INTO @ tnum FROM btc_user_coin') # # pre-processing dynamic SQL to be executed, where stmt is a variable PREPARE stmt FROM @ strsql; # # execute SQL statement EXECUTE stmt; # # release the preprocessing segment deallocate prepare stmt; # # assign to the defined variable SET _ num = @ tnum; SELECT _ numEND;;mysql stored procedure using dynamic sql statement

Dynamic sql statements are supported after Mysql 5.0.We can get the values we want by passing different parameters

Here are two kinds of dynamic sql in stored procedures

1.set sql = (preprocessed sql statement, which can be concatenated with concat)

Set @ sql = sql

PREPARE stmt_name FROM @ sql

EXECUTE stmt_name

{DEALLOCATE | DROP} PREPARE stmt_name

Process example:

CREATE DEFINER = `root` @ `% `PROCEDURE `NewProc` (IN `root` varchar (36), IN `USER_ NAME` varchar (36)) BEGIN declare SQL_FOR_SELECT varchar (500);-- define the preprocessing sql statement set SQL_FOR_SELECT = CONCAT ("select * from user where user_id ='", USER_ID, "'and user_name ='", USER_NAME, "'");-- stitching query sql statement set @ sql = SQL_FOR_SELECT PREPARE stmt FROM @ sql;-- preprocess dynamic sql statement EXECUTE stmt;-- execute sql statement deallocate prepare stmt;-- release prepareEND

The above is a simple stored procedure for querying the user table. When we call this stored procedure, we can get different values according to different parameters passed in.

However, in the above stored procedure, we must define the USER_ID,USER_NAME before concatenating the sql statement, and after concatenating the sql statement, we cannot change the value of USER_ID,USER_NAME, as follows:

CREATE DEFINER = `root` @ `% `PROCEDURE `NewProc` (IN `root` varchar (36), IN `USER_ NAME` varchar (36)) BEGIN declare SQL_FOR_SELECT varchar (500);-- define the preprocessing sql statement set SQL_FOR_SELECT = CONCAT ("select * from user where user_id ='", USER_ID, "'and user_name ='", USER_NAME, "'");-- stitching query sql statement set @ sql = SQL_FOR_SELECT PREPARE stmt FROM @ sql;-- preprocess dynamic sql statement EXECUTE stmt;-- execute sql statement deallocate prepare stmt;-- release prepare set USER_ID = '2percent;-- actively specify the value of parameter USER_ID set USER_NAME =' lisi'; set @ sql = SQL_FOR_SELECT; PREPARE stmt FROM @ sql -- preprocessing dynamic sql statement EXECUTE stmt;-- executing sql statement deallocate prepare stmt;-- releasing prepareEND

We use call aa to call the stored procedure, the first dynamic execution, we get the information of 'Zhang San', and then we change USER_ID,USER_NAME to lisi on lines 14 and 15, we hope to get the relevant information of Li Si, but the result is still Zhang San's information, indicating that we can no longer change the parameters after concatenating the sql statement.

In order to solve this problem, the second way is introduced below:

2.set sql = (preprocessed sql statement, which can be concatenated with concat, with parameters using? Instead of)

Set @ sql = sql

PREPARE stmt_name FROM @ sql

Set @ var_name = xxx

EXECUTE stmt_name USING [USING @ var_name [, @ var_name].]

{DEALLOCATE | DROP} PREPARE stmt_name

We can change the above code to:

CREATE DEFINER = `root` @ `% `PROCEDURE `NewProc` (IN `root` varchar (36), IN `USER_ NAME`

Varchar (36)) BEGIN

Declare SQL_FOR_SELECT varchar (500);-define preprocessing sql statements

Set SQL_FOR_SELECT = "select * from user where user_id =? and user_name =?"

-- stitching query sql statements

Set @ sql = SQL_FOR_SELECT

PREPARE stmt FROM @ sql;-- preprocessing dynamic sql statements

Set @ parm1 = USER_ID;-- pass sql dynamic parameters

Set @ parm2 = USER_NAME

EXECUTE stmt USING @ parm1, @ parm2;-- execute the sql statement

Deallocate prepare stmt;-release prepare

Set @ sql = SQL_FOR_SELECT

PREPARE stmt FROM @ sql;-- preprocessing dynamic sql statements

Set @ parm1 = '2parameters;-- pass sql dynamic parameters

Set @ parm2 = 'lisi'

EXECUTE stmt USING @ parm1, @ parm2;-- execute the sql statement

Deallocate prepare stmt;-release prepare

END

In this way, we can actually use different parameters (of course, we can also logically generate different parameters in the stored procedure) to use dynamic sql.

A few notes:

The variable that stores the value of the dynamic SQL cannot be a custom variable, but must be a user variable or a global variable such as: set sql = 'xxx'; prepare stmt from sql; is wrong, correct: set @ sql =' xxx'; prepare stmt from @ sql

Even if in the preparable_stmt statement? What it represents is a string, and you don't need to? Enclose it in quotation marks.

If in is used in dynamic statements, the normal way to write it is as follows: select * from table_name t where t.field1 in.

The sql statement should be written like this: set @ sql = "select * from user where user_id in (?,?)"

I tried to write this because I'm not sure how many parameters there are in the in statement.

Set @ sql = "select * from user where user_id in"

Then the parameter I passed is "'1century sql," which I thought would parse my dynamic sql ("select * from user where user_id in" ("" 1 "), but it didn't. When you write the list in the stored procedure in with an input parameter, you need to use the following method:

1. Use the find_in_set function

Select * from table_name t where find_in_set (t.field1 recorder, 1 recorder, 2 mine3, 4')

two。 Another clumsy way is to assemble a string and then execute

DROP PROCEDURE IF EXISTS photography.Proc_Test;CREATE PROCEDURE photography.`Proc _ Test` (param1 varchar (1000)) BEGINset @ id = param1;set @ sel = 'select * from access_record t where t.ID in ('; set @ sel_2 =')'; set @ sentence = concat (@ sel,@id,@sel_2);-- the connection string generates the SQL statement to be executed prepare stmt from @ sentence;-- precompile it. Name of "stmt" precompiled variable, execute stmt;-- execute SQL statement deallocate prepare stmt;-- release resource END

Mysql stored procedures in the use of a variety of dynamic sql statements to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.