Mysql-prepare statements

Source: Internet
Author: User
Tags pow stmt

Mysql-prepare statements

Feature Description:

MySQL Prepare statement usage

In order to prepare the statement using MySQL, you need to use the other three MySQL statements as follows:

PREPARE -The statement that is ready to be executed.

Execute -executes PREPARE the statement defined by the statement.

  deallocate PREPARE -publish PREPARE statement.

PREPAREStatement using:

PREPARE ' SELECT ProductCode, ProductName From the products =?'                      ;  's10_1678';  EXECUTE stmt1 USING @pc; deallocate PREPARE stmt1;

First, use the PREPARE statement to prepare the execution statement. We use SELECT the statement to query the product data from the table according to the specified product code products . Then use the question mark ( ? ) as a placeholder for the product code.

Second, a product code variable @pc is declared and its value is set to S10_1678 .

Third, use EXECUTE the statement to execute the prepared statement for the product code variable @pc.

We use it DEALLOCATE PREPARE to publish the PREPARE statement.

Instance:

This statement is used to calculate the hypotenuse of a triangle given the length of two edges.

Example 1: Shows how to create a prefabricated statement that provides the text of a statement by using a literal string:

' SELECT SQRT (Pow (?, 2) + POW (?, 2)) as Hypotenuse ' MySQL> Set @a = 3; MySQL> Set @b = 4; MySQL> EXECUTE stmt1 USING @a, @b; +------------+| Hypotenuse |+------------+|     5 |+------------+mysql> deallocate PREPARE stmt1;

Example 2:: Similar to Example 1, the text of the statement is provided as a user variable:

' SELECT SQRT (Pow (?, 2) + POW (?, 2)) as Hypotenuse ' mysql> PREPARE stmt2 from @s;mysql> Set @a = 6; MySQL> Set @b = 8; MySQL>
   
     EXECUTE stmt2 USING @a, @b; +------------+| Hypotenuse |+------------+|     Ten |+------------+
    mysql> deallocate PREPARE stmt2;
   

Example 3: For a prepared statement, you can use the position hold character.

The following statement returns a row from the TB1 table:

mysql> SET @a=1; MySQL"select * from TBL LIMIT? " ; MySQL> EXECUTE STMT USING @a;

The following statement returns the second to the sixth row from the TB1 table:

SETSET@numrows=5;
 mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?" ;  mysql> EXECUTE STMT USING @skip, @numrows;

Reference Blog: http://www.cnblogs.com/simpman/p/6510604.html

Mysql-prepare statements

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.