Recently wrote some of the mysql stored procedures and functions, found that there are few valuable documents on the Web, mostly according to the manual copied, some practical problems can not be solved, such as the use of variables for the table name.
After repeated debugging, finally found a solution, a few simple records, more fragmented. Part of the content transferred from http://my.opera.com/Dereky/blog/show.dml/322997
1. Use the variable to make the table name:
Simply defining the variable with a set or Declare statement, and then directly as the SQL table name, is not possible, and MySQL will use the variable name as the table name. In other SQL databases as well, the workaround for MSSQL is to use the entire SQL statement as a variable, with the variable as the table name, and then invoke the statement with sp_executesql.
This is not possible before mysql5.0, 5.0 introduced a completely new statement, can achieve similar sp_executesql function (only valid for procedure, function does not support dynamic query):
PREPARE stmt_name from preparable_stmt;
EXECUTE Stmt_name [USING @var_name [, @var_name] ...];
{deallocate | DROP} PREPARE Stmt_name;
In order to have a perceptual understanding,
Here are just a few small examples:
Mysql> PREPARE stmt1 from ' 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;
mysql> SET @s = ' 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 |
+------------+
| 10 |
+------------+
mysql> deallocate PREPARE stmt2;
If your MySQL version is 5.0.7 or higher, you can also use it in the LIMIT clause, as in the following example:
mysql> SET @a=1;mysql> PREPARE STMT from "select * from TBL LIMIT?";
mysql> EXECUTE STMT USING @a;
mysql> SET @skip = 1; SET @numrows = 5;
Mysql> PREPARE STMT from the "select * from TBL LIMIT?,?";
mysql> EXECUTE STMT USING @skip, @numrows;
Several points of note using PREPARE:
A:prepare stmt_name from preparable_stmt; A statement is predefined and assigned to Stmt_name, and tmt_name is case insensitive.
B: Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclose them in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original will be released immediately! Even though this new PREPARE statement cannot be executed correctly because of 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 predefined statement, you can use the deallocate PREPARE syntax.
F:execute stmt_name syntax, if the stmt_name does not exist, an error will be raised.
G: If you do not explicitly invoke the deallocate PREPARE syntax to release resources when terminating a client connection session, the server will release it on its own.
H: In the predefined statements, 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, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions!
Here's an example:
CREATE PROCEDURE ' P1 ' (in ID INT unsigned,in name VARCHAR (11))
BEGIN Lable_exit:
BEGIN
SET @SqlCmd = ' SELECT * from TA ';
IF ID is not NULL then
SET @SqlCmd = CONCAT (@SqlCmd, ' WHERE id=? ');
PREPARE stmt from @SqlCmd;
SET @a = ID;
EXECUTE stmt USING @a;
LEAVE Lable_exit;
END IF;
IF name is not NULL then
SET @SqlCmd = CONCAT (@SqlCmd, ' WHERE name like? ');
PREPARE stmt from @SqlCmd;
SET @a = CONCAT (name, '% ');
EXECUTE stmt USING @a;
LEAVE Lable_exit;
END IF;
END Lable_exit;
END;
Call ' P1 ' (1,null);
Call ' P1 ' (NULL, ' QQ ');
DROP PROCEDURE ' P1 ';
Knowing the usage of prepare, it is easy to use variables to make table names. However, some other problems are found in the actual operation, such as variable definitions, declare variables and set @var =value variables, and the variables passed in by parameters.
After the test, it is found that a variable defined by set @var =value will be converted as a variable directly in the string, and the variable passed by declare variable and parameter must be connected by concat. The specific principle is not studied.
EXECUTE stmt using @a; Such statements using the following variables can only be set @var =value this, declare and arguments passed in the variable does not work.
In addition, PHP calls the MySQL stored procedure also encountered a lot of problems, always appear procedure P can ' t return a result set in the given context such a problem.
http://www.bkjia.com/PHPjc/317913.html www.bkjia.com true http://www.bkjia.com/PHPjc/317913.html techarticle recently wrote some of the mysql stored procedures and functions, found that there are few valuable documents on the Web, mostly according to the manual copied, some practical problems can not be solved, such as the use of variable table name ...
