MySQL Stored procedure Summary (i)

Recently more interested in studying the MySQL scheduled task stored procedure, also began to learn MySQL several query management tools, mainly have navicat for MySQL, SQLyog, MySQL Workbench 6.0, Toad for MySQL 6.0 several tools, Are very powerful, are learning in succession, the following first to the MySQL stored procedures to do some summary.

one, stored procedures

The MySQL stored procedure is a new feature that has been added from MySQL5.0, and the advantages of the stored procedure are many, but the most important is the efficiency of execution and the encapsulation of SQL code. In particular, the SQL code encapsulation, the stored procedure is easy to maintain, the execution is high efficiency.

Second, simple grammar

Create procedure stored procedure name ()

(

[In|out|inout] Parameter datatype

)

Begin

MySQL statement;

End

MySQL stored procedure parameters if the specified ' in ', ' Out ', ' inout ' are not displayed, the default is "in", which is customary, for parameters that are "in", we are not explicitly specified.

Examples of stored procedures

drop procedure if exists pr_add;

--Calculate the sum of two numbers

CREATE PROCEDURE Pr_add (

a int,

b int

) begin

declare c int;

If A is null then

Set a = 0;

End If;

If B is null and then

Set B = 0;

End If;

Set C = a + B;

Select C as Sum; /* Return c;-cannot be used in MySQL stored procedures. Return can only appear in the function.  

Iv. calling a stored procedure

Call pr_add (10, 20); Executes the mysql stored procedure with the stored procedure parameter as a MySQL user variable.

set @a = ten; set @b =;Call Pr_add (@a, @b);

Five, MySQL needs to pay attention to the problem

1. The "()" after the name of the MySQL stored procedure is required, even if there is no parameter, "()"2. MySQL stored procedure parameter, cannot add "@" before parameter name, for example: "@a int". The following create stored procedure syntax is wrong in MySQL (correct in SQL Server). The variables in the MySQL stored procedure do not need to be "@" before the variable name, although the MySQL client user variable should be added "@". CREATE PROCEDURE Pr_add (@a int,-Errorb INT-correct       )3. The parameters of the MySQL stored procedure cannot specify a default value. 4. The MySQL stored procedure does not need to precede the procedure body with "as". The SQL Server stored procedure must be added with the "as" keyword. CREATE PROCEDURE Pr_add (a int,B intas-error, MySQL does not require "as"beginMySQL statement ...;end;5. If the MySQL stored procedure contains more than one MySQL statement, the BEGIN END keyword is required. CREATE PROCEDURE Pr_add (a int,B int) BeginMySQL statement 1 ...; MySQL statement 2 ...;end;6. At the end of each statement in the MySQL stored procedure, add a semicolon ";"       ...declare c int;If A is nullThen set a = 0; End If;      ... end;7. Comments in the MySQL stored procedure. / * This is aMulti-line MySQL annotations.     /declare c int; -This is a single-line MySQL note (note-After at least one space)If A is null then this is also a single-line MySQL commentset a = 0; End If;... end;8. The "return" keyword cannot be used in a MySQL stored procedure. Set c = a + B;select C as sum;/ * Return c;-cannot be used in MySQL stored procedures.  Return can only appear in the function.       /end;9. When calling the MySQL stored procedure, you need to add "()" After the procedure name, even if there is no parameter, "()"Call Pr_no_param ();10. Because the MySQL stored procedure parameter does not have a default value, the parameter cannot be omitted when the MySQL stored procedure is called. can be substituted with null. Call Pr_add (null);