Shulou(Shulou.com)06/01 Report--

Benefits of stored procedures

A stored procedure is a set of precompiled sql statements that are used to perform a particular function. In this way, the process of sql parsing, compilation and optimization can be saved, and the execution efficiency is improved. at the same time, only the name of a stored procedure is passed instead of a lot of sql statements, which reduces the network transmission. It also indirectly improves the efficiency of execution.

The difference between a stored procedure and a custom function A stored procedure is a set of sql statements that are generally executed independently in order to achieve an independent function. Custom functions appear more as part of other sql statements. A custom function can have only one return value, while a stored procedure can have multiple return values. The function of stored procedure is complex, and the function is more targeted. Stored procedure definition

A stored procedure is a precompiled collection of sql statements and control statements, stored in a name and processed as a unit.

Create the syntax create [definer = {user | current_user}] procedure proc_name ([parameter [,....]]) some body code.... Parameter: [in | out | inout] Parameter name type in passed in parameter out returned parameter inout changed and returned parameter call syntax

Call proc_name (parameter)

If there are no parameters, parentheses can be omitted

The example creates a stored procedure create procedure ver () select version () with no parameters

Call

Call ver (); or call ver

Of course, it doesn't make sense to put a function into a stored procedure here. It's just for example.

Create a stored procedure for the in parameter

We often query users whose id is a certain value, so we write it as a stored procedure.

Delimiter / / create procedure findUserById (in userId int unsigned) beginselect * from user where id = userId;end / / delimiter

Note: the parameter name should not be the same as the field name in the data table, otherwise there will be unexpected consequences.

Call

Call findUserById (1)

Create a stored procedure with an in and an out parameter

Delete a user and return the number of remaining users

Delimiter / / create procedure delUserByIdAndReturnNums (in userId int unsigned,out nums int unsigned) begindelete from user where id=userId;select count (id) from user into nums;end / / delimiter

Call:

Call delUserByIdAndReturnNums (1 nums nums); after the call, we can look at the value of @ value. After the br/ > call, we can look at the value of @ nums.

Create a stored procedure with multiple out parameters for in

Delete a user and return the total number of remaining users and the name of the deleted user

Delimiter / / create procedure delUser (in userId int,out nums int,out username varchar (20)) beginselect name from user where id = userId into username;delete from user where id = userId;select count (id) from user into nums;end / / delimiter

Call

Call delUser (3 mentions nums reign username)

You can view the values of the two variables after the call.

Select @ nums

Select @ username

Create a stored procedure with an inout parameter

Define a stored procedure and pass in an integer to get its square value.

Delimiter / / create procedure square (inout num int) begin set num=num*num;end / / delimiter

Call:

Because its argument is of type inout, we need to pass in an assigned variable so that it can be returned after modification within the stored procedure.

Set @ num=3;call square (@ num); select @ num

The results are as follows:

Mysql > set @ num=3

Query OK, 0 rows affected (0.00 sec)

Mysql > call square (@ num)

Query OK, 0 rows affected (0.02 sec)

Mysql > select @ num

+-+

| | @ num |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.