Learning the basic grammar of the main reference to the mysql User's Guide
Mysql database stored procedure needs to have proc Table. The table created during the MySQL 5.1 installation. If you are upgrading from an earlier version to MySQL 5.1, make sure to update your grant tables to ensure that the proc table exists. (Mysql database installed in the mysql database which has a proc table)
Create mysql stored procedure permissions problem
. Creating Stored Routines and CREATE ROUTINE privileges.
Remind or remove storage routines need ALTER ROUTINE privileges. The permission granted automatically subroutine founder.
* Implementation of the subroutine needs EXECUTE permissions. However, the rights automatically granted to the creator of subroutines. Similarly, the subroutine default SQL SECURITY characteristic is DEFINER, which allows users to access the database of the subroutine and the implementation of the subroutine linked together.
Stored procedures and functions is to use CREATE PROCEDURE and CREATE FUNCTION statement to create a subroutine. The current study only the CREATE PROCEDURE
In MySQL 5.1, a stored subprogram or a function associated with a particular database. Here are a few meanings:
* When a subroutine is called, an implicit USEdb_name be executed (when the subroutine to stop the implementation of the termination). USE statements within stored routines when not allowed.
* You can use the database name qualified subroutine name. This can be used to refer to a subroutine is not in the current database. For example, to refer to a database associated with the test procedure p or function stored f, you can say CALL test.p () or test.f ().
* Remove the database when it is associated with all the storage routines are also removed.
CREATE PROCEDURE basic syntax:
CREATE PROCEDURE sp_name ([proc_parameter [,...]])
[Characteristic ...] routine_body
| [NOT] DETERMINISTIC
| (CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA)
| SQL SECURITY (DEFINER | INVOKER)
| COMMENT 'string'
Valid SQL procedure statement or statements
Note: The specified parameter is IN, OUT, or INOUT is valid only PROCEDURE. (FUNCTION parameters are always considered to be IN parameters)
Compound statement BEGIN ... END
Storage subroutine can use BEGIN ... END compound statement to include multiple statements. statement_list statement on behalf of one or more of the list. Each statement within statement_list must use a semicolon (;) to the end.
Begin_label exist unless otherwise end_label can not be given, and if both are present.
Stored procedure variables
1, DECLARE Local Variables
2, the variable SET statement
3, SELECT ... INTO statement
1.1 DECLARE var_name [,...] type [DEFAULT value]
This statement is used to declare local variables. To give the variable a default value, please include a DEFAULT clause. Value can be specified as an expression, do not need a constant. If no DEFAULT clause, the initial value of NULL.
Scope of local variables declared in it is within the BEGIN ... END block. It can be used in the nested block, in addition to those variables with the same name statement block.
2.1 Variable SET Statement
SET var_name = expr [, var_name = expr] ...
3.1 SELECT ... INTO statement
SELECT col_name [,...] INTO var_name [,...] table_expr
This SELECT syntax stores the selected columns directly into variables. Therefore, only a single line can be recovered.
SELECT id, data INTO x, y FROM test.t1 LIMIT 1;
Note that user variable names in the MySQL 5.1 is not case sensitive.
Important: SQL variable names and column names can not be the same. If such a SQL SELECT ... INTO statement contains a column reference, and includes a local variable the same name listed, MySQL currently interpreted to refer to a variable name. For example, in the following statement, xname be interpreted as reference to xnamevariable not to xnamecolumn of:
CREATE PROCEDURE sp1 (x VARCHAR (5))
DECLARE xname VARCHAR (5) DEFAULT 'bob';
DECLARE newname VARCHAR (5);
DECLARE xid INT;
SELECT xname, id INTO newname, xid
FROM table1 WHERE xname = xname;
When this procedure is called, regardless of the value of table.xname out what the return value variable newname 'bob'.