MYSQL stored procedure and function bitsCN.com
I. Mode
II. stored procedures and functions
Syntax:
Create procedure sp_name ([proc_parameter [,...])
[Characteristic...] routine_body
Create function sp_name ([func_parameter [,...])
RETURNS type
[Characteristic...] routine_body
Proc_parameter:
[IN | OUT | INOUT] param_name type
Func_parameter:
Param_name type
Type:
Any valid MySQL data type
Characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT string
Routine_body:
Valid SQL procedure statement or statements
Example: Stored Procedure
Mysql> use test;
Mysql> delimiter // ### define "//" as the statement execution start symbol
Mysql> create procedure simpleproc (out param1 int)
Begin
Select count (*) into param1 from t;
End
//
Mysql> delimiter; #### redefinition; indicates the start of a statement.
Mysql> call simpleproc (@ );
Mysql> select @ a; #### a is a variable.
+ ------ +
| @ A |
+ ------ +
| 1 |
+ ------ +
Difference: The process only processes one thing and does not return a value. However, the processed value can be returned through the exit parameter param1. the stored function can return a value.
Example: storage functions
1. string concatenation function hello ()
Mysql> use test;
Mysql> delimiter //
Mysql> create function hello (t char (20) returns char (50)
Return concat ('Hello, ', t ,'! ');
//
Mysql> delimiter;
Mysql> select hello ('world ');
BitsCN.com