Some problems of stored procedures and functions in MySQL _php tips

Recently wrote some mysql stored procedures and functions, found that the online more valuable documents are very few, most of them are copied from the manual, some practical problems can not be solved, such as the use of variables as table name.

After repeated debugging, finally found a solution, is a few simple records, relatively fragmented. Part of the content transferred from http://my.opera.com/Dereky/blog/show.dml/322997


1. Use variable to make table name:

The simple use of a set or Declare statement to define a variable, and then directly as the SQL table name is not possible, MySQL will be the variable name as the table name. This is also true in other SQL databases, where the solution is to use the entire SQL statement as a variable, interspersed with a variable as the table name, and then invoke the statement with sp_executesql.

This is not possible before the mysql5.0, after 5.0 introduced a completely new statement, can achieve similar sp_executesql function (only for procedure valid, 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 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 shown 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 "select * from TBL LIMIT?,?";
mysql> EXECUTE STMT USING @skip, @numrows;

Several points of attention using PREPARE:
A:prepare stmt_name from PREPARABLE_STMT, predefined a statement and assigning it to Stmt_name, Tmt_name is case-insensitive.
B: Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclosed in quotes.
C: If the new PREPARE statement uses an existing stmt_name, then 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 predefined statement, you can use the deallocate PREPARE syntax.
F:execute stmt_name syntax, if Stmt_name does not exist, an error will be raised.
G: If 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 predefined statements, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most show syntax are supported.
I:prepare statements cannot 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 ';

It's easy to understand the usage of prepare and use variables to make table names. However, some other problems were found during the actual operation, such as variable definition, declare variable and set @var =value variable and parameter passed in variable.

After testing, it is found that the set @var =value such defined variables are converted as variables directly in the string, and declare variables and arguments passed in must be connected by concat. The specific principle is not studied.

EXECUTE stmt using @a; A variable with the following using a statement can only use the set @var =value this, declare and arguments passed into the variable.

In addition, PHP calls the MySQL stored procedures also encountered a lot of problems, always appear procedure P can ' t return a result set in the given context such a problem.