Some problems with stored procedures and functions in mysql_PHP tutorial

I recently wrote some MySQL stored procedures and functions, and found that there are very few valuable documents on the Internet. Most of them are copied from the manual, and some practical problems cannot be solved, such as using variables as table names.

After repeated debugging, I finally found a solution. Here are some simple records, which are relatively fragmented. Part of the content is reproduced from http://my.opera.com/Dereky/blog/show.dml/322997


1. Use variables as table names:

Simply use set or It is not possible to define a variable with the declare statement and then use it directly as the table name of SQL. MySQL will treat the variable name as the table name. The same is true in other sql databases. The solution for mssql is to use the entire sql statement as a variable, interspersed with variables as table names, and then use sp_executesql to call the statement.

This was not possible before MySQL 5.0. After 5.0, a brand new statement was introduced, which can achieve functions similar to sp_executesql (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 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. The example is as follows:
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> * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;

A few points to note when using PREPARE:
A: PREPARE stmt_name FROM preparable_stmt; predefined one statement and assign it to stmt_name, tmt_name is not case-sensitive.
B: Even if ? in the preparable_stmt statement represents a string, you do not need to enclose ? in quotes.
C: If the new PREPARE statement uses an existing stmt_name, the original one will be released immediately! Even if this new PREPARE statement cannot be executed correctly due to an error.
D: The scope of PREPARE stmt_name is visible to the current client connection session.
E: To release the resources of a predefined statement, you can use the DEALLOCATE PREPARE syntax.
F: In the EXECUTE stmt_name syntax, if stmt_name does not exist, an error will be triggered.
G: If the DEALLOCATE PREPARE syntax is not explicitly called to release the resource when terminating the client connection session, the server will automatically release it.
H: Among the predefined statements, CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported.
I: The PREPARE statement cannot be used for stored procedures and custom functions! But starting from MySQL 5.0.13, it can be used in stored procedures, but use in functions is still not supported!

The following is 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`;

After understanding the usage of PREPARE, it is easy to use variables as table names. However, some other problems were discovered during the actual operation, such as variable definition, usage of declare variables and set @var=value variables, and variables passed in as parameters.

After testing, it was found that variables defined like set @var=value will be converted as variables if they are written directly in the string. Declare variables and variables passed in as parameters must be connected using CONCAT. The specific principle has not been studied.

EXECUTE stmt USING @a; The variables after USING in such a statement can only be set @var=value. Variables passed in by declare and parameters cannot be used.

In addition, PHP also encounters many problems when calling mysql stored procedures. Problems like PROCEDURE p can't return a result set in the given context always occur.

http://www.bkjia.com/PHPjc/317913.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/317913.htmlTechArticleI recently wrote some mysql stored procedures and functions, and found that there are very few valuable documents on the Internet, and most of them follow the instructions Copied from the manual, some practical problems cannot be solved, such as using variables as table names...