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'.
Related Posts of The basic syntax of mysql stored procedure articles
ROR is Ruby on Rails. Ruby is a well-known has been very good dynamic language It's dynamic language. Simple and easy. Dynamic languages are interpreted, but the performance may make a discount, but not absolute, because the application is complex, th
Today, the use of hibernate in the company encountered a troublesome problem, the use of hibernate when the primary key generation strategy set sequence, but always reported in the implementation could not get next sequence value of the error, then o ...
hibernate using myeclipse automatically generated HBM mapping file will be schema ="***" catalog ="***" Separately are the database instance name and database name, but in fact has been stated jdbc url connection database, are the ...
hibernate lock mechanism 1. Pessimistic lock It refers to the modification of data by outsiders hold a conservative attitude. The assumption that at any time access to data, may also have another client to access the same data, in order to maintain t ...
[Problem] Now, in the development of the so-called multi-storey JavaEE applications, data persistence layer is always essential, and "Automatic" of ORM - Hibernate, and "all-manual-type" of SqlMap - IBatis, equivalent data are Per ...
The use of hibernate, implementation of data persistence. Has the following several processes. One configuration database connection information. Hibernate.config 2 configuration mapping. 3 use: the use of the process are the following steps: 3.1: Ge ...
The origin of ideas are pretty long history of reasons: [Use iBATIS history] The use of iBATIS has been a long time, the system is to use the CRUD template tool to generate the code, although there are tools to generate, but looked at a lot of CRUD the Sq
hibernate Lazy strategy can be used in: <class> tag, it can be true / false Tags can <PROPERTY> values true / false type of necessary tools to enhance <set> <list> can tag values true / false / extra <many-to-one> <on ...