- PREPARE statement_name from sql_text/* definition */
- Execute statement_name [USING variable [, variable ...]]/* Execute preprocessing statement */
- deallocate PREPARE statement_name/* Delete definition */
This is used in my project, as a reference to use:
DELIMITER $ $DROP PROCEDURE IF EXISTS ' gpsdata '. ' Sp_test ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_test ' ( Gpstime_ varchar (gpsname_), Gpsinfo_ varchar (begindeclare tbname varchar) DEFAULT ' 0 ';D Eclare v_sql varchar (1024x768) DEFAULT ' 0 '; SET v_sql=concat (' select * from ', Tbname, ' where Gpsname = ', gpsname_, ' ORDER by gpstime desc limit 1 '); SET @lastdata = V_sql; PREPARE lastdata from @lastdata; EXECUTE lastdata;deallocate PREPARE lastdata; Select V_sql; end$ $DELIMITER;
Several points of note for using PREPARE:
A:prepare stmt_name from preparable_stmt;
Predefined a statement and assigns it to Stmt_name, Stmt_name is case insensitive.
B: Even if the PREPARABLE_STMT statement represents a string, you do not need to enclose it in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original will be released immediately!
Even if the new PREPARE statement cannot be executed correctly because of an error. The scope of the
D:prepare Stmt_name is that the current client connection session is visible.
E: To release a resource for a pre-defined statement, you can use the deallocate PREPARE syntax.
F:execute stmt_name syntax, if Stmt_name does not exist, an error will be raised. &NBSP
G: If you do not explicitly call deallocate PREPARE syntax to release resources when terminating a client-side connection session, the server will release it itself.
H: In a predefined statement, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported. The
I:prepare statement cannot be used for stored procedures (more than 5.0 can be used) and custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions!