MYSQL stored procedures and functions

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 ');