MySQL database stored procedure dynamic table creation (PREPARE)

Source: Internet
Author: User

  1. PREPARE statement_name from sql_text/* definition */
  2. EXECUTE statement_name [USING variable [, variable ...]/* Run pre-processing statement */
  3. deallocate PREPARE statement_name/* Delete definition */

This is used in my project and used as a reference:

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 using PREPARE:
A:prepare stmt_name from preparable_stmt;


Define a statement in advance and assign it to Stmt_name, Stmt_name is not distinguished by uppercase or lowercase.


B: Even in the PREPARABLE_STMT statement? Represents a string, and you do not need to. Include them with the quotes.


C: Assuming that the new PREPARE statement uses an existing stmt_name, the original will be released immediately!


Even though this new PREPARE statement cannot be run correctly due to an error.


The scope of 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.




In F:execute stmt_name syntax, it is assumed that Stmt_name does not exist. An error will be raised.




G: Assume that the client connection session is terminated. Without explicitly invoking the deallocate PREPARE syntax to release the resource, the server side will release it itself.


H: In the pre-defined statement. CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported.




I:prepare statements can not be used for stored procedures (more than 5.0 can be used), their own definition of functions!

But from the beginning of MySQL 5.0.13, it can be used for stored procedures, still do not support the use of functions!

MySQL database stored procedure dynamic table creation (PREPARE)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.