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 ...]]/* Execute preprocessing statement */
  3. 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!

Related Article

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.