MySQL Writing stored procedures

First look at the example:

1, delimiter $$
2. Drop procedure if exists ' test_procedure ' $$
3. CREATE procedure test_procedure (in puser_id varchar ($), in pitem_id varchar ($), out result INT)
4. Begin

5. Select id into result from train_tbl where user_id = puser_id;
6, set result=10;
7, End $$
8, delimiter;

Detailed

1. First use delimiter to change the Terminator from ";" to $$, so that the ";" in the procedure or function is not interpreted by MySQL as the end of the statement and the error is indicated.

After the stored procedure or function has been created, use "delimiter" and then the Terminator to ";".

2, demonstrates the deletion of stored procedures, the name of the stored procedure to delete a stored procedure

3, create a stored procedure, set the name of the stored procedure "Test_procedure", set the parameters of the stored procedure, and its parameter types. The parameters of a stored procedure can be in, out, inout types, and the parameters of the function are only in types.

The difference between out InOut: see http://www.blogjava.net/nonels/archive/2009/04/22/233324.html for details

In type is similar to value passing , this parameter may be modified inside the MySQL stored procedure, but modifications to parameters in the type are not visible to the caller.

For example:

drop procedure if exists pr_param_in;create procedure pr_param_in (   in ID INT--the MySQL stored procedure parameter of type) begin   if (ID I s not null) then      set id = id + 1;   End If;   Select ID as Id_inner;end;

Set @id = 10;call pr_param_in (@id); select @id as Id_out;

Mysql> call pr_param_in (@id); +----------+| Id_inner |+----------+|       |+----------+mysql> Select @id as id_out;+--------+| Id_out |+--------+| Ten     |+--------+

Out  type : The MySQL stored procedure "out" parameter: passes the value from inside the stored procedure to the caller. Inside the stored procedure, the initial value of the parameter is NULL, regardless of whether the caller sets a value for the stored procedure parameter.
INOUT The MySQL stored procedure inout parameter is similar to out, and can be passed from inside the stored procedure to the caller. The difference is that the caller can also pass the value to the stored procedure through the InOut parameter.

4. Begin and end  see: http://dev.mysql.com/doc/refman/5.1/en/begin-end.html
BEGIN end is used to write and coordinate complex sentences, such as stored procedures. A side-by-side compound sentence can include more than one statement, and 0 is also legal.
BEGIN ... The end block can be embedded.
The statements are separated by a delimiter (delimiter) ";".
5, their own query statements.

Resources:

Http://www.blogjava.net/nonels/archive/2009/04/22/233324.html

(The stored procedure has no return value, the function must have a return value)

MySQL Writing stored procedures