Mysql-prepare statements

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