(6) MySQL Stored Procedure

In the database system, in order to ensure data integrity and consistency, and to improve its application performance, most databases often use stored procedures and storage functions (such as functions in C language ). MySQL5.0 and later support stored procedures and storage functions. 1. createproceduresp_name ([proc_parameter [,…])

In the database system, in order to ensure data integrity and consistency, and to improve its application performance, most databases often use stored procedures and storage functions (such as functions in C language ). MySQL5.0 and later support stored procedures and storage functions. 1. create procedure sp_name ([proc_parameter [,…])

In the database system, in order to ensure data integrity and consistency, and to improve its application performance, most databases often use stored procedures and storage functions (similar to functions in C language ). MySQL5.0 and later support stored procedures and storage functions.

1. Create a stored procedure

Create procedure sp_name ([proc_parameter [,…])

[Characteristic…] Routine_body

Sp_name indicates the name of the stored procedure

Proc_parameterList of stored procedure parameters

Characteristic representation of features in Stored Procedures

Routine_body indicates the SQL code content of the Parameter

Note:Proc_parameterIt consists of the input and output types, parameter names, and parameter types. The format is [IN | OUT | INOUT] param_name type.

Example: delimiter //

Create procedure proc_name (in parameter integer)

Begin

Declare variable varchar (20 );

If parameter = 1 then

Set variable = 'mysql ';

Else

Set variable = 'php ';

End if;

Insert into tb (name) values (variable );

End;

//

Delimeter // --- define // here as the end mark

(2) create a storage function

Create function sp_name ([func_parameter [,…])

Returns type

[Characteristic…] Routine_body

For example:

Delimiter //

Create function getName (std_id int) -- defines the name of the stored function as getName.

Returns varchar (50) -- return value type is varchar

Begin

Return (selectname from tb_student where id = std_id );

End

//

(3) define variables

Local variables:

Local variables are declared using declare, followed by the variable name and variable type: declare num int;

You can also specify the default value declare num int default 100;

For example:

Delimiter //

Create procedure test1 ()

Begin

Declare x varchar (10) default 'chen ';

Begin

Declare x varchar (10) default 'Qing ';

Select x; ------ qing will be output here

End;

Select x; ------ chen is output here

End

Call the above Code:

Call test1 ()//

Global variables:

In mysql, global variables can be used without being declared. global variables are valid throughout the session. The global variables start. For example:

Delimiter //

Create procedure test2 ()

Begin

Set @ id = 1;

Begin

Set @ id = 2;

Select @ id; ---------- output 2

End;

Select @ id; ---------- output 2

End;

//

Call the above Code:

Call test2 ()//