Mysql stored procedures and functions

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

After repeated debugging, we finally found a solution, which is simple and fragmented. Some content is transferred from http://my.opera.com/Dereky/blog/show.dml/322997

1. Use variables as table names:

Simply use the set or declare statement to define the variable, and then directly use it as the SQL table name. mysql regards the variable name as the table name. This is also true in other SQL databases. The solution of mssql is to use the entire SQL statement as a variable, interspersed with variables as the table name, and then call this statement with sp_executesql.

This does not work before mysql5.0. After MySQL 5.0, a new statement is introduced to achieve functions similar to sp_executesql (only effective 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;

To have a perceptual knowledge,
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 later, you can use it in the LIMIT clause, for example:
Mysql> SET @ a = 1; mysql> prepare stmt from "SELECT * FROM tbl LIMIT? ";
Mysql> execute stmt using @;
Mysql> SET @ skip = 1; SET @ numrows = 5;
Mysql> prepare stmt from "SELECT * FROM tbl LIMIT ?, ? ";
Mysql> execute stmt using @ skip, @ numrows;

Notes for using PREPARE:
A: PREPARE stmt_name FROM preparable_stmt; predefines A statement and assigns it to stmt_name. tmt_name is case insensitive.
B: Even in the preparable_stmt statement? It represents a string, and you do not need? Enclosed in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original one will be released immediately! Even if the new PREPARE statement cannot be correctly executed due to an error.
D: The scope of PREPARE stmt_name is visible to the current client connection session.
E: to release resources of a predefined statement, use the deallocate prepare syntax.
F: In the EXECUTE stmt_name syntax, if stmt_name does not exist, an error is thrown.
G: If the deallocate prepare syntax is not explicitly called to release the resource when terminating the client connection session, the server will manually release the resource.
H: In predefined statements, create table, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW syntaxes are supported.
I: The PREPARE statement cannot be used in stored procedures. It is a custom function! However, MySQL 5.0.13 and later versions can be used in Stored Procedures and cannot be used in functions!

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 @;
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 @;
LEAVE lable_exit;
End if;
END lable_exit;
END;
CALL 'p1' (1, NULL );
CALL 'p1' (NULL, 'qq ');
Drop procedure 'p1 ';

After learning about PREPARE usage, it is easy to use variables as the table name. However, some other problems are also found in the actual operation process, such as the definition of variables, the usage of the declare and set @ var = value variables, and the variables passed in by parameters.

After the test, it is found that the variables defined by set @ var = value are directly written in the string and converted as variables. The variables passed in by declare and parameters must be connected using CONCAT. The specific principle is not studied.

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

In addition, php also encountered many problems when calling the mysql stored PROCEDURE. PROCEDURE p can't return a result set in the given context.